Data Modelling

[SANKETPATIL]

Summary: I am working survey data report & getting many to many relationship

Data Details:

300 respondents (who given responses #panel)

30 questions. (This were recorded in pivot form e.g. 1st column = Respondent then for Q1 have 5 responses those were accommodated in 5 columns. & so on for Q2 to Q30)

Total we have 232 column for 30 questions

So table comprises of 232 columns & 300 rows

Questions & Creation of tables from Original Table:

Now Q1 have 5 choices (a,b,c,d,e)

Subsequent other 5 questions (from Q2 to Q6) which are dependent on Q1 response

Eg take Q2

If Q1 response = 3 (3rd i.e. (C) choice out of 5) then,

Go with Q2  d,e,f options out of 10 options (e.g. a to j)

That’s how they dependent

So my approach was

Step 1) Create table by duplicating original table

Step 2) take column directly associated with Q1

(e.g. Respondent id + 5 responses)

Unpivot Q1 response wrt respondent id. Now we have 3 column respondent id, Attributes & values

Do same procedure for Q2 to Q6 by filtering according to dependency &

Then pull all this responses back to Q1 table by merging wrt. combine key.

That’s how I created 8 tables for 30 columns by merging their dependent tables but issue is in Data Modeling

Challenges: We get many to many relationship (Due to unpivoting we have Many cardinality

To overcome it I created 1 table with column respondent id & defined relationship but the flow is Now totally altered

It is 1 to many from respondent to questions merged column with that I am unable to create interactive dashboard as every table in its own silos

Can you guide me how we can overcome this

@suparnababu8, @rajendraongole1 

[SANKETPATIL]

It works. Thanks @DataNinja777 

[v-aatheeque]

Hi @SANKETPATIL ,

Glad to hear your query has been resolved! If the response provided by @DataNinja777  addressed your issue, please mark it as Accepted Answer and click Yes if you found it helpful. This will help other community members with similar questions find a solution more quickly.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[DataNinja777 · Accepted solution]

Hi @SANKETPATIL ,

The problem here lies in the fact that you have kept questions tables separate in your data model.  In your case, the best method is to simply append all question tables into one combined table in Power Query, as long as each table already has one row per respondent per option. Just add a column to identify the question number before appending. This structure avoids many-to-many issues and allows proper relationships for interactive reporting.

Best regards,