Power Pivot guidance - data structure and avoiding many-many relationships

gwoolley0302

Board Regular
Joined
May 13, 2014
Messages
55
Hello,


I’m looking for some guidance on how to build my data set, so I can effectively use Power Pivot to present my results. In my attempts so far, I have come up against the challenge of many to many relationships when trying to create relationships between my tables. I have tried to create bridging tables, but these do not seem to have worked so far or I’m not sure how to use them.


I would really appreciate if anyone would be able to review my data set and point me in the right direction.
Also;
• To may be help for context, the last tab includes an initial list of the results I am trying to present at the end.
• One potential problem I came across was how I have structured data in the ‘surveys conversion tab’, hence the creation of an alternative structure in the adjacent tab although not complete). I’m hoping the first is work-able as it is less time consuming.


It feels like the mos straight forward method of sharing the issue, would be by sharing the document? But I can't attach it? And I need a specific email address in order to gain a link to share through Office 365. Would any one have any suggestions?


Thank you so much to any one who can support!


Best wishes, Greg
 
;you need re-define relationship from Register to Survey_Conversion1, (I added new tables: Survey Conversion1 and Outcome_Themes1)
see relationships in DataModel (re-download file from post#7)
 
Last edited:
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello,

I have re downloaded the file from post 7, and looked at the new tables you've put in. But I don't understand them unfortunately. And I'm not sure whether they are the crux of the problem, which I *think* is successfully assigning 'technical sub categories' to their 'technical area' "parent" (as shown in the potential tables of results in my previous post #8 ). Which, in turn will allow results grouped by the 4 "technical areas" (e.g. 1. engagement/enjoyment, 2. accuracy, 3. fluency, 4. comprehension).

Any further thoughts? (Sorry for consuming lots of your time!)
 
Upvote 0
see pivot table sheet and choose appropriate columns to add: example

btw. see text in white frame at the bottom of the post :)
 
Upvote 0
Unfortunately still isn't what I need. But I completely understand that's because I'm not sharing the right info with you !! I've added some notes into the 'Pivots sheet' next to your suggested pivot. Does this help explain what i'm looking to do...?
 
Upvote 0
I've added some notes into the 'Pivots sheet' next to your suggested pivot. Does this help explain what i'm looking to do...?

Honestly? No, because I don't see "some notes" :LOL:

===
you need to convert (if necessary) all tables to 1NF
then create proper relationships
then create PivotTable as you want
 
Last edited:
Upvote 0
I resolved the first problem - relationships
I did two tables into 1NF
I did relationships with the rest of the tables
I did example PT (if it is wrong you can create your own)

the rest it's up-to-you

Have a nice day
 
Upvote 0
You cannot group subcategory you show because there is more subcategories which are not duplicated, you can filter it if you want

screenshot-126.png
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top