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.
Hey guys,
I'm pulling data out of a POST API, and one of the requested headers is the date period that will be retrieved. Here's what I've got so far:
let
url = "https://my.url.com/Report/SomeReport",
body = "{""DtInicio"": ""2022-10-01"",""DtFim"": ""2022-10-21""}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXXXX-8784"],
Content = Text.ToBinary(body)]))
in
#"Source"
The above code runs just fine, however I must keep the date contained on the body variable manually updated, and of course it would be a way better to have it updated automatically. I've tried doing some codes, but none of them works properly. Here's some of the code I've authored:
let
url = "https://my.url.com/Report/SomeReport",
hoje = Date.ToText(DateTime.LocalNow(), "yyyy/mm/dd", type text),
body = "{""DtInicio"": ""2022-10-01"",""DtFim"": "& hoje &"}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXXXX-8784"],
Content = Text.ToBinary(body)]))
in
#"Source"
but when i use the above piece of code, the following message error pops up
And when I use this one
let
url = "https://my.url.com/Report/SomeReport",
hoje = DateTime.LocalNow(),
body = "{""DtInicio"": ""2022-10-01"",""DtFim"": "& hoje &"}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXXXX-8784"],
Content = Text.ToBinary(body)]))
in
#"Source"
the following error message is shown
Any thoughts on how to use a DAX alike function TODAY() in advanced editor?
Thanks in advance.
Hi @Pedro503 ,
Please have a try.
let
url = "https://my.url.com/Report/SomeReport",
body = "{""DtInicio"": Date.FromText("2022-10-01"),""DtFim"": DateTime.LocalNow()}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXXXX-8784"],
Content = Text.ToBinary(body)]))
in
#"Source"
The ""2022-10-01"" returns a text value instead of date type.
So it returns the error.
Date.FromText - PowerQuery M | Microsoft Learn
DateTime.LocalNow - PowerQuery M | Microsoft Learn
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, Polly.
When I used this code you provide I got this error message:
Token comma expected
body = "{""DtInicio"": Date.FromText("2022-10-01"),""DtFim"": DateTime.LocalNow()}",
I tried myself tweak this not to get the error, but this new message pops up:
inicio = Date.FromText("2022-10-01"),
hoje = DateTime.LocalNow(),
body = "{""DtInicio"": " & inicio & ",""DtFim"": " & hoje & "}",
Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&
Left={"DtInicio":
Right=10/1/2022
Just a remark: even if I don't use the clauses inicio and hoje, the very same error appears.
Your solution seems to work, but I cannot work through this problem.
Thanks for your response.
Hi @Pedro503 ,
Please have a try.
let
url = "https://my.url.com/Report/SomeReport",
body = "{""DtInicio"": #date(2022,10,1),""DtFim"": DateTime.LocalNow()}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXXXX-8784"],
Content = Text.ToBinary(body)]))
in
#"Source"
Check the value type.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey, @v-rongtiep-msft . Once again, thanks for your help.
I've replicated this code you provided me, but this error message pops up:
Details:
statusCode=500
mensagem=Unexpected character encountered while parsing value: #. Path 'DtInicio', line 1, position 13.
I've also tried to insert this symbol &, but it also bears in error:
body = "{""DtInicio"":" & #date(2022,10,1) & ",""DtFim"":" & DateTime.LocalNow() & "}",
Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&
Left={"DtInicio":
Right=10/1/2022
Do you know what else can be done to make this error message disappear?
@Pedro503 , Please try this
current date
Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")
Month Start
Date.ToText(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), "yyyy-MM-dd")
Thanks for your reply, amitchandak.
Any thoughts on why this happened?
let
url = "https://my.url.com/Report/SomeReport",
hoje = Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"),
body = "{""DtInicio"": ""2022-10-01"",""DtFim"": "& hoje &"}",
Source = Json.Document(Web.Contents(
url,
[Headers = [#"Content-Type"="application/json", #"Authorization"="XXXX-8784-XXXXX-8799-XXXXXXXXXXX"],
Content = Text.ToBinary(body)]
)
),
result = Source[result]
in
#"Renamed Columns"
@Pedro503 , it is checking for numbers
may be like
Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyyMMdd")
Thanks for your response.
The following message shows up:
When I used the code with slashs, I got the following error
Code:
...
Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy/MM/dd")
...
Error message:
“Error parsing comment. Expected: *, got 1. Path 'DtFim', line 1, position 40.”
Is it possible to create a function and use this created function into my code? I'm a total newbie on Power Query's M language, so it's just a guess.
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.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |