Forum Discussion

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper 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
v-lgarikapat's avatar
v-lgarikapat
Icon for Community Support rankCommunity Support
1 year ago

Hi mike_asplin ,

 

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

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


Best Regards,
Lakshmi.

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper V
1 year 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
    Icon for Helper V rankHelper V
    1 year ago

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

    • mike_asplin's avatar
      mike_asplin
      Icon for Helper V rankHelper V
      1 year 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
      Icon for Community Support rankCommunity 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
        Icon for Helper V rankHelper 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.