Spreadsheets

Standardise column names and order across different statement files

Build a column-mapping table and use Power Query to rename and reorder different statement headings into one standard layout before you append the files.

Editorial illustration for Standardise column names and order across different statement files

Make a small mapping table with two columns, Source and Standard, listing every heading you have seen across your banks and what each should be called. Use it in Power Query to rename columns, then select the standard columns in a fixed order, and the files append into one consistent table.

Why do statements need standardising?

Different banks print different headings for the same thing. One may say Txn Date, another Date, and a third Value Dt, with a column order that also differs. Power Query matches by heading, so unlike names produce extra half-empty columns. A mapping table sorts that out once and stays editable when a new bank appears.

What does the mapping table look like?

Create an Excel Table called ColMap. The names below are invented examples; copy the real headings from your files.

SourceStandard
Txn DateDate
Value DtDate
ParticularsNarration
DescriptionNarration
WithdrawalsDebit
DepositsCredit
Closing BalBalance

Two sources can map to the same standard name, which is fine as long as a single file never contains both. If a file has both Txn Date and Value Dt, map only one of them, or rename one differently.

How do you apply the map in Power Query?

  1. Load ColMap as a query: Data, From Table/Range, name it ColMap, Close & Load To, Only Create Connection.
  2. Create a blank query (Get Data, From Other Sources, Blank Query), open Advanced Editor and paste the function below. Name it fnStandardise.
  3. In each statement's query, add a step that calls fnStandardise on the previous step, then append the queries.

The function body: (t as table) as table => let map = Table.ToRows(Table.SelectColumns(ColMap, {"Source","Standard"})), renamed = Table.RenameColumns(t, map, MissingField.Ignore), ordered = Table.SelectColumns(renamed, {"Date","Narration","Debit","Credit","Balance"}, MissingField.UseNull) in ordered

Table.ToRows turns each ColMap row into a pair of old and new names, which is exactly the shape RenameColumns wants. MissingField.Ignore means a heading that is not in this file is skipped, so one list serves all banks. SelectColumns then puts the five standard columns in your chosen order and creates any missing column filled with nulls.

The function first selects just the Source and Standard columns of ColMap. That means you can add a Notes column to the table for yourself, such as which bank first used a heading, without upsetting the rename step, which would fail on a list of three names instead of two.

What if one bank only has a single Amount column?

Handle that inside the same function or just before it. Where a file has Amount and a Dr or Cr marker, add two custom columns, for example Debit as if [Type] = "Dr" then [Amount] else null, and the same for Credit. Then the standard five-column shape holds. Be careful with signs: some exports show money out as negative numbers in the Amount column, and some do not.

Set the data types after renaming and ordering, not before, because the type step refers to column names that only exist once the rename has run. Dates go to Date, Debit, Credit and Balance to Decimal Number, and Narration stays Text.

How do you test it?

  • Load each statement alone and confirm the heading row reads Date, Narration, Debit, Credit, Balance in that order.
  • Append them and check the count of columns is five, plus any extras such as Account.
  • Compare total rows with the sum of the individual files.

When a new bank appears

Open the new file, write down its headings, add a row to ColMap for each one that is not there yet, and refresh. No query edits are needed, which is the whole point. Keep ColMap sorted so that you can see duplicates quickly.

If you would rather not keep this yourself, SpendixAI lets you pick the account before importing a PDF, CSV or Excel statement; do not expect every layout to parse perfectly, so review the rows before saving.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports