Power BI is a powerful data visualizer and analytics tool that can connect to a number of sources. Most business will eventually move to using a tool like Power BI if they’re getting serious about data analytics.
One great feature about Power BI is its out of the box connectors to different platforms. Power BI has a Salesforce Connector that can pull in standard and custom objects, so Salesforce data can be added to reports, dashboards, etc. One disadvantage to the Salesforce Connector is that is does not provide query folding in the GUI:
Hold on, what is query folding?
Query folding is an important concept when using Power BI. Query folding is when Power BI adds a filter to the query it makes to the data table before loading that data into Power BI. This is extremely useful when dealing with large data sets.
Imagine you had 5 million Account records in Salesforce. Pulling them all into report on them would take hours for Power BI, and most of the data is probably unnecessary for the analytics you are performing. Using the normal GUI shown above, Power BI will pull in all the Accounts!
Let’s say you wanted to only pull in Accounts that were of a certain Record Type or Account that were created after a certain data. To filter the Accounts before pulling them in, you need to specify the criteria you want to filter by and then let Power BI know that criteria through the Advanced Editor.
Using the Advanced Editor to Query Fold
Though the sample size is small, take a long at the following Accounts in my Salesforce instance:
Let’s pull these into Power BI. Click on Get Data and choose Salesforce Objects, then select Connect:
Select Production or Custom (if your org using a custom domain) and do not select Include relationship columns:
Choose the Account object and then select Transform Data:
This is where we can apply our query folding. The Accounts have not actually been pulled into the dataset yet. The right-hand panel shows the actions Power BI will take when it pulls the data into our dataset. Currently, the steps are Source and Navigation, which were automatically configured when selecting the data source and the table in the previous steps.
To query fold, click on Advanced Editor in the toolbar:
From here, you can add the criteria you want to fold by. Here is the original query:
For this example, I am going to query fold on Account Type being Customer – Channel:
Applying this criteria will then limit the rows shown in the Preview:
If you then select Close & Apply in the upper-left corner of the menu bar, the criteria will be applied to your dataset:
You folded a Salesforce query!
Assuming you apply the query after the Navigation step, the syntax for query folding is as follows:
#"[Name of Returned Rows]" = Table.SelectRows([Name of Table from the step above this one], each [Field Name you want to filter against] = [filter value])You can add multiple filters as separate steps. You can also filter by date values using the following syntax:
#"Filtered Rows" = Table.SelectRows(Account1, each [CreatedDate] >= #date(2021, 10, 1))With query folding, you can pull in just the data you really care about modeling, saving Power BI doing a lot of unnecessary calculating and you from a lot of waiting!