Defining today's date on advanced editor

[Pedro503]

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.

[v-rongtiep-msft]

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.

[Pedro503]

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.

[v-rongtiep-msft]

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.

[Pedro503]

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?

[amitchandak]

@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")

[Pedro503]

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"

[amitchandak]

@Pedro503 , it is checking for numbers

may be like

Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyyMMdd")

[Pedro503]

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.