Efficient way to convert table into database friendly data

gwoolley0302

New Member
Joined
May 13, 2014
Messages
48
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
 

gwoolley0302

New Member
Joined
May 13, 2014
Messages
48
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,227
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
 

gwoolley0302

New Member
Joined
May 13, 2014
Messages
48
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,227
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

New Member
Joined
May 13, 2014
Messages
48
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,227
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 :)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,227
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:

Forum statistics

Threads
1,077,825
Messages
5,336,595
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top