Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI, I am new to queries and have searched high and low for a way to do what I am trying to do but unfortunately I can't seem to figure out how to adapt any solutions to my problem!
I have 7 multiple choice quizzes in Microsoft Forms that I am using power automate to download the responses when someone submits. I would like to have all quizzes dump into a single sheet and assess the results for each respondee but each quiz has their own answer table. Thus far I have been able to follow the link below to create one quiz that looks at one answer table and tells me how many points a respondee has received based on their answers.
So, in short- one quiz respondee table with a column for quiz title that is able to use the quiz title to direct to which answer table to use. I have also considered having a single answer table that also has a column for quiz title but I still can't solve how to make that a conditional factor when the Table.NestedJoin occurs.
I would like to stick to M as I'm honestly not even sure how to go about putting in DAX.
This is a link to the workbook:
Thank you in advance!
Solved! Go to Solution.
The key here is to load all the answers into a single table like this:
Quiz Name | Question | Answer | Points |
Module 1 | Q1 | a | 0 |
Module 1 | Q1 | b | 1 |
Module 1 | Q1 | c | 0 |
... | ... | ... | ... |
Module 2 | Q1 | All of the above | 1 |
Module 2 | Q1 | Budget | 0 |
... | ... | ... | ... |
Then you can merge on two columns Quiz Name and Question instead of just Question.
The key here is to load all the answers into a single table like this:
Quiz Name | Question | Answer | Points |
Module 1 | Q1 | a | 0 |
Module 1 | Q1 | b | 1 |
Module 1 | Q1 | c | 0 |
... | ... | ... | ... |
Module 2 | Q1 | All of the above | 1 |
Module 2 | Q1 | Budget | 0 |
... | ... | ... | ... |
Then you can merge on two columns Quiz Name and Question instead of just Question.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.