Power BI is a data analytics and visualization tool that hooks into a number of sources, including Salesforce. Salesforce has its own reporting and querying language, but sometimes you need to use Power BI to leverage and depict data in ways Salesforce cannot. To efficiently build reports in Power BI, a key concept to learn is query merging. This article has a Salesforce focus, but you can use this technique to merge tables from any source.
What is Query Merging?
Imagine a Salesforce instance containing Accounts and Opportunities. In the Salesforce data model, Opportunities lookup to Accounts. This means that one Account can have many Opportunities related to it. Because a lookup field in Salesforce holds the key for the record it looks up to, Salesforce allows you to query objects that are related using the lookup field.
For example, an Opportunity query can also include fields from the Account:
SELECT Id, Name, Account.Name from Opportunity
The above SOQL query can pull in the Name of the Account related to the Opportunity because there is a lookup field on the Opportunity called AccountId. Any field from the Account can be pulled in, as long as the user running the query has permissions to that field.
In Power BI, we use query merging to model the same behavior. Query merging allows you to pull in data from a related table into a source table. This way, you can report on data that spans multiple records.
How to Query Merge
Using the above example, let’s pull in the Salesforce Opportunity and then pull in the Account’s Name. To start, get your Opportunity table by selecting Get Data and then selecting Salesforce Objects:
Provide your credentials for the org you want to connect to and do not check the Include relationship columns checkbox:
Select the Opportunity table and select Transform Data:
Now, you are in the Power Query Editor window. Because we want to pull in Account Names, we need to get the Account table as well. Click on New Source and select the Account table:
With both tables pulled in, we can now merge our queries. The first thing to do is right-click on the Account table and deselect Enable Load:
This is important because when we pull the data for our report, we do not want to pull in both tables in their entirety. There may be Accounts that do not have Opportunities, so we only want to pull in Account’s that are related to Opportunities.
Select your Opportunities table and select Merge Queries from the toolbar:
From here, select the AccountId column and then select the Id column from the Account table:
This is how Power BI is told what table is related to what other table. Because there is a match on Ids between these two columns, we can now pull in data from the Account table to the Opportunity. In this case, I selected a Left Outer join, which only pulls in data from the second table that matches data in the first table. There are other types of joins that you can explore, but Left Outer is one of the most common joins for reporting use cases.
Select OK and your Opportunity table will have an extra Table column appended to it:
Pulling in the entire table is technically possible, but it has performance considerations since it will pull in all the data. for this use case, we only want the Name of the Account. To select the columns from the other table we want, click on the icon in the upper-right corner of the column:
This will open a window that allows you to select what columns you want to pull in from the Account:
I have only selected the Name column. Select OK will now update the Opportunity table and pull in the Account Name field:
Select Click & Apply in the upper-left corner and your Opportunity table will now have Account data merged into it! Here is a sample Matrix report using the Opportunity and Account Name data, showing which Accounts have which Opportunities:
With query merging, you can control what data you want to have in your report without sacrificing depth of data. Here is another article I wrote about filtering data before it is pulled from the source, which can also help improve your performance and give you more control over what data you bring into your report.
Leave a comment with any questions or thoughts! Happy reporting!