How to change date to text without changing the format?

[AnkitaChatterje]

Hi,

I want to import certain jsons automatically from a URL and accordingly I have written a query in power BI Query Editor which incorporates the system date to fetch the current date json. The current date format should be "YYYY-MM-DD". Further, I have to convert the date to "text" format to construct the json URL, but it is changing the format to "MM/DD/YYY" which is causing an error while connecting to the URL.

Kindly suggest me a command which will convert my date to text without changing the format (i.e. "YYYY-MM-DD").

Here's the query that I have written so far :

[v-jiascu-msft]

Hi @AnkitaChatterje,

Please set up a proper value for "Regional Settings -> Locale for import". Please refer to the snapshot below.

Best Regards,

Dale

[AnkitaChatterje]

Hi,

Thanks for your suggestion. But this won't work in my case. In this case, I don't have a list of dates or a table with "Date" column. I have to use the current date (which I am extracting from system date) and further convert it to type text in order to fetch data from a API which is date dependent.

Here's the query that I have written so far :

let
     Date = Date.ToText( DateTime.Date(DateTime.LocalNow()) )

in

     Date

But the command "Date.ToText()" is changing the format of "Date" to "M/DD/YYYY" while I need to publish the "Date" in the format "YYYY-MM-DD" (exactly in this format).

Kindly suggest me a way to incorporate this in power query.

[v-jiascu-msft · Accepted solution]

Hi @AnkitaChatterje,

Actually, I used the same code with yours. Please refer to m/date-totext. Maybe we can add one more parameter to Date.ToText.

let
    Source = Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"),
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"

Please give it a try.

Best Regards,

Dale

[AnkitaChatterje]

Thank you....the last one solved my problem.