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"
AlexisOlson
Super User
Super UserOne 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.
- 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"