Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
1 year ago

Help using SQl to limit the data being pulled form Microsoft Dynamics by Odata

I current have a model that is pulling whole tables of data form Microsoft Dynamics anbd then doing al lthe filtering in Power query.  Is therer a way to do the filtering at source like you woudl wit...
Need Help
mike_asplin's avatar
mike_asplin
Helper V
11 months ago

Took this step out and works but extremely slow and in fact crashed 

mike_asplin's avatar
mike_asplin
Helper V
11 months ago

Also this query does not just return the columns listed but all columns

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"),
    FilterQuery = "$filter=starttime ge " & FilterDateText,


    ODataQueryOptions = [
        Query = "$select=" & SelectColumns & "&$" & FilterQuery
    ],

       Source = OData.Feed(Url, null, [Implementation="2.0"]),
    bookableresourcebookings_table = Source{[Name="bookableresourcebookings", Signature="table"]}[Data]
    
in
    bookableresourcebookings_table

I deleted this line as was getting the error, but maybe that was critical to get the right columns. also date filter isnt doing anything. 

 

Data = OData.Feed(bookableresourcebookings_table, null, ODataQueryOptions)