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.
-
Super UserHi mike_asplin seems like you have some issue with this line Source{[Name="bookableresourcebookings", Signature="table"]}[Data]
you can try this
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
Thanks
mike_asplinHi
Sorry that just produced this a list of available tables
If i expand the relevant table seems to contain all the columns and hasnt filtered by date i.e. it didnt do anything. appreciate your help
- Royel
Hi mike_asplin at least the SQL Type error is gone and now from here you can add more transformation logic to get your exact data.
- mike_asplin
But this didnt achieve anything as just the same as opening the source. None of the column sleection or date filtering has affected the data? Just gone round in a circle. Your code is producing the same as what I started with I believe
let Source = Cds.Entities("https://chaxxxxxltd.api.crm11.dynamics.com", [ReorderColumns=true, UseFormattedValue=true]), entities = Source{[Group="entities"]}[Data],