Combining tables in Power Query trips up almost every beginner. The tool offers two commands that sound alike: Merge and Append. Pick the wrong one, and you get duplicate rows or a wall of nulls. The truth is simple once you see it. Append stacks tables on top of each other. Merge joins them side by side on a shared key.
This guide makes the difference stick for good. First, it shows the two ways to combine tables. Then it covers append, merge, and the join types. A decision guide and a full troubleshooting section follow. By the end, you will pick the right command every time.
Two Ways to Combine Tables
Every combine falls into one of two shapes. You either add more rows or add more columns. Append adds rows by stacking tables. Merge adds columns by joining on a key, as below.
So the question is always the same. Do you need more rows or more columns? More rows means Append. More columns means Merge.
Append: Stack Rows
Append is the tool for same-shaped tables. It places one table's rows below another's. The columns line up by their names. So three monthly files become one long table.
Merge: Join Columns
Merge is the tool for related tables. It pulls columns from one table into another. The two must share a key, like CustomerID. Then Merge lines up the rows by that key.
Merge Join Types
A merge asks which rows you want to keep. That choice is the join type. Left Outer keeps all rows from the first table. Other types keep matches, everything, or non-matches, as below.
Which One Should You Use
One quick question settles it every time. Ask whether the tables share the same columns. If they do, you want more rows, so Append. If they share a key, you want columns, so Merge, as below.
Expand the Merged Columns
A merge does not show the new fields at once. Instead, it adds one column of nested tables. You click its expand icon to pull fields in. Then you choose exactly which columns to keep.
Troubleshooting Merge and Append
All three problems below are the most common. Each has a clear cause and a quick fix.
Appended columns do not line up
Append matches columns strictly by their names. So a small header difference splits a column. For example, "Amount" and "amount " become two columns. First, open each query and compare the headers. Then rename them so they match exactly. A quick step to trim and clean names helps here. After that, the append stacks into single, tidy columns.
My merge is full of nulls
Nulls usually mean the keys are not matching. A join finds no partner, so it returns blank. First, check the key columns on both sides. Look for trailing spaces or different data types. A text key will not match a number key. So clean and align the keys, then set matching types. Once the keys agree, the matches fill in.
A merge created duplicate rows
Duplicates appear when the second key is not unique. Each first row then matches several second rows. So the result multiplies rows unexpectedly. First, check the table on the join side for repeats. Then remove duplicates on the key column there. A dimension table should hold each key once. After that, the merge returns one row per match.
Frequently Asked Questions
- What is the difference between Merge and Append in Power Query?Append stacks tables to add more rows. Merge joins tables to add more columns. Append needs the same columns; Merge needs a shared key. So one grows rows and the other grows columns.
- When should I use Append instead of Merge?Use Append when tables share the same columns. For example, combine monthly files into one. It simply stacks the rows together. So think of it as a UNION of data.
- What join type should I pick for a merge?Left Outer is the everyday default. It keeps all first rows plus any matches. Use Inner for matches only, or Left Anti for gaps. So the join type controls which rows survive.
- Why does my merge return blank columns?Because you have not expanded the merged column yet. A merge adds one column of nested tables. So click its expand icon and pick the fields. Then the matched columns appear.