Append and Merge Queries in Power BI

Power BI is a robust business analytics tool that allows users to visualize data and share insights across an organization, or embed them in an app or website. Two of the most powerful features in Power BI are append and merge queries. These features are part of Power Query, a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. 

Append Queries in Power BI

Appending queries is essentially about combining data from two or more tables into one comprehensive table. This is particularly useful when you have data of the same type spread across multiple tables and you need to perform a unified analysis. 

Using an Append Query 

In the example below, we will take a look at combining two tables, cookies and chips, into one table called products using the append query. Combining these tables gives us a better sense of all of the products our fictional convenience store offers. 

Our two tables have the same columns with the same data types. Product name, price, and calories. This will allow us to perform an append query to create the master products table mentioned above. 

  

 In the power query editor, you’ll want to click “Append Queries” in the top right hand corner. Then click, “Append Queries as New”. This will create an entirely new query for us instead of replacing the two existing tables with one appended table. 

Next, you’ll want to select the tables you want to append. You can select two tables or three or more tables. Click Ok. 

This will create a new query for us, typically it will be called “Append 1” unless you already have another query with that name. I went ahead and renamed it to products to give it a bit more meaning. 

  

If everything looks alright, you can click close & apply and your new table will be there to create some awesome reports with! 

Merge Queries in Power BI

Merging queries is similar to SQL JOIN operations. It’s about connecting two tables based on a related column, allowing you to enrich one table with the data from another. 

Using a Merge Query 

In our next example, we have some data from the NBA on Players and Teams. We have Player data, but if we’re only looking at the Player table, we don’t know which team they play for. All we have is the Team ID associated with the record on the Team table, and that’s where the merge query comes in handy.  

In our Team table, we have a Team ID, which acts as the unique identifier that we will need to merge the two tables. Once we complete the merge, the new table will contain all of the player information as well as the team information! 

In our power query editor, we’ll go ahead and click the “Merge Queries” and click “Merge Queries as New”. 

For our example, we will select Players as the first table, and then Teams as the second table. We will then match the related columns, which in our case is the Team ID column. Finally, we will select a left outer join. There are many other types of joins that mimic SQL joins, but I won’t go in depth on them in this blog post. 

This will create a new query for us called, “Merge 1”, but we’re not done yet. In our new query, we’ll see a column called Basketball Team, which contains all the columns from the Basketball Team table. If we click on the diverging arrows, we can then select the columns from the other table and include them in this new query! Click ok when you have made your final selection. 

Your new column names will look a little funny. That’s normal. That’s carry over from how SQL performs join operations. It’s ok to rename the columns and remove any columns that you may not need. 

Finally! Our query is complete. Click close & apply and your new table will be ready to go.  

With the power of a merge query, we were able to merge two tables to see our players and their team names. With a dataset this small, it’s rather easy to match the ID’s with the team names if you have access to both tables. However, most databases have hundreds of tables with hundreds of thousands of rows, which would making manually matching those records nearly impossible. With merge queries, you can merge your tables with a few simple clicks. 

Best Practices for Append and Merge Operations 

Ensure that your data is clean and well-structured before performing append or merge operations. Inconsistent data with null values and empty strings can throw off your reports. For append operations, align the schemas of the tables to avoid null values in the combined table. For merge operations, ensure that there is a clear relationship between the tables based on the columns selected for merging. By mastering append and merge queries, you can significantly enhance your data modeling capabilities in Power BI, leading to more insightful and comprehensive analytics. 

Reach out to Beringer today! 

Do you need assistance with Power BI? Contact us today!  Take a look at our Microsoft Power Platform services page for more information.

Beringer Technology Group, a Microsoft Solutions Partner for Business Applications,  specializing in Microsoft Dynamics 365 and CRM for Distribution, also provides expert Managed IT Services, Backup and Disaster Recovery.