Merging datasets – or queries – in Power BI or Excel through Power Query is a critical skill when working with multiple data tables that may need to be combined into a single table – or query. This blog post provides a step-by-step guide on how to merge queries in Power Query.
NOTE
While screenshots presented here are from Power Query inside of Power BI, the steps are the same if you are accessing it through Excel.
Why Merge Queries in Power Query?
The process of merging queries in Power Query is usually initiated by the need to combine related data. Often, relevant data is spread across different sources or tables. Merging queries in Power Query allows you to combine this data based on a common key or identifier (like IDs or dates), allowing you to conduct a comprehensive analysis. For example, you might merge employees’ personal information with their pay history or department information.
Throughout this blog post, we provide screenshots from a sample employee database. Want to follow along with our examples? Download the demo datasets and Power BI file (.pbix) below.
Download Files
Click to download demo data and Power BI file (.pbix)
What Do You Need to Merge Queries in Power Query?
To merge two tables – or queries – in Power Query, whether you are working in Power BI or Excel, you will need at least two queries with a common key or identifier. Whether the data in each query comes from different sources, such as SQL Server and Excel, does not matter but having a common identifier is necessary.
Putting it in more technical terms, the datasets must contain a common column used to match rows between them, similar to a primary key in databases. This common identifier could be an ID, name, date, or any field that both tables share and can be used to align the data correctly.
In addition to a common key or identifier, you should also have a good understanding of the join type you want to apply before merging queries. Power Query gives you six join types to choose from and selecting the correct one is crucial, as each serves different purposes and affects the outcome of the merge.
The choice will depend on whether you want to keep all records from one table, both, or just the matching ones. The following graphic summarizes the six join kinds in Power Query and when to use each.
Steps to Merge Queries in Power Query
To merge two queries in Power Query, navigate to the Home tab and expand the Merge Queries option under the Combine section. This will present you with the option to Merge Queries or Merge Queries as New. The choice between these options depends on whether you prefer to merge the data into an existing query or to create a new, separate query for the merged data.
Upon selecting one of the two options, the Merge dialog box will open. This allows you to select the two queries you wish to merge. Once the two queries are selected, select the key or identifier column in each of the two queries.
Next, you will need to select one of the six Join kinds available from Power Query. Finally, click OK to finalize the merge of the two queries in Power Query.
The last step when merging queries in Power Query is to expand the resulting table to display the desired columns from the joined query. After clicking OK in the Merge dialog box, you will notice that the new ‘merged column’ displays results as a table. The final step is to expand this table.
To expand the table, click the icon on the top right of the column to choose which columns to expand. By default, all columns are selected but you can use this dialog box to choose only the columns you need for your analysis.
You can also choose whether you want to use the original query name as a prefix for the resulting column name. For example, the column Employee_Pay from the Employee_Pay_History query would display as Employee_Pay_History.Employee_Pay. This can be useful if you have columns with the same name. But, if this is not the case in your dataset, you can deselect this option to reduce the need to rename columns later on.
Wrapping It Up...
Merging datasets in Power BI and Excel in Power Query is a powerful technique that allows analysts to combine related data and uncover deeper insights by providing a holistic view of the data. By following the steps outlined in this guide, and ensuring a clear understanding of join types, you can confidently merge queries in Power Query.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
![Read more about the article [How To] Connect to an Excel Workbook in Power BI Desktop](https://goanalyticsbi.com/wp-content/uploads/2022/11/Connect-to-Excel-in-Power-BI-300x157.png)
