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.
-
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.
Community SupportHi 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")