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]

or like this?

sandy
	G	H	I	J	K	L	M	N
2			Value	Values
3			0		1		2
4	"Parent" Technical Area	Technical Area sub-category	Count of Value	Count of Value2	Count of Value	Count of Value2	Count of Value	Count of Value2
5	Accuracy	CVC	20	0.94%	20	0.94%	60	2.82%
6		CVCC		0.00%	40	1.88%	60	2.82%
7		Di/tripgraphs		0.00%	60	2.82%	60	2.82%
8		Tricky words	60	2.82%		0.00%	120	5.64%
9	Comprehension	Able to answer questions on text	60	2.82%	120	5.64%		0.00%
10		Able to make inferences	20	0.94%	40	1.88%	80	3.76%
11		Able to remember repeated words	20	0.94%	60	2.82%	80	3.76%
12		Can re-tell story	20	0.94%	40	1.88%	40	1.88%
13	Enjoyment / engagement	Confident reader	20	0.94%	40	1.88%	80	3.76%
14		Engaged reader & enjoys book	21	0.99%	58	2.73%	79	3.71%
15		Focus time	30	1.41%	60	2.82%	90	4.23%
16		Reading record signed	30	1.41%	30	1.41%	90	4.23%
17	Fluency	Able to sound out & blend	1	0.05%	59	2.77%	59	2.77%
18		Reads whole sentences fluently	60	2.82%	120	5.64%		0.00%
19		Uses expression		0.00%	60	2.82%	60	2.82%
TEST

 

[gwoolley0302]

Hi again. Sorry, it is more like the second option I'm looking to create. I actually think I've managed to create what I need in a pivot table - see here. On reflection I don't think I need numbers and percentages.

However,I would really appreciate on two other questions that I have come across in my workings - see Sheet Pivot test (questions in red). Any thoughts?

And then one final question is, as I have updated table names in the document I understand I need to update these in the Power Query in order to be able to refresh. how do I do this?
s666-PQ-Impact system 2019.11.15a-ver4.xlsx
Thank you so much!!

 

[sandy666]

Sorry mate, I can't help with PowerPivot (DAX), maybe someone else

 

[gwoolley0302]

No worries. Thanks for your help!

Should I post in a new thread because its a different type of solution?

 

[sandy666]

IMHO new thread because these two questions are different then question from OP