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.
-
Helper Vso 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
Helper Vah table name was missing so now works. Brilliant thanks for all your help resolving this