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

Hi sorry I'm getting same error . The step above seems to produce a table so maybe this step isnt needed? 

 

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)

     

     

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

    Hi mike_asplin ,

     

    Could you please try the troubleshooting steps mentioned below and let us know how it goes? We’ll be happy to assist you further

     

     

    • Refresh the page 

    • Open it in a new tab or window

    • Restart your browser

    • Clear your cache and cookies

    • Disable extensions temporarily

    • Try accessing the site from another browser (like Chrome or Firefox)

     

    Best Regards,

    Lakshmi.

     

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