- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to link labels and values of different tables
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-07-2023 12:20 PM | |||
06-03-2024 07:29 PM | |||
07-01-2024 02:04 PM | |||
08-26-2024 08:55 PM | |||
08-16-2024 07:05 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |