Power Query Problems

Extracting Tables from Multiple PDFs Using Power Query in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to combine tabular data from multiple PDF files using Power Query, specifically addressing the issue where tables shift to different pages across documents due to varying text lengths.

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from a folder of PDF files where the target tables do not reside on a fixed page number.
Observed behavior: Relying on fixed page numbers for extraction fails because the table content shifts across pages, requiring extraction based on table structure instead.

Before you start

Ensure all your source PDF files are saved in a single folder and verify that the tables you want to extract share identical column headers or structural labels.

Solution 1Recommended

Combine PDFs Using Consistent Table Headers

Use Power Query to get data from a folder and filter the extracted tables based on their column names or structure, which safely handles shifting pages.

When dealing with PDFs where varying text length pushes tables to different pages, relying on fixed identifiers like 'Page 1' or 'Page 2' will cause data extraction errors. Power Query allows you to filter the combined data by the actual table structure and column headers, ensuring accurate extraction regardless of the page number.

Get Data from Folder

Open Excel, go to the 'Data' tab, click 'Get Data' > 'From File' > 'From Folder', and browse to the folder containing your PDF files.

Transform Data

In the preview window, click 'Transform Data' to open the Power Query Editor instead of combining the files immediately.

Invoke PDF Custom Function

Filter the 'Extension' column to only include '.pdf' files. Go to 'Add Column' > 'Custom Column', name it 'PDFData', and enter the formula '= Pdf.Tables([Content])' to parse the PDFs.

Expand and Filter by Kind

Expand the new 'PDFData' column to reveal the underlying tables. Locate the 'Kind' column and filter it to only show 'Table' (excluding 'Page'), so you are targeting structured data rather than raw page text.

Filter by Table Structure

Instead of filtering by the 'Id' (page number) column, expand the 'Data' column to see the headers. Apply a text filter to one of the key columns to only keep rows containing your specific, consistent table headers or structural markers.

Clean and Load

Remove unnecessary columns such as the original binary content, promote your headers if necessary, and click 'Close & Load' on the Home tab to load the combined dataset into your workbook.

Preprocessing Requirement: If the PDFs lack consistent headers or structural identifiers, reliable automatic extraction in Power Query may require preprocessing the files with custom parsing logic or using dedicated OCR tools.

Free Microsoft Office alternative

Convert PDFs to Spreadsheets Easily with WPS Office

If configuring complex Power Query extraction logic is too time-consuming, consider using WPS Office. It provides a lightweight, highly compatible alternative to Microsoft Office with built-in PDF tools that can quickly extract tables from PDF files into fully editable spreadsheets without any complicated formulas.

1. Open WPS Office: Launch the WPS Office desktop application and select the 'PDF' tab.
2. Use PDF to Excel: Click on 'PDF to Excel' and add the PDF files you want to convert.
3. Extract and Save: Click 'Convert' to automatically extract the tables into a fully formatted WPS Spreadsheet (.xlsx) file.

Fully compatible with Microsoft Excel (.xlsx) file formatsBuilt-in PDF to Excel converter for quick, reliable table extractionLightweight application that runs smoothly on all devicesFamiliar ribbon interface requires no learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query extract the wrong tables from my PDFs?

If your PDFs have varying text lengths, tables may shift to different pages. If Power Query is set to extract data based on a fixed page number (e.g., extracting from Page 2), it will pull incorrect data when the target table shifts to Page 3. You should filter by table headers instead of page numbers.

How do I filter PDF tables by column names in Power Query?

After expanding the PDF contents in the Power Query Editor using the Pdf.Tables function, you can use the 'Filter' dropdown on your expanded data columns to select specific text or labels that only appear in your desired table's headers, automatically excluding irrelevant pages.

Can Power Query handle PDFs with completely different layouts?

Power Query works best when extracting data from multiple files that share a consistent table structure. If the layouts, identifiers, and column labels differ drastically across the PDFs, automatic extraction will likely fail, requiring custom parsing logic or manual preprocessing.

What M function is used to extract PDF contents in Power Query?

The `Pdf.Tables([Content])` function is used in Power Query to read and extract tables and structural elements from a PDF binary file.

Excel & Data Problems