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.
mike_asplinso 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
- 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