It’s a Thursday morning. The kettle hisses. The fan turns slowly above the counter. Reena has the laptop open, Power BI on screen, the Power Query editor loaded — and this time she has four queries in the left-hand pane instead of one:
Jan Sales—412rowsFeb Sales—389rowsMar Sales—447rowsProducts—38rows
The three monthly files came from the same till software, so they have the same 6 columns: Date, Bill No, Product ID, Quantity, Unit Price, Amount. The Products file is different — it has 38 rows, one per item she sells, with Product ID, Category and Unit Cost.
Her cousin is coming on Sunday. His message arrived last night and, as usual, contains two jobs disguised as one: “Reena, one dashboard. All three months together, and profit broken down by category.”
She looks at the Home ribbon. Two buttons sit next to each other, in the same group, with almost the same icon:
Merge Queries Append Queries
She mutters, “Both of them combine tables. Why are there two?”
Her phone buzzes. The cousin, late as always: “Pick the wrong one and you’ll either lose February or double your revenue. Both look fine on the screen.”
The awning rustles.
A woman steps in under the awning — tall, quick, sleeves pushed up, dark teal shirt. In her right hand she carries a metal receipt spike, the kind that stands on a shop counter with bills skewered onto it. Three sheets are already on the spike. She sets it down with a clack and pushes a fourth sheet onto the point.
She says, “I am Append. I stack. Same shape, more of it. You hand me Jan, Feb and Mar, and I skewer them one on top of another into a single query. Your table gets taller.”
A second woman walks in behind her — deliberate, unhurried, deep maroon shirt, reading glasses pushed up into her hair. She is carrying a long-arm stapler. She lays two sheets of paper side by side on the counter, edge touching edge, and staples along the seam.
She says, “I am Merge. I widen. Same rows, more about each row. You hand me your sales table and your Products table, and I bring the Category column across and attach it to every row that matches. Your table gets wider.”
Reena looks at the two of them. “So both of you combine two tables.”
They reply, together, “In perpendicular directions.”
Append speaks first
Append taps the spike. The four sheets sit flush, edges lined up.
She says, “My requirement is simple, and it is not about your data at all. It is about your column names. If Jan Sales has a column called Quantity, then Feb Sales must also have a column called Quantity. Spelled the same. Then I can stack them and the values fall into the same column.”
Reena says, “And the row count?”
“Adds up. 412 + 389 + 447 = 1,248 rows. Still 6 columns — the same 6 you started with. Nothing about a single row changes. There are just more rows now.”
She adds, “And notice I do not care whether your rows are related to each other. Jan row 1 and Mar row 1 have nothing to do with one another. I never compare them. I never look for a match. I just put one below the other and hand the pile back.”
Reena says, “So for the first half of my cousin’s request — three months in one table — you’re the one.”
“I am. Use Append Queries as New, pick all three, and you get a fourth query holding all 1,248 rows. Keep the three monthly queries loaded as staging queries and disable their load so they don’t clutter the model.”
She pauses, then adds, with feeling, “One more thing, because people set this up badly and then blame me. If a new file arrives every month, do not append Jan, Feb, Mar by hand and then come back in April to edit the step. Point Power Query at the folder instead — Get Data → Folder — and it appends every file in it automatically. Drop Apr Sales.csv into the folder, hit Refresh, and your 1,248 becomes whatever it should be. No editing. That is the same job as mine, done once instead of twelve times a year.”
Merge speaks
Merge sets the stapler down and squares up her two sheets.
She says, “My requirement is the harder one. I need a key — a column that exists in both tables and tells me which row goes with which row. In your case that is Product ID. Without a key I cannot do anything at all, because I have to look each row up.”
She continues, “Here is what I actually do. I take your appended sales table — 1,248 rows. For each row I read the Product ID, go across to the Products table, find the row with that same Product ID, and bring its Category and Unit Cost back with me.”
Reena says, “And the row count?”
Merge says, “Unchanged. Still 1,248. That is the point — I did not add any sales. I added knowledge about each sale. 6 columns became 8.”
Reena writes that down.
Merge says, “You will also be asked to pick a join kind, and Power Query defaults to Left Outer. That means: keep every row from the first table, whether or not it finds a match. If a Product ID in your sales file has no matching row in Products — a discontinued item, a typo — the row survives, and the new columns come through blank.”
Reena says, “Is blank good or bad?”
Merge says, “It is information. Blank means your lookup table is incomplete. If instead you had picked Inner, those unmatched rows would have been silently dropped and your total sales would quietly fall — and you would have no idea why. Filter the merged column for blanks after every merge. It is the cheapest data-quality check in the tool.”
What just happened
Reena pours herself a glass of tea, sits down on her own counter, and looks at the two of them.
She says, slowly, “You’re not alternatives at all. My cousin asked for two different things and I thought it was one thing.”
Both nod.
She says, “All three months together is you.” She points at Append. “Profit by category is you.” She points at Merge. “And I need to do them in that order — append the three months first, then merge the product lookup onto the result. Otherwise I’d be doing the same merge three times.”
Append says, “Correct, and that ordering matters more than people think. Append first, merge second. Fewer steps, one place to fix.”
Reena writes the two rows in her notebook:
| Append | Merge | |
|---|---|---|
| Direction | vertical — stacks | horizontal — widens |
| Needs | matching column names | a matching key column |
| Rows after | 412 + 389 + 447 = 1,248 | unchanged — still 1,248 |
| Columns after | unchanged — still 6 | 6 + 2 = 8 |
| Typical use | same table, many files or months | fact table, plus a lookup |
| SQL cousin | UNION ALL | JOIN |
She looks at the last row. “It’s UNION and JOIN with different names.”
Merge says, “It is exactly UNION and JOIN with different names. If you ever learn SQL, you already know us.”
The same chat, in a chart
That picture is the same conversation, drawn. The first panel is the spike: three blocks with identical headers becoming one tall block, and nothing about any individual row changing. The second panel is the stapler: the same block gaining two columns from a small lookup table, its height untouched — and beside it, in red, what happens when the key is not unique.
One last warning before they leave
Merge picks the stapler back up but does not leave yet. She says, “Three traps. The first one is mine and it is the expensive one.”
She counts them off.
“One. A merge on a non-unique key multiplies your rows. Everything I said assumed each Product ID appears once in Products. Suppose someone added a second row for P-114 last month to record a new unit cost, and forgot to delete the old one. Now when I look up P-114, I find 2 matches. I bring back both. When you click Expand, that one sales row becomes 2 sales rows — with the amount copied into both.”
Reena’s face changes.
Merge says, “Your 1,248 becomes 1,291. Your revenue rises by about 3%. Nothing errors. Nothing turns red. The dashboard just quietly reports a number that is too big, and the more it is refreshed the more you trust it.”
She says, “So here is the rule, and it costs you 4 seconds. Check the row count before and after every merge. Power Query prints it at the bottom of the window. After a Left Outer merge it must be identical. If it went up, your key is not unique, and the fix is upstream — deduplicate the lookup table, not the result.”
Append says, “Two, and this one is mine. I match on column name, exactly. If Feb Sales calls it Qty and the other two call it Quantity — or if one of them has a trailing space after the name, which no human being can see — I will not error either. I will create two columns. Quantity will be filled for 859 rows and blank for 389, and Qty will be the mirror image. Your row count will be perfectly correct and your totals will be nonsense. After appending, scroll right and count your columns. If there are more than you started with, you have a spelling problem.”
Merge says, “Three, and this one is about restraint. Do not merge a lookup table into your fact table just to get a column you want to slice by. Fact and Dimension came through here a while ago and told you to keep those tables separate, joined by codes. In Power BI that join is a relationship in the model, and that advice still stands. Merge in Power Query when you need the value for a calculation in Power Query — a cost column you’re about to subtract, or a key you’re about to build. Slice by it through a relationship instead.”
Reena writes all three down. Row count before and after. Count the columns. Merge for maths, relate for slicing.
The bill
They left the way people leave a tea stall — Append tucking the receipt spike under one arm, sheets still on it, and Merge sliding her two stapled sheets into a folder and finishing her tea standing up.
Reena built it in the order they told her. She appended the three monthly queries into one called Sales, and the status bar read 1,248 rows. She merged Products onto it on Product ID, Left Outer, and the status bar still read 1,248 — so she knew her product list was clean. She expanded Category and Unit Cost, filtered Category for blanks, and found 3 rows pointing at a Product ID that had been retired in February and never added to the master file.
She fixed those 3 rows before building a single chart.
Her cousin, on Sunday, looked at the dashboard for a long moment and said the only thing he ever says when the numbers are right: “Good.” Then, later, over the second glass: “How did you know the total was correct?”
Reena turned the laptop round and showed him the row count — 1,248, before and after.
She had written the sentence she wanted to keep at the top of her notebook page:
Append makes the table taller. Merge makes it wider. If a merge changed the height, the merge was wrong.
For the math-curious
The M code. Append is
= Table.Combine({#"Jan Sales", #"Feb Sales", #"Mar Sales"})Merge is
= Table.NestedJoin(Sales, {"Product ID"}, Products, {"Product ID"}, "Products", JoinKind.LeftOuter)followed by a
Table.ExpandTableColumnstep, which is what the Expand arrow generates. The nested-then-expand shape is why row multiplication is invisible until you expand: before expansion, the new column holds a table per row, and a duplicated key simply means that cell holds2rows instead of1.Join kinds.
LeftOuterkeeps all left rows.Innerkeeps only matches.LeftAntikeeps only left rows with no match — the diagnostic one.RightOuter,FullOuterandRightAntiare the mirrors. Cardinality is what decides the row count: a1to1or many-to-1merge preserves it, a many-to-many merge does not.Append and column names.
Table.Combineunions on column name and fills missing columns withnull. There is no positional matching and no fuzzy matching —"Qty"and"Quantity"are simply two different columns.Table.TransformColumnNamesor an explicit rename step before the append is the fix.The equivalent in SQL. Append is
UNION ALL— noteALL, because Power Query does not deduplicate. Merge isLEFT JOIN. The row-multiplication trap is the same trap SQL has always had, and the fix is the same one: make sure the right-hand side is unique on the join key.
Two buttons, side by side in the same ribbon group. One changes how many rows you have. The other changes how much you know about each one.