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