Power BI
Power Query Basics for Cleaning Messy Excel Data
A beginner-friendly guide to using Power Query to clean messy Excel files before reporting.
Many Power BI reports begin with Excel files containing merged headers, blank rows, inconsistent dates, comments inside tables, and manually typed categories. Power Query helps turn that into repeatable preparation.
The value of Power Query is repeatability. Instead of cleaning the same file by hand every week, you record a sequence of steps that can run again when the file updates.
The practical context
Use Power Query for repeatable extraction, cleaning, shaping, and standardisation.
Bad source structure can still create fragile queries.
Report authors own transformations and documentation.
A cleaner input table that refreshes with less manual work.
How to approach it
A useful approach is deliberately simple. Start with the business question, make the data and ownership visible, then add technical detail only where it improves reliability or action.
- Keep a copy of the original file and avoid editing the raw source manually.
- Remove blank rows, merged-header problems, and notes above the table.
- Promote headers and set data types deliberately.
- Split or merge columns only when it improves the model.
- Create a clean table output with stable column names.
- Name queries clearly so the next analyst understands the flow.
Common mistakes
Cleaning the Excel file manually before every refresh.
Leaving data types as Any.
Renaming columns casually when downstream steps depend on them.
Combining cleaning and business calculations in one messy query.
A simple example
If a weekly sales file has three title rows before the real table, Power Query can remove the top rows, promote headers, set dates, trim text, and load the result every week.
That repeatability is the difference between a personal spreadsheet and a report process.
Checks before you move on
The audience can explain what the output means without the analyst in the room.
The data source, calculation logic, refresh, and access model have owners.
There is a clear path for questions, exceptions, and corrections.
Success is measured by better decisions or less manual effort, not page views alone.
Key takeaway
Power Query is often the first step from manual reporting to professional reporting.
Reader Comments
Add a comment with your name and email. Your email is used only for basic validation and is not shown publicly.