Lecture video · L03 Power Query II

[Slide 1] Last week we cleaned one file. This week the truth gets one notch more realistic: data does not arrive in one file. Today's inbox holds six monthly sales exports, the customer list you cleaned last lesson, and a budget grid from finance that is beautiful to read and useless to analyze. By the end of the session you will know the two verbs that combine tables, the one dropdown that can silently delete most of a company's sales, and the transform that turns finance's grid into something you can actually chart.

[Slide 2] Combining tables has two fundamentally different verbs, and mixing them up is one of the oldest mistakes in data work. Append stacks: same-shaped tables, one on top of another, producing more rows. Merge looks up: two different-shaped tables joined by a shared key column, producing more columns. Six monthly files of identical shape want appending. Sales lines that need customer segments attached want merging. Here is the thing to respect: get them backwards and Power Query will not stop you. It will do exactly the wrong thing you asked for, fluently and without complaint. Before you open either dialog, say the sentence: do I want more rows, or more columns?

[Slide 3] The six monthly exports share identical columns, so they stack. Home, Append Queries, Append Queries as New, select all six, and the result is one table of twenty-six thousand, one hundred thirty-nine rows covering January through June. One line chart instead of six files. And once you have done this by hand, learn the professional shortcut: the Folder connector points at a directory and combines every file in it on every refresh. When July's file lands in that folder, it joins the table by simply existing. Twelve files by December, twenty-four next year — zero edits to your recipe.

[Slide 4] Append has one demand: column names must match, because it aligns columns by name. If one file renamed Quantity to Q-t-y, the append still runs — and the result quietly grows an extra column that is mostly null, next to a Quantity column that is null wherever that file's rows sit. Nothing warns you; the table simply gets wider and worse. The defense is last week's habit wearing this week's clothes: after every append, thirty seconds with the column list and the null bars. A profile pane that shows a column suddenly half empty is an append telling you two files disagreed about a name.

[Slide 5] A quick prediction beat. You append the six files. January alone has three thousand nine hundred three rows; all six together should give twenty-six thousand one hundred thirty-nine. Your result shows twenty-two thousand two hundred thirty-six. What do you check first? Do the arithmetic. Twenty-six thousand one hundred thirty-nine minus twenty-two thousand two hundred thirty-six is three thousand nine hundred three — exactly January. One file missed the append. The point is not this particular bug; the point is that you only caught it because you knew what count to expect. Predict, then run. That habit is about to matter much more.

[Slide 6] Now merge — the lookup. Home, Merge Queries: pick the sales table, pick the customer table, click the key column in each. And then the dialog asks the question this whole lesson exists to make you take seriously: Join Kind. That dropdown is not a technicality. It is a claim about which rows deserve to exist in the result. To show you what the claim does, we run an experiment with real counts: our twenty-six thousand appended sales rows, merged against the five-hundred-customer marketing list you cleaned last week. Same tables, same key, three different join kinds — three wildly different tables.

[Slide 7] Here are the results, and this table is the lesson. Left Outer, the default, returns all twenty-six thousand one hundred thirty-nine sales — every row survives, with customer details attached where they exist and nulls where they do not. Inner returns one thousand six hundred twenty-seven rows: only the sales whose customer appears in the five-hundred-person list. And Left Anti returns twenty-four thousand five hundred twelve: precisely the sales with no match — the complement of Inner. Notice the two smaller numbers sum to the big one, always and exactly, because Inner and Anti split the left table between them. Now look at Inner's row once more. Choosing it instead of the default did not error and did not warn. It produced a smaller, plausible-looking table missing ninety-four percent of the company's sales.

