7.3 Messy Excel Exports: Title Rows, Headers, Footers and Transpose

By Ravindra Bagale Last updated: 25 September 2026

Mitrano, store managers often send Excel files like this. There is a report title, a blank line and the export date above the real header, and a total row at the bottom.

Before

Column1	Column2	Column3
Blinkit Kothrud – Daily Sales	null	null
Exported on 14-03-2025 10:05 IST	null	null
null	null	null
Order ID	Product	Amount
BLK-10001	Gokul Cow Milk 500 ml	32
BLK-10002	Kolhapuri Misal Masala	85
Total	null	117

After

Order ID	Product	Amount
BLK-10001	Gokul Cow Milk 500 ml	32
BLK-10002	Kolhapuri Misal Masala	85

Steps in Power BI

Home › Remove Rows › Remove Top Rows › Number of rows = 3 › OK.
Home › Use First Row as Headers (also on Transform › Use First Row as Headers). Power Query adds a Promoted Headers step and usually a Changed Type step.
Home › Remove Rows › Remove Bottom Rows › 1 (removes the "Total" line).
To be safe when the number of footer rows can change, use a filter instead: click the Order ID filter arrow › Text Filters › Begins With… › "BLK-".
Set the data types (Order ID and Product = Text, Amount = Whole Number or Decimal).

Skipped   = Table.Skip(Source, 3),
Promoted  = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
NoFooter  = Table.RemoveLastN(Promoted, 1),
OnlyOrders = Table.SelectRows(Promoted, each Text.StartsWith([Order ID], "BLK-"))

Use Headers as First Row (the dropdown under Use First Row as Headers) does the reverse. It is handy before a Transpose (ओळी आणि स्तंभ उलटणे).

Transpose (Transform › Transpose) turns rows into columns. It is useful when a report is laid out sideways, for example metrics in rows and cities in columns:

Before

Metric	Pune	Nashik	Nagpur
Orders	1,240	610	705
Stores	5	2	2

After

City	Orders	Stores
Pune	1,240	5
Nashik	610	2
Nagpur	705	2

Steps: Transform › Use First Row as Headers › Use Headers as First Row → Transform › Transpose → Use First Row as Headers → rename Metric to City. (The numbers are practice values.)

Aata pudhe jaauya duplicates kade – pan aadhi ha Before/After table swatah Power Query madhe karun bagha.

Ravindra Bagale's Tip

Mitrano, khup students change the data type before promoting headers, so the header text causes errors, and they use Remove Top Rows with a fixed count that breaks when next month's file has an extra title row. Promote headers first, then set types. Where possible, filter by a pattern (udaharan mhanje rows that begin with "BLK-") instead of removing a fixed number of rows. Practice kara, mag ekdum sope vatel.

Practice task

Take a Nagpur (Dharampeth) store export with 2 title rows and a "Grand Total" row. Clean it so that only order rows remain, then give every column the correct type.