7. Data Cleaning A–Z in Power Query
7.3 Messy Excel Exports: Title Rows, Headers, Footers and Transpose
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
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.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students headers promote करण्याच्या आधी data type बदलतात, त्यामुळे header text मुळे errors येतात, आणि ते fixed count सोबत Remove Top Rows वापरतात, जे पुढच्या महिन्याच्या file मध्ये एक जास्तीची title row आली की तुटतं. आधी headers promote करा, मग types set करा. शक्य असेल तिथे ठराविक rows काढण्याऐवजी pattern ने filter करा (उदाहरणार्थ "BLK-" ने सुरू होणाऱ्या rows). Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students headers promote करने से पहले data type बदल देते हैं, तो header text से errors आते हैं, और वे fixed count के साथ Remove Top Rows लगाते हैं जो अगले महीने की file में एक extra title row आते ही टूट जाता है. पहले headers promote करो, फिर types set करो. जहाँ हो सके, तय गिनती की rows हटाने की बजाय pattern से filter करो (जैसे "BLK-" से शुरू होने वाली rows). Practice करो, फिर बहुत आसान लगेगा.
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.