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...
Need Help
  • 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"
TonyACT's avatar
TonyACT
Frequent Visitor
4 years ago

Thanks for the reply. I can't give example data due to the nature of the data and my employer, sorry. An example of what I am after would be, if I had a table that was say 150 columns and 150 rows and wanted to clear all 'cells' that do not match a list of words, how would I do it. In Excel I could use a formula to say <> or VBA to loop through and remove anything not on the list. In query I can't seem to find the equivalent.

After doing this I could then merge all columns and the result would be text separated by a “|” .I hope this makes more sense.

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
4 years ago

You could create a dummy data set that illustrates the problem; and also shows exactly what you expect for a result.