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

Sorry i dont understand I'm in power BI desktop?

 

using this query

let

    Url = "https://chaxxxxx.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(Dateparameter, "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],
    Data = OData.Feed(bookableresourcebookings_table, null, ODataQueryOptions)

in
    Data

 with the extra T removed I Get this error. If i remove the Data = line i get a table but it includes everythnig so this Data = line is obviously critical. 

 

 

v-lgarikapat's avatar
v-lgarikapat
Community Support
11 months ago

 
Hi mike_asplin , apologies for the late reply.


Could you please try the workaround suggested below?
You don’t need to call OData.Feed twice. The first call already pulls the full dataset. Instead, you should apply filtering directly in the initial OData.Feed call using the query options.


let
Url = "https://chaxxxxx.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(Dateparameter, "yyyy-MM-ddTHH:mm:ssZ"),
FilterQuery = "$filter=starttime ge " & FilterDateText,
QueryOptions = "$select=" & SelectColumns & "&" & FilterQuery,

Source = OData.Feed(Url & "?" & QueryOptions, null, [Implementation="2.0"])
in
Source

 

 

 

Best Regards,

Lakshmi.

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    11 months ago

    Hi mike_asplin ,

     

    We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.

     

    We appreciate your engagement and thank you for being an active part of the community.

     

    Best Regards,

    Lakshmi