Forum Discussion
Extract dates from text field
Hi,
How would I go about removing all text apart from the date values from this column below?
Any help would be greatly appreciated.
5 Replies
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoysxRMDIwMlaK1YFxjQ10QEImmEJmCuWZJRkKefkKrsH6oT6OYBVepXkweUOwgG9iUXKGgrEhWMgIqiYHLgAxoyAxMwVoio4C0BgdhfwiBVenYIXyxMyy1CKIvaXpUB2GZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ to = Text.Combine (List.Transform (List.FirstN( Text.Split([DATA]," "),3), (x)=> Text.Replace(x,",","")),".") ][to]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type1"Hi, Thanks very much for the above code, how would I go about using it as the last step in the steps below.
here are the steps you need
[to = Text.Combine (List.Transform (List.FirstN( Text.Split([DATA]," "),3), (x)=> Text.Replace(x,",","")),".") ][to]
Simple enough if the format of your data is consistent.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoysxRMDIwMlaK1YFxjQ10QEImmEJmCuWZJRkKefkKrsH6oT6OYBVepXkweUOwgG9iUXKGgrEhWMgIqiYHLgAxoyAxMwVoio4C0BgdhfwiBVenYIXyxMyy1CKIvaXpUB2GZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA", type text}}), #"Extracted Date" = Table.AddColumn(#"Changed Type", "Date", each Date.From(Text.BeforeDelimiter([DATA], " ", 2))) in #"Extracted Date"Hi, Thanks very much for the above code, how would I go about using it as the last step in the steps below.
