Forum Discussion
Defining today's date on advanced editor
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.
8 Replies
Anonymous , 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"Anonymous , it is checking for numbers
may be like
Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyyMMdd")
Hi Anonymous ,
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/2022Just 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 Anonymous ,
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.
