DAO1704 Chap.3 Repeatable Data Preparation with Power Query
Repeatable Data Preparation with Power Query
Preparation becomes a recorded process
Power Query connects to source files and records transformations as Applied Steps. That makes a monthly workflow replayable, but it also repeats every assumption.
A reliable query begins with a source contract: intended folder, expected file type, header row, column names, units and data grain.
Grain controls cleaning and merging
Removing complete-row duplicates can be defensible while removing repeated identifiers may destroy legitimate event history. A merge can multiply rows when the reference key is not unique.
The chapter therefore declares what one row represents, compares row counts before and after destructive steps, counts unmatched keys and inspects one-to-many relationships before expanding a merged table.
Refresh needs a receipt
A successful refresh proves only that the recorded sequence ran.
The useful receipt includes source files accepted, conversion errors, row counts, duplicate keys, unmatched merges and a stable control total.
These checks separate a genuine change in incoming data from an accidental change in the process and make dashboard results traceable to the transformation that produced them.
Exceptions are evidence about the process
Conversion errors, null matches and unexpected categories should remain visible long enough to be explained.
Replacing them automatically with zero can turn a schema change into a false business result. Keep an exception query with source filename, key and row context, then repair the right layer: correct a source convention, update the reference table or retain an explicit unknown category. A reconciled refresh ends with a short record of what changed, what was rejected and why the control totals still agree.
What this chapter covers
- 01
Get and Transform source connections
- 02
Applied Steps as an audit trail
- 03
Data types and conversion errors
- 04
Duplicate records and declared grain
- 05
Merge keys, expansion and row multiplication
- 06
Refresh reconciliation and control totals
Worked example · free
Removing exact duplicates without collapsing event history
- 2Remove only the complete-row repeats.
- 1Calculate the remaining row count.
- 2Explain why timestamped identifier repeats may remain.
- 2Name the grain that controls the decision.
Key terms
- Power Query
- Excel's recorded process for connecting, transforming and loading data.
- Applied Step
- One transformation stored in the ordered query sequence.
- Data Grain
- The real-world event or entity represented by one row.
- Exact Duplicate
- A repeated row with the same values across every field relevant to its grain.
- Merge Key
- The field or fields used to match records between tables.
- Refresh
- A replay of the query steps against the current source data.
Repeatable Data Preparation with Power Query FAQ
Why is a completed refresh not enough evidence?
The sequence can finish while the source schema, units or key uniqueness have changed. Reconcile row counts, errors, unmatched keys and a control total so that successful execution is paired with evidence that the result still represents the intended table.
When should repeated identifiers be kept?
Keep them when the declared grain permits several events for one identifier, such as status changes over time. Remove them only after a deliberate decision to aggregate to one row per entity and after choosing which event or summary survives.
How can a merge increase the row count?
A source row matches several reference rows when the merge key is duplicated. Expanding the match then produces one output row per match. Inspect reference-key counts and confirm the intended relationship before accepting the increase.
Exam move
Build a query over two small monthly files, then add a third file with one deliberate schema change. Preserve the filename, assign types, remove an exact duplicate and merge a lookup table. Before refreshing, predict the row count and control total. After refreshing, locate the schema error through Applied Steps. Repeat with a duplicated lookup key so you can see row multiplication rather than merely read about it.
Keep a refresh receipt with accepted files, error count, unmatched keys and totals. On the next attempt, diagnose the failure using only that receipt before opening the query. This tests whether your controls carry enough information to make the process reviewable. Finally, ask another person to trace one dashboard number back through the prepared table to its source file.
Record the first point where the trail becomes ambiguous and improve that step name or retained field.
Working through Repeatable Data Preparation with Power Query in DAO1704? Sia is AskSia’s AI Statistics tutor — ask any DAO1704 Repeatable Data Preparation with Power Query question and get a clear, step-by-step explanation grounded in how DAO1704 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
