Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I've been told by an engineer to move to the Dynamics 365 9.0 api for Power BI queries but I'm finding that I receive an error when filtering by date. I suspect it is a formatting issue because I can filter by text field but not by date.
I've tried this with various date formats but getting the error below:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq datetime'2018-01-01'")
Error
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2018-01-01'' at '13' in 'createdon gt datetime'2018-01-01''.)
Any chance someone can point me in the right direction?
In case it helps, the original query was: https://<tenant>.api.crm.dynamics.com/XRMServices/2011/Organization.svc/InvoiceSet?$filter=CreatedOn gt datetime '2018-01-01'
Mark
Solved! Go to Solution.
@Mark_Holtham,
Change your code to the following:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01 ")
Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx
Regards,
Lydia
@Mark_Holtham,
Change your code to the following:
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01 ")
Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx
Regards,
Lydia
Thank you, that worked. Thats the one variation I didnt try!
One version of this issue I still have though relates to cross table filtering by date. For example I have the similar date filtering requirement for saleorderdetails where I want to filter only the ones where the salesorder has a specific date.
Normally I would expand and then filter but this does not seem to be supported anymore.
Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01
Any chance you can point me in the right direction for this one as well?
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.