Forum Discussion
Find dates in text
Use this in a custom column
= List.Max(List.Transform(List.Transform(Text.Split(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Data],".","/"),".","/"),"*","/"),"-"," ")," "),(x)=>Text.Select(x,{"0".."9","/"})),(y)=>if Text.Contains(y,"/") then try Date.From(y) otherwise null else null))Full code for testing
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/BbsMgEER/ZZVTGzUxBhts5VOsHMCwCQRjN+A2/vsSN1XV9tDjzL4Z7XTd5lX1UZuq7jWQck/4nhJKYQcsi/ouGPQRBzM6prXZHF+6jXc4ovKHCDVoqeWPIG2/ct5MowugFc4WBbSkaQPgo2KcHBqLFQJhO1I9MhQmG6K2s8qXK67o5MK7Bi+jZDJ/CqUoyqYoW7gMaEd04WpPquYr28uIEQ2r8JTO6Qq02RK6XUdEfdLnxAWPn60+zFy4YOf70pLuaQW3rNS8cKEu/rBSQ3Dnt1RzsajgM1iUtFjflEOcpY2Ki6YlK/okQ4J0S8/ASMGLzGRngWSy9Wvyf4u/q9q/TccP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Max(List.Transform(List.Transform(Text.Split(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Data],".","/"),".","/"),"*","/"),"-"," ")," "),(x)=>Text.Select(x,{"0".."9","/"})),(y)=>if Text.Contains(y,"/") then try Date.From(y) otherwise null else null))) in #"Added Custom"
In a custom column, pur following formula (here [Data] is column name) and select the column and Transform menu - Data type - Date
= List.Last(Text.Split(Text.Select(Text.Replace(Text.Replace([Data],".","/"),"*","/"),{"0".."9","-","/"}),"-"))For in place replacement of the column, insert following step where Source should be replaced with your previous step and don't forget to change column type to date
= Table.ReplaceValue(Source,each [Data], each List.Last(Text.Split(Text.Select(Text.Replace(Text.Replace([Data],".","/"),"*","/"),{"0".."9","-","/"}),"-")),Replacer.ReplaceValue,{"Data"})
Thank you Vijay_A_Verma
You did get me a step closer to the objectif. But still I need some steps. And I really appreciate your solution.
If in the txt I would not have also numbers your I would have been much closer. But as people inputing data are not the same, their inputs are really different.
In case you could have another tip, I would be grateful.
Thank you!
Use this in a custom column
= List.Max(List.Transform(List.Transform(Text.Split(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Data],".","/"),".","/"),"*","/"),"-"," ")," "),(x)=>Text.Select(x,{"0".."9","/"})),(y)=>if Text.Contains(y,"/") then try Date.From(y) otherwise null else null))Full code for testing
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/BbsMgEER/ZZVTGzUxBhts5VOsHMCwCQRjN+A2/vsSN1XV9tDjzL4Z7XTd5lX1UZuq7jWQck/4nhJKYQcsi/ouGPQRBzM6prXZHF+6jXc4ovKHCDVoqeWPIG2/ct5MowugFc4WBbSkaQPgo2KcHBqLFQJhO1I9MhQmG6K2s8qXK67o5MK7Bi+jZDJ/CqUoyqYoW7gMaEd04WpPquYr28uIEQ2r8JTO6Qq02RK6XUdEfdLnxAWPn60+zFy4YOf70pLuaQW3rNS8cKEu/rBSQ3Dnt1RzsajgM1iUtFjflEOcpY2Ki6YlK/okQ4J0S8/ASMGLzGRngWSy9Wvyf4u/q9q/TccP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Max(List.Transform(List.Transform(Text.Split(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Data],".","/"),".","/"),"*","/"),"-"," ")," "),(x)=>Text.Select(x,{"0".."9","/"})),(y)=>if Text.Contains(y,"/") then try Date.From(y) otherwise null else null))) in #"Added Custom"Vijay_A_Verma Thank you very much for your help!