Transform PDF to Excel Data with Power Query Cleanup

To transform PDF data in Excel into clean columns, load it into Power Query and apply a short list of steps: filter out repeated header rows, split stuck columns, turn bracketed amounts into negatives, then set data types. The steps are saved, so the same cleanup reruns on the next file.

What needs transforming after a PDF lands in Excel?

Five kinds of mess show up after a PDF reaches Excel: header rows repeated on every page, columns that hold two values, amounts in brackets or with a trailing minus, numbers and dates stored as text, and blank or subtotal rows between the real data. Power Query can fix all five with saved steps.

A PDF has no table structure of its own, so whatever reads it has to infer rows and columns from the position of text on the page. The result is a table that looks right at a glance and misbehaves when you sort, filter or total it. Before you transform anything, scan the first screen and the page breaks and note which of the five problems you have.

The usual defects, with signs to look for

• Repeated headers. The words "Date", "Description" and "Amount" appear again partway down the sheet, once for each page of the PDF.
• Stuck columns. A date and a description share one cell, such as 03/14 CARD PURCHASE, because the gap between them was too narrow to split.
• Bracketed negatives. A withdrawal appears as (45.00) instead of -45.00. Accountants read brackets as negatives, but Excel reads them as text.
• Text types. Numbers align left and dates refuse to sort by month. Fixing numbers stored as text covers the cell-level repairs when you do not use Power Query.
• Junk rows. Page footers, "Continued on next page" lines, opening balance rows and blank lines sit between transactions.

Why transform instead of editing by hand

Hand edits last for one file. When next month's PDF arrives, you do the same fixes again, and each repeat is a chance to miss a row. Power Query records each action as a named step in a list. Run the query on a new file and every step reapplies in order.

That repeatability is the reason to use it. A cleanup you do once on a small file is quicker by hand. A cleanup you do every month on a many-page file belongs in a query.

Loading the data

Open the converted workbook, select the table or range, and choose Data, then From Table/Range. The Power Query editor opens with your data and a list of Applied Steps on the right. If you start from the PDF itself rather than from a converted sheet, importing a PDF into Excel with Get Data from PDF walks through the first clicks. The cleanup steps below work the same either way.

Removing header and footer rows with Power Query filters

Repeated header and footer rows come out of a PDF import because every page carries its own header and footer. Remove them by filtering the column that identifies a real transaction, such as the date column, so that only rows with a valid date remain. One filter step handles every page at once.

Filtering out the repeated header

The simplest filter removes rows whose first column equals the header text. Click the filter arrow on the Date column, untick "Date" in the list and confirm. Power Query adds a step named Filtered Rows, and every repeat disappears, whichever page it came from.

This works when the header text is exact. If a header sometimes reads "Date" and sometimes "Posting Date", untick each variant, or use Text Filters, then Does Not Contain.

Keeping only rows that look like transactions

A stronger approach flips the logic: keep rows that qualify instead of removing rows that fail. After you set the Date column to the Date type, errors appear in every row that is not a date. Choose Home, then Remove Rows, then Remove Errors. Header repeats, footers and blank lines all fail the date test and vanish together.

Take care with this method. Remove Errors discards any row that fails the type change, including a real transaction with an unusual date format. Count the rows before and after, and compare the count to the number of lines you can see on the PDF.

Footers and balance rows

Footers such as "Page 2 of 5" fall out with the date test. Opening and closing balance rows may hold a date, so they survive it. Filter them by the Description column: untick "Opening balance" and "Closing balance", or keep them deliberately if you plan to check totals against them.

Splitting a combined column during the PDF transform

A combined column appears when two values were too close on the page for the converter to separate them. The common case is a date and description in one cell. Power Query splits it with Split Column, either by a delimiter such as the first space, or by a fixed number of characters from the left.

Split by position or by delimiter

Choose by what the data guarantees:

• By delimiter suits 03/14 CARD PURCHASE when the date is always followed by a space. Select the column, choose Split Column, then By Delimiter, pick Space, and choose "Left-most delimiter" so that only the first space splits.
• By number of characters suits fixed-width cells, where the date is always five characters. Choose Split Column, then By Number of Characters, enter 5, and choose "Once, as far left as possible".

Delimiter splits are safer when widths vary, such as 3/4 against 03/14. Position splits are safer when the first word of the description could contain spaces of its own.

Dates without a year

Many statements print the date as month and day only. After the split, you have 03/14 and no year. Without a year, the type change cannot know which year a row belongs to, so do not let it guess. Add a custom column that appends the statement year, for example [Date] & "/2026", then set the type. Handle a statement that crosses New Year by checking the month: a January date on a December statement belongs to the following year.

Rename and retype

After a split, the new columns are called Date.1 and Date.2 or similar. Rename them at once, then set each type. A rename step costs nothing and prevents confusion later.

Fixing negative amounts in parentheses after transform

Bracketed amounts are text in Excel, and a minus sign is what a number needs. Power Query fixes them with Replace Values: replace the opening bracket with a minus sign, replace the closing bracket with nothing, then change the column type to Decimal Number. (45.00) becomes -45.00.

