Forum Discussion
Helper VFinal help using "SQL type" query for an OData feed from Microsoft Dynamics
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:
-
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") -
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 & "'" -
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.
-
Community SupportThankyou, 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:
- Use the entity endpoint (for example, .../bookableresourcebookings) rather than the service root.
- Apply the $select and $filter query options in the request URL so that filtering and column projection are performed on the server.
- Ensure the date filter is formatted as yyyy-MM-ddTHH:mm:ssZ and quoted where required, since the field is of type DateTimeOffset.
- 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.
Looking at the code Royel suggest it seems to meet the structure suggested in this article you suggested
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_asplinHi
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")