Transform the column format form Text >> Date/Time on PowerQuery

[NilsLam]

Hi All,

I required to transform the Date/Time format from raw data shown as Text: "20-Mar-16 1137" to "3/16/2020 11:37", tried to use M language as below but found failed with error message "The Date value must contain the Date component. Details: Mar".
My setting as below and how can I fix its. Thanks.


= Table.AddColumn(#"Removed Columns", "In Time", each DateTime.FromText(
Date.Year (Text.Combine({"21", Text.Middle([Unit Time In], 0,2)}),
Date.Month (Text.Middle([Unit Time In], 3, 3)),
Date.Day (Text.Middle([Unit Time In], 7, 2)),
Time.Hour (Text.Middle([Unit Time In], 10, 2)),
Time.Minute (Text.Middle([Unit Time In], 12, 2)))))

 

[AlanSidman]

I have reformatted in Power Query column A only as an example. You should be able to do this for each column.

let
    Source = Excel.CurrentWorkbook(){[Name="MoveEvent_20200316_T9__2"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Unit Time In", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Unit Time In.1", "Unit Time In.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Unit Time In.1", type date}, {"Unit Time In.2", type time}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Unit Time In.1", type text}, {"Unit Time In.2", type text}}, "en-US"),{"Unit Time In.1", "Unit Time In.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Time Carry Che Dispatch.1")
in
    #"Merged Columns"

 

[NilsLam]

Hi Alan,

Thanks for your assist, I've already used the other way to transform the Date/Time columns.

= Table.AddColumn(#"Removed Columns1", "Facility In Time", each Text.Combine
({"20", Text.Range([Unit Time In], 0,9), " " , Text.Range([Unit Time In], 10,2) , ":" , Text.Range([Unit Time In], 12,2)}), type datetime)