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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.