Forum Discussion
Solved
Extracting multiple date formats from text column in Power query
Hello:
I have a text column within which the date can be found. However, the format of the date varies from yyyy-MM-dd, yyyy/MM/d, dd/MM/yyyy, MM-yyyy, yyyy etc. How can only the date be extracted from the column into a new column? I am interested only in the year. e.g.
Liz Coleman Online 12/12/2017 Notebooks Pencils
Candy Shanner-Mail 09/2009- Folders
Thanks in advance.
You split the date string by various delimiters, and select the one with four digits: (if you have additional delimiters, edit the Text.SplitAny function accordingly.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNU1MFKK1YFw9Q1M9SE8AyMwGygG4ZrqwtkQRiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), //Type "Date" as text #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), //year will be the segment with four digits #"Add Year" = Table.AddColumn(#"Changed Type","Year",(r)=> List.Select(Text.SplitAny(r[Date],"-/"), each Text.Length(_)=4){0}, Int64.Type) in #"Add Year"
1 Reply
You split the date string by various delimiters, and select the one with four digits: (if you have additional delimiters, edit the Text.SplitAny function accordingly.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNU1MFKK1YFw9Q1M9SE8AyMwGygG4ZrqwtkQRiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), //Type "Date" as text #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), //year will be the segment with four digits #"Add Year" = Table.AddColumn(#"Changed Type","Year",(r)=> List.Select(Text.SplitAny(r[Date],"-/"), each Text.Length(_)=4){0}, Int64.Type) in #"Add Year"