The sequence matters

Run the steps in this order:

1. Replace ( with -.
2. Replace ) with nothing.
3. Remove thousands separators only if your locale needs it.
4. Change type to Decimal Number or Fixed Decimal Number.

If you change the type first, every bracketed cell becomes an error. Replacing text before typing is the rule for every amount fix.

Trailing minus signs and credit markers

Some banks print 45.00- or 45.00 DR. Use the Transform tab's Extract commands, or a custom column, to move the sign to the front. Where a "CR" or "DR" marker carries meaning, split it into its own column first, then use a conditional column to make debits negative.

A cell that still refuses to convert usually holds an invisible character. The formula fixes in VALUE and NUMBERVALUE for stubborn text amounts show how to identify and remove it, and Power Query's Transform, then Clean command and a Replace Values on the character do the same job.

A worked example: the full step list

The table below shows a few rows of an example converted statement before the transform, and the steps that clean it. The values are made up for illustration.

The steps, in order:

1. Promoted Headers. Row 1 becomes the header row.
2. Filtered Rows. Remove rows where Date equals "Date" (row 4).
3. Split Column by Delimiter. Split Date at the left-most space into Date and Type.
4. Removed Blank or Footer Rows. Filter out empty Date cells (row 6).
5. Replaced Value (twice). Turn ( into - and ) into nothing in Amount.
6. Changed Type. Date to Date (after appending the statement year, as in the section on dates without a year), Amount to Decimal Number.

After the steps, the table holds three clean rows:

Each step is visible in Applied Steps, and you can click any one to see the table as it stood at that point. That is the main debugging tool: when a result looks wrong, click back through the list until the problem disappears, and the step just after it is the culprit.

Should the PDF transform happen in Power Query or formulas?

Power Query is easier to maintain when the cleanup repeats, because the steps rerun on new files and show their order. Formulas suit one-off fixes, small sheets, and cases where you want to see each result in a cell. Choose by how often the file returns and how many people must understand your work.

Where Power Query wins

• Repeatable. The same query handles next month's file with a refresh.
• Ordered and visible. Applied Steps reads like a recipe, so a colleague can follow it.
• Safe on the source. The original data is not altered, because the query reads it and writes a separate output.
• Quick on long files. Filtering thousands of rows is one step, not a formula filled down a column.

Where formulas win

• One-off jobs. A single small statement does not repay setting up a query.
• Visible logic in the grid. Formulas such as SUBSTITUTE and VALUE show their effect cell by cell.
• Older Excel. Power Query ships with current Excel versions and as an add-in in some older ones, and a formula works anywhere.
• Light edits. Fixing a handful of cells does not need a data model.

A rule of thumb

If you will do the cleanup twice, build the query. If you will do it once and the sheet is small, use formulas. Mixing the two is fine: let Power Query shape the table, and add formulas on the sheet that reads from it.

A related choice is how the cleaned data spreads across sheets. If your converter put each page on its own tab, merging page sheets into one with VSTACK comes first, then the cleanup runs on a single table.

Transform steps that break when the PDF layout shifts

A query breaks on a new file when a step names something the new file does not have: a column name, a column position, a header text or a row count. The error appears in the first step that cannot find its target, usually with a message that a column of the table was not found.

The next month's PDF may look identical to you, yet the conversion can differ by a column. A long description wraps, a column drifts, or a page adds an extra footer. The causes are predictable, and so are the defences.

The common causes

• Renamed or missing columns. A step that refers to Column3 fails when a new file has only two columns.
• Hard-coded positions. Split by position breaks when the date format changes from 3/4 to 03/04.
• Fixed row counts. "Remove top 3 rows" removes data when the PDF has a shorter header.
• Changed types. A step typed to Date errors on a text value that appeared this month.
• Extra pages. A page that did not exist last time shifts the table index, so the query loads the wrong table.

Defences that hold

1. Prefer filters on content over steps on position. "Keep rows where Date is a date" survives a longer header; "remove first three rows" does not.
2. Name columns after promoting headers, and refer to them by name.
3. Keep an error-handling step, such as keeping a count of removed errors, so a surprise is visible instead of silent.
4. After every refresh, check the row count and one total against the PDF.
5. Keep a copy of the working query before you edit it for a new layout.

When it still fails

Open the query, find the first step with an error, and click it. Read the message, fix that step, and check the steps after it. A different layout is sometimes better handled by a second query than by a stretched first one. For refreshing the same query when the next month's file arrives, see refreshing a PDF import when next month's file arrives.

If your files are bank statements, a converter that returns dates, descriptions, amounts and balances in separate columns removes most of these steps. FastBankConvert is getting ready to open for that job, and you can ask to be told when it does through Get started. For a statement-focused view of getting the table ready, converting a PDF to an Excel spreadsheet you can sort and sum gives a readiness checklist, and keeping the look of a PDF when it lands in Excel deals with the formatting side.