Date format

Question

Date format

Alex Wong 21

Hi all,

my data original input the date format as text "dd.mm.yyyy", I have tried to change to Data type by using "Date" function, it came out as the format "m/dd/yyyy", and I also tried to use "Using Locale..." function to change the date format, no matter how I change the region (US, UK, HK....), the out come only came out as the format "m/dd/yyyy", but I need to have the format "dd/mm/yyyy", may someone could help me on this issue, thank you.

Thanks
Alex

1 answer

Your answer

Answer 1

Lz._ 9,016

Hi @Alex Wong

Could you try the following and if this doesn't work let me know what your System Regional Settings are

let  
    // Table for demo  
    Source = Table.FromColumns(  
        { {"01.01.2021","01.02.2021"} },  
        type table [Date=text]  
    ),  
    //  
    ReplacedValue = Table.ReplaceValue(Source, ".", "/", Replacer.ReplaceText, {"Date"}),  
    ChangedType = Table.TransformColumnTypes(ReplacedValue, {{"Date", type date}})  
in  
    ChangedType

Alex Wong 21 Reputation points

2021-05-14T12:42:19.663+00:00

Dear @Lz._ , thank you for your help, I have tried but its doesn't work.....the "Date column is the original data I have extracted from SQL sever, and the "Custom.Date" is the result with the following I have added below, and my System Regional language and date/time setting as below, thank you again of your kind assistance.

let
// Table for demo
Source = Table.FromColumns(
{ {[Date]} },
type table [Date=text]
),
//
ReplacedValue = Table.ReplaceValue(Source, ".", "/", Replacer.ReplaceText, {"Date"}),
ChangedType = Table.TransformColumnTypes(ReplacedValue, {
{"Date", type date}})
in
ChangedType
Lz._ 9,016 Reputation points

2021-05-14T13:04:45.127+00:00

OK @Alex Wong

With your System Regional Settings = US where the date format is mm/dd/yyyy, Dates will always display as mm/dd/yyyy in the Power Query Editor (except if your change your System Regional Settings to i.e. UK)

So the question is: what do you need to do in PQ once you get a Date value from the Text value?
Alex Wong 21 Reputation points

2021-05-14T13:24:40.063+00:00

Dear @Lz._ , well noted and I have changed the system date format, it gives me what I want, thank for your help again.
Lz._ 9,016 Reputation points

2021-05-14T13:36:04.357+00:00

OK @Alex Wong . So, if topic closed would you mind Accepting as answer to help others please? Thanks

Share via

Date format

1 answer

Your answer