Power Query PDF import: fixing repeated headers, page totals and wrapped rows

Excel's Get Data from PDF gets the data out, but it brings the junk along: the header row on every page, page totals in the middle of the data, and notes that wrap onto a second line. Here's how to fix each one.

Updated October 18, 2026 · 9-minute read

Why PDF imports come out messy

A PDF doesn't contain a table. It contains pieces of text placed at positions on a page. Power Query looks at those positions and guesses where the rows and columns are. On a report printed by business software, it also finds everything else the report prints: the title and column headings repeated at the top of every page, "Page 3 of 9" at the bottom, group subtotals, and long notes that wrap onto a second line.

The fixes below are all done in the Power Query Editor (Data > Get Data > From File > From PDF, pick your file, then Transform Data). The examples use an open orders report with the columns Order #, Customer, Notes and Amount. Swap in your own column names.

Fix 1: pick the right item in the Navigator

The Navigator shows two kinds of items. Table items are tables Power Query detected. Page items are everything on one page. Start with the Table items. By default, Power Query combines similar tables on consecutive pages into one, so a 9-page report often shows up as a single Table item with all the rows.

If your table comes through split into one table per page instead, pull every page in one step with a blank query (Home > New Source > Other Sources > Blank Query, then Advanced Editor) and keep only the tables:

let
    Source = Pdf.Tables(File.Contents("C:\Reports\open_orders.pdf")),
    TablesOnly = Table.SelectRows(Source, each [Kind] = "Table"),
    Combined = Table.Combine(TablesOnly[Data])
in
    Combined

For very large PDFs, Microsoft suggests reading a range of pages at a time with the StartPage and EndPage options, for example Pdf.Tables(File.Contents(path), [StartPage = 1, EndPage = 20]).

Fix 2: remove the header row repeated on every page

1. Use Home > Use First Row as Headers so the first copy of the headings becomes the column names.
2. Every other page's heading row is now a data row whose Order # column literally says "Order #". Filter it out: open the Order # filter and untick "Order #", or add this step:

NoHeaders = Table.SelectRows(Promoted, each [#"Order #"] <> "Order #")

Fix 3: remove page totals, page numbers and report titles

These rows always contain the same words, so filter on those words. Usually the ID column is empty and another column says "Subtotal", "Page 2 of 9" or "Printed by". Don't simply delete every row with an empty ID: wrapped lines have an empty ID too, and you need them for Fix 4.

NoJunk = Table.SelectRows(NoHeaders, each
    not Text.StartsWith(Text.From([Customer] ?? ""), "Subtotal")
    and not Text.Contains(Text.From([Notes] ?? ""), "Printed by")
    and not Text.StartsWith(Text.From([Customer] ?? ""), "Page "))

Look at your own report's junk rows first and match on the exact text they contain. Filtering on a word that can also appear in real data (a customer called "Page Street Deli") removes real rows.

Fix 4: join notes that wrapped onto a second row

When a note or customer name is too long, the report prints it on two lines, and Power Query reads the second line as its own row with an empty Order #. To put it back with its order:

1. Make sure the empty Order # cells are truly empty (null): Transform > Replace Values, replacing an empty value with null if needed.
2. Select the Order # column and use Transform > Fill > Down. The continuation row now carries its order number.
3. Use Transform > Group By on Order #, and combine the text columns back into one line each:

Filled = Table.FillDown(NoJunk, {"Order #"}),
Grouped = Table.Group(Filled, {"Order #"}, {
    {"Customer", each Text.Combine(List.RemoveNulls([Customer]), " "), type text},
    {"Notes", each Text.Combine(List.RemoveNulls([Notes]), " "), type text},
    {"Amount", each List.Max([Amount])}
})

Then set the column types (Amount as a number, dates as dates). Microsoft's own documentation points to fill down and group by for multi-line rows the connector doesn't identify correctly.

Fix 5: make it work again next week

A query remembers the exact file path. Either save each new PDF over the old one (same name, same folder) and use Data > Refresh All, or change the file with Data > Get Data > Data Source Settings > Change Source.

The weak point is the guess in Fix 1. If next week's report has a longer customer name, an extra page or a column that moved, Power Query may detect the columns differently, and steps that refer to column names break. Check the totals after every refresh.

When Power Query is more work than it's worth

If the report is regular and you're comfortable maintaining the query, Power Query is a solid answer. If you're fighting it every week, or the person who built the query has moved on, a tool built for that one report can be simpler.

Wheelbarrow reads the report the way the steps above do, but you describe the result in plain words instead of building steps: "only orders on hold, a tab for each region, red after 5 days". It skips repeated headers, page numbers and subtotals, joins wrapped lines back to their row, and you see the finished workbook from your own PDF before you pay. The tool you keep runs on your PC, even offline, and rebuilds the report from next week's file in seconds.

Sources

• PDF connector (Power Query), Microsoft Learn: Table and Page items, MultiPageTables (on by default), StartPage and EndPage, and fill down or group by for multi-line rows.

More from Wheelbarrow