Bank statements, government reports, and supplier invoices all arrive as PDF files. Re-typing the numbers is slow. Copy-paste introduces errors. Power Query's PDF import feature extracts tables from these files directly into Excel — no manual retyping, no third-party software. It detects tables within PDF pages automatically. Available in Excel for Microsoft 365 on Windows, it loads detected tables as clean, structured data ready for transformation, analysis, and connection to Power Pivot. This guide covers the full import process, how to handle multi-page and multi-file PDFs, six real-world scenarios, and every common issue.
Digital vs Scanned PDF — Know Before You Import
Power Query only extracts text from digital PDFs — files created by software where text can be selected with a cursor. Scanned PDFs are images of physical documents. In a scanned PDF, text is not actually text — it is a photograph of text, and the Navigator pane appears empty when you try to import it. Checking which type you have before attempting import saves time. Open the PDF in any viewer and try to highlight a word with your cursor. If individual words select cleanly, it is a digital PDF. If the entire page selects as a single image block, it is a scanned PDF.
The PDF Import Workflow — Step by Step
The import process follows a consistent five-step pattern regardless of the PDF source. Steps 3 and 4 apply specifically when the data spans multiple pages or requires cleaning before use.
Example 1: Bank Statement — Extract Transactions from a PDF
Most UK and European bank PDF statements are digital and extract reliably. After importing, the typical cleaning steps are: promote Row 1 to headers (the statement may not put the header row first), change the Date column to Date type, change the Amount column to Currency type, and filter out blank rows caused by PDF page breaks. The cleaned query refreshes automatically when you save a new statement to the same file path and click Refresh, making monthly reconciliation a one-click operation.
Example 2: Multi-Page Report — Combining Tables Across Pages
When a single data table spans multiple PDF pages, Power Query splits it into one query per page in the Navigator. To reassemble the full table, Ctrl+click all relevant page table entries in the Navigator, load them to Power Query editor, and append them using Home > Append Queries. After appending, remove any duplicate header rows that appear mid-table — these are caused by the repeated column header row that some PDF generators include at the top of each page.
Example 3: Government Statistical Data — Clean Multi-Row Headers and Footnotes
Government statistical publications contain multiple formatted tables in a single PDF. ONS, HMRC, and similar bodies publish economic data as PDF reports with complex layouts — multi-row headers, footnotes, and merged cells. Power Query can extract any table from these files. The common cleaning challenges are multi-row headers at the top of the table, footnote rows at the bottom, and merged category cells in the left column that show the value only in the first row and leave subsequent rows blank.
Example 4: Batch Import — Combine Multiple PDF Files from a Folder
When you receive monthly PDF statements in a folder, combining them automatically with Power Query eliminates the need to import each file individually. Use Data > From Folder to read all PDFs in the folder. Then create a custom function from a single sample file's query and apply it to each file's binary content. This approach processes all 12 monthly statements in a single query that refreshes automatically when new files arrive in the folder — a fully automated PDF data pipeline requiring no manual intervention each month.
Example 5: Irregular PDF Layouts — Handle Inconsistent Column Structure
Not every PDF table has clean column alignment. Supplier invoices often have narrative description text in a wide column alongside narrow numeric columns. Power Query may split content into more columns than expected, or merge columns that should be separate. Use Table.SelectColumns to keep only the needed columns, and Text.AfterDelimiter or Text.Between to reshape mixed-content columns into the structure you need. Two or three custom column transformations are usually sufficient to clean even significantly irregular layouts.
Example 6: Dynamic File Path — Parameter-Driven PDF Source
Hard-coding a file path in the query source step breaks the query when you move the file or run it on a different machine. Power Query parameters solve this. Create a text parameter for the folder path and a second parameter for the filename. Reference both parameters in the source step. Subsequently, updating either parameter value redirects the query to the new location without editing any M code — simply change the parameter values in Manage Parameters and click Refresh.
Troubleshooting PDF Import
Both issues below are the most common problems users encounter. The first — an empty Navigator — is by far the most frequent and has a definitive diagnosis step that confirms the cause immediately.
The Navigator pane is empty after selecting the PDF
An empty Navigator almost always means the PDF is scanned rather than digital. Open the PDF in any viewer and try to select text with your cursor. If you cannot select individual words, it is a scanned image. Use Adobe Acrobat Pro's OCR feature (Tools > Scan & OCR > Recognise Text), or a free online tool like Smallpdf, to convert it to a searchable digital PDF. Import the converted file into Power Query. A genuinely empty PDF — with no tables on any page — also produces an empty Navigator, though this is much less common.
The extracted table has incorrect column splits or missing data
Column splitting issues occur when the PDF table has inconsistent column alignment — common with proportional fonts and hand-assembled layouts. Power Query detects column boundaries from character position patterns, which breaks when content wraps across visual columns. Specifically, use Table.SelectColumns to keep only the columns you need, discarding any spurious split columns. Then use Table.CombineColumns or a custom column formula to reshape content back into the correct structure.
The PDF import option is missing from the Get Data menu
PDF import is only available in Excel for Microsoft 365 on Windows — it is not available in Excel 2019, Excel 2021 standalone, or Excel for Mac. If the option is missing from Data > Get Data > From File, confirm that Excel is a Microsoft 365 subscription version, not a perpetual licence. Additionally, verify that the Excel installation is fully up to date — the PDF connector was added in a 2020 update and may be absent in installations that have not received recent updates.
Frequently Asked Questions
- How do I import a PDF table into Excel using Power Query?Go to Data > Get Data > From File > From PDF. Select your PDF file and click Open. The Navigator pane opens showing all detected tables labelled by page. Click a table to preview it. Click Transform Data to clean it in Power Query editor, or Load to send it directly to a worksheet. This feature requires Excel for Microsoft 365 on Windows. It is not available in Excel 2019 standalone, Excel 2021 standalone, or Excel for Mac.
- Why does the Navigator show no tables when I import my PDF?An empty Navigator almost always means the PDF is scanned rather than digital. Open the PDF and try selecting text with your cursor — if individual words highlight cleanly, it is a digital PDF. If the entire page selects as one block, it is a scanned image. Power Query can only extract text from digital PDFs. Convert scanned PDFs to digital format using Adobe Acrobat Pro's OCR feature or a free online converter like Smallpdf before attempting import. The converted file typically imports cleanly.
- Can Power Query combine tables from multiple pages of the same PDF?Yes. In the Navigator, use Ctrl+click to select tables from each page simultaneously. Click Transform Data to open all of them in Power Query editor. Use Home > Append Queries to combine them into a single continuous table. After appending, add a filter step to remove duplicate header rows — these appear when the PDF generator repeats column headers at the top of each page. Filter the first column where it does not equal the header text to remove them cleanly.
- Does the PDF import refresh automatically when the source file changes?Yes. After the query is created, clicking Data > Refresh All re-runs the import from the same file path. If you replace the source PDF with an updated version at the same path and same filename, clicking Refresh imports the new data automatically. For monthly files with changing filenames, use a Power Query parameter for the filename and update the parameter value each month before refreshing. The parameter approach means only one value needs to change to redirect the entire query to the new month's file.