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

Hi 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  

sorry I thought that bit was obvious.

The columns to keep are 

"name,_cha_clientid_value,_owningteam_value,duration,starttime,_cha_carercontactid_value,_msdyn_workorder_value,msdyn_totalcost,endtime,statuscode,msdyn_milestraveled,msdyn_actualarrivaltime"

 and i have a parameter #'Load Date' that controls the earliest of starttime

much appreciated any help as refresh taking for ever. 

Thankyou, Royel, for your response.

Hi mike_asplin,

We appreciate your inquiry to the Microsoft Fabric Community Forum.

Based on my understanding, the error occurs because OData.Feed was supplied with a Table object instead of a valid entity URL. OData.Feed expects a text URL pointing directly to the entity (for example, /bookableresourcebookings) and including $select and $filter options. Passing a Table causes the cannot convert Table to Text error, and connecting only to the root service URL returns the full list of tables without applying any filters.

Please follow the approach below, which might help resolve the issue:

1. Use the entity endpoint (for example, .../bookableresourcebookings) rather than the service root.
2. Apply the $select and $filter query options in the request URL so that filtering and column projection are performed on the server.
3. Ensure the date filter is formatted as yyyy-MM-ddTHH:mm:ssZ and quoted where required, since the field is of type DateTimeOffset.
4. For dynamic filters such as the Load Date parameter, use OData parameter aliasing a method to pass dynamic values and to avoid refresh issues in the Power BI service.

For further reference, please consult the following links:
OData.Feed - PowerQuery M | Microsoft Learn
Power Query OData Feed connector - Power Query | Microsoft Learn
Use OData to query data - Power Apps | Microsoft Learn
Filter rows using OData - Power Apps | Microsoft Learn

We hope the information provided will assist in resolving the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

Hi mike_asplin,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

Hi mike_asplin,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

Hi mike_asplin,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

Hi mike_asplin,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

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.