Merge and Append Queries in Power BI

Power BI is a data visualization tool used to create interactive reports and dashboards. It allows combining data from multiple sources using Merge and Append queries, where Merge joins tables based on a common column and Append stacks tables vertically to add rows.

Merge Queries

Merge Queries combine data from two tables based on a common column, adding related information from one table to another in a horizontal manner.

• Works horizontally by adding new columns
• Matches rows using a common key column
• Default join type is Left Outer Join
• Keeps all rows from the primary table
• Adds matching data from the secondary table
• Row count usually remains unchanged unless another join type is used

Consider the following two tables:

1. Sales Dataset

You can download full dataset from here

2. Product Dataset

Steps to Merge Queries

Step 1: Open Power Query Editor and select the primary table you want to merge from the left pane.

Step 2: With the table selected go to the Home tab to access the Ribbon menu.

Step 3: In the Combine section click Merge Queries.

Step 4: Choose Merge Queries to merge directly into the selected table or Merge Queries as New to create a new merged table.

Step 5: From the first drop-down select sales Data and choose the common column

Step 6: From the second drop-down select product and choose the matching column .

Step 7: The matching data from the secondary table will be added to the selected (primary) table based on the chosen key.

Join Types

There are several types of joins we can choose from when performing a merge:

1. Left Outer Join: Includes all rows from the first table and matching rows from the second table.
2. Right Outer Join: Includes all rows from the second table and matching rows from the first table.
3. Full Outer Join: Includes all rows from both tables.
4. Inner Join: Includes only matching rows from both tables.
5. Left Anti Join: Includes only the rows from the first table that don’t have matching rows in the second table.
6. Right Anti Join: Includes only the rows from the second table that don’t have matching rows in the first table.

Fuzzy Match

When using the merge function, we have the option to perform fuzzy matching. This increases the merge’s reach by matching similar but not necessarily identical, values in the tables. The similarity threshold ranges from 0 to 1:

• A value of 0 enforces strict matching, allowing only exact or very close matches.
• A value closer to 1 allows more lenient matching, accepting less similar values.

This feature is useful when our data might have slight differences in text values but should still be considered a match.

Append Queries

Append Queries are used to combine multiple tables by stacking them vertically, adding rows from one table to another. When the column structure matches data from the appended table is placed below the existing data in the base table.

• Works vertically by adding new rows
• Keeps the same number of columns in the base table
• Combines two or more queries into a single table
• Requires matching column names and structure

How to Append Queries

Follow these steps to append queries in Power BI:

You can download sales 2018 dataset from here

Step 1: In the Power Query Editor, select the query (table) that we want the other query to append to. For example, choose Sales Data.

Step 2: After selecting Sales Data Table click the Home Tab to access the Ribbon Menu.

Step 3: Select Append Queries from the Combine menu.

Step 4: Select Add Queries as New. A pop-up menu will appear.

Step 5: Pick first query from the first drop-down selection.

Step 6: Choose second query from the second drop-down selection.

Step 7: Once selected click OK to append the queries.

Note: If we want to preserve the current query result and generate a new query with the appended data, select Add Queries as New. Otherwise, choose Append Queries.

Duplicate rows cannot be eliminated by appending queries instead we must use Group by or remove duplicate records.

Handling Different Columns in the Queries

If the columns in the source queries are different, Power BI will add a column for each new one. Rows without values for the new columns will show null. This is similar to SQL UNION ALL where rows are appended without any changes to the column structure.

Why Should We Combine Queries

Combining queries provides several benefits:

• Unify Data: Combine information from multiple sources into a single dataset for comprehensive reporting.
• Create Relationships: Link datasets using common columns to enable deeper analysis.
• Clean and Transform: Prepare data by removing duplicates and applying transformations.
• Improve Performance: Optimize the data model for faster refresh and responsive dashboards.

Merge vs. Append Query

Here we compare merge and append queries in PowerBI