cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors