Introduction
Combining data in Power BI involves merging multiple datasets into a single unified dataset for efficient analysis and visualization. Power BI provides several tools and techniques to combine data from various sources, including tables, databases, files, and web pages.
One of the ways you can combine data in Power BI is by merging queries – which allows you to combine two or more datasets based on a common column.
For example, these three tables have been combined based on the common column “Product code” to create a unified table that can be used for analysis and visualizations.
Note: You can merge data using Merge Queries feature available on the Power Query Editor.
How to merge data in Power BI?
Launch your Power BI Desktop and load your data.
Click on Transform Data to open the Power Query Editor
On the Queries pane, select the query you want to merge into (the primary query).
On the Home tab, select Merge Queries in the Combine section.
Note: You can choose between.
Merge Queries – Which merges into the selected query in this case “Sales” query.
Merge Queries as New – Which creates a new query based on the merge, leaving the original queries unchanged.
In this case, I will choose Merge Queries as New – to create a new query.
In the merge dialog box.
I have selected the Inventory query which I need to merge on the Sales query.
Next, I have selected the columns which will serve as keys for merging from both the queries - in this case, “Product code”
Lastly, I have chosen the right join type. In this case, Inner Join – which returns all matching rows from both the tables.
Executing this adds a new query on the queries pane called “Merge 1”, which I have renamed as “Master Dataset”.
Note, I can add more data on the Master Dataset by repeating the same process.
To add Returns query to the Master Dataset query.
I will select Master Dataset query as my primary query.
On Merge Queries in the combine section, I will choose Merge Queries – to merge queries into the primary query – in this case “Master Dataset”
Configure how the queries will be merged as shown below and click OK.
To view specific columns from the merged table, click on the expand icon next to the new column header. Select which columns you want to include in your primary query and click OK.
From the Inventory column I will include the column “Stock”, while from the Returns column I will include the column “Returned (Qty)” as shown below.
Executing these results to a unified table, which contains data from the three tables namely. Sales, Inventory, and Returns as shown below.
Conclude
Merging data in Power BI not only facilitates better insights and performance but also simplifies the modelling process while providing flexibility for complex analyses. It is a valuable tool in the Power BI ecosystem that enhances overall data management capabilities.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!
