Learn to Automate Everything with Power Query in Excel (Download Files): Excel learning resource
Learn to Automate Everything with Power Query in Excel (Download Files)
A real-world-problems tour of Power Query rather than a single-feature deep dive.
Learn to Automate Everything with Power Query in Excel (Download Files)
Leila Gharani
Open on YouTube (opens in a new tab)What this video actually covers
A real-world-problems tour of Power Query rather than a single-feature deep dive. Gharani starts on a genuinely messy source range — blank rows, a duplicated header row partway down the sheet — and cleans it step by step inside the Power Query Editor, calling out the Applied Steps panel as the thing that makes every transformation undoable and re-runnable. From there she covers the Column Quality indicator, a quick way to spot errors or blanks in a column before building anything on top of it, plus fixing text case and replacing values, and doing basic calculations inside the query instead of the worksheet. The second half moves from cleaning one file to cleaning many: combining every CSV in a folder into one table, then unpivoting a wide cross-tab report into the long, tidy shape a PivotTable actually needs, finishing with a PivotTable built from the refreshed query.
Where to jump in
What to practice while watching
- Load the messy-data practice file into Power Query and remove the blank rows and duplicate header row the way the video does, using Applied Steps to see and undo each change.
- Turn on the Column Quality indicator and fix whichever column shows errors or blank values first.
- Replace inconsistent text values and fix inconsistent case, for example Proper Case a name column, inside the query rather than with a worksheet formula.
- Refresh the query after manually adding a new row to the source data and confirm the cleaned output updates automatically.
Exercise file
Practice the steps above on a real workbook instead of a blank sheet — no sign-up required.
Download Messy data cleanup practice (.xlsx)Recommended learning path
Start by watching the lesson once without pausing, then reopen Excel and rebuild the example with your own small dataset. Save one clean practice workbook before moving to the next topic.
Source details
- Channel: Leila Gharani
- Format: video
- Level: Intermediate
- Topic: Power Query
Go deeper with this skill
Create a repeatable cleanup and refresh process instead of doing the same manual edits every month. For this article, the goal is to practice: A real-world-problems tour of Power Query rather than a single-feature deep dive.
Practice workbook setup
Use a raw export with extra columns, inconsistent text, blanks, or repeated monthly files.
Practice workflow
- Import the data into Power Query and rename every meaningful step.
- Remove columns only after confirming they are not needed downstream.
- Set data types deliberately for dates, numbers, IDs, and text.
- Load the cleaned result to a table and refresh it after changing the source.
Quality checks
- Applied steps are named clearly enough to review later.
- Data types are correct before loading the result.
- Refresh works without manual cleanup in the worksheet.
Common mistakes
- Editing the loaded table instead of the query steps.
- Removing columns before checking whether reports depend on them.
- Ignoring errors caused by new values or changed source headers.
Next actions
- Create a small refresh checklist and keep it beside the report.
- Try combining two files or appending a new month to test repeatability.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
