Efficient way to convert table into database friendly data

[gwoolley0302]

Hello,

To help build some effective power pivot tables, I *think* I need to convert my data from Format A to Format B (demonstrated below). You can also see my spreadsheet that my data currently feeds through into Format A in sheet number 5.

If I wanted to translate sheet 5 into a format like shown in Table B below - is there a quick way to do this? (I can't see past a very laborious and time consuming method!!)

Any help is most appreciated.

Thanks, Greg

Table A

Student ID	Metric 1 (Baseline score)	Metric 1 (Post-programme score)	Metric 1 (change in score)	Metric 2 (Baseline score)	Metric 2 (Post programme score)	Metric 2 (Change in score)	Metric 3 (etc...)
xxxx001	0	2	2	1	1	0
xxxx002	1	1	0	1	2	1

Table B

Student ID	Score type	Metric 1	Metric 2
xxxx001	Baseline	0	1
xxxx001	Post programme	2	1
xxxx001	Change	2	0
xxxx002	Baseline	1	1
xxxx002	Post programme	1	2
xxxx002	Change	0	1

 

[sandy666]

sandy
	A	B	C
1	ID	Attribute	Value
2	To-RR-20-1001	Engaged reader & enjoys book (Ba)	3
3	To-RR-20-1001	Engaged reader & enjoys book (End)	3
4	To-RR-20-1001	Engaged reader & enjoys book (Change)	0
5	To-RR-20-1001	Confident reader (Ba)	2
6	To-RR-20-1001	Confident reader (End)	2
7	To-RR-20-1001	Confident reader (Change)	0
8	To-RR-20-1001	Focus time (Ba)	1
9	To-RR-20-1001	Focus time (End)	2
10	To-RR-20-1001	Focus time (Change)	1
11	To-RR-20-1001	Reading record signed (Ba)	2
12	To-RR-20-1001	Reading record signed (End)	2
13	To-RR-20-1001	Reading record signed (Change)	0
14	To-RR-20-1001	CVC (Ba)	2
15	To-RR-20-1001	CVC (End)	3
16	To-RR-20-1001	CVC (Change)	1
17	To-RR-20-1001	CVCC (Ba)	1
18	To-RR-20-1001	CVCC (End)	3
19	To-RR-20-1001	CVCC (Change)	2
20	To-RR-20-1001	Di/tripgraphs (Ba)	2
21	To-RR-20-1001	Di/tripgraphs (End)	3
22	To-RR-20-1001	Di/tripgraphs (Change)	1
Sheet2

maybe it would be enough to change (Ba) - Baseline, , (End) - Post programme & (Change) - Change and split it to two columns (if necessary) ?

 

[gwoolley0302]

Yes that would work well I think.

 

[gwoolley0302]

Yes that would work well I think.

As long as that structure would enable me to create the table I drafted in the sheet "Results table needed" ,... which would require creating relationships of the various tables using Power Pivots?

 

[sandy666]

check example again

 

[sandy666]

In this case I don't use PowerPivot but PowerQuery

 

[gwoolley0302]

Hi , I don't see any updates. Are you still using 's666-PQ-Impact system 2019.11.15a' ?

 

[sandy666]

try this: example

 

[gwoolley0302]

That's looking great thank you.

You mentioned in this you don't use PowerPivot but you use PowerQuery. But am I right in thinking that I would now need to use PowerPivot in order to create a summary table such as the one below?

Techincal area parent group	Technical sub-category	Number of students with a Change score of 0	% of students with a Change score of 0	Number of students with a Change score of 1	% of students with a Change score of 1	Number of students with a Change score of 2	% of students with a Change score of 2
Enjoyment / engagement	Engaged reader & enjoys book
Enjoyment / engagement	Confident reader
Enjoyment / engagement	Focus time
Enjoyment / engagement	Reading record signed
Accuracy	CVC
Accuracy	CVCC
Accuracy	Di/tripgraphs
Accuracy	Tricky words
Fluency	Able to sound out & blend
Fluency	Uses expression
Fluency	Reads whole sentences fluently
Comprehension	Able to answer questions on text
Comprehension	Able to make inferences
Comprehension	Able to remember repeated words
Comprehension	Can re-tell story

 

[sandy666]

you can use PT or PQ if you know how to achieve what you want

 

[sandy666]

something like this?

sandy
	H	I	J
3	Value	Count of ID	Count of Value
4	0	362	13.41%
5	1	807	29.89%
6	2	958	35.48%
7	3	511	18.93%
8	4	62	2.30%
TEST

this is not what you show above but example only