OData source error

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.

So the models that dont work have this credential required and it wont accept OAuth2.

The CDS connection is fine with OAuth2

I spent ages stripping out this query and replacing it with a standard CDS query that is filtered inside the query editor

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
        ]
    )),

 and so far the refresh hasnt crashed and I dont see that extra web connection in the data source credentials list. So seems to be this causing the issues.   Is it possible to write similar query that filters the data BEFORE it arrives that uses a CDS connection?

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 
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.

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.