Lecture video · L02 Power Query I

[Slide 1] Last week's file was clean, and I told you at the time that this was a lie of omission. Today we meet the truth. A colleague in marketing has sent over the customer list — quote, should be ready to go, unquote — and our whole session is about what ready to go actually means when a file has been touched by real people, real exports, and real mergers of three different systems. By the end of today you will be able to find every problem in a file before it hurts you, and fix all of it in a way that stays fixed.

[Slide 2] Here is the file, and here is what is actually inside it. Five hundred fourteen rows that look perfectly fine when you open them. Hiding in there: fourteen rows that are exact copies of other rows. Forty-nine names that end with a space you cannot see. Twenty-two customers whose market segment is the literal text N slash A. One date column that writes its dates three different ways, because somebody merged three systems years ago and nobody noticed. And a State column with thirty different values for what should be twenty-six states, because Texas also appears fully spelled out, and so do California, New York, and Michigan. Every one of these will quietly corrupt a report. None of them will announce itself. Finding and fixing this, repeatably, is where most real analyst time goes — and it is today's entire agenda.

[Slide 3] Last week we clicked Load, because the file deserved it. This week we click the other button: Transform Data. A new window opens — the Power Query Editor — and it has three regions worth naming. The data preview fills the center. Down the right side runs a list called Applied Steps, and notice it is not empty: Source, Promoted Headers, Changed Type. Power BI recorded what it did just to open your file. And above the data sits a formula bar showing each step written in Power Query's own language, called M. You will read a little M this semester; you will almost never need to write it.

[Slide 4] Now the mental model, and it is the whole lesson in four sentences. Power Query is a recorded recipe. Every action you take becomes a step in that list, and the steps replay from the top every time the data refreshes. The source file is never modified — the messy CSV on disk stays exactly as messy as the day it arrived, and your recipe re-cleans it on every refresh. So when marketing sends next month's export with all the same diseases, nothing needs redoing: the recipe replays and the new file comes out clean. Hold onto the difference between cleaning data and having cleaned it once. A spreadsheet fix dies with the next export. A recipe is immortal.

[Slide 5] Before we fix a single thing, we make the file confess. On the Editor's View tab live three checkboxes: column quality, column distribution, and column profile. Turn all three on and every column grows a little dashboard — what share is valid, what share is error, what share is empty, and how many distinct values it holds. Click the State column: thirty distinct values. A customer file drawn from twenty-six states should have twenty-six. The file just told us where it is broken, sixty seconds in, before we touched anything. One caveat to file away: by default these bars evaluate only the first thousand rows of a table. Our five hundred fourteen are fully covered. A hundred-sixty-five-thousand-row sales table would not be — so a clean bill of health from the profiler means no errors in what it looked at, which is a smaller claim than no errors.

[Slide 6] Quick discussion before the cleanup begins. Why does the order matter — profile first, fix second? What actually goes wrong for the analyst who starts clicking fixes right away? Talk it over. The answer we are heading toward: you fix what you expected to find, and you ship what you did not. Every one of us walked into this file expecting duplicates, maybe some missing values. Did you expect three date formats in one column? The profile pane found it anyway. Expectation-driven cleaning has a blind spot precisely where the surprises live, and surprises are what data does for a living.

[Slide 7] First fix. Select every column — control A — then Home, Remove Rows, Remove Duplicates. The row count drops from five hundred fourteen to exactly five hundred. Fourteen rows were complete photocopies of other rows — the classic signature of somebody clicking export twice. And look at the right side of the screen: a step called Removed Duplicates just appeared in the list. You did not write that down; the recipe recorded itself. That is what every fix looks like from now on.

[Slide 8] Now the part that separates a cleanup from a catastrophe, using the same button. Remove Duplicates keys on whichever columns are selected. With all of them selected, only exact full-row copies can die — safe. Select just the Name column, though, and it keeps one row per name. Sounds harmless — until you remember that real customer files contain different people who share a name. On this very file, Name-only deduplication deletes seventy-seven real customers. Seventy-seven — fifteen percent of the file, gone. And here is the theme of this whole course showing up again: no warning appears. No error. The step runs in serene silence either way. The Hot practice this week hands you a colleague's recipe that made exactly this mistake, and your job is to catch it.

[Slide 9] Fix two. Forty-nine names in this file end with a space you cannot see. Invisible — right up until Avery Thorne with a space and Avery Thorne without one become two different customers in every count, every filter, and every join you ever run. The fix is one step: select the Name column, Transform, Format, Trim. All forty-nine healed, and — because it is a recorded step — every future file's trailing spaces are already healed too.

