Search code examples
excelnestedpivotnest

Excel Pivot nest a column under some of my other columns problem

I have this source of data cells: enter image description here

I am trying to create a pivot nest the Model under the colors. Each color individually. Haven't succeeded so far. This is the result I'm aiming for:

enter image description here

Any help will be much appreciated

Solution

  • You can use Power Query to pre-process the table:

    • Select the first three columns
    • Unpivot other columns
    • Remove the values column
    • Close and load as Pivot Table

    Once you have the pivot table loaded

    • Drag Model and Attribute to the Rows area
    • Drag Order Total and Order Amount to the Values area

    M Code

    let
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
        Source, 
        {"Model", "Order Total", "Order Amount"}, 
        "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"})
in
    #"Removed Columns"

    enter image description here

    Edit

    To use Power Query, available in Windows Excel 2010+ and Office 365 Excel

    • Select some cell in your original table
    • Data => Get&Transform => From Table/Range
    • When the PQ UI opens, navigate to Home => Advanced Editor
    • Make note of the Table Name in Line 2 of the code.
    • Replace the existing code with the M-Code below
    • Change the table name in line 2 of the pasted code to your "real" table name
    • Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps