Power Query: clean once, refresh forever
Learn Power Query through repeatable data-cleaning steps: profile messy columns, transform safely, combine files, and verify every refresh.
Learn it here, one small step at a time.
4 guided lessons · About 2h 30m including practice · No subscription
- Spot common data quality issues
- Build clear, repeatable transformation steps
- Validate that refreshes preserve the intended data
Recommended first: Excel: from first cell to confident analysis. These foundations are suggestions, not a requirement to begin.
Inspect before you clean
Power Query records data-preparation steps so you can repeat them next time. First decide what one row means, then inspect headers, types, blank cells, and repeated values. A blank may mean “not supplied,” “not applicable,” or a true zero; those meanings are not interchangeable.
Keep an untouched source. Profile the columns before removing records, and write down each cleanup rule so someone else can review it.
| Value | Possible meaning | Safe first action |
|---|---|---|
| Blank region | Not supplied or not applicable | Count and investigate blanks |
| 0 amount | A recorded zero | Keep it unless the source says otherwise |
| Repeated row | Duplicate or repeated transaction | Check its unique ID before removing |
Load a small CSV and record its row count, column types, number of blanks, and which column should uniquely identify a record.
Reveal a suggested answer
Zero is a real numeric value; a blank might mean unknown or not applicable, which has a different meaning.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Transform text and numbers safely
Use named steps such as Trimmed Text, Changed Type, and Replaced Value. Trimming removes spaces around text; it does not decide whether two spellings refer to the same place. Set dates and decimal types deliberately, especially when files use different regional formats.
The Applied Steps list is a recipe. Rename confusing steps, remove only steps you understand, and preview the result after each important change.
| Step | Example | Verify |
|---|---|---|
| Trim text | " North " → "North" | Compare distinct region names |
| Set type | "12.50" → decimal 12.5 | Check decimal separator and total |
| Remove duplicates | Only by a unique transaction ID | Compare removed row count |
Trim the Region column. Convert Amount to a decimal number. Before removing any duplicates, check whether each transaction has a unique ID.
Reveal a suggested answer
A documented unique key and the meaning of a row, not merely two rows that look similar.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Combine files with the same shape
Append stacks rows from similar files; merge matches columns from related tables. To append monthly sales files, confirm they have the same headers and compatible types. To merge products into sales, choose the shared ProductID and verify that the lookup key is unique.
Preview row counts before and after a combine. A merge can multiply rows when the lookup has duplicate keys, and a changed header can leave a new column full of blanks.
| Need | Operation | Example |
|---|---|---|
| Stack more transactions | Append | January sales + February sales |
| Bring in product names | Merge | Sales matched to Products by ID |
Append two monthly files with the same columns. Record each file’s row count and check that the appended result equals their sum.
Reveal a suggested answer
Use Merge to match columns from related tables; use Append to stack rows with the same shape.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Refresh and reconcile
A refresh reruns the steps against the current source; a “success” message does not prove the output is correct. Compare row counts, key uniqueness, nulls, and important totals with the source. Keep a small known test case so you can notice if a step changes unexpectedly.
If a step fails after a source change, inspect the first failing step and the changed column names or types. Fix the cause rather than deleting every later step.
| Measure | Before refresh | After refresh |
|---|---|---|
| Source rows | Record count | Should match expected additions |
| Unique IDs | Duplicate count | Investigate new duplicates |
| Total amount | Known total | Reconcile or explain change |
Add one known row to the source and refresh. Confirm that exactly one expected row appears and its amount is included in the total.
Reveal a suggested answer
Refresh checks whether steps ran; reconciliation checks whether they produced the right business result.
Optional further reading
Core lesson and practice are on this page. For more detail, visit the official free reference ↗.
Your final knowledge check.
Score 80% or more to pass. Sign in to save your best result.