cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Analysis - Multiple Match

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?

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Frequent Visitor

Thank you for your insight

Super User

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