Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Select Rows in Table contained in a list

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 effi...
Need Help
  • HotChilli's avatar
    HotChilli
    3 years ago

    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], _))))

     

HotChilli's avatar
HotChilli
Community Champion
3 years ago

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's avatar
ValeriaBreve
Post Partisan
3 years ago

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