Forum Discussion
OData source error
all good
Hi mike_asplin
yes, it is possible to achieve the same “filter before data is retrieved” behavior using the CDS connector.
When you apply filters in Power Query (for example on starttime), Power BI will attempt to push those filters back to Dataverse (query folding). This means only the filtered data is retrieved from the source similar to your previous filter logic.
You can validate this by right-clicking the last step in Power Query and checking View Native Query if it’s enabled, the filtering is happening at the source.
Hope this helps!!
Thank You.
So what i did was completely uneccessary as Power BI can do it for me i.e. I am not pulling in loads of uneccessary data and then throwing it way? Does this apply to the columns as well as the date filtering?
My replacement code looks like this. However view native queries is greyed out in Query editor so how do I know if its doing what you say?
let
Source = Cds.Entities("https://chaxxx.api.crm11.dynamics.com", [ReorderColumns=true, UseFormattedValue=true]),
entities = Source{[Group="entities"]}[Data],
products_table = entities{[EntitySetName="bookableresourcebookings"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(products_table,{{"starttime", type datetime}, {"endtime", type datetime}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"bookableresourcebookingid", "bookingstatus", "cha_carercontactid", "cha_clientid", "duration", "endtime", "msdyn_actualarrivaltime", "msdyn_milestraveled", "msdyn_totalcost", "msdyn_workorder", "name", "owningteam", "resource", "starttime", "statuscode"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"starttime", Order.Ascending}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "starttime", "Actual Start Time"),
#"Filtered Rows3" = Table.SelectRows(#"Duplicated Column", each [starttime] >= #"Load Date"),
I tried researching and seems I might need to add some extra code, but could not work out where the extra code would go for a CDS query as this doesnt work
let
Source = Cds.Entities("https://chaltd.api.crm11.dynamics.com", [ReorderColumns=true, UseFormattedValue=true]),
entities = Source{[Group="entities"]}[Data],
#"Run Native Query" = Value.NativeQuery(
entities{[EntitySetName="bookableresourcebookings"]}[Data],
[EnableFolding=true]),Can you tell me how to force query folding for a CDS query please?
Hi mike_asplin
Just following up to see if everything is working fine after switching to the CDS connector.
Please let me know if you still have any questions or need help validating the setup.Hi mike_asplin
Query folding cannot be explicitly forced for CDS (Dataverse). Unlike SQL sources, it doesn’t support native queries (Value.NativeQuery), so folding is handled automatically by Power BI.
To maximise folding, apply filters and column selection as early as possible and avoid transformations (like sorting or adding columns) before filtering. Also, View Native Query being greyed out is expected for Dataverse and doesn’t necessarily mean folding isn’t happening.Reference : https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding
Hope this helps!!
Thank You.
Hi mike_asplin
Just following up on this I wanted to check if you had a chance to review the earlier questions ?Ok understood. so can you just confrim this query is an OData query?
let BaseUrl = "https://chaxxxx.api.crm11.dynamics.com", Endpoint = "api/data/v9.1/bookableresourcebookings", SelectColumns = "bookableresourcebookingid,_bookingstatus_value,_resource_value,name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime", FilterDateText = DateTime.ToText(#"Load Date", "yyyy-MM-ddTHH:mm:ssZ"), QueryRecord = [ #"$select" = SelectColumns, #"$filter" = "starttime ge " & FilterDateText ], Source = Json.Document(Web.Contents( BaseUrl, [ RelativePath = Endpoint, Query = QueryRecord ] )),Hi mike_asplin
The query you are using is indeed an OData query because the Dynamics 365 / Dataverse API exposes data via the OData v9.1 endpoint. Even though you are calling it with Web.Contents the $select and $filter options are OData system query options.
Microsoft recommends using the Dataverse connector in Power BI Desktop instead of manually calling the OData feed. The connector handles authentication and schema more reliably and avoids errors like DMTS_OAuthFailedToGetResourceIdError.
Hope this helps!!
Thank You.
all good
Hi mike_asplin
Glad to hear everything is working fine now!
If any of the suggestions helped, please acknowledge any of the solution so it can help others in the community who will face a similar issue.
Thank You.