Results 1 to 9 of 9

Thread: Transform multi-level table using PivotTable

  1. #1
    Seeker montaguelord's Avatar
    Join Date
    Jun 2018
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016

    Transform multi-level table using PivotTable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,755
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    could you please post your sample sheet ( click Go advanced - Manage attachments) showing your data and expected results?
    Thx
    Thank you Ken for this secure forum.

  3. #3
    Seeker montaguelord's Avatar
    Join Date
    Jun 2018
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    Attached please find the sample data with Actual and Desired data shown.
    Thank you in advance for your help.
    Attached Files Attached Files

  4. #4
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    AM
    Montaguelord

    Is this what you need?
    Attached Files Attached Files

  5. #5
    Seeker montaguelord's Avatar
    Join Date
    Jun 2018
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    Yes, excellent. How to do it?

  6. #6
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016

    Depends on your level within Get data(Power Query) and Pivot tables

    Quote Originally Posted by montaguelord View Post
    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.

  7. #7
    Seeker montaguelord's Avatar
    Join Date
    Jun 2018
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    I'll follow your steps above and will do the same for the full data set.
    Many many thanks for your great help!

  8. #8
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    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 by Ed Kelly; 2018-07-01 at 06:31 PM.

  9. #9
    Seeker montaguelord's Avatar
    Join Date
    Jun 2018
    Posts
    6
    Articles
    0
    Excel Version
    Excel 2016
    Excellent, Thank you. And the link helps a lot!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •