Spreadsheets

Power Query: remove top rows and promote headers on a statement

Remove top rows, promote the first row to headers and remove bottom rows in the right order in Power Query, so a same-layout statement loads every month.

Editorial illustration for Power Query: remove top rows and promote headers on a statement

In Power Query, remove the top junk rows first, then use the first row as headers, then remove the bottom rows, and only then set column types. That order matters, because promoting headers too early freezes junk as column names and typing columns too early produces errors you then have to chase.

Which steps go in which order?

  1. Load the file with Data, Get Data, From File, From Text/CSV (or From Workbook), then choose Transform Data.
  2. If a Changed Type step was added automatically, delete it. It locks in types that are wrong for now.
  3. Home, Remove Rows, Remove Top Rows, and enter the number of account-detail lines above the header.
  4. Transform, Use First Row as Headers.
  5. Home, Remove Rows, Remove Bottom Rows, and enter the number of footer lines.
  6. Set data types last: dates as Date, amounts as Decimal Number, narration as Text.

What do these steps look like in M?

Open Home, Advanced Editor to see them as code. A typical result reads like this, with invented counts of 7 top rows and 3 bottom rows:

StepM functionPurpose
Skip the topTable.Skip(Source, 7)Drops account-details lines
PromoteTable.PromoteHeaders(Skipped, [PromoteAllScalars=true])Row 1 becomes the heading
Trim the bottomTable.RemoveLastN(Promoted, 3)Drops footer lines
TypeTable.TransformColumnTypes(Trimmed, {{"Date", type date}})Turns text dates into dates

Each name in the second column is a step name from the Applied Steps list on the right. If you rename a step, the next step must refer to the new name, so rename carefully or leave the defaults.

Why do you have to remove the top rows before promoting?

Promote takes whatever is in the first row as headings. If you promote first and then remove rows, the junk becomes your column names, and removing the rows afterwards takes the real header with them. Doing it in the order above gives a table whose first row is the real header, ready to promote.

Remove Rows also offers Keep Range of Rows, where you give a first row and a count, handy if the transactions always start at row 11 and you know how many follow. Remove Top Rows is usually simpler, because it does not break when the number of transactions changes from month to month.

What if the file loads with too few columns?

A CSV with a few short lines at the top can make Power Query guess the column count from those lines, cutting off wider rows below. Look at the Source step: the Csv.Document call includes a Columns argument. Set it to the real number of columns, say 7, and the full table appears. This is a detail to check in your own file, not a given.

How do you reuse the steps for every month?

Save the query once and keep each month's file at the same path, or better, in one folder that the query reads. Next month, replace the file or drop in the new one and choose Data, Refresh All. If the layout is unchanged, the same steps give a table in the same shape. If one of the steps shows an error, click it in Applied Steps to see which row or column caused it.

A closing checklist

  • Auto Changed Type step deleted before the row removals.
  • Top rows, promote, bottom rows, then types, in that order.
  • The Columns number in the CSV source matches the file.
  • Header word used in any conditional skip copied from your own file.
  • Row count of the loaded table checked against the file.

If a sheet-based routine feels heavy, note that SpendixAI imports statements as PDF, CSV or Excel without any of these steps, and flags possible duplicate imports.

SPENDIXAI JOURNAL

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

Explore your reports