Several relations between two tables (survey/questionnaire)

[munk80]

Hi

Isn't it possible to define (several) relations between one table (eg. results from survey - i.e. many columns/questions with standardized answers) to another table that provide the labels/categories?

That is without having to deactivate the existing relation each time I want to look at another variable (because only one path is allowed)..

Simplified example:

Example.jpg


Regards
Munk

[sandy666]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[munk80]

Thanks Sandy!

I try again.

Here's an attached file with four short workbooks (the last one being "desired result"). I've tried to make a simple power pivot with the tab1 and tab2 workbooks, which however, doesn't work due to invalid relations. (Had the same problem in Power BI).

I'm only allowed to set a relation between the variable "Value" (workbook tab2) and (e.g.) "Question 1" (workbook tab1), not at the same time also set a relation between "Value" (tab2) and (e.g.) "Question 2" (etc.).

I'm also aware that this would be a simple procedure in like SPSS, but that's not the concern here..



Munk

[sandy666]

you can do that with PowerQuery (your profile has excel version 2007 which is not compatible with PowerPivot, PowerQuery - so update profile )
see green table

[munk80]

Thanks a lot Sandy, this worked very nice
The 2007 version I hope is just charming..

[sandy666]

The 2007 version I hope is just charming..

Maybe it is but doesn't support PQ & PP so your profile version making noise

General Note: Read me Try to avoid joining to my Black List by doing this below If that takes care of your original question, & to say Thanks and for better Motivation, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. If you did it already - ignore it. Thank you.

[sandy666]

thanks for profile update