Problem with Numbers and Dates

[PowerBIAdept1]

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!

[Anonymous · Accepted solution]

@PowerBIAdept1 

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.

[mahoneypat]

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