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
PromotedResult
| ClaimID | Employee | Amount |
|---|---|---|
| C-1 | Ana | 120 |
| C-2 | Ben | 45 |
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
NoTotalResult
| ClaimID | Employee | Amount |
|---|---|---|
| C-3 | Cleo | 60 |
| C-4 | Dev | 15 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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.
- beginner~7 minFree
Skip the title rows in a report export (consultancy)
The export starts with a report title and a blank line. Skip them, then promote the real headers.
- beginner~7 minPro
Skip the title rows in a report export (school)
The export starts with a report title and a blank line. Skip them, then promote the real headers.
- beginner~6 minFree
Remove the two footer lines from a stock export (hardware)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.
- beginner~6 minPro
Remove the two footer lines from a stock export (pharmacy)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.
- beginner~6 minPro
Remove the two footer lines from a stock export (cafe)
The export tool appends two summary lines at the bottom of every file. Remove them, then type the counts.