Forum Discussion
Help using SQl to limit the data being pulled form Microsoft Dynamics by Odata
Hi mike_asplin ,
Thanks for reaching out to the Microsoft fabric community forum
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
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_asplin1 year ago
Helper V
Hi sorry I'm getting same error . The step above seems to produce a table so maybe this step isnt needed?
- mike_asplin1 year ago
Helper V
Took this step out and works but extremely slow and in fact crashed
- mike_asplin1 year ago
Helper V
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_tableI 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)
- theferret11 months agoFrequent Visitor
Hi sorry on holiday in Brazil so will check when I get back next week
- v-lgarikapat11 months ago
Community Support
Hi mike_asplin ,
Thank you for the updates. Please continue posting them here they’ll be very helpful
Best Regards,
Lakshmi.
- v-lgarikapat10 months ago
Community Support
Hi mike_asplin ,
Just following up to see if you’ve had a chance to look into the solution we provided and if there’s any feedback or questions.
Best Regards,
Lakshmi.