Final help using "SQL type" query for an OData feed from Microsoft Dynamics

[mike_asplin]

sorry to post this again, but got 95% of the answer and now no one replying!!!

I was suggested this code to filtler the Odata feed for only the riht columns and date filter

let

    Url = "https://chaxxxxxltd.api.crm11.dynamics.com/api/data/v9.1/",

    SelectColumns = "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"),
    FilterQuery = "$filter=starttime ge " & FilterDateText,


    ODataQueryOptions = [
        Query = "$select=" & SelectColumns & "&$" & FilterQuery
    ],

       Source = OData.Feed(Url, null, [Implementation="2.0"]),
    bookableresourcebookings_table = Source{[Name="bookableresourcebookings", Signature="table"]}[Data],
 Data = OData.Feed(bookableresourcebookings_table, null, ODataQueryOptions)

in
    Data

The bit up to bookableresourcebooking_table generates a table of seemingly all values, but the last clause gives me this error

This is all gobbledegook to me so can anyone help me over this last hump please? 

Appreciate any help as taking ofrever each time I tweak the query as big table behind. 

[v-pnaroju-msft]

Hi mike_asplin,

Thank you for the follow up.

Based on my understanding, the same approach can be applied to CDS or Dataverse queries, since Dataverse is built on the OData v4 Web API. The main distinction is that the request must target the specific entity endpoint, for example: /api/data/v9.1/bookableresourcebookings, rather than the service root. The query parameters serve the following purposes:

1. Implementation=2.0, specifies the Power Query connector implementation mode.
2. ODataVersion=4, ensures compatibility with the Dataverse Web API.
3. OmitValues=ODataOmitValues.Nulls, excludes null fields, this does not affect filtering.

We hope the information above helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi mike_asplin,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi mike_asplin,

Thank you for the update.

Based on my understanding, the issue occurs because Power Query cannot evaluate M functions like 

