Forum Discussion
Changing Column Type from 5 digit Number to date
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 '...
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"
Was hoping there was an easier way but that works! Thank you.
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"