Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Extract specific text from a column

Hello,   I have a column with some values    Exemple :    P1,A,P2 P2,B,C,P1 P3,A,P1,D,E,G   Etc ...   I only want to extract P1, P2 and P3 (the result of what i want to extract is known) ...
Need Help
Tips and Tricks
Tutorial Requests
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

You can use the List.Intersect function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjDUcdQJMFKK1QGyjXScdJx1AgwhPGOQjKGOi46rjrtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Extract", each 
        Text.Combine(
            List.Intersect(
                {Text.Split([Column1],","),{"P1","P2","P3"}})
            ,","),type text)
in
    #"Added Custom"