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

[gwoolley0302]

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

 

[sandy666]

;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)

 

[gwoolley0302]

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!)

 

[sandy666]

see pivot table sheet and choose appropriate columns to add: example

btw. see text in white frame at the bottom of the post

 

[gwoolley0302]

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...?

 

[sandy666]

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"

===
you need to convert (if necessary) all tables to 1NF
then create proper relationships
then create PivotTable as you want

 

[sandy666]

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

 

[gwoolley0302]

- can you not see any text on the link below? (In the 'Pivots' sheet?)

https://onedrive.live.com/view.aspx?resid=790318D1FDBD538A!2521&ithint=file%2cxlsx&authkey=!AOYOiAPOOCzD1RU

Oooo - in the meantime, I will read about First Normal Form - that's new to me!

 

[sandy666]

You cannot group subcategory you show because there is more subcategories which are not duplicated, you can filter it if you want