Power BI Lesson 22 – Append & Merge Queries | Dataplexa

Power Query · Lesson 22

Appending and Merging Queries

Most real-world Power BI projects pull data from more than one table. Append stacks tables on top of each other — rows from one table added below rows from another. Merge joins tables side by side — columns from one table brought into another based on a matching key. These are Power Query's equivalents of SQL UNION ALL and JOIN. Knowing which operation to use, and which join type to choose, prevents the most common data modelling errors beginners make.

Append vs Merge — The Core Difference

⬇️

Append — adds rows

Takes two or more tables with the same columns and stacks them vertically. The result has the same column count as the inputs but more rows. Think: combining monthly sales files into one annual table.

OrderID

Revenue

1001

1,200

1002

850

↕ rows from second table appended below

1003

430

1004

➡️

Merge — adds columns

Takes two tables and brings columns from one into the other by matching on a shared key column. The result has the same row count as the primary table but more columns. Think: adding customer name to an orders table by matching on CustomerID.

OrderID

CustomerID

CustomerName

1001

C101

Alice Brown

1002

C102

Bob Singh

↔ CustomerName column joined from Customers table

Append Queries

Append is used whenever you have multiple tables with the same structure that should be treated as one. The most common scenario is monthly or regional data files — January sales, February sales, March sales — that all have identical columns and need to be combined into a single annual table for analysis.

Append Queries dialog

Two tables Three or more tables

Primary Table

Sales_Q1 ▾

1,240 rows · 5 columns

Table to Append

Sales_Q2 ▾

1,180 rows · 5 columns

Result: 2,420 rows · 5 columns — all rows from Sales_Q1 followed by all rows from Sales_Q2

Cancel

Option	When to use it
Append Queries	Modifies the currently selected query by adding rows from the second table at the bottom. The primary query grows. Use when you want the result to replace the primary query.
Append Queries as New	Creates a brand new query containing the appended result. Both original queries remain untouched. Use this — it preserves the originals and the result is a separate named query you can work with independently.

What Happens When Column Names Do Not Match

Append matches columns by name, not by position. If Sales_Q1 has a column called "Revenue" and Sales_Q2 calls it "Sales", Append does not combine them — it creates two separate columns in the result, with nulls wherever a row comes from a table that does not have that column name.

Mismatched column names — problem

OrderID	Revenue	Sales
1001	1,200	null
1002	850	null
1003	null	430
1004	null	95

Fix — rename columns before appending

In the Sales_Q2 query, rename "Sales" to "Revenue" before running the Append step. Both queries now use the same column name and Append will correctly combine all values into a single Revenue column with no nulls.

Rule: standardise column names across all tables before appending

Merge Queries — Joining Two Tables

Merge is the equivalent of a SQL JOIN. It takes a key column from each of two tables, finds matching rows, and brings selected columns from the second table into the first. The result has all the columns of the primary table plus any columns you choose to expand from the second table.

Merge Queries dialog — Orders LEFT JOIN Customers

First Table (primary)

OrderID	CustomerID ←	Revenue
1001	C101	1,200
1002	C102	850

Click CustomerID column to select it as the join key (highlighted in yellow)

Second Table

Customers ▾

CustomerID ←	CustomerName	City
C101	Alice Brown	Austin
C102	Bob Singh	Denver

Join Kind

Left Outer (all from first, matching from second) ▾

✓ The selection matches 2 of 2 rows from the first table.

The Six Join Types

Power Query offers six join types. Choosing the wrong one silently produces the wrong number of rows — and the error is often not obvious until a total looks wrong in a visual. Understanding what each join type keeps and discards is essential.

Left Outer

All rows from left table · matching rows from right

Keeps every row from the primary (left) table. Brings in matching data from the second table. Rows with no match get nulls for the second table's columns. Most commonly used join.

Use when: You want all orders, and want to add customer info where available — even if some orders have no customer match yet.

Right Outer

All rows from right table · matching rows from left

Keeps every row from the second (right) table. Rows from the primary table with no match are discarded. Rarely needed in Power Query — you can usually achieve the same result by swapping the tables and using Left Outer.

Use when: You want all customers, even those with no orders, with order info attached where it exists.

Inner

Only rows that match in both tables

Keeps only rows that have a match in both tables. Any row in either table with no matching key in the other is discarded. Row count in the result is always ≤ the smaller of the two tables.

Use when: You only want orders that have a valid matching customer — orphaned orders with no customer record are excluded.

Full Outer

All rows from both tables

Keeps every row from both tables. Where there is a match, columns from both are combined. Where there is no match, nulls fill the missing columns. Result can be larger than either input table.

Use when: You want a complete union of both tables and need to identify which rows exist in one but not the other.

Left Anti

Rows in left with NO match in right

Returns only the rows from the primary table that have NO matching key in the second table. No columns from the second table are added — this is a filtering operation, not a column join.

Use when: Find all orders that have no matching customer record — orphaned or invalid orders. Excellent for data quality checks.

Right Anti

Rows in right with NO match in left

Returns only the rows from the second table that have NO matching key in the primary table. The mirror image of Left Anti.

Use when: Find all customers who have never placed an order — they exist in the Customers table but have no matching row in Orders.

Expanding the Merged Column

After a Merge step, the second table's data appears as a single column containing nested table objects — not as separate columns yet. You must expand this column to choose which fields to bring in. This step is what most beginners miss.

After Merge step — Customers column is a nested table (not yet expanded)

OrderID	CustomerID	Revenue	Customers ⇔ Expand
1001	C101	1,200	Table
1002	C102	850	Table

