How to Extract Structured Data from Multiple PDFs with Power Query in Excel
Question details
The user wants to import multiple PDF files from a folder, extract specific chapter or section data, reshape it into a structured table, and apply conditional formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating and reshaping data extracted from multiple PDF reports into a single table for analysis, while applying specific visual formatting rules.
- Observed behavior
- The user successfully imported data using Get Data from Folder but needs a structured method to pivot the data and apply conditional formatting rules (e.g., green for 1, red cross for blanks).
Ensure all the target PDF files are stored in a single folder and have a consistent internal structure or table layout to ensure accurate data extraction.
Extract and Reshape PDF Data Using Power Query
Use the 'From Folder' feature in Power Query to consolidate multiple PDFs, extract the required tables, and reshape the data using the Pivot Column feature.
By pointing Power Query to a folder, you can automatically process every PDF inside it. You can then expand the contents, filter for specific sections, and pivot the data into a usable format.
In Excel, go to the Data tab, select Get Data > From File > From Folder, and browse to the folder containing your PDF files.
Click 'Transform Data'. In the Power Query Editor, filter the file list if necessary, then click the two-arrow expand icon on the 'Content' column to combine the files.
Select the specific tables or pages (e.g., Chapter 5 fields). Keep essential columns like file name, section number, and target values, removing unnecessary columns.
Replace any empty or null values if needed. Select the column containing your categories, go to the Transform tab, and click 'Pivot Column' to reshape the data, counting or summing values by file.
Once the data is structured correctly, click 'Close & Load' on the Home tab to output the structured table to your Excel worksheet.
Apply Conditional Formatting to Extracted Data
Format the loaded Excel table to visually distinguish specific values, such as marking '1' in green and blanks with a red cross.
Handle PDF Data Extraction with WPS Office
While Power Query is a specific feature of Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative with powerful built-in PDF to Excel conversion tools and robust data analysis features.
- 1. Download WPS Office: Visit the official WPS website and click 'Download WPS Office Free'.
- 2. Install the Software: Run the downloaded installation file and follow the simple on-screen instructions.
- 3. Convert PDFs and Analyze Data: Open WPS Spreadsheet, use the PDF to Excel tool to extract your data, and apply conditional formatting directly within the application.
Frequently Asked Questions
Why does Power Query return null values when extracting data from PDFs?
This often happens if the PDF structure is inconsistent or if tables span multiple pages. You may need to manually replace empty values using the 'Replace Values' feature in Power Query, or adjust the page selection in the Navigator window to ensure the correct tables are targeted.
How can I conditionally format blank cells with an icon set?
In Excel, you can use Icon Sets under Conditional Formatting. Edit the rule to 'Show Icon Only', set the red cross for values less than or equal to 0, and ensure blank cells are interpreted correctly by the rule. Sometimes using a dedicated formula rule like =ISBLANK() provides better control.
Can I extract data from specific pages of a PDF using Power Query?
Yes. When you expand the PDF content in Power Query, the Navigator will display a list of detected pages and tables. You can filter the 'Kind' column to include only 'Page' or 'Table', allowing you to select and extract data from the specific sections you need.
