Forum Discussion

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper V
11 months ago
Solved

Final help using "SQL type" query for an OData feed from Microsoft Dynamics

sorry to post this again, but got 95% of the answer and now no one replying!!!   I was suggested this code to filtler the Odata feed for only the riht columns and date filter   let Url = "h...
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    10 months ago

    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:

    1. 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")

    2. 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 & "'"

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

v-pnaroju-msft's avatar
v-pnaroju-msft
Icon for Community Support rankCommunity Support
10 months ago

Hi mike_asplin,

Thank you for the follow up.

Based on my understanding, the same approach can be applied to CDS or Dataverse queries, since Dataverse is built on the OData v4 Web API. The main distinction is that the request must target the specific entity endpoint, for example: /api/data/v9.1/bookableresourcebookings, rather than the service root. The query parameters serve the following purposes:

  1. Implementation=2.0, specifies the Power Query connector implementation mode.
  2. ODataVersion=4, ensures compatibility with the Dataverse Web API.
  3. OmitValues=ODataOmitValues.Nulls, excludes null fields, this does not affect filtering.

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.