Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

text to date format

Hello, 

I have this text field containing date as text, I want to properly format it as a date using M language in query Editor.
Can anyone help me with it ?



thank you.

  • Split first your column.

    Then Merge them with space then change data type to Date Time.

     

    Here's a guide

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUD04t0DcyMLS0MjC1MgGSlkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column4.2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column4.2", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column4.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column4.2.1", "Column4.2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column4.2.1", type date}, {"Column4.2.2", type time}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column4.2.1", type text}, {"Column4.2.2", type text}}, "en-GB"),{"Column4.2.1", "Column4.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
    in
        #"Changed Type2"

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion

    Split first your column.

    Then Merge them with space then change data type to Date Time.

     

    Here's a guide

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUD04t0DcyMLS0MjC1MgGSlkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column4.2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column4.2", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column4.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column4.2.1", "Column4.2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column4.2.1", type date}, {"Column4.2.2", type time}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column4.2.1", type text}, {"Column4.2.2", type text}}, "en-GB"),{"Column4.2.1", "Column4.2.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
    in
        #"Changed Type2"
    • Anonymous's avatar
      Anonymous
      Not applicable

      thank you but I already tried that, it doens't work