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

Hi mike_asplin 

As you are using odata, it is possible to acheive that

You can find more information under the following link:
https://www.odata.org/getting-started/basic-tutorial/

But to achieve that, you can edit your power query with the following approach:

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-ddTTHH: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

Just to be sure to call the date Parameter to Dateparameter and replace the url by the right url and it should work

Here is where you can input your SQL statement

The statement will be something like

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

FROM

bookableresourcebookings

WHERE

              the condition on the start time and a final ;

here you find examples to build your syntax

https://www.w3schools.com/sql/sql_examples.asp

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI