Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.