Forum Discussion
Search Multiple Value in different columns
Hello,
I'll start by saying that I'm French so my English can be a little messy.
I have a table which contains one identifier column and 4 columns filled with different words like below:
| Id | Word1 | Word2 | Word3 | Word4 |
| 1 | Green | Wood | null | null |
| 2 | Sea | Green | Tree | null |
| 3 | Forest | Tree | Green | null |
| 4 | Red | null | null | null |
| 5 | Wood | Orange | Red | null |
| 6 | Blue | Green | Orange | Pink |
| 7 | Pink | Road | House | Tree |
| 8 | Wood | Tree | Forest | null |
I want to filter this table dynamically.
A first slicer that contains all unique words appearing in all columns
- Green, Wood, Sea, Tree, Forest, Red, Orange, Blue, Pink, Road, House
If i choose Green, it updates and displays the following table:
| Id | Word1 | Word2 | Word3 | Word4 |
| 1 | Green | Wood | null | null |
| 2 | Sea | Green | Tree | null |
| 3 | Forest | Tree | Green | null |
| 6 | Blue | Green | Orange | Pink |
After that i want to filter it again and again up to 4 times, to only keep the rows containing all the words i want.
One of the biggest problem is that the first word to select isn't only in the first columns.
The other problem is that i want a solution easy to use for users.
I've tried so many things but couldn't get a satisfactory result.
Any help is much appreciated. And Thank you in advance for your help.
For your question, here is the method I provided:
You need a virtual table and go into the power query to do unpivot.
"Table"
" virtual table"
Click "Transform data" to enter the power query.
Unpivot columns.
Create a relationship.
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
For your question, here is the method I provided:
You need a virtual table and go into the power query to do unpivot.
"Table"
" virtual table"
Click "Transform data" to enter the power query.
Unpivot columns.
Create a relationship.
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your solution. It works perfectly
