Forum Discussion
How to adjust date to desired format in Power Query?
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.
Once I loaded my raw data into PQ, it is turning into DD/M/YYYY hh:mm:ss
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.
Select Date/Time for date format-preview
Select 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.
You Should be able to have this as shown.
You Should be able to have this
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.
Once I loaded my raw data into PQ, it is turning into DD/M/YYYY hh:mm:ss
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.
Select Date/Time for date format-preview
Select 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.
You Should be able to have this as shown.
You Should be able to have this
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")SergeiBaklan This worked beautifully! Thank you for the code suggestion. Is there a way to convert the time aspect to 24 hour time?
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
but
More about available formats is here Custom date and time format strings | Microsoft Docs
