Automated-PDF-Tables-Extraction-with-Excel-PowerQuery

Content

1. Overview
2. Extraction steps
3. Transformation Example
4. Where's the Automation?
5. Conclusion

Overview

Excel Power Query offers amazing options that eliminate the need for repetitive tasks, which can be overwhelming when dealing with extensive data transformations. In this example, I will create a folder connected to Excel Power Query. This folder will extract tables containing employee data, transform the data, and automatically apply the same transformation steps each time a new pdf is added to the folder.

Extraction steps

1. Create a folder that contains the pdf files from which the tables to be exctracted.

2. From Data tab in excel, choose Get data > From file > From folder

3. Choose the created folder containing the pdf, the connection window of PowerQuery will launch showing all the files contained in the folder. Choose Combine & Transform.

   

4. From the Combine Files window, choose a Sample Files and below click on Parameter1 and then OK.

   

5. This will launch PowerQuery editor to begin transforming the tables, filter the Kind column to choose only the Tables not Pages.

   

6. Click the Expand button at the top of the Data column, this will expand the existing tables and show them in the PowerQuery edetor, confirm the expand process.

   

7. Remove the first 4 columns that contain the table information after expanding, and use the first row as headers.

8. IMPORTANT | Filter over one column of the table columns. You will find that it still contains the table name. Why? This happens because we expanded multiple tables that existed in the file. To easily get rid of them, uncheck the name from the filter button.

   

9. These steps are more than enough to close & load the tables into Excel. When loaded, this shows all the rows of the first pdf are loaded to the Excel file as shown below.

   

Transformation Example

Suppose that your manager assigned you a task to be done to the sent pdf files. He wants to extract only the JOB DESCRIPTION and get rid of the DEPARTMENT from the DESIGNATION column. (All the details are in this file here). Alright, I got you:

1. Open the PowerQuery editor again and navigate to the DESIGNATION column. Right-click on it, choose Split Column > By Delimiter. In our case, the delimiter is (. Type it in the dialog box and click OK.

   

2. This will result in two columns. Rename the first one to JOB DESCRIPTION and remove the second one.

   

3. With that, we’ve completed the task. And here's my favorite part: Close & Load.

Where's the Automation?

Well, that's a good question. PowerQuery keeps all the transformation steps so that it can apply them to additional files in the future. Here's a real-world scenario:

1. Your manager sends you a new version of the previously sent file. It has the same structure and needs to be transformed the same way and added to the existing data.

2. Simply, place the file in the chosen folder from the first step.

3. In Excel, refresh the query, and you'll see the new data added to the query.

   

Conclusion

Finally, with the 161 rows of the original files existing in the excel file, we accomplished two main tasks:

1. Extracted data from the pdf file.
2. Linked a folder to automate the same steps for future files.

This ensures we avoid repetitive tasks, especially when dealing with a large number of files and pages.