Click the ⇔ expand icon in the Customers column header → select which columns to bring in → uncheck "Use original column name as prefix" → OK

Full Merge + Expand sequence in M code:

// Step 1 — Merge Orders with Customers on CustomerID
#"Merged Queries" = Table.NestedJoin(
    Orders,           // primary table
    {"CustomerID"},   // key column in Orders
    Customers,        // second table
    {"CustomerID"},   // key column in Customers
    "Customers",      // name for the nested table column
    JoinKind.LeftOuter
),

// Step 2 — Expand the nested Customers column
// Choose which columns from Customers to bring in
#"Expanded Customers" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Customers",
    {"CustomerName", "City"},   // columns to expand
    {"CustomerName", "City"}    // names for the new columns
)

Orders after Merge + Expand — CustomerName and City added

OrderID	CustomerID	Revenue	CustomerName	City
1001	C101	1,200	Alice Brown	Austin
1002	C102	850	Bob Singh	Denver
1003	C103	430	Carol Lee	Seattle
1004	C999	95	null	null

Order 1004 has CustomerID C999 which has no match in Customers → Left Outer keeps the order row, nulls appear for CustomerName and City

Merge vs Model Relationships — Which to Use

You can bring customer name into your Orders table in Power Query using Merge, or you can leave the tables separate and create a relationship in the model. Both produce the same result in visuals, but they have very different implications for file size, flexibility, and maintenance.

	Merge in Power Query	Relationship in Model View
Model size	Customer columns are duplicated into every row of the Orders table — increases file size proportionally.	Data stored once in Customers table. Orders only stores the CustomerID key. Much smaller model.
Slicer filtering	Works — the merged column is in the Orders table and can be filtered.	Works — relationship propagates filter from Customers to Orders automatically.
Customer changes	If a customer's name changes in the source, the merged value in Orders only updates on the next refresh — and only reflects the state at refresh time.	Customers table is independent. Updates to customer data are reflected everywhere automatically through the relationship.
Recommended?	Avoid for dimensions — use only for lookup data that is genuinely flat and will not grow.	Always preferred for dimension tables — smaller, cleaner, more flexible.

Teacher's Note: The most important rule is this — use Append freely, use Merge with caution. Append is almost always the right tool when combining files with the same structure. Merge in Power Query should be reserved for cases where you genuinely need a flat denormalised table for a specific purpose — like preparing data for export or feeding a specific visual that cannot use relationships. For everything else, build the relationship in Model View instead. Your model will be smaller, faster, and easier to maintain.

Practice

Practice 1 of 3

You have three monthly sales files — Jan, Feb, and Mar — all with identical columns. To combine them into one table while keeping all three original queries intact, you use Home → Append Queries as ___ in the Power Query Editor.

Practice 2 of 3

After a Merge step, the second table's data appears as a single column showing the word "Table" in every row. To convert this into actual columns, you click the ___ icon in that column's header.

Practice 3 of 3

You want to find all orders in the Orders table that have a CustomerID with no matching record in the Customers table — a data quality check. The correct join type to use in Merge is Left ___.

Lesson Quiz

Quiz 1 of 3

You append Sales_Q1 (1,240 rows) and Sales_Q2 (1,180 rows). The result has 2,420 rows but the Revenue column shows many nulls. What is the most likely cause?

Append automatically deduplicates rows — the nulls represent rows that existed in both tables and were merged into one row with partial data The Revenue column is named differently in the two tables — for example "Revenue" in Q1 and "Sales" in Q2. Append creates two separate columns and fills each with nulls for rows from the other table. The Revenue column type is set to Decimal in Q1 and Whole Number in Q2 — the type mismatch causes nulls for mismatched rows during the append operation Appending more than 2,000 rows exceeds the Power Query preview limit and nulls are used as placeholder values for rows beyond the preview threshold

Quiz 2 of 3

You merge Orders with Customers using Left Outer join on CustomerID. The Orders table has 500 rows. After the merge and expand, the result has 620 rows. What happened?

Left Outer join always adds rows from both tables — 500 from Orders plus 120 from Customers who had no orders equals 620 total rows The Customers table had 120 extra rows that were appended to the Orders table automatically during the expand step Some CustomerIDs in Orders matched multiple rows in the Customers table — a one-to-many relationship on the Customers side. Each matching Customers row creates a separate output row. The key columns are not truly unique in the Customers table. The expand step added 120 new rows because 120 Orders had null CustomerIDs — nulls are expanded into one row per null during the expand operation

Quiz 3 of 3

A colleague merged the Orders and Customers tables in Power Query to bring CustomerName into Orders, then deleted the Customers query. Six months later the business changes 200 customer names in the source. What problem will occur on the next refresh?

The merge step in Orders re-reads the Customers source on each refresh — the 200 updated names will appear correctly. However, without the standalone Customers table in the model, there is no dimension table to use for customer-level slicers or customer-based DAX calculations. The merge step cached the Customers data at the time of the original merge — deleting the Customers query freezes the names permanently and the 200 changes will never appear even after refresh Power BI will throw an error on refresh because the Merge step references the Customers query by name — deleting the query breaks the M code reference and the entire Orders query fails Nothing changes — once columns are expanded from a merged table, the data is embedded directly in the Orders M code and no longer reads from the Customers source at all

Next up — Lesson 23 covers Advanced Cleaning Techniques — handling errors in cells, replacing null values, standardising inconsistent text, and building robust cleaning steps that do not break when the source data is messy in unexpected ways.

← Previous Course Index Next →

Power BI Course