Power BI and Power Query can apply so-called “query folding”. Query folding in Power BI and Power Query forwards filters from subsequent steps into the actual download. Additionally, query folding can forward operations into to the actual source (the OData-download server).
This can extremely improve performance. For instance, it is 1.000x faster to download the last day’s transactions when the server just present the last day’s worth of transactions, instead of returning all transactions and Power BI locally filtering the data.
More information on query folding can be found in:
Query folding is also primarily available for OData feeds from Invantive Cloud. However, to view the native query please use Invantive Bridge Online Monitoring. Currently Power BI Desktop does not offer the native query view for folding in Power BI with OData-based sources.
Query folding is highly similar to “server-side filtering” offered by Invantive SQL when retrieving data sets from on many cloudplatforms. The Invantive SQL-engine incorporate filters (when possible) into the actual query on the sources such as a database or cloud platform. Server-side filtering can be combined with joins, where the join strategy may be altered given the characteristics of the data sets/tables selected.
Limitations
Not all operations are forwarded by query folding (see Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn). For instance, search for a text at the start of a string is not forwarded, whereas “greater than” is available, but can not be selected in the user interface of Power BI and requires direct entry in Power Query.
Example
A filter on the name column on ActiveCampaign like in:
let
Source = OData.Feed("https://bridge-online.cloud/invantive-activecampaign/odata4", null, [Implementation="2.0"]),
#"ActiveCampaign.V3.Accounts@acn_table" = Source{[Name="ActiveCampaign.V3.Accounts@acn",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"ActiveCampaign.V3.Accounts@acn_table", each [name] > "Start")
in
#"Filtered Rows"
is forwarded and results in the following Invantive SQL-query being executed:
select t.*
from ActiveCampaign.V3.Accounts@acn t
where ([name] > :w1)