Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

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

By Last updated:

Start with a problem

The Pune export has a report title and a generated-time line above the real header, and a Total row at the bottom. Power BI reads all of them as data.

Explain simply

Shape the table first: Remove Top Rows, Use First Row as Headers, then remove the footer. A filter (Order ID begins with BLK-) is safer than Remove Bottom Rows when the footer size changes.

Show the data

Before:

Column1 Column2 Column3 Column4
Blinkit Pune daily export (fictional practice data)
Generated 08-04-2026 07:00 IST
Order ID Order Date Store Amount
BLK-401 06-04-2026 "Kothrud " ₹1,250
BLK-402 06-04-2026 baner ₹980
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-404 Baner ₹320
Total ₹3,450

About this data: This table uses columns: Column1, Column2, Column3, Column4. This table uses columns: Rows, Last row. This table uses columns: Order ID, Order Date, Store, Amount. This table uses columns: Column1, Column2, Column3. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_raw_pune_export.csv (source: src/practice/).

Demonstrate

  1. Home › Remove Rows › Remove Top Rows › 2 › OK.

    Your result should look like this:

    Row 1
    Order ID

  2. Home › Use First Row as Headers.

    Your result should look like this:

    Rows
    6

  3. Order ID filter arrow › Text Filters › Begins With… › BLK- › OK.

    Your result should look like this:

    Rows Last row
    5 BLK-404

  4. Rename the steps Removed title rows, Promoted headers, Kept order rows.

Show the result

Title rows and Total row removed from the Pune export, sample layout with fictional data

Your result should look like this (fictional sample):

Order ID Order Date Store Amount
BLK-401 06-04-2026 "Kothrud " ₹1,250
BLK-402 06-04-2026 baner ₹980
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-403 07-04-2026 KOTHRUD ₹450
BLK-404 Baner ₹320

Explain what happened

Remove Top Rows skips the title lines, Promote Headers uses the real header, and the BLK- filter drops the Total row however many footer lines there are.

Explore further

  • Transpose fixes sideways reports (metrics in rows) – see the reference below.

Detailed reference for this lesson:

Friends, 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

  1. Home › Remove Rows › Remove Top Rows › Number of rows = 3 › OK.
  2. 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.
  3. Home › Remove Rows › Remove Bottom Rows › 1 (removes the "Total" line).
  4. 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-".
  5. 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.)

Next we move on to duplicates – but first, do this Before/After table yourself in Power Query.

Ravindra Bagale's Tip

Friends, many 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 (for example rows that begin with "BLK-") instead of removing a fixed number of rows. Practise, and it will feel very easy.

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.

Fix common mistakes

Wrong result Reason Correction
Header row has Generated 08-04-2026… Removed only 1 top row Remove 2
Total row still counted (₹3,450 + ₹3,450) Footer not removed Filter Begins With BLK-
Next file has 3 title rows and breaks Fixed number of rows Filter on a pattern, or ask for a stable export

Practise independently

Practice task

Why might Remove Bottom Rows 1 fail next month?

Hints
  • Footer could grow.
Solution

If the export adds a second footer line (for example Generated by…), one row is not enough; the BLK- filter handles any number.

Think like a professional

Design queries for the file you will get next month, not only for today's file.

Ravi Sir's Cards for this lesson

Messy Excel exports: title rows, headers, footers. Exports made for printing have titles and totals. Loaded as is, they break columns and double totals.Revision card

मराठी cards