Conditionally Join Tables with M

[kzoppo]

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.

https://techcommunity.microsoft.com/gxcuf89792/board/message?board.id=MicrosoftForms&message.id=3304... 

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:

https://swainc-my.sharepoint.com/:x:/g/personal/kzoppo_swinter_com/EQ--OQK1DbhPlJS1b0_8XPkBxPgnTfNoM... 

Thank you in advance!

[AlexisOlson · Accepted 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.

[kzoppo]

@AlexisOlson- that was so simple, I was thinking too much into it. Thank you!