Select Rows in Table contained in a list

[ValeriaBreve]

Hello,

I have a column from a table that I need to filter. It is a txt column and I only need to keep the values if contained in a specified list ({"Jan","Feb","Mar"...})

How can I do it in an efficient way? 

Thanks! 🙂

Kind regards

Valeria

[HotChilli · Accepted solution]

Quick response. You are looking for a text search , not a direct match from the items in a list.

This will return a true/false

Table.AddColumn(#"Changed Type", "Is a Month Contains", 
(x) => List.AnyTrue(List.Transform(ListMonth, each Text.Contains(x[Attribute], _))))

[ValeriaBreve]

Clear thanks! So I need to go through adding a column first, and then filter that column for a true condition. Thanks!

[HotChilli]

Post some sample data please because the code is accurate.

[ValeriaBreve]

Hello, here it is a sample query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU3DLL0pNTiwuUdJRMlSK1YlWCshJzANyjIzBPLfUJGQlJmDBpJxEIAJyTcFc38QiZDVmYEHHAhRBS4jhzvk5pbl5xsZAEWOgulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
ListMonths = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},

Filtering= Table.SelectRows( #"Changed Type" , each List.Contains(ListMonths,[Attribute]))

in
Filtering

with the last step giving me the blank rows.

The expected result is to have only rows containing months abbreviations in [Attribute] left after filtering...

[AlienSx]

Hello, @ValeriaBreve List.Contains gives you true only when your [Attribute] equals to one of the list items. Unfortunately PQ does not have a function like Text.ContainsAny. But you can create one yourself! Look at this code where Text.ContainsAny is a function that gives you true whenever any list item is found in your string. 

Another alternative would be to split your text by delimiter(s) and use List.ContainsAny function. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU3DLL0pNTiwuUdJRMlSK1YlWCshJzANyjIzBPLfUJGQlJmDBpJxEIAJyTcFc38QiZDVmYEHHAhRBS4jhzvk5pbl5xsZAEWOgulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
    ListMonths = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
    Text.ContainsAny = (string as text, lookup_list as list) =>
        List.AnyTrue(
            List.Transform(
                lookup_list, 
                each Text.Contains(string, _)
            )
        ),
    Filtering = Table.SelectRows( #"Changed Type" , each Text.ContainsAny([Attribute], ListMonths))
    // Filtering= Table.SelectRows( #"Changed Type" , each List.Contains(ListMonths,[Attribute]))
in
    Filtering

[HotChilli]

Use the dropdown in the column header to create a filter.      Select   text filters->Equals.  Then put in a dummy value.

It doesn't matter what, we just want the code to be generated so we can edit it. It will generate a Table.SelectRows line.

Then, in the formula bar replace the section of code after the 'each' with 

List.Contains({"Jan","Feb","Mar"}, [theNameofTheColumn])

Get the brackets right and that should work.

[ValeriaBreve]

@HotChilli Hello, no unfortunately it does not work... I had tried it... I get an empty table back when the values of the list are definitely contained in the column

This is my code:

= Table.SelectRows(Custom1, each List.Contains(ListMonths,[Attribute]))

where ListMonth is the list in question.

When I put this in a new column the result is indeed "false" for every row...

Thanks

Kind regards

Valeria