📎 This article includes 2 downloadable practice files ↓
In this article
Exports from accounting and ERP systems are made for printing, not analysis: title lines at the top, a group label only on the first row of each group, subtotal rows mixed into the data, the odd duplicate. Power Query turns that into a clean table.
1. Remove top rows and promote headers
Home › Remove Rows › Remove Top Rows › 3 (the title, date and blank line). Then Home › Use First Row as Headers.
2. Fill down the group labels
Region appears only on the first row of each group. Make sure the blanks are real nulls (Replace Values: empty → null if they show as blank text), then right-click Region › Fill › Down. Every row now carries its region.
3. Filter out subtotal rows
Click the drop-down on OrderID › untick Subtotal, or Text Filters › Does Not Equal “Subtotal”. Filtering on a value is safer than removing rows by position, which breaks when next month’s file has a different number of rows.
4. Remove duplicates
Select the column(s) that define a duplicate (here OrderID) › Home › Remove Rows › Remove Duplicates. Select all columns to remove only identical rows.
5. Set types, then deal with errors
Set Qty and Amount to Whole Number. The bad row with “two” and “n/a” becomes Error. Options:
- Home › Remove Rows › Remove Errors: drop them.
- Transform › Replace Errors: put a value such as 0 or null instead.
- Keep a separate query that keeps only errors (Keep Rows › Keep Errors) to review what’s being thrown away.
Check
The practice file should end with 32 clean rows (8 per region). Compare your row count and total with the expected-results workbook.
Where beginners go wrong
| Mistake | Why it hurts |
|---|---|
| Remove Bottom Rows to drop a grand total | Breaks when row counts change; filter by value |
| Fill Down on blank text, not nulls | Nothing fills; replace “” with null first |
| Removing errors silently | Real data loss goes unnoticed; review errors once |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 📗pq-03-erp-export.xlsxA report-style export: title rows, region labels only on the first row, subtotal rows, a duplicate and a bad row.⬇ XLSX · 6 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 6 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.