[Slide 8] The defense against the silent wrong join is the same habit the append beat taught: write down the row count you expect before you click OK, and compare after. A left join returns exactly the left table's count — if, and only if, the right table's key column is unique. If the lookup has duplicate keys, each duplicate multiplies its matching rows, and your table grows. Twenty-nine thousand rows out of a twenty-six thousand row merge does not mean bonus data; it means the key you trusted is not a key. Ten seconds with the profile pane before merging — five hundred rows, five hundred distinct — is the guarantee. If the count after any merge surprises you, the merge is telling you something about your data. Listen before you ship.

[Slide 9] That left anti count — twenty-four thousand five hundred twelve sales with no matching customer — looked like a failure a minute ago. Flip the question and it becomes an instrument. Which sales reference a customer missing from our marketing list? That is exactly what an auditor asks, and the anti join answers it in one step. Generalize the pattern and keep it for life: anti joins find orphans. Orders that reference a product the catalog no longer contains. Payments that reference no invoice. Payroll rows for people missing from the active-employee roster. Any time two tables are supposed to agree, the anti join measures precisely how much they do not.

[Slide 10] Last stop: finance's budget file. Thirty rows — one per region and category pair — with twelve month columns marching across. This shape is called a crosstab, and I want to be fair to it: for human reading, it is the right shape. Finance should keep making it. But for analysis it is useless, for one precise reason: the month is trapped in the column headers instead of living in the data. You cannot put column headings on a chart axis. You cannot filter to the second quarter. You cannot join January's target against January's actuals. The information is all there — it is just stored in the furniture.

[Slide 11] The cure is called unpivot. Select the two identity columns — Region and Category, the ones that describe each row — then Transform, Unpivot Other Columns. The twelve month columns collapse into two: Attribute holds the month names, freed from the headers, and Value holds the numbers that sat beneath them. Rename them properly, set the types, and the thirty-row grid becomes three hundred sixty tall rows — thirty times twelve, a multiplication you can verify. Each row now says region, category, month, target, and that shape joins, filters, and charts like every other table you own. One deliberate choice hiding in there: we unpivoted the other columns rather than selecting the twelve months directly. Same result today — different result in January, when finance adds a thirteenth column and other-columns catches it automatically while a fixed selection would quietly miss it.

[Slide 12] One last discussion, and it is this week's Hot practice wearing a preview. You run a duplicate check on the combined twenty-six thousand row sales table and it finds twenty-three exact duplicate rows. Do you delete them? Talk it through. Last week the answer was an easy yes — a customer file has one row per customer, so a full-row copy can only be an error. But these are transaction lines, and this export carries no line-number column. A customer who puts the same lamp in the cart twice produces two genuinely identical rows. Are your twenty-three rows export errors, or real sales that happen to look alike? Here is the resolution: you cannot tell from inside the table — and that is the point. You settle it with an outside reference. This table reconciles to finance's independent half-year figure to the cent with those rows left in. Delete them and you are the one creating the error. Same button as last week; different data contract; opposite answer.

[Slide 13] A translation note before we close. If you have met SQL, today was familiar: merge is JOIN, append is UNION, and the join kinds are the same animals in friendlier clothing. If you have never touched SQL, then quietly, today, you learned the concept that half of SQL interviews revolve around — from a dialog box instead of a keyword. When you meet the keyword someday, it will already be yours.

[Slide 14] Here is the week in one breath. Append stacks same-shaped tables — six files became twenty-six thousand one hundred thirty-nine rows, and the Folder connector makes next year free. Merge looks up by key, and the join kind is a claim about which rows deserve to exist: the default kept everything, Inner silently kept six percent, and the anti join turned the leftovers into an audit tool. Unpivot freed finance's months from the column headers — thirty rows became three hundred sixty, a multiplication you predicted before you ran it. And that is the deepest habit of the week: predict, then run. Counts you expected are checks; counts you merely received are decoration. This week's assignment grades exactly those counts — twenty-six thousand one hundred thirty-nine, twice, then three hundred sixty. Next lesson, Module Two begins: the tables stop being a pile and start being a model, and you meet the diagram that will run the rest of your course.