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 'We couldn't parse the input provided as a Data value'. Please help.
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"
4 Replies
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"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"
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