Wednesday, October 09, 2019

Querying Dynamics CRM using PowerBi - Query Folding

When you use the Common Data Service connector, then PowerBi will attempt to fold your query into a single call.

As an example:


When you look at the query in the Advanced Editor, this is what it shows:

let
    Source = Cds.Entities("https://xxxx.api.crm.dynamics.com/api/data/v9.1/", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    territories = entities{[EntitySetName="territories"]}[Data],
    #"Filtered Rows" = Table.SelectRows(territories, each [createdon] > #datetimezone(2019, 10, 1, 0, 0, 0, -6, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"createdon", "name"})
in
    #"Removed Other Columns"

And this is the query that goes out (removed url encoded characters for legibility):
/api/data/v8.2/territories?$filter=createdon gt 2019-10-01T00:00:00-06:00&$select=territoryid,createdon,name&$top=1000

As you can see, even though the query was broken out over 4 steps, there was only one call that went out to CRM, which is very cool!