DateTime.ToText(#"Load Date", …) within a URL string. The $filter parameter in an OData request must be plain text, not an M expression. As a result, Power Query treats the URL as a literal string instead of interpreting the embedded formula, causing the $filter expression to fail and default to the service root.

Please follow the approach below which might help to resolve the issue:

1. Define the parameter value before constructing the URL. Convert the Load Date parameter into a text variable first, for example:
   FilterDateText = DateTime.ToText(#"Load Date", "yyyy-MM-ddTHH:mm:ssZ")

2. Concatenate the variable into the query string rather than embedding it inside the URL literal, for example:
   FullUrl = "https://<your-org>.crm11.dynamics.com/api/data/v9.1/bookableresourcebookings?$select=name,_cha_clientid_value,_owningteam_value,duration,starttime,endtime,statuscode&$filter=starttime ge " & "'" & FilterDateText & "'"

3. For URL encoding, encode special characters such as spaces or quotes using %20 or %27 where necessary.

If, after correcting the URL structure, the query still returns all tables, please raise a Microsoft support ticket using the link:Microsoft Fabric Support and Status | Microsoft Fabric

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[mike_asplin]

Sorry one last question can you use the same apporach for CDS queries?

If so what does this part of the query look like?

 Source = OData.Feed(FullUrl, null, [
        Implementation="2.0",
        ODataVersion = 4,
        OmitValues = ODataOmitValues.Nulls

[mike_asplin]

so seems the idea I was given before should work? This works but does not actually filter out any columns or dates? 

let

    Url = "https://chaltd.api.crm11.dynamics.com/api/data/v9.1/",

       SelectColumns = "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"),
    
    FullUrl = Url & "?$select=" & SelectColumns & "&$filter=starttime ge " & FilterDateText,
    
    Source = OData.Feed(FullUrl, null, [
        Implementation="2.0",
        ODataVersion = 4,
        OmitValues = ODataOmitValues.Nulls
    ]),
    bookableresourcebookings_table = Source{[Name="bookableresourcebookings",Signature="table"]}[Data]
in
    bookableresourcebookings_table

[mike_asplin]

ah table name was missing so now works. Brilliant thanks for all your help resolving this

[v-pnaroju-msft]

Hi mike_asplin,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi mike_asplin,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi mike_asplin,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @Royel, for your response.

Hi mike_asplin,

We appreciate your inquiry to the Microsoft Fabric Community Forum.

Based on my understanding, the error occurs because OData.Feed was supplied with a Table object instead of a valid entity URL. OData.Feed expects a text URL pointing directly to the entity (for example, /bookableresourcebookings) and including $select and $filter options. Passing a Table causes the cannot convert Table to Text error, and connecting only to the root service URL returns the full list of tables without applying any filters.

Please follow the approach below, which might help resolve the issue:

1. Use the entity endpoint (for example, .../bookableresourcebookings) rather than the service root.
2. Apply the $select and $filter query options in the request URL so that filtering and column projection are performed on the server.
3. Ensure the date filter is formatted as yyyy-MM-ddTHH:mm:ssZ and quoted where required, since the field is of type DateTimeOffset.
4. For dynamic filters such as the Load Date parameter, use OData parameter aliasing a method to pass dynamic values and to avoid refresh issues in the Power BI service.

For further reference, please consult the following links:
OData.Feed - PowerQuery M | Microsoft Learn
Power Query OData Feed connector - Power Query | Microsoft Learn
Use OData to query data - Power Apps | Microsoft Learn
Filter rows using OData - Power Apps | Microsoft Learn

We hope the information provided will assist in resolving the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[taavi]

Looking at the code Royel suggest it seems to meet the structure suggested in this article you suggested

Use OData to query data 

let
    Url = "https://chaxxxxxltd.api.crm11.dynamics.com/api/data/v9.1/bookableresourcebookings",
    
    SelectColumns = "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"),
    
    FullUrl = Url & "?$select=" & SelectColumns & "&$filter=starttime ge " & FilterDateText,
    
    Source = OData.Feed(FullUrl, null, [
        Implementation="2.0",
        ODataVersion = 4,
        OmitValues = ODataOmitValues.Nulls
    ])
in
    Source

however it just doesnt work. It does not actually do anything just retruns a list of all the tables on the DB as shown lower down in the post. The question is what is wrong with it as seems to follow the construction in the article in creating a url containing the column names and date filter?

Appreciate any further guidence as sure this isnt that difficult for someone in the know. 

[v-pnaroju-msft]

Hi taavi,

Based on my understanding, the reason you are still seeing the full table list is likely that the request URL is not being interpreted as an entity query. This might be due to one or more of the following issues: the filter string has not been URL encoded, the field name (for example: starttime) does not match the exact Dataverse Web API logical name, or the connector/authentication is redirecting to the service root.

Please try the following steps which could help to resolve the issue:

1.Paste the full URL including $select or $filter directly into the OData Feed option under Get Data in Power BI.

2.Ensure that the filter portion is properly URL encoded.

3.Confirm the column names against the Dataverse Web API metadata.

If the Web API URL works outside Power BI but the same issue persists within Power BI, we recommend raising a Microsoft support ticket using the link:Microsoft Fabric Support and Status | Microsoft Fabric

We hope the information above helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[mike_asplin]

Hi

sorry just back from holiday

I tried using the get data odata feed advanced settings as suggested.

This gives me a url preview of this , but sure my syntax is wrong as just throws lots of errors.  I dont see how it can know the load date as that is a parameter.  I dont know what the proper URL encoding is? 

https://chaxxx.api.crm11.dynamics.com/api/data/v9.1/$select="name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime"& $filter=starttime ge DateTime.ToText(#"Load Date", "yyyy-MM-ddTHH:mm:ssZ")

[mike_asplin]

sorry I thought that bit was obvious.

The columns to keep are 

"name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime"

 and i have a parameter #'Load Date' that controls the earliest of starttime

much appreciated any help as refresh taking for ever. 

[Royel]

Hi @mike_asplin  seems like you have some issue with this line Source{[Name="bookableresourcebookings", Signature="table"]}[Data]
you can try this 

let
    Url = "https://chaxxxxxltd.api.crm11.dynamics.com/api/data/v9.1/bookableresourcebookings",
    
    SelectColumns = "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"),
    
    FullUrl = Url & "?$select=" & SelectColumns & "&$filter=starttime ge " & FilterDateText,
    
    Source = OData.Feed(FullUrl, null, [
        Implementation="2.0",
        ODataVersion = 4,
        OmitValues = ODataOmitValues.Nulls
    ])
in
    Source

Thanks  

[mike_asplin]

Hi

Sorry that just produced this a list of available tables

If i expand the relevant table seems to contain all the columns and hasnt filtered by date i.e. it didnt do anything. appreciate your help

[Royel]

Hi @mike_asplin  at least the SQL Type error is gone and now from here you can add more transformation logic to get your exact data. 

[mike_asplin]

But this didnt achieve anything as just the same as opening the source. None of the column sleection or date filtering has affected the data? Just gone round in a circle. Your code is producing the same as what I started with I believe

let

    Source = Cds.Entities("https://chaxxxxxltd.api.crm11.dynamics.com", [ReorderColumns=true, UseFormattedValue=true]),
    entities = Source{[Group="entities"]}[Data],

[Royel]

Sorry, would you please add more context on this like which date range you like to have and what will be the name of the column you like to keep. 

In simple word, let us know this is your current situation and what you want to achieve.