Efficient way to convert table into database friendly data

gwoolley0302

Board Regular
Joined
May 13, 2014
Messages
55
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 IDMetric 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...)
xxxx001022110
xxxx002110121

Table B
Student IDScore typeMetric 1Metric 2
xxxx001Baseline01
xxxx001Post programme21
xxxx001Change20
xxxx002Baseline11
xxxx002Post programme12
xxxx002Change01
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
as mentioned above with PowerQuery with basic M-code

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 IDMetric 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)
xxxx001022110
xxxx002110121
Student IDScore typeMetric 1Metric 2
xxxx001Baseline01
xxxx001Change in20
xxxx001Post programme21
xxxx002Baseline11
xxxx002Change in01
xxxx002Post programme12


you need to fix your source table against text errors
 
Upvote 0
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...)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top