SOLVED

How to adjust date to desired format in Power Query?

Copper Contributor

System date format is DD/M/YYYY hh:mm:ss  which is 25/1/2021 14:08:00

How could I edit in Power Query to make it as what we want?

I would like to change into YYYY-MM-DD hh:mm:ss  where it should be 2021-01-25 14:08:00

Thank you very much!

Rachael_Tsang_0-1611570674755.png

 

 

10 Replies

@Rachael_Tsang 

Power Query shows data in accordance to locale settings. You may change it here

image.png

@Sergei Baklan 

Indeed I browsed the previous questions asked by other persons they had mentioned your suggestion as well. Not work for me.

Already selected  English (Canada) but the format is not changing.

Rachael_Tsang_0-1611570496587.png

Rachael_Tsang_1-1611570529280.png

 

 

@Rachael_Tsang 

To be sure, could you please share screenshot with Power Query locale setting?

@Sergei Baklan 

 

Sorry my power query is in Chinese. 

 

I did selected [English (Canada) ]as the locale. 

Rachael_Tsang_1-1611626501426.png

 

Rachael_Tsang_0-1611626446486.png

best response confirmed by Rachael_Tsang (Copper Contributor)
Solution

@Rachael_Tsang 

I found out the solution my own and would like to share with other people who had the same question. It is pretty simple after I noticed the function. You dont need to use any PQ formula actually. 

 

So here is my raw data with date format as DD MMM YYYY hh:mm

where I would like to change it into YYYY-MM-DD hh:mm:ss 

then I am going to split it into 2 columns which is Update Date and Update Time

meaning it is should be 1 columns is YYYY-MM-DD while another is hh:mm:ss.

Rachael_Tsang_0-1611629741188.png

 

Once I loaded my raw data into PQ, it is turning into DD/M/YYYY hh:mm:ss

1.JPG

Click the calendar and click the last option [Use region setting] 

*I am using a Chinese version so the translation is made by myself, it might not be the same as your default language. 

擷取.JPG

 

Select Date/Time for date format-preview

Select Date/Time for date format-previewSelect Date/Time for date format-previewSelect Date/Time for date format-previewSelect Date/Time for date format-preview

 

 

Once you pick the desired format. Change the upper section [Data Type] : Text

Then select again the one you have chosen. 

4.jpg

 

 

You Should be able to have this as shown. 

You Should be able to have thisYou Should be able to have this

@Rachael_Tsang 

Yes, but resulting [createTime] column contains texts, not datetime. Try to apply DateTime format to it.

 

If you need text, not datetime, you may create custom column with formulas like

Text.From([updateTime], "mgo-CM")

or better

DateTime.ToText([updateTime], "yyyy-MM-dd HH:mm:ss") 

 

@Sergei Baklan This worked beautifully! Thank you for the code suggestion. Is there a way to convert the time aspect to 24 hour time?

@JoeJitsu 

If you use DateTime.ToText(...) you may define any desired format, otherwise it is as regional format for the selected locale or culture.

If for hours "hh" it will be 12-hours clock, with "HH" - 24-hours clock. "tt" at the end adds AM/PM.

For example

image.png

but

image.png

More about available formats is here Custom date and time format strings | Microsoft Docs

Awesome! You are totally right and I didn't catch that. Thanks so much for this solution sir!

@JoeJitsu , you are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by Rachael_Tsang (Copper Contributor)
Solution

@Rachael_Tsang 

I found out the solution my own and would like to share with other people who had the same question. It is pretty simple after I noticed the function. You dont need to use any PQ formula actually. 

 

So here is my raw data with date format as DD MMM YYYY hh:mm

where I would like to change it into YYYY-MM-DD hh:mm:ss 

then I am going to split it into 2 columns which is Update Date and Update Time

meaning it is should be 1 columns is YYYY-MM-DD while another is hh:mm:ss.

Rachael_Tsang_0-1611629741188.png

 

Once I loaded my raw data into PQ, it is turning into DD/M/YYYY hh:mm:ss

1.JPG

Click the calendar and click the last option [Use region setting] 

*I am using a Chinese version so the translation is made by myself, it might not be the same as your default language. 

擷取.JPG

 

Select Date/Time for date format-preview

Select Date/Time for date format-previewSelect Date/Time for date format-previewSelect Date/Time for date format-previewSelect Date/Time for date format-preview

 

 

Once you pick the desired format. Change the upper section [Data Type] : Text

Then select again the one you have chosen. 

4.jpg

 

 

You Should be able to have this as shown. 

You Should be able to have thisYou Should be able to have this

View solution in original post