I want to extract word which matches a pattern like ‘RXXXXX’ from a text column into a new column. The word starts with letter ‘R’ and contains alphanumeric characters and the delimiter can be ‘;’ or nothing.The word length can vary. Below is the sample input and output required
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlGK1YlWCjIx0jNU8HWMNDQ2MrF29AjVDQnKcXR3hUoaQySNgJJgEYQCaxRJ65CMVAWfTKihuFQpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a = Text.Split([Input], ";"),
b = List.Select(a, each Text.StartsWith(_,"R"))
in b{0}?)
in
#"Added Custom"