Power QueryExercises

Remove report banners before promoting headers

Clean the top and bottom of a report-style export so the header row is promoted correctly.

~8 min read · beginner · Module 2: Importing and inspecting data

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Import CSV files: delimiters, encodings and headers

Exports made for printing often start with a report title, a run date or empty lines. If you promote headers straight away, the title becomes a column name and the real headers end up as a data row. The fix is a step before promotion that removes the banner.

Fixed number of banner rows

Input file ClaimsReport

Expense claims report
Run date: 2026-10-31
ClaimID,Employee,Amount
C-1,Ana,120
C-2,Ben,45
let
    Source = Csv.Document(ClaimsReport),
    Skipped = Table.Skip(Source, 2),
    Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true])
in
    Promoted

Result

ClaimIDEmployeeAmount
C-1Ana120
C-2Ben45

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

In the editor this is Home → Remove Rows → Remove Top Rows. The order matters: remove, then promote, then set types.

When the banner length varies

A fixed count works only while the export always has the same number of banner lines. If a blank line or an extra note appears, a count of 2 removes the wrong rows. Table.Skip also accepts a condition: it skips rows while the condition is true, so you can skip until the header row appears.

Input file ClaimsReportV2

Expense claims report
Run date: 2026-11-30

ClaimID,Employee,Amount
C-3,Cleo,60
C-4,Dev,15
Total,,75
let
    Source = Csv.Document(ClaimsReportV2),
    Skipped = Table.Skip(Source, each [Column1] <> "ClaimID"),
    Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
    NoTotal = Table.SelectRows(Promoted, each [ClaimID] <> "Total")
in
    NoTotal

Result

ClaimIDEmployeeAmount
C-3Cleo60
C-4Dev15

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The same query works for last month's file (two banner lines) and this month's (three, including a blank one).

Footers: filter by content, not by count

The bottom of a report has the same problem in reverse. Remove Bottom Rows by count eats real data when the footer shrinks, and leaves a total row behind when it grows. Filtering out the row whose first column says Total works for any length. Re-total in the report instead of importing the export's total.

Where to find this in Excel

Excel for Windows
Home > Remove Rows > Remove Top Rows, then Home > Use First Row as Headers
What you see
You enter the number of banner rows to drop, then promote the real header row.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Filter a table by row position (Microsoft Learn); Promote or demote column headers (Microsoft Learn).

Common mistakes

  • Promoting headers before removing the banner, so the report title becomes a column name.
  • Removing a fixed number of top or bottom rows when the export's banner or footer length varies.
  • Importing the export's own total row and then totalling again in the report, doubling the figure.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Remove Rows offers Remove Top Rows, Remove Bottom Rows, Remove Alternate Rows, Remove Blank Rows and Remove Errors. Skipping by a condition needs a small edit in the formula bar.
  • Report-style Excel sheets often have the same banners; the same Table.Skip pattern applies to a sheet's Data table.
  • The simulator supports both forms of Table.Skip (count and condition).

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Skip the title rows in a report export (consultancy)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.