Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 56 ยท Power Query

Merge and Append Queries

Combine data in Power Query: merge queries with join kinds and fuzzy matching, expand columns, append tables and combine a folder of files, with pandas stand-ins.

Data seldom lives in one place. Orders are in one table and customer details in another; twelve monthly files should become one yearly table. Power Query has two operations for this, and keeping them apart in your head solves most confusion:

  • Merge adds columns: it looks up matching rows in another table (like a SQL JOIN or an Excel XLOOKUP).
  • Append adds rows: it stacks tables with the same columns on top of each other (like UNION ALL).

Both are on the Home ribbon under Combine. Later you will learn that a proper data model often uses relationships instead of merging, so this lesson also tells you when each is the better tool.

Merge or append?

Merge Add columns from a second table by matching a key.
Append Stack rows from tables with similar columns.

Merge = wider. Append = taller.

1 / 2

Merge queries

Select the first table, choose Merge Queries, pick the second table, then click the key column in each table (hold Ctrl to match on several columns, and keep the order the same in both). Choose a join kind and confirm. Power Query adds a new column holding the matching table (or a record per row), shown as Table in each cell.

M
Merged = Table.NestedJoin(
    Orders, {"CustomerID"}, Customers, {"CustomerID"}, "Customers", JoinKind.LeftOuter
),
Expanded = Table.ExpandTableColumn(Merged, "Customers", {"Name", "Country"}, {"Name", "Country"})

The six join kinds in the dialog are:

Join kind Keeps
Left Outer all rows of the first table; matches from the second
Right Outer all rows of the second table; matches from the first
Full Outer all rows from both
Inner only rows with a match in both
Left Anti rows of the first table with no match
Right Anti rows of the second table with no match

Left Outer is the everyday choice: keep every order, and add customer details where they exist. Left Anti is a quick quality check, for example "which orders have a customer ID that is not in the customer table?".

Expand the new column

The merged column is only a placeholder until you click the expand icon (two arrows) in its header. Choose which columns to bring over and clear the box "Use original column name as prefix" if you prefer short names. Expand only the columns you need: every extra column increases the size of the model.

Keys must match exactly

Join keys are compared exactly, including case and spaces. "C-01" and "c-01 " are different. Types must also match: a text key does not match a number key. The result of a failed match is a row with null in the expanded columns (or no row at all for an inner join). Clean both keys first with the steps from Cleaning Data: Types, Nulls, Trim and Duplicates.

Also beware duplicate keys in the second table. If a customer appears twice, each matching order is repeated twice, and your totals double. The lookup table should have one row per key.

Fuzzy merge

Tick Use fuzzy matching to perform the merge in the dialog to match values that are similar rather than identical, such as "Microsoft Corp" and "Microsoft Corporation". The options include a similarity threshold (0 to 1; higher is stricter), ignore case, ignore spaces and maximum number of matches. You can also supply a transformation table with pairs such as Inc. and Incorporated.

M
Table.FuzzyNestedJoin(
    Orders, {"Company"}, Companies, {"Company"}, "Matches", JoinKind.LeftOuter,
    [IgnoreCase = true, IgnoreSpace = true, Threshold = 0.85]
)

Fuzzy matching is a convenience for one-off cleanup, not a guarantee. Always inspect the matches, and prefer fixing the source or building a mapping table when the process must be reliable.

Append queries

Append Queries stacks tables. Choose Two tables or Three or more tables, then pick them. Columns are matched by name, not by position; a column that exists in only one table is filled with null for the other.

M
Appended = Table.Combine({Sales2024, Sales2025})

Because matching is by name, a header like Qty in one file and Quantity in another produces two columns, both half empty. Rename before appending. If you want to remember where each row came from, add a column first (for example Year) in each source query.

Combine files from a folder

The pattern that saves the most manual work is Get Data > Folder. You point to a folder of files with the same layout (monthly exports, for example) and choose Combine & Transform Data. Power Query then:

  1. Lists the files with their content.
  2. Builds a sample query using the first file.
  3. Wraps your cleaning steps into a custom function.
  4. Calls that function on every file and appends the results.

When next month's file appears in the folder, Refresh picks it up automatically, with no change to the report. The generated function is explained in Custom Functions and Reusable Queries, and connecting to folders in Connecting to Data Sources.

All files in the folder are combined. A stray backup file or a test export in the same folder will be appended too. Filter the file list (by extension or name) as the first step.

Merge and append in pandas

In pandas, merge is the join and concat is the append. The data has an order with an unknown customer and a customer without orders, so you can see the join kinds differ.

Python Join kinds with merge

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Order 3 has customer C9, who is not in the customer table. It survives in the left and full joins with a missing name and disappears in the inner join. Customer C3 has no orders and appears only in the right and full joins.

Append, and the duplicate-key trap

This second stand-in appends two files and then shows how a duplicate key in the lookup table inflates totals.

Python Append with concat and the duplicate-key trap

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first print shows Units and Qty as two half-empty columns. After renaming, the totals are right. In the last lines, the duplicate lookup row doubled the order's amount from 100 to 200, which is exactly the trap described above.

Everything in the M snippets is read-only: Power Query's engine runs in Power BI. These pandas programs are stand-ins to show the logic.

When to merge and when to use relationships

Merging copies columns into a table, which makes it wider and the model larger. A relationship in the data model (see Star Schema: Facts and Dimensions) keeps tables separate and joins them at query time. As a rule: use append to combine tables of the same kind; use relationships between a fact table and its dimension tables; use merge when you need a column before the model (for example to compute something row by row, or to filter one table using another).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between Merge and Append?

    Merge is a join that makes a table wider. Append is a union that makes a table taller.
  2. 2. Which join kind shows orders that have no matching customer?

    Left Anti returns the rows of the first table that have no match in the second.
  3. 3. Append two files where one header is Qty and the other Quantity. What happens?

    Append matches columns by name. Different names produce separate columns, so rename before appending.
  4. 4. Why can a Merge inflate your totals?

    If the lookup table has two rows for a key, each matching row in the first table appears twice, so sums double.

Summary

  • Merge adds columns (a join); Append adds rows (a union, matched by column name).
  • Left Outer is the everyday join; anti joins find non-matches; keys must match exactly in type, case and spaces.
  • Expand only the columns you need, and make sure the lookup table has one row per key.
  • Fuzzy merge matches similar text; inspect results and do not rely on it for critical processes.
  • The Folder connector combines many files and picks up new ones on refresh.

Next, summarize tables in Group By and Aggregations in Power Query.