[Slide 10] Fix three. Twenty-two customers have the text N slash A in the Segment column. To a human that reads as missing. To the software it is a perfectly legitimate third segment called N slash A, which will proudly appear in every slicer and chart. We replace it with a true null — a genuinely empty cell — and the column profile flips to reporting twenty-two empty. That is the honest state: a hole the tools handle correctly, instead of a fake value they trust. One trap on the way: if you type the word null into the replacement box, you create the text n-u-l-l — the same disease in a new costume. The profile is your referee: twenty-two empty means you succeeded; zero empty means the costume changed and the lie survived.

[Slide 11] Fix four, the one the profiler found for us. Four Replace Values steps on the State column: Texas becomes TX, California becomes CA, New York becomes NY, Michigan becomes MI. Twenty-nine rows change, and the distinct count falls from thirty to twenty-six — the number a US state column is supposed to have. Four small recorded steps, and no chart in this report will ever again split one state into two bars. And because they are steps, not edits, the twenty-nine afflicted rows in every future export are already cured.

[Slide 12] Pause and look at what we have built, because the step list is more than a history. Click any step and the preview shows the data exactly as it stood at that moment — a time machine for your cleanup. Every step has a gear icon that reopens its settings for editing. Steps drag to reorder and delete with a click. Nothing here is a fait accompli; it is a small program you are free to revise. And notice one more thing it gives you for free: when an auditor asks precisely how this table was derived from the vendor's raw file, the answer is not your memory — it is this list, complete, ordered, and inspectable.

[Slide 13] The last disease is the subtle one. The Joined column holds dates as text in three formats: one hundred seventy-eight rows like month slash day slash year, one hundred seventy-one in the dashed international style, and one hundred sixty-five like fourteen dash M-a-r dash twenty-four. We change the column type to Date, and here is the honest truth: what happens next depends on your machine. Date parsing follows your locale — your computer's cultural settings for how dates are written — so some of you will see zero errors and some will see hundreds, from the same file. Which is the lesson itself. A type change is a claim, not a formality. Two tools settle it: Keep Errors, on a copy of the query, shows you exactly the rows that refused to parse. And Using Locale, under the type menu, tells Power Query whose date-writing rules a stubborn format follows. The only acceptable end state — and this is the standard your assignment is graded against — is an error count you have personally watched equal zero.

[Slide 14] A quick one, because it happens in this room every time this lesson runs. Two of you sit side by side, run the same type change on the same file — one sees zero percent errors, the other three percent. Nobody made a mistake. What differs? Talk it over. The answer is the machine, not the analyst: date parsing follows each computer's locale settings, so the same text parses differently on differently-configured machines. Which is exactly why the standard is not trust the defaults — it is check your own error count, on your own machine, until you have seen it hit zero.

[Slide 15] The recipe is finished; now it has to actually run. Home, Close and Apply. The Editor closes, the recipe executes top to bottom, and the model on the report side receives five hundred honest rows. Remember that Editor work stays in the Editor until this moment — if your report still shows five hundred fourteen rows, your recipe has not run. And with that, you have the rhythm you will use on every dataset for the rest of this course and, frankly, the rest of your career: Transform Data, profile, clean, set types, Close and Apply.

[Slide 16] One piece of judgment before we close, because cleanup can become a compulsion. The Phone column also holds three formats — parentheses, dashes, dots. And we are going to leave it alone. Why? Nothing in this report analyzes phone numbers; a human dialing one does not care about the punctuation. Cleaning has a cost, and it should be spent where analysis actually rides on the column — the State column had to be fixed because charts group by it. Here is what the recipe model buys you: deferral is safe. If the call-center asks for dial-ready formatting next month, that fix is one added step, not a do-over. The Medium practice has you make exactly that fix — when someone actually asks for it.

[Slide 17] Here is the week in one slide. You learned to make a file confess before touching it — profile first, fix second, sixty seconds of reading that finds the surprises expectation misses. You cured five diseases with five recorded steps: duplicates removed with every column selected — remembering that a partial selection silently deletes real customers, seventy-seven of them on this very file. Invisible spaces trimmed. Fake N slash A values turned into honest nulls — twenty-two of them, and the profile is the referee. Thirty states standardized back to twenty-six. And a date column converted with your eyes open, because parsing follows locale and the only acceptable error count is a zero you have personally seen. This week: the practice trio runs from a guided rebuild to reviewing a colleague's broken recipe. Assignment A-zero-two hands you the same file and grades the state your recipe produces — five hundred rows, twenty-two nulls, twenty-six states, dates typed with zero errors. Next lesson: the data stops arriving in one file, and we learn the two ways to combine tables — and why confusing them is the oldest mistake in data work.