Refresh failing in PBI Service - Date.ToText syntax causing error?

[arekay]

Hi all,

I'm fairly new to Power Query, and am having trouble with a query that refreshes fine in PBI desktop, but fails when I upload it to our workspace.

The query makes a call to a REST API that takes a start and end date as parameters. The start date is static, but the end date needs to be dynamic to always take whatever today's date is when the code runs.

Here is the relevant section of my code.

let
    Source = (Page as number) =>

let
    Source = Json.Document(Web.Contents(
        "[MY API ENDPOINT]",
        [
            Query=
            [
                startdate = "2021-09-01",
                enddate = Date.ToText(Date.From(DateTime.LocalNow()),[Format = "yyyy-MM-dd"]),
                page_index = Number.ToText(Page)
            ],
            Headers = header, //define elsewhere, contains authentication keys for the API
            Content = Text.ToBinary("")
        ]
    ))

This works fine locally, but when I upload it to our workspace, I get an error. However, if instead of defining "enddate" dynamically, I just pass a static text string the same way I do for "startdate", everything works.

I know the "How to get your questions answered" post says don't post screenshots of error messages, but this one seems relevant so apologies in advance! This is what I get when I use the dynamic date

Obviously I'm not an expert, but the second and the last lines here look like they're saying that the problem is the format option in Date.ToText function. I can't test that because if I don't include that option in the code, the default result is dd/MM/yyyy, which the API I am connecting to won't recognise (it only accepts yyyy-MM-dd)

Is there another way to generate a dynamic date, with the format the API I'm accessing requires, without triggering this error?

Thanks in advance!

Ryan

[BA_Pete · Accepted solution]

Hi @arekay ,

Try changing your format argument like this:

enddate = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),

Pete

[arekay]

Thanks @BA_Pete ! That seems to have sorted it