Forum Discussion
Problem with Numbers and Dates
Hey there,
My Data looks Like this:
Date
| 5052021 |
| 7052021 |
| 7052021 |
| 5052021 |
| 7052021 |
| 7052021 |
| 10052021 |
The format should then look like this after the transformation with hopefully a m function: dd.mm.yyyy.
Please Help!
Thanks yall!
To change the number to date, please copy and past the following code into a blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwNTIwMlSK1YlWMsfBJkaNoQGMEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Split Column by Position" = Table.SplitColumn(Source, "Date", Splitter.SplitTextByPositions({0, 4}, true), {"Date.1", "Date.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}}), #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date.1", type text}}, "zh-CN"), "Date.1", Splitter.SplitTextByPositions({0, 2}, true), {"Date.1.1", "Date.1.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Date.1.1", Int64.Type}, {"Date.1.2", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Date.1.1", type text}, {"Date.1.2", type text}, {"Date.2", type text}}, "zh-CN"),{"Date.1.1", "Date.1.2", "Date.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Date"), #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}) in #"Changed Type2"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
2 Replies
Here is one way to do it. Add a custom column and put this expression in the pop-up box.
= let paddedtext = Text.PadStart([Date], 8, "0") in Text.Combine({Text.Start(paddedtext,2), ".", Text.Middle(paddedtext,2,2), ".", Text.End(paddedtext, 4)})
Pat
To change the number to date, please copy and past the following code into a blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwNTIwMlSK1YlWMsfBJkaNoQGMEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Split Column by Position" = Table.SplitColumn(Source, "Date", Splitter.SplitTextByPositions({0, 4}, true), {"Date.1", "Date.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}}), #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date.1", type text}}, "zh-CN"), "Date.1", Splitter.SplitTextByPositions({0, 2}, true), {"Date.1.1", "Date.1.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Date.1.1", Int64.Type}, {"Date.1.2", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Date.1.1", type text}, {"Date.1.2", type text}, {"Date.2", type text}}, "zh-CN"),{"Date.1.1", "Date.1.2", "Date.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Date"), #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}) in #"Changed Type2"Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
