Changing Column Type from 5 digit Number to date

[Anonymous]

I have imported data from a csv file and one column is returning in power BI a five digit number ie. 091221 when it should be the date 9/12/21. When I try to change the type to date I get the error 'We couldn't parse the input provided as a Data value'. Please help.

[Anonymous]

You could also use two Text.Insert functions to insert the "/", then use parse or Date.From to make it a proper date column.

= Table.TransformColumns(Table, each Text.Insert([Date], 2, "/"))

Repeat this step but change the offset from 2 to 4, and of course refer to your last step. That should give you 09/27/2021, and lots of functions can take it from there.

--Nate

[AlexisOlson · Accepted solution]

One possible method is to split the column every two characters and then recombine them into a new custom column #date(2000+[Year], [Month], [Day]).

Here's a sample query you can paste into the advanced editor to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrA0NDIyVIrViVYyNDKwMDIAMw0MDQxBzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(2), {"Month", "Day", "Year"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month", Int64.Type}, {"Day", Int64.Type}, {"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #date(2000+[Year],[Month],[Day]), type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"})
in
    #"Removed Other Columns"

[Anonymous]

Was hoping there was an easier way but that works! Thank you. 

[AlexisOlson]

You can do it in one step if you're willing to write a bit more complicated transformation function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrA0NDIyVIrViVYyNDKwMDIAMw0MDQxBzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Text to Date" = Table.TransformColumns(Source,{{"Column1", each
        #date(
            Number.FromText(Text.Start(_, 2)) + 2000,
            Number.FromText(Text.Middle(_, 2, 2)),
            Number.FromText(Text.Middle(_, 4, 2))
        ), type date}})
in
    #"Text to Date"