Forum Discussion
Anonymous
6 years agoNot applicable
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
- mussaendaCommunity 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" - kentylerSolution Sage
- Date.FromText("2010-02-19") // Date, yyyy-MM-dd
- AnonymousNot applicable
thank you but I already tried that, it doens't work