Forum Discussion

TonyACT's avatar
TonyACT
Frequent Visitor
4 years ago
Solved

Not Like function

Hi All, I’m new here and hope someone can help. My problem is that I have an imported file with 150 plus columns with certain strings (in a separate table) I need to pull out. Unfortunately, the co...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can do this in a single step with a custom column.

    Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|")

     

    Full sample query you paste into the Advanced Editor:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslIVVBITixRKE4sUdJRcslPB5K5lQoZ+aXFqUqxOtFQofyy1CKFxLwUBRADyHcGKkfIOoM1+1YqpCcWpaTmgWU88ouAJsDkXPKB6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|"), type text)
    in
        #"Added Custom"