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

 

[SpillerBD]

You'll find the general methods grouped under UN-Pivot.

My preference is using the PowerQuery, now known as Get and Transform.
Watch Mike do it.
Mike Girvin #10 to Unpivot

 

[gwoolley0302]

Hi SpillerBD

Thanks for the reply. I've looked through, that's a useful to know about thank you! But do you know whether there is a method unpivoting which:
a) allows there to be a column for more than one attribute (as shown in my Table B above) (or does that not comply with the objective of flat data?)
b) allows the formulae to be retained so the links to cells in another sheet are retained (as I need to make sure those links remain 'live'.)

Best wishes, Greg

 

[sandy666]

as mentioned above with PowerQuery with basic M-code

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Student ID"}, "Attribute", "Value"),
    Split1 = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    Split2 = Table.SplitColumn(Split1, "Attribute.2", Splitter.SplitTextByDelimiter(" score)", QuoteStyle.Csv), {"Attribute.2.1", "Attribute.2.2"}),
    RC = Table.RemoveColumns(Split2,{"Attribute.2.2"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[Attribute.1]), "Attribute.1", "Value"),
    Rename = Table.RenameColumns(Pivot,{{"Attribute.2.1", "Score type"}}),
    Sort = Table.Sort(Rename,{{"Student ID", Order.Ascending}, {"Score type", Order.Ascending}})
in
    Sort

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)
xxxx001	0	2	2	1	1	0
xxxx002	1	1	0	1	2	1
Student ID	Score type	Metric 1	Metric 2
xxxx001	Baseline	0	1
xxxx001	Change in	2	0
xxxx001	Post programme	2	1
xxxx002	Baseline	1	1
xxxx002	Change in	0	1
xxxx002	Post programme	1	2

you need to fix your source table against text errors

 

[gwoolley0302]

Thank you sandy666. Unfortunately I have never used m code. I tried to convert the code you suggested into my spreadsheet but I couldn't get it to work. Would you be able to kindly enact the change for me in the spreadsheet - Impact system 2019.11.15a

(Also can I just check, through this method, will the formulae which essentially link with cells in another sheet within the document remain?. As basically the data in sheet number 4 is currently dummy data, so will need updating after I have teh system set up...)

 

[sandy666]

you need to define how you want to see Attribute values
eg.

Engaged reader & enjoys book (Ba)

Engaged reader & enjoys book (End) should be? Engaged reader & enjoys book Engaged reader & enjoys book look at TEST sheet example

 

[gwoolley0302]

I would like the attribute values to show as in the table below:

(with a row each for Baseline, Post programme, and change)

However, I am hoping to keep sheet 4 "survey responses" in the current format (i.e. a cross table) as the user (not me) understands that format and will be recording data manually. Sheet number 5 will not be handled by the end user (it just converts data from sheet number 4) ... so that can take any format needed to create the required flat data structure..

Technical Area sub-category:
Engaged reader & enjoys book
Confident reader
Focus time
Reading record signed
CVC
CVCC
Di/tripgraphs
Tricky words
Able to sound out & blend
Uses expression
Reads whole sentences fluently
Able to answer questions on text
Able to make inferences
Able to remember repeated words
Can re-tell story

 

[sandy666]

is that what you want? Re-download example from post#6, if not post the result what you want to achieve
your original table will stay as is

 

[gwoolley0302]

is that what you want? Re-download example from post#6, if not post the result what you want to achieve
your original table will stay as is

Thank you. I've made a Test 2 tab in the same workbook, with the proposed structure. Is that do-able?

s666-PQ-Impact system 2019.11.15a.xlsx

Microsoft Excel Workbook

1drv.ms

 

[sandy666]

how do you want to get data like this from survey responses table if they are not exist there ?

Value Type

Baseline

Post programme

Change

Baseline

Post programme

Change

Baseline

Post programme

Change