Excel Pivot display multiple columns

Question

I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise

below is the sample data

Required Sample output of single employee. I have tried using PIVOT but not getting required result.

what you need is not a simple pivot. You need to unpivot "fields" first, before you can make pivot. See unpivot solution here: https://superuser.com/questions/78439/how-to-unpivot-or-reverse-pivot-in-excel — Máté Juhász, Apr 04 '22 at 06:24

score 1 · Accepted Answer · answered Apr 06 '22 at 10:09

You can obtain your desired output using Power Query, available in Windows Excel 2010+ and Office 365 Excel

Select some cell in your original table
Data => Get&Transform => From Table/Range or From within sheet
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
- We Select the first three columns and unpivot the rest
- Then Pivot on the Date column with the Values column as the value
  - AND with the Advanced "No Aggregation" set
- Then add the Diff column

M Code

let

//change table name in next line to your actual name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Emp. Code", Int64.Type}, {"Name", type text}, {"ProcessDate", type date}, 
        {"EARNED MEDICAL", Int64.Type}, 
        {"EARNED CONVEYANCE", Int64.Type}, 
        {"NetPay", Currency.Type}, 
        {"ESI", Currency.Type}, 
        {"Income Tax", Currency.Type}, 
        {"PF Amount", Currency.Type}, 
        {"TOTAL DEDUCTION", Currency.Type}}),

//Select the first three columns and Unpivot Other Columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Emp. Code", "Name", "ProcessDate"}, "Attribute", "Value"),

//Pivot on the Date column with Values = the Values column
    #"Pivoted Column" = Table.Pivot(
        Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"ProcessDate", type text}}, "en-US"), 
        List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", 
            {{"ProcessDate", type text}}, "en-US")[ProcessDate]), 
            "ProcessDate", "Value"),

//Add the Diff column
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Diff", each [#"3/1/2022"]-[#"2/1/2022"], Currency.Type)
in
    #"Added Custom"

Data

Results

Excel Pivot display multiple columns

1 Answers1