Transform multi-level table using PivotTable

montaguelord

New member
Joined
Jun 30, 2018
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
I have an Excel 2016 file with a large set of panel data in the following actual form:
VAR
ISO
VALUE


1990
1991
1992
IMPORTS
ARG
1,287
NA
NA
IMPORTS
AUS
9,178
5,447
10,792
IMPORTS
AUT
NA
NA
1,325
GDP
ARG
153
206
248
GDP
AUS
323
324
317
GDP
AUT
167
174
196
I need to transform it to the following desired form:
ISO
YEAR
VAR


IMPORTS
GDP
ARG
1990
1,287
153
ARG
1991
NA
206
ARG
1992
NA
248
AUS
1990
9,178
323
AUS
1991
5,447
324
AUS
1992
10,792
317
AUT
1990
NA
167
AUT
1991
NA
174
AUT
1992
1,325
196
Can anyone suggest a way to automatically transform the data from the actual format to the desired form:
I thought that a pivot table might work. But I don’t know how to do a multi-level pivot table that would have the ISO and YEAR in the same line.
Any suggestions would be grately appreciated!
 
Hi and welcome
could you please post your sample sheet ( click Go advanced - Manage attachments) showing your data and expected results?
Thx
 
Attached please find the sample data with Actual and Desired data shown.
Thank you in advance for your help.
 

Attachments

  • SAMPLE.xlsx
    12.9 KB · Views: 17
AM
Montaguelord

Is this what you need?
 

Attachments

  • SAMPLE.xlsx
    26.3 KB · Views: 20
Depends on your level within Get data(Power Query) and Pivot tables

Yes, excellent. How to do it?

Assuming you are very comfortable with it then I transposed the table, made your 2 header row lines into 1, re-transposed it a second time and then unpivoted the "other Columns" to future proof the potential addition of any future years into the data set. Then I pivot tabled that and used the tabular format with repeat all labels. I was going to add some slicers and a few charts however does not look like you need that.

If the above does not make a great deal of sense then consider sending your exact data structure with fictitious data and I will set it up. You can then swap out your real data and you will just then need to click refresh as new data gets added.

Apologies I do not get to the site much so please excuse any tardiness in getting back to you.
 
I'll follow your steps above and will do the same for the full data set.
Many many thanks for your great help!
 
Here is the code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column2", type text}}, "en-CA"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","VALUE|","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","|","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","NA","0",Replacer.ReplaceText,{"Column3", "Column4", "Column5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value2", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"1990", type number}, {"1991", type number}, {"1992", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"VAR", "ISO"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}})
in
#"Renamed Columns"

and here is where to find it

https://www.screencast.com/t/yIQ44R7TeO
Knock 'em Dead Tiger!
 
Last edited:
Excellent, Thank you. And the link helps a lot!
 
Back
Top