Merging is another powerful transformation to optimize our tables and information that we might be getting from various sources. Learning how to merge queries in Power BI is relevant as this transformation can simplify our data models.
Getting data from different sources in Power BI isn’t a big issue. It doesn’t really matter where the data comes from since they will just become a query. What really matters is how we structure those tables in our model.
With that said, merging is a great technique to create tables that are totally different to what we ordinarily have since we can combine a lot of tables. In this article you will learn how to merge queries in Power BI effectively.
Merge Queries Option In Power BI
For example, the Channel Details table can be a logical lookup table that we can put inside our data model. But we need to leave it as a staging query because we can still utilize it by physically merging it with our Sales table.
To do that, let’s select the Sales table, then click the Merge Queries option within the Home ribbon.
The table from Channel Details always branches all dimensions based on the channel. So, let’s select and highlight the Channel column.
Then, let’s find and select the Channel Details table here.
This will give us a preview. We’ll see that the Channel Name column breaks out Export, Distributor, and Wholesale, which are the three types of elements within our fact table (Sales table). Let’s select the Channel Name column.
Merge Queries in Power BI Via Join Kind Options
There are different Join Kind options that we can use. I highly recommend exploring the options that you can use. We won’t go through every option because we can simply use the Left Outer option.
However, there are rare occurrences when we might need to use the other Join Kind options. We just need to test them and if we’re not getting the right result, then we need to try the other Join Kind options.
In this example, we’re going to join everything from our Channel column, which is in the Sales table, to the left side of the Channel Name column from the Channel Details table or query.
Select the Left Outer option, and it’ll give us a preview below stating that there’s a total match. After that, click the OK button.
Now, a few things happened in our Sales table. The first thing that we’d notice is that we have an added transformation within our APPLIED STEPS, which indicates that we’ve Merged Queries.
However, it hasn’t given us all of the additional columns from the Channel Details table. In this case, we need to click on the double arrows beside the Channel Details column name.
We’ll then see all the different available options, which are the columns from the Channel Details table. For example, we are merging tables, and there are a lot of columns that we didn’t want to merge. All we have to do is select only the columns that we want to bring in. Let’s first leave them all selected and click the OK button.
We’ll see that all the columns from that supporting table have now appeared on every single row inside our fact table.
Applying Additional Transformations
Moreover, we can just remove this Channel column here because we already have a similar column that was added using the merging technique that we previously did.
Then, let’s rename the newly-added columns in our table into Channel, Short Code, Alt. Name, Importance, and Prior Code.
These are all different dimensions that we can now use on our models and visualizations. We’re able to easily create this detailed query that’s ultimately going to become a table.
Now, if we check our APPLIED STEPS section, all the things that we’ve done were automatically added. We appended queries, merged, expanded, removed, and renamed columns.
If we check our Advanced Editor, the M code indicates exactly the same thing from our APPLIED STEPS section. Those are the transformations that we’ve previously done.
***** Related Links *****
Converting A YYWWD Code Into A Date Value In Power BI
Organizing Your Queries: Power BI Query Editor Tutorial
Advanced Transformations in Power BI
Conclusion
Ultimately, merging queries is another powerful transformation that optimizes our queries and tables for the data models. It’s important to understand how we’re going to use them and bring them all together as one. This will allow us to create these queries and tables that are fully optimized. We can then use them inside our data model to make our DAX calculations more effective.
Indeed, the merge queries transformation is an indispensable addition to our analytical thinking and model development in Power BI.
Sam
