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

[mike_asplin]

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 with an SQL DB? does it speed things up?

My current query is

Source = OData.Feed("https://chaxxxxx.api.crm11.dynamics.com/api/data/v9.1/", null, [Implementation="2.0"]),
    bookableresourcebookings_table = Source{[Name="bookableresourcebookings",Signature="table"]}[Data],

but from this massive table i only want these columns

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

Ideally I would have a load parameter of 01/08/2025 00:00:00 and filter startime by this

If anyone can help be much appreciated as no idea where to start on the syntax.

Thanks 

[FBergamaschi]

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

[mike_asplin]

The data is not on a SQL server it sin Microsoft dynamics so i'm accessing via OData?

[Cookistador]

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

[mike_asplin]

Seems ot be taking for ever. Can I check there arent any typoes

Whats the ge for?

FilterQuery = "$filter=starttime ge " & FilterDateText,

Why is time surrounded by TT and Z?

    FilterDateText = DateTime.ToText(Dateparameter, "yyyy-MM-ddTTHH:mm:ssZ"),

Thanks

[v-lgarikapat]

Hi @mike_asplin , 

Thanks for reaching out to the Microsoft fabric community forum

@Cookistador , @FBergamaschi 

Thanks for your prompt response,

1.

FilterQuery = "$filter=starttime ge " & FilterDateText,

ge is one of the OData comparison operators, and it stands for:

ge = Greater than or Equal to

It’s the OData equivalent of SQL’s >=. So when you write:

text

$filter=starttime ge 2025-08-01T00:00:00Z

2. 

 FilterDateText = DateTime.ToText(Dateparameter, "yyyy-MM-ddTTHH:mm:ssZ"),

T

Time separator between date and time

2025-08-01T00:00:00

Z

Zulu time (UTC) indicator

2025-08-01T00:00:00Z

Open Data Protocol (OData) - Finance & Operations | Dynamics 365 | Microsoft Learn

We truly appreciate your continued engagement and thank you for being an active and valued member of the community.

If you're still experiencing any challenges, please don’t hesitate to reach out   we’d be more than happy to assist you further.

We look forward to hearing from you.

Best regards,
Lakshmi

[v-lgarikapat]

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.

[theferret]

Hi sorry on holiday in Brazil so will check when I get back next week

[v-lgarikapat]

Hi @mike_asplin ,

Thank you for the updates. Please continue posting them here they’ll be very helpful

Best Regards,

Lakshmi.

[mike_asplin]

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

[mike_asplin]

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

[v-lgarikapat]

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]

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]

 
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]

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

[mike_asplin]

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)

[mike_asplin]

Thats great and should I expect a big increase in speed?

[Cookistador]

Yes, I encountered a similar issue with a customer. It used to take a few minutes for the change to take effect, but now it only takes a few seconds.

The idea is to filter your data before importing it.

If you do not see an improvement, please post again and we will see how we can improve it.

[mike_asplin]

I took out the extra T and the ge bit as didnt look right 🙂

Getting an error on this step?