Analysis - Multiple Match

[Veternus]

Hello guys! 

I feel like this should have some obvious solution, however I'm not seeing it and o I decided to ask you for help. 

I need to do an analysis of questions asked by our customers about our products. We track contents of the conversations and have output table looking like this: 

ID	Date	Department	Product	Question
1	01.01.2024	Dpt1	"Product A", "Product B", "Product C"	"Question A", "Question B"
2	01.01.2024	Dpt2	"Product A", "Product B"	"Question A", "Question B"
3	02.01.2024	Dpt2	"Product A"	"Question A"
4	03.01.2024	Dpt1	"Product A"	"Question C"
5	03.01.2024	Dpt3	"Product B", "Product C"	"Question A", "Question B", "Question C"

What I want to achieve is, among other things, a graph, that would be showing numbers of asked question, but visually divided (legend) by type of product. Something like this: 

But I'm struggling to find a way of connecting multiple products of one row to multiple questions. I've tried calculations with SEARCH function, but that doesn't work as some of the product names are just widened names of other products (Product A and Product AB). Also there is 20+ question categories and 20+ products, so something like creating a measure for every combination is something that I would like to avoid. 
My first impulse was to create two more tables, that look like this: 

ID	Question
1	Question A
1	Question B
2	Question A
2	Question C
3	Question A

ID	Product
1	Product A
1	Product B
1	Product C
2	Product A
2	Product B

But that didn't get me far as the relation between them would be M:M. Is there something I'm missing about connecting them through the main table? Or would you take completely different approach? 

[Veternus]

Thank you for your insight

[Ritaf1983 · Accepted solution]

Hi @Veternus 
You need to make some "ETL" to prepare the table for analysis with PQ.

In the first step, clean unnecessary quotation marks at the columns of products and answers :

in the next step split these columns into rows :

Then you will get a clean vertical table that you can work with and create desired and other visuals:

The pbix is attached with all the steps of PQ recorded so you can follow them

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly