How to link labels and values of different tables

[dejudicibus]

Let's suppose that I have three spreadsheet. The first one (QUESTIONS.XLSX) is a list of questions. Each question has a unique identifier. For example:

QCode	QText
Q1	Do you like potatos?
Q2	Do you like tomatos?
Q3	Do you like bananas?

The second one (ANSWERS.XLSX) is a list of answers that are good for each question. For example:

ACode	AText	AValue
A1;	Not at all	0
A2	Not much	1
A3	Quite a lot	2
A4	A lot	3

The third (SURVEY.XLSX) is the result of an anonymous survey using those questions and answers:

PCode	Q1	Q2	Q3
P1	A3	A2	A1
P2	A1	A3	A3
P3	A3	A1	A1
…
P100	A1	A2	A2

How can I connect them in PowerBI considering that the LABELs of each column in SURVEY but the first one — representing anonymously the people who participated to the survey — are the values of column QCode in QUESTIONS; and that the VALUES of each column in SURVEY but the first one, are the values of column ACode in ANSWERS?

[amitchandak · Accepted solution]

@dejudicibus , Unpivot the third table

https://radacad.com/pivot-and-unpivot-with-power-bi

if need merge all three tables. https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Icey · Accepted solution]

Hi @dejudicibus ,

Just like what @amitchandak mentioned, it is suggested to use "UnPivot" and "Merge" to transform your data. For detailed stpes, please check:

Or, just unpivot the Survey table and don't merge them. Then use relationships to connect three tables.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dejudicibus]

Think of all, thank you for advice. I do not know Unpivot feature, so it looks like I have to study. I will work on that and let you know if works. I appreciate your help.

[amitchandak · Accepted solution]

@dejudicibus , Unpivot the third table

https://radacad.com/pivot-and-unpivot-with-power-bi

if need merge all three tables. https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[dejudicibus]

Unpivot is really a powerful feature. Thank you very much, to you and to @Icey for tutorial. Thank you.