Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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"
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"