1.2Power Query: clean & transform data
The essentials in 30 seconds
- Power Query is the same engine as in Excel: it cleans in steps recorded and replayed on each refresh.
- The golden rule: you fix the query, never the data by hand — the recipe is replayed automatically.
- Unpivoting is THE transformation of analysis: it turns month columns into two columns Attribute/Value (long format).
Running mini-case — Zineb cleans her SME's cross-tab
Zineb receives a production cost file where the months are in columns (Jan, Feb, Mar…), with stray headers and amounts typed as text. She applies the section: in Transform data, she types each column (dates as date, amounts as decimal), removes the title rows, then unpivots the month columns to get two columns Attribute/Value — a long format analyzable by month. Each gesture is recorded in "Applied steps" and will be replayed on refresh. Her rule: she fixes the query, never the value by hand.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 1.2.
1. An engine of replayable steps
Power Query is the same engine as in Excel: the editor that cleans and transforms data in recorded and replayable steps. Typing columns, removing the useless, splitting, replacing, merging: each gesture is recorded in "Applied steps" and replayed on every refresh. The golden rule: you never fix the data by hand, you fix the query. Zineb always starts by typing each column.
2. Typing and cleaning
The first reflex in the editor is typing: each column must have the right type (text, whole/decimal number, date) — half of beginners' errors come from there. Then you remove the title rows and totals, filter the empties, rename badly named columns. Zineb thus cleans her cost file without ever touching the source.
3. Unpivoting
The most valuable transformation for analysis: unpivoting. It turns crossed columns (Jan, Feb, Mar…) into two Attribute/Value columns — the gesture that makes data analyzable ("long" format). Also worth knowing: Merge (join between two queries, like XLOOKUP) and Append (stack tables of the same structure). Zineb systematically unpivots her tables by month.
🔎 Concrete example — 🔎 Concrete example — Unpivoting
Zineb transforms a cross-tab into a long, analyzable format:
BEFORE (crossed): Product | Jan | Feb | Mar
Select the month columns → right-click → Unpivot
AFTER (long) : Product | Attribute (Month) | Value
→ analyzable by month in visuals and DAX.Reading: the months in columns become "Attribute/Value" rows — a format usable everywhere.
Lesson: type first, unpivot next: well-prepared data feeds all visuals.
Section vocabulary
- Power Query editor
- The preparation workshop (Transform data): transformations without code, recorded as steps.
Simple example: Zineb cleans her cost file there.
What it is not: entering values into a report cell.
Reflex: prepare data → Power Query.
- Applied steps
- The panel on the right: each transformation listed, editable, removable.
Simple example: Zineb clicks each step to replay the cleaning movie.
What it is not: a one-off correction not replayed.
Reflex: each step replayed on refresh.
- Data type
- Text, whole/decimal number or date: it conditions aggregation and calculations.
Simple example: Zineb types her amounts as decimal before anything.
What it is not: a column left as text (hence a total in "Count of").
Reflex: type each column = 1st reflex.
- Unpivot
- Transforming crossed columns into two Attribute/Value columns (long format).
Simple example: Zineb unpivots her months in columns.
What it is not: a cross-tab left as is (unanalyzable).
Reflex: long format = analyzable.
- Merge / Append
- Merge = join between two queries (like XLOOKUP); Append = stack tables of the same structure.
Simple example: Zineb merges sales and the product reference.
What it is not: mixing two incompatible structures.
Reflex: enrich → Merge; stack → Append.
In practice — In practice — The typical cleaning of an export
- On your sales query (Transform data): check the type of EACH column — dates as date, amounts as decimal number, like Zineb.
- Remove an unnecessary column, rename two badly named columns, filter any empty rows.
- Unpivot exercise: import a table with months in columns → select the month columns → right-click → Unpivot columns.
- Browse the Applied steps: click each one to replay the movie, then Close & Apply.
Step-by-step script — cleaning like Zineb (10 min)
1) Zineb opens Transform data on her sales export. 2) She selects each column and correct its type (icon at the left): date, decimal, text. 3) Remove the title rows, filter the empties, rename the badly named columns. 4) For a table with months in columns: select those columns → right-click → Unpivot columns. 5) Reread the Applied steps one by one, then Close & Apply.
If a step shows an error after a source change, it's often the "Changed type" step referencing the old column name: fix it.
Copy-paste template — my cleaning recipe
1. TYPE each column: date / decimal / text
2. REMOVE: title rows, totals, unnecessary columns
3. FILTER the empties; RENAME the columns
4. UNPIVOT the crossed columns → Attribute / Value
5. Reread the APPLIED STEPS → Close & Apply
Rule: fix the query, never the data by hand.Costs per month
Zineb unpivots a cost table with months in columns to analyze it by month.
Dirty headers
Zineb removes the stray title rows of a raw export before typing.
Join
Zineb merges her sales with the product reference to enrich each row.
Stacking
Zineb appends three exports of the same structure into a single query with Append.
✓ Best practices
- Type each column (the editor's first reflex).
- Fix the query, never the data by hand.
- Unpivot the cross-tabs to make them analyzable.
✗ Common pitfalls
- Retouching the source file at each export.
- Leaving a column badly typed (aggregation errors later).
- Keeping the months in columns instead of unpivoting.
Key takeaways
- Each transformation is a step replayed on refresh.
- You fix the query, never the data by hand.
- Type all the columns: first reflex in the editor.
- Unpivoting cross-tabs makes data analyzable.
48h challenge — apply tomorrow
Take a raw export like Zineb and build a pipeline: type all the columns, remove the stray rows, then unpivot a table with months in columns. Prove that everything replays by clicking your Applied steps.
- I clean a source in replayable steps in Power Query.
- I correctly type each column.
- I unpivot a cross-tab into a long, analyzable format.
Frequently asked questions
A step shows an error after changing the source file, why?
An expected column changed name or type: click the step in error (often the "Changed type" step referencing the old name) and fix it. Stable column names at the source avoid these breaks.
Power Query or DAX to create a column: how to choose?
Structural transformation of data (cleaning, splitting, typing, unpivoting) = Power Query; business logic depending on the visuals' context (aggregates, ratios) = DAX measure. When in doubt: Power Query to prepare, DAX to calculate on the fly.
What is the most important Power Query transformation for analysis?