cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pedro503
Resolver I
Resolver I

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

Pedro503_0-1666327174660.png

 

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

Pedro503_1-1666327344714.png

 

Any thoughts on how to use a DAX alike function TODAY() in advanced editor?

 

Thanks in advance.

8 REPLIES 8
v-polly-msft
Community Support
Community Support

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-polly-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
Super User
Super User

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Thanks for your reply, amitchandak.

 

Any thoughts on why this happened?

 

Pedro503_0-1666329746226.png

 

Pedro503_1-1666329967912.png

 

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Thanks for your response.

 

The following message shows up:

Pedro503_0-1666359036047.png

 

 

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.