Forum Discussion
Helper IIHow to filter a list based on select items in list?
Hey all,
I have a column full of a list of values written like the following table below. They are manually entered, can contain words like 'and', and have spaces.
| Val1 | ColumnOfInterest |
| One | 1,2,3,4,5,6,7,8,9, 10 |
| Two | 2,4,6,8,10 |
| Three | 1,A,2,B,3, and C |
| Four | 1,10, 11 |
| Five | 1, 12, 10, 13 |
| Six | A,B,C,D,E |
| Seven | 1,2,4,5 |
I have a request to make a way to filter to each option in said table with a slicer. For example, someone wants to use a dropdown slicer to select '3' and 'A', the only rows showing up in the visual should be the ones with Val1 values of 'One', 'Three', and 'Six'.
I was wondering if there was a simple solution for making this?
I'd assume I have to make a custom table containing just the records that COULD be in here, since those are, to my knowledge, pre set, but not sure where I would go from there. Issues with numbers like 1 appearing in numbers like 12 and 10 and 11 draw concerns, not sure how to get around that.
If it is helpful any, I am getting this data from a SQL database and can alter SQL queries to adjust the data there or add additional columns before relying just on PBI DAX and PowerQuery.
Any and all advice is greatly appreciated.
I think you may need to shred the list of values where there is a single row for every Val1 value and each of its Column of Interest. values. Is there a "to rows" type function in Power Query that could do that? You may need to strip out noise words like "and" and spaces. Lord help you if someone does not use that annoying Oxford comma and puts "1, 2 and 3" instead of "1, 2, and 3"
If not, you can brute-force the T-SQL with a function that does it. Now your filter table is things like:
One, 1
One, 2
One, 3
etc,
Now, join that table to the fact table on a many-to-many join. Filter for Val1 and it should work.
4 Replies
- ToddChitt
Super User
I think you may need to shred the list of values where there is a single row for every Val1 value and each of its Column of Interest. values. Is there a "to rows" type function in Power Query that could do that? You may need to strip out noise words like "and" and spaces. Lord help you if someone does not use that annoying Oxford comma and puts "1, 2 and 3" instead of "1, 2, and 3"
If not, you can brute-force the T-SQL with a function that does it. Now your filter table is things like:
One, 1
One, 2
One, 3
etc,
Now, join that table to the fact table on a many-to-many join. Filter for Val1 and it should work.
- Ashish_Mathur
Hi,
I'd suggest that you split data by rows in PQ. This M code works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY6xDoJAEER/5bL1FOwhKCUgtBbaXa4w8RJojoRE9PMd7rDY5r2dyTgntxgEorAocUKFGmdc0MBoIR5OHp+F3tLV5H84rSHHWgY7Rs0zvkyf5Li81+S0YItmNm/536jdq3llEvf5S96yo8cVQ2ZhC/EYxUni/Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Val1 = _t, ColumnOfInterest = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"ColumnOfInterest", Splitter.SplitTextByAnyDelimiter({",","and "}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ColumnOfInterest"), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"ColumnOfInterest", Text.Trim, type text}}), #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each [ColumnOfInterest] <> null and [ColumnOfInterest] <> "") in #"Filtered Rows"Hope this helps.
v-hashadapuCommunity Support
Hi jarwest , Thank you for reaching out to the Microsoft Community Forum.
We find the answers shared by Ashish_Mathur and ToddChitt are appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily
Thank you Ashish_Mathur & ToddChitt for your valuable response.
- v-hashadapu
Hi jarwest , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
