Help on data structure - and visualising data in different columns

[winwell]

Main_Event Table:

id	Name	SO1	SO2	SO3	SO4	SO5	Benefit1	Benefit2	Benefit3	Benefit4	Benefit5
1	One	SO1 Strategic Objective	SO1 Strategic Objective	SO2 Strategic Objective	SO5 Strategic Objective		Benefit Type 1	Benefit Type 10	Benefit Type 17	Benefit Type 19
2	Two	SO2 Strategic Objective	SO2 Strategic Objective	SO2 Strategic Objective	SO5 Strategic Objective	SO5 Strategic Objective	Benefit Type 2	Benefit Type 11	Benefit Type 18	Benefit Type 20	Benefit Type 21
3	Three	SO1 Strategic Objective	SO1 Strategic Objective				Benefit Type 3	Benefit Type 12
4	Four	SO2 Strategic Objective	SO3Strategic Objective				Benefit Type 4	Benefit Type 13
5	Five	SO3Strategic Objective	SO4 Strategic Objective				Benefit Type 5	Benefit Type 14
6	Six	SO4 Strategic Objective	SO4 Strategic Objective				Benefit Type 6	Benefit Type 15

Pre_event_scores table

id	EventID	Benefit1	Benefit2	Benefit3	Benefit4	Benefit5
1	1	2	3	4	5
2	1	3	4	3	5
3	1	4	4	5	4
4	1	3	3	3	5
5	1	2	5	4	3
6	1	3	3	3	4
7	1	4	2	2	3
8	1	4	5	3	2
9	2	5	3	2	4	2
10	2	3	3	3	3	3
11	2	3	2	2	2	2
12	2	4	4	4	4	4
13	2	3	3	3	3	3
14	3	2	2
15	3	3	4
16	3	3	3
17	3	2	3
18	4	4	1
19	4	2	4
20	4	3	2
21	4	4	3
22	5	2	4
23	5	3	3
24	5	2	4
25	5	1	3
26	6	4	4

Posr_event_scores table

id	EventID	Benefit1	Benefit2	Benefit3	Benefit4	Benefit5
1	1	5	6	5	7
2	1	6	7	8	6
3	1	7	6	7	5
4	1	6	8	6	6
5	1	5	7	7	7
6	1	6	8	6	6
7	1	7	7	7	7
8	1	6	6	5	6
9	2	7	7	6	5	6
10	2	8	8	7	6	5
11	2	6	7	6	6	6
12	2	7	6	7	6	7
13	2	6	7	6	7	8
14	3	8	8
15	3	7	9
16	3	6	8
17	3	7	7
18	4	6	6
19	4	5	7
20	4	6	8
21	4	5	7
22	5	6	6
23	5	7	7
24	5	8	8
25	5	7	7
26	6	8	6
27	6	7	5

 We run training events and each record in the Main_Events table is a different event. We use the id as an identifier. For each event we ask the client to identify which benefit they want to see improved. There are many benefits. Each benefit can be mapped to one of 5 high level Strategic Objectives (SO1, SO2, SO3, SO4, SO5). When the initial record is created we identify the SO for each benefit. Some benefits can map to the same SO. 

Before the event we run a candidate survey to get the scores for each benefit (i.e. how they rate themselves against each one). The results are caputed for each Benefit and we use the Main_event table id -> to the EventID in the Pre_event_scores so its a one to many relationship - we can identify which set of survey responses relate to which record on the Main_event table.

After the event we run another survey asking the same questions, capturing the same data in the same way. 

The difference between the scores is our uplift.

My Ask...

I am using Power BI desktop to create a dashboard showing benefits realised for each event, and ideally by Strategic Objective.I have tried googling the answer but get no real guidance, so I hope you can point me in the right direction.

I have these tables in Power BI and can easily slice the results by id - so show benefit results for a single event - we can do that.But ... I cannot figure out how to show results for a specific Strategic Objective as they can appear in different columns.

So if I wanted to show the overall uplift for say, Strategic Objective1, for say Events 1, 2 and 3 is that doable?

Is our data structured in a way to even make it possible?

So any help you can offer is appreciated even if it means restructuring the data as we are in the early stages of this,

Apologies for the vague title but I am struggling how to articulate the issue.

Many thanks

[winwell]

if it helps, I can upload a pbix file with some sample data and visuals

[winwell]

 SO I tried to delete the columns from a copy of the table and just unpivoted the relevnt cols leaving the id col. That worked so the data unpivoted fine. However, because the id column is my primary key column that is on the ONE side of the one to many relationship to the other two tables (Pre and Post) I dont appear to be able to get the values of the relevant Event ID from those two tables now. Is that because there are multiple instances of the id in the main table? 

[amitchandak]

@winwell , I think unpivot of columns can help 

Use in blank query in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZE9D8IgEIb/CmHuIF9VVwdXhnYjHbQ5FYfWEPz690LjILkgaROOXO6Fe+4FYyijFdUDhL3RjDTeHTycbU/08Qq9t4+SwrOKyigkxA4GOFlP2vcNCEOFFaqsUWU7teoqQ3nI2udYGGeJkrOQV5IZORoaW92gS8g8Z5NNEW1eHCz9K/ITSX+BhuLJ8QiXIduPd1d4MDETLRFaILSK6AKg0XKua4XQEqHr2Nq+/gIWoGuEVt9jcXXdBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Name = _t, SO1 = _t, SO2 = _t, SO3 = _t, SO4 = _t, SO5 = _t, Benefit1 = _t, Benefit2 = _t, Benefit3 = _t, Benefit4 = _t, Benefit5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Name", type text}, {"SO1", type text}, {"SO2", type text}, {"SO3", type text}, {"SO4", type text}, {"SO5", type text}, {"Benefit1", type text}, {"Benefit2", type text}, {"Benefit3", type text}, {"Benefit4", type text}, {"Benefit5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id", "Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

[winwell]

Thanks for pointing me in the right direction

I tried to unpivot the actual tables but receive this error when applying th echanges:

[My Actual Table name]

Column 'Id' in Table '[My Actual Table Name]' contains a duplicate value '2' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

So the id column is causing me a problem as it's the unique col from the sharepoint list and it is this id value that has a one to many relationship with the other two tables containing the Pre and Post scores.

Is there a way to overcome this error please?

Thank you