Change Date column from dd/mm/yyyy to yyyy/MM/dd

[Skybluekid]

Hi

I would like to change a date column in Power Query from dd/mm/yyyy to yyyy/MM/dd?

 

[RoryA]

Why do you need it changed in PQ? Surely the output table is where the formatting is needed?

 

[Skybluekid]

I guess so, just thought it would save a step afterwards. Is it possible though?

 

[GraH]

Maybe you can define the date date as text then use Text.Reverse([date as text])?
Edit: no, you cannot. What was I thinking? #sleepyhead morning...

Split on delimiter and combine the date Parts in the order you want them.

PS: you can't change a format in PQ, only a data type. As @RoryA said, that's done in the output. And you only need to do this once.

 

[djmartrump]

I convert it in SQL Server before I bring it in:
convert(datetime,'18-11-2019 00:00:00',105) --will return 2019-11-18 00:00:00.000

 

[GraH]

I convert it in SQL Server before I bring it in:
convert(datetime,'18-11-2019 00:00:00',105) --will return 2019-11-18 00:00:00.000

I guess it is still seen as date in PQ and the type "date" is applied, undoing your convert. Look for the #"Changed Type" step and update that field to text. Remember to confirm "Replace current".