Power Query I: Shape and Clean

Why this matters

Last lesson's file was a gift: clean columns, correct types, nothing in your way. It was also a lie of omission, because almost no file arrives like that. This week's file is the truth. A colleague in marketing sends you customer_export.csv — "the customer list, should be ready to go" — and it looks fine in a spreadsheet. It is not fine. Hiding in its 514 rows are 14 exact duplicates, 49 names with invisible trailing spaces, 22 customers whose segment is the text "N/A", dates written three different ways in the same column, and a State column with 30 different values for what should be 26 states — because Texas also appears as "Texas," Michigan as "Michigan," and so on.

None of that will announce itself. Every one of those problems will quietly corrupt a report built on top of it: the duplicated customers inflate counts, "Texas" and "TX" become two bars in every chart, and the text "N/A" means the Segment column can never be trusted by a filter. The skill this lesson teaches — finding and fixing all of it in a recorded, repeatable way — is, by most working analysts' accounting, where the majority of real BI time goes.

The workshop: what Power Query actually is

Choose Get data → Text/CSV as before, but this time click Transform Data instead of Load. A new window opens: the Power Query Editor. Three things to orient on:

• The data preview fills the center — rows and columns, like Table view but editable-looking.
• The Applied Steps list sits on the right. It already contains steps — Source, Promoted Headers, Changed Type — because Power BI recorded what it did to open your file.
• The formula bar above the data shows each step in Power Query's own language, M. You will read a little M this semester; you will rarely need to write it.

Now the mental model, and it is the whole lesson: Power Query is a recorded recipe, not an edit. Every action you take becomes a step in that list. The steps replay from the top every time the data refreshes. And critically — the source file is never modified. The CSV your colleague sent stays exactly as messy as the day it arrived; your recipe re-cleans it on every refresh. When marketing sends next month's export with the same problems, you point the query at the new file and the entire cleanup replays in seconds. That replayability is the difference between cleaning data and having cleaned it once.

See the problems before you fix them

On the Editor's View tab, turn on Column quality, Column distribution, and Column profile. Small bars and percentages appear under every column header — Valid, Error, Empty — plus a distribution of values.

Click the State column and read its profile: 30 distinct values. A US customer file drawn from 26 states should have 26. Scan the value list and the culprits are right there — "Texas" (8 rows), "California" (10), "New York" (7), "Michigan" (4) sitting alongside their two-letter versions. Twenty-nine rows wearing long-form state names, splitting every future chart's bars in two.

One more profiling fact to file away: by default the quality bars evaluate only the first 1,000 rows of a big table — the pane says so in its corner. Our 514-row file is fully covered; a 165,000-row file would not be. "Column profile says no errors" means "no errors in what it looked at."

The cleanup, step by recorded step

1 · Remove exact duplicates — 514 becomes 500

Select all columns (Ctrl+A in the preview), then Home → Remove Rows → Remove Duplicates. The row count drops from 514 to 500 — the export contained 14 rows that were exact copies, the classic signature of someone clicking "export" twice. Watch the Applied Steps list: Removed Duplicates just appeared. That is the recipe recording itself.

2 · Trim the invisible spaces — 49 names

Forty-nine names in this file end with a space you cannot see. Invisible — until "Avery Thorne" and "Avery Thorne " become two different customers in every count, join, and filter you ever run. Select the Name column, then Transform → Format → Trim. One step, all 49 fixed, and future rows with the same disease are fixed by the same recorded step forever.

3 · Turn the text "N/A" into a real null — 22 segments

Twenty-two customers have the literal text N/A in the Segment column. To a human that reads as "missing." To Power BI it is a perfectly valid third segment called "N/A" that will appear in every slicer and chart. The fix: select Segment, right-click → Replace Values, replace N/A with nothing at all — then, in the same column, use Replace Values → Advanced → Replace empty with null if needed so the cell becomes a true null. The column profile now honestly reports 22 empty values instead of pretending the column is complete. An honest hole beats a fake value every time — and Module 2's models treat nulls correctly precisely because they are not text.

4 · Standardize the states — 29 rows, 4 replacements

Replace Values again, four times on the State column: Texas→TX, California→CA, New York→NY, Michigan→MI. The profile's distinct count falls from 30 to 26. Four recorded steps that heal 29 rows now and every future export automatically.

Pause on that screenshot. Every step has a gear — click it and the step's dialog reopens for editing. Steps can be deleted or reordered. Click any earlier step and the preview shows the data as it stood at that moment, a time machine for your cleanup. Nothing in this list is a fait accompli; it is a program you are free to revise, and Lesson 3 will lean on that freedom.

5 · Set the types on purpose — and find what breaks

Finally, the Joined column. Its profile says type text, and the values explain why: this one column writes dates three different ways — 178 rows like 03/14/2024, 171 rows like 2024-03-14, and 165 rows like 14-Mar-24. Someone merged three systems' exports, once, long ago, and nobody noticed.

Click the type icon on the column header and choose Date. Power Query converts what it can parse under your locale's rules — and any row it cannot parse becomes an Error value. Here is the professional move: assume a type change broke something until proven otherwise. The column's quality bar shows the error share; Home → Keep Rows → Keep Errors shows you exactly the offending rows (on a copy of the query, so you can look without harming the recipe); and Using Locale… (under the type menu) lets you tell Power Query which culture's date-writing rules a stubborn format follows. Whether your conversion produces zero errors or a few hundred depends on your machine's locale settings — which is itself the lesson: date parsing is locale arithmetic, not magic, and the only acceptable end state is an error count you have personally seen equal zero.

6 · Close & Apply

Home → Close & Apply runs the finished recipe and loads the clean result into the model. The Editor closes; the report side sees 500 honest rows. From here on, this is the rhythm of every dataset you touch: Transform Data → profile → clean → types → Close & Apply.

Common mistakes

• Fixing before profiling. You fix what you expected and miss what you didn't. Profile pane first, every file, sixty seconds.
• Remove Duplicates with a partial selection. The quietest data-loss button in the product. All columns selected = exact duplicates only.
• Replacing "N/A" with the text "null". Typing the word null into Replace Values creates the text "null" — a fake value with a costume change. The cell must become a true empty null, and the column profile (22 empty) is how you confirm it did.
• Trusting a type change that "seemed to work." The only acceptable end state is an error count you have personally seen equal zero. Quality bar, then Keep Errors if it is not.
• Forgetting Close & Apply. The Editor's work stays in the Editor until applied. If the report side still shows 514 rows, your recipe has not run.

Summary