Forum Discussion
Post PartisanSelect 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 efficient way?
Thanks! 🙂
Kind regards
Valeria
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], _))))
7 Replies
- HotChilli
Community Champion
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.
ValeriaBreveHotChilli 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
- 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
Filteringwith 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
Super User
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
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!
