Creating a relationship between two tables of unpivoted survey data.

[Malvolio]

Creat

Hopefully someone can help me!

I have a spreadsheet of survey data which contains one multi select question. Each option for the multi select question appears in its own column in the raw data.

I have given each row in the data a unique identifier(numbers). This column is called ID.

I have used power query to create two tables:

Table one: contains the unpivoted multi select data.
Table two: contains all of the single select answer data.

Obviously table one contains duplicates of the of the unique identifier. Table two doesn't contain duplicates.

I want to be able to cross tab between the two tables. I have tried creating a relarionship between the ID column on each table, but this doesn't allow me to cross tab between the two tables.

I have also tried creating a key table with no duplicates and using it as a bridge table

What am I missing here?

 

[Automatrix]

I think we need something a little more visual to help here.