OData source error

[mike_asplin]

Hi

I have amodle that did have some OData sources, but i changed them all to CDS sources. So now i just have CDS, shareoint excle files and a public website. Even so when I try to refresh in service I get this error

Data source error:

{"error":{"code":"DMTS_OAuthFailedToGetResourceIdError","pbi.error":{"code":"DMTS_OAuthFailedToGetResourceIdError","details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Failed to get OAuth resource, please make sure the OAuth is supported"}}],"exceptionCulprit":1}}} Previously I was seeing OData listed under Data source credentials but not now All my sources are listed as maps to personal cloud connect and no sign of any OData source?  Any suggestions gratefully received as thought I had cracked it

[collinq]

HI @mike_asplin ,

Along with the suggestions by @Odet_Maimoni , you might try going back to desktop and republishing with a brand new name.  This will give it a new dataset.  This would be soemthing to try to get rid of any potential cache or other issues where it still is trying to use the old connection.

[mike_asplin]

They already are new names as trying to get an exisitng model that is manually refreshed to work online 😞

[Odet_Maimoni]

Hey @mike_asplin , try go to the relevant gateway and reassign the credentials.

Odet Maimoni  

BI Engineer | Microsoft MVP  

I share practical Power BI tips on YouTube.

[mike_asplin]

sorry what does reassign credentials mean? In theory there is no odata source in this model? 

[Odet_Maimoni]

Go to gateways > the relevant gateway > Edit credentials 

[mike_asplin]

There is no gateway as all sources are cloud?  Sharepoint or Microsoft Dynamics

[Odet_Maimoni]

Ok so I would start by clearing and reconfiguring the credentials at the dataset level.

Since I see multiple Web sources, this is likely causing Power BI to fail resolving the OAuth resource.

If possible, also check whether using a dedicated connector (like SharePoint) instead of Web would resolve the issue.

[mike_asplin]

Maybe thaty other web source is this which someone help me put together to limit the amount of data being pulled?

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

 Can that be changed to be a CDS type source.   I really have no clue what the difference is between CDS and OData !!!!

[v-aatheeque]

Hi @mike_asplin 

This issue is often related to credential mismatches or stale OData connections. You can try the following steps to resolve it:

1.Clear permissions in Desktop

• In Power BI Desktop → File → Options and settings → Data source settings.
• Clear all stored permissions for CDS, SharePoint, and web sources.
• Re‑authenticate each source with the correct account.

2. Re‑enter credentials in Service

• Go to Power BI Service → Settings → Datasets → Data source credentials.
• For each source (CDS, SharePoint, Excel, public web), re‑sign in using OAuth.
• Ensure you’re using Organizational account for CDS/SharePoint, not “Anonymous” or “Windows.”

3. Check gateway configuration

• If any source is on‑premises (Excel on local SharePoint or file server), configure an On‑Premises Data Gateway.
• Cloud sources should not require a gateway, but mismatched settings can trigger OAuth errors.

4.Verify resource URLs

• Make sure the CDS and SharePoint URLs are consistent (e.g., using https://<tenant>.sharepoint.com/...).
• If you changed site collections or environments, update queries accordingly.

5. Republish the report

• After resetting credentials, republish from Desktop to Service.
• This often clears stale OData references that the Service still holds.

Hope this helps !!
Thank You.

[mike_asplin]

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?

[v-aatheeque]

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.

[mike_asplin]

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?

[v-aatheeque]

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.

[v-aatheeque]

Hi @mike_asplin 

Just following up on this I wanted to check if you had a chance to review the earlier questions ?

[mike_asplin]

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

[v-aatheeque]

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.

[mike_asplin]

Ok so went through every source and  replaced web.contents with Sharepoint.Contents. I'm left with a CDS source, Sharepoint and 2 web sources. 1 is a public website and the other one seems to be the same as the CDS?

Still getting same Odata error but no idea where it is coming from. Have cleared the permissions, saved model under new name and still stuck

Data source error: {"error":{"code":"DMTS_OAuthFailedToGetResourceIdError","pbi.error":{"code":"DMTS_OAuthFailedToGetResourceIdError","details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Failed to get OAuth resource, please make sure the OAuth is supported"}}],"exceptionCulprit":1}}}