Mass transform different formats of time data in a column --- using power query

[yxz152830]

Hi Gurus,
I have a dataset with a column of time data that looks like this:

basically it contains two forms of time I know how to tranform into one format in excel with additional column but i heard power query can process data much faster. Thank you!

 

[alex78]

Hi @yxz152830,

An attempt

let
A = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
B = {"Column1", "Column2"}, // Columns to be modified
C = Table.TransformColumns(A, List.Transform(B, each {_, each ((x)=> try #date(x{2},x{0},x{1}) otherwise #date(x{2},x{1},x{0}))
    (List.Transform(List.FirstN(Text.SplitAny(Text.From(_), "-/ "),3), Number.From))})) in C

Regards,

 

[alex78]

Sorry, I didn't understand.

let
A = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
B = {"Column1", "Column2"}, // Columns to be modified
C = Table.TransformColumns(A, List.Transform(B, each {_, each ((x)=> #date(x{2},x{1},x{0}))
    (List.Transform(List.FirstN(Text.SplitAny(Text.From(_), "-/ "),3), Number.From))})) in C

Regards,

 

[GraH]

All UI based solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}})
in
    #"Changed Type1"

Book1
	C	D	E	F
2	Date			Date
3	2/04/2016 12:00:00 AM			2/04/2016
4	9/07/2016 12:00:00 AM			9/07/2016
5	27-02-2016			27/02/2016
6	16-01-2016			16/01/2016
7	18-02-2017			18/02/2017
Sheet1