A lot of people have problems with the performance of OData data sources when loading data into Power BI and Excel. One possible cause of these problems is query folding not taking place – if this is the case then the Power Query engine will be requesting more data that is needed and applying any filters itself locally, rather than requesting filtered data from the data source. How do you know whether query folding is taking place or not though? The Power Query Editor UI doesn’t give you this information, unfortunately. Last week, at the Data and BI Summit in Dublin, Matt Masson demonstrated how to use Fiddler to check if query folding is taking place when loading data from an OData data source and he’s very kindly allowed me to write about what he showed here.
The following examples use the UK Parliament’s public OData API which is documented here. The M query below, generated using the Power Query Editor in Power BI Desktop, returns all the rows from the Government Organisation table from the API:
[sourcecode language=’text’ padlinenumbers=’true’]
let
Source = OData.Feed(“https://api.parliament.uk/odata”),
GovernmentOrganisation_table =
Source{[
Name=”GovernmentOrganisation”,
Signature=”table”
]}[Data]
in
GovernmentOrganisation_table
[/sourcecode]
To monitor communication between Power BI Desktop and the OData API you will need to install Fiddler, a free tool from Telerik. You can download it here:
https://www.telerik.com/fiddler
You’ll probably also need to configure Fiddler to intercept https traffic, which you can find out how to do here. It’s a very powerful tool and I am by no means an expert in using it, but even a basic understanding of its features can be very useful for a Power BI developer.
With Fiddler running you can add a filter so that it only shows traffic to certain hosts; in this case I’m filtering to only show traffic to api.parliament.uk:
Refreshing the query shown above in the Query Editor results in the following activity being shown in Fiddler:
There are several calls to the service root URL, but the important call is to get the top 1000 rows from the GovernmentOrganisation table:
https://api.parliament.uk/odata/GovernmentOrganisation?$top=1000
Notice the use of the $top query option to restrict the number of rows returned – the first example of the Power Query engine pushing a filter back to the data source. This only happens when you refresh the query in the Power Query Editor so you can see a sample of the data; when you click the Close and Apply button and load the query into your dataset you’ll see that this $top filter is not applied and all the rows from the table are requested.
Altering the query to filter the rows down to where the GroupName column equals “Cabinet Office” like so:
Results in the following M query:
[sourcecode language=’text’ ]
let
Source = OData.Feed(“https://api.parliament.uk/odata”),
GovernmentOrganisation_table =
Source{[
Name=”GovernmentOrganisation”,
Signature=”table”
]}[Data],
#”Filtered Rows” =
Table.SelectRows(
GovernmentOrganisation_table,
each ([GroupName] = “Cabinet Office”)
)
in
#”Filtered Rows”
[/sourcecode]
Fiddler shows the following call that includes the filter:
[The Power Query Editor does quite a lot of caching so you may need to click the Refresh button on the ribbon to make sure it actually does call the API]
It can be quite difficult to work out what’s going on in a call like this, so with the highlighted row in the screenshot above selected you can go to the Inspectors tab and then the WebForms sub-tab, and it will show the different query options used:
In this case you can see the $filter query option has been used to do the filter, so query folding has taken place in this case and the API is only returning the one row that the query returns:
However it is all too easy to do something in your query that prevents query folding from happening. For example if you add an index column to the table before filtering by GroupName, as shown in this query:
[sourcecode language=’text’ highlight=’9,10,11,12,13′]
let
Source = OData.Feed(“https://api.parliament.uk/odata”),
GovernmentOrganisation_table =
Source{[
Name=”GovernmentOrganisation”,
Signature=”table”
]}[Data],
#”Added Index” =
Table.AddIndexColumn(
GovernmentOrganisation_table,
“Index”,
0,
1),
#”Filtered Rows” =
Table.SelectRows(
#”Added Index”,
each ([GroupName] = “Cabinet Office”))
in
#”Filtered Rows”
[/sourcecode]
…then Fiddler shows that only the $top filter is being applied in the call to the API, not the $filter on GroupName, so query folding is no longer taking place for the filter step:
Not everything you do in your M queries can or will be folded back to an OData API, but in general you should aim to get query folding to take place on the steps in your query that reduce the amount of data returned by the API the most. Row filters like the one shown above are a prime example of the type of transformation that will need to be folded in order to get the best possible data refresh performance. You may need to experiment with reordering steps and applying transformations in different ways to get the Power Query engine to call the API in the way you want.
Hi Chris,
from Fiddler I see my requests trough the HTTP protocol instead of HTTPS, is there an option or some setting I am missing? I now remember I had an OData connection which I could not get to work with PBI desktop, but copy pasting the link into the browser replied normally.
You need to configure Fiddler to decrypt https traffic: https://www.fiddlerbook.com/fiddler/help/httpsdecryption.asp
I certainly missed that, thanks! Still got my problem connecting to SSL enables MS Dynamics NAV, if anyone had a similar situation (web address in browser works).
It’s a shame that Group by in Power BI is not folded to OData.
Is there a possibility to use Table.View to Implement Query Folding for GroupBy?
https://docs.microsoft.com/en-us/power-query/handlingqueryfolding
Is Group By really not folded? I don’t know, and I suspect it might vary depending on the OData data source you use. But yes, it should be possible to use Table.View to implement it I think.
I’ve tried in the lates Power BI Desktop version
let
Source = OData.Feed(“https://services.odata.org/V4/Northwind/Northwind.svc/”, null, [Implementation=”2.0″]),
Order_Details_table = Source{[Name=”Order_Details”,Signature=”table”]}[Data],
GroupedRows = Table.Group(Order_Details_table, {“ProductID”}, {{“Count”, each List.Sum([Quantity]), type number}})
in
GroupedRows
Fiddler shows
https://services.odata.org/V4/Northwind/Northwind.svc/Order_Details?$select=OrderID,ProductID,Quantity&$skiptoken=11022,19