People mix these two "combine" operations up all the time. Both live on the Home tab and both put data together, but they answer opposite questions.
Append stacks rows and makes the table longer. Use it when you have several tables of the same shape and want them as one longer table. Think of this month and last month, North region and South region, or three CSVs from a folder. Go to Home ▸ Append Queries, or ▸ Append Queries as New to produce a fresh query and leave the inputs untouched. Choose Two tables or Three or more tables and pick them. The result has the same columns and the sum of the rows.
One rule trips up everyone. Append matches columns by name, and names are case-sensitive. A Revenue column and a revenue column are treated as two different columns. Power Query keeps both and fills the non-matching one with null for every row from the other table. The same goes for Sales Amt and SalesAmt. So before appending, standardise the headers across all inputs, renaming them until they match letter for letter, case included. This is the append failure, and it gives you a table that looks plausible but is riddled with nulls.
Advertisement
How does a merge work in Power Query?
Merge joins columns and makes the table wider. Use it when you have different tables that share a key and you want to pull columns from one onto the other. It's the query-side XLOOKUP. Go to Home ▸ Merge Queries, or ▸ Merge Queries as New. Pick the two tables and click the key column in each to pair them. Ctrl-click multiple columns if you need a composite key. Then choose the Join Kind:
- Left Outer is the default, and the one you want ~90% of the time. It keeps all rows of the first (top) table and attaches matches from the second, and unmatched keys get
null. It behaves just like a lookup. Every order keeps its row, and the product's category rides along. - Inner keeps only rows that match in both. Use it to intersect, and watch out, because it drops rows without telling you. It's a common source of "where did my rows go?"
- Right Outer keeps all rows of the second table.
- Full Outer keeps all rows of both, matched where possible.
- Left Anti keeps only first-table rows with no match in the second. It's brilliant for "which orders reference a product that isn't in the master?" Right Anti is its mirror.
After the merge you get a new column holding nested tables. Click its expand icon, the double-headed arrow in the header, and tick which fields to bring in. Untick "Use original column name as prefix" if you want clean names. And once more, merge keys are matched case-sensitively, and stray spaces throw them too. Trim/Clean/case-normalise your key columns on both sides before merging. Otherwise matches fail with no error, and a Left Outer fills your new columns with nulls.
Which join kind should you use?
Choosing the wrong join kind is a top-tier bug. A Left Outer where you needed Inner keeps unmatched rows with null attributes, and those nulls then poison your sums. An Inner where you needed Left Outer deletes rows without a word. Your total drops and you may not notice. Always ask yourself: do I want every row of my main table (Left Outer), only the matches (Inner), or the non-matches (Anti)?