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.
Can't figure out the issue here, but it may have something to do with different rules regarding using one or two citation marks between US and EU. I am in Europe, and I am getting an "expected a comma here" error when using this line of code, to get some ODATA results:
OData.Feed("https://api.url/Report?$filter=Posting_Date ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),'yyyy-MM-dd')) "
, null, [Implementation="2.0"])
Can someone help? Thanks
Solved! Go to Solution.
Hi @phkGZ ,
Please set ODATA version to 4, and then use DateTimeOffset to query.
let
Source = OData.Feed("http://services.odata.org/V4/northwind/northwind.svc/Orders?$filter=OrderDate ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-300), "yyyy-MM-ddT23:59:59.99Z"), null, [ODataVersion=4])
in
Source
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @phkGZ ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
let
Source = OData.Feed("https://api.url/Report?$filter=Posting_Date ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),'yyyy-MM-dd')) "
, null, [Implementation="2.0"])
in
Source
Hi @phkGZ ,
Please try the following code.
let
Source = OData.Feed("https://api.url/Report?$filter=Posting_Date ge '" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd") & "'"
, null, [Implementation="2.0"])
in
Source
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Winniz, sorry for not replying, was on a quick vacation.
Tried your solution and I got a step further, now its complaining about datatypes. The way I read it is that I cannot use "greater than or equal" with PostingDate, is that correct?
DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThanOrEqual'. CorrelationId: 5e77805e-4973-4b5b-af16-c9323a6ef884.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThanOrEqual'. CorrelationId: 05233492-e120-40f7-99e8-023b747d8734.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
Details:
DataSourceKind=OData
Hi @phkGZ ,
Take uniconta api as an example, DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as
let
Source = OData.Feed("https://odata.uniconta.com/odata/GLTransSumClient?$filter=Date ge datetime'" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd") & "T00:00:00'" , null, [Implementation="2.0"])
in
Source
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-msft
Thanks for coming back to this! I copy pasted your code to make sure no typing errors snuck in, and got a new error this time, so I think we are close now!
This is the error I get:
DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2022-01-07T00:00:00'' at '16' in 'Posting_Date ge datetime'2022-01-07T00:00:00''. CorrelationId: 3e03fbab-a1e1-47af-976f-9a13c6779d0b.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2022-01-07T00:00:00'' at '16' in 'Posting_Date ge datetime'2022-01-07T00:00:00''. CorrelationId: b6bcc1c8-5cde-4e18-9c89-00997007477a.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
Details:
DataSourceKind=OData
DataSourcePath=xxxxx/Finansposter_Excel
Hi @phkGZ ,
Please set ODATA version to 4, and then use DateTimeOffset to query.
let
Source = OData.Feed("http://services.odata.org/V4/northwind/northwind.svc/Orders?$filter=OrderDate ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-300), "yyyy-MM-ddT23:59:59.99Z"), null, [ODataVersion=4])
in
Source
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi winniz!
Great, it definitely seems to work now - the code computes without error, and I can pull the data.
However I can't figure out the parameter you put in - you put in the number 300. If I use that, it selects all data I have in my source. I want to try and select the last 6 months, or there about.
If I put in "0" as a parameter, it takes today and forward in time.
If I put in "-1" as a parameter, it takes yesterday, and forward in time.
So that makes sense. So I figured if I put in "-10", it's going to take the last 10 days, and forward in time. But no - then it selects all data in my source, again.
HAH! Just writing this, I figured it out. "-2" gave me the last two months. So then "-6" gave me the desired result - from january 7th and forward.
AWESOME, thanks again 🙂
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.