Merge and Append Queries in Power BI

Power BI is a data visualization tool that allows us to create interactive reports and dashboards. One of its key features is the ability to combine data from multiple sources using Merge and Append queries. Merge Queries are used to combine two tables based on a common column while Append Queries stack multiple tables vertically to add more rows.

Understanding Merge Queries

Merge queries are used to combine data from two tables based on a common column. This operation works horizontally, adding new columns from the second table to the first table where there are matching rows. The result is a new table with additional columns but the number of rows in the primary table will remain the same unless a different type of merge is used. By default, the merge operates like an SQL left outer join which means all rows from the primary table will be included with matching rows from the secondary table added.

How to Merge Queries?

Consider the following two tables:

1. Sales Data:

2. Product Data:

Steps to Merge Queries:

Step 1. In the Power Query Editor, select the query (table) that we want to combine with another query. For example, select Sales Data from the left pane.

Step2. Select Sales Data Table. To access the Ribbon Menu, click the Home Tab.

Step 3. In the Combine section, click Merge.

Step 4. We will see two options: "Merge Queries" and "Merge Queries as New." Select Merge Queries to merge the tables directly or Merge Queries as New if we want to create a new table.

Step 5. From the first drop-down menu, we select Sales Data then choose the shared column like Product Key that links the tables.

Step 6. From the second drop-down menu, we select Product Data and choose the matching column (Product_Key).

Step 7. Click on OK. The data from Product Data will be added to Sales Data based on the common column with matching rows appearing in the new table.

• If we want to generate a new table by merging two or more, we select Merge Queries as New.
• We can also select multiple columns for merging by choosing more than one column in the merge dialog.
• We are utilizing Product_Key from the Sales Data table and Product_Key from the Product Data dataset below.

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 shows exact matches (similar to a full outer join in SQL).
• A value of 1.00 shows more lenient matching (similar to an inner join).

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

Append Queries

Append Operation is used to combine queries by stacking them vertically. When the column values in two tables match, rows from one table are added to the bottom of the other table’s data.

The base table will maintain the same number of columns but with additional rows added. Append refers to combining two or more queries (tables) into a single query. For example, appending a 250-row query to a 150-row query results in a 400-row table.

The columns in each query must match. If Query 1 has columns Column1, Column2, ..., Column7 and Query 2 has the same columns, appending combines the rows into one unified table.

How to Append Queries?

Follow these steps to append queries in Power BI:

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:

• Combination Of Data From Multiple Sources: Merging and appending queries allow us to bring together data from various systems such as CRM, ERP or external databases into a single table. This makes it easier to create comprehensive reports and get a holistic view of the data.
• Create New Relationships: By merging tables based on a common column we can establish new relationships between datasets. These relationships help us gain deeper insights helps us to analyze data across different domains and uncover meaningful patterns.
• Clean and Transform Data: Combining queries not only helps us consolidate data but also allows us to clean and transform it before reporting. For example, we can remove duplicate rows, rename columns or apply other transformations to ensure our data is ready for analysis.
• Improve performance: Efficient data models are important for smooth report generation. Combining queries helps optimize the model by reducing redundancy and improving query performance. This leads to faster load times and more responsive dashboards helps the users can work with data without delays.

Difference Between Merge And Append Query

By understanding when and how to use Merge and Append queries in Power BI, we can efficiently combine data to enhance our analysis, create meaningful relationships and streamline reporting processes.