Search Multiple Value in different columns

[RomainGeniteau]

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.

[RomainGeniteau]

Thank you for your solution. It works perfectly

[Anonymous · Accepted solution]

Hi @RomainGeniteau 

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.