locked
Covert excel table with multiple numbers columns into datatable format RRS feed

  • Question

  • Hi, I've a table in excel where each row has static information or each resource, plus next few columns for month-wise information for same resource. It was OK to keep it as table, however now i'm planning to use data for pivot, and not able to use full functionality unless all monthly data stored under single column. i hope following illustration will help:

    Current:        
    Name Age Dept Jan Feb Mar
    AA 22 Finance 1000 1200 1250
    BB 25 HR 1100 1100 1120
    CC 23 Finance 1200 1250 1370

    Desired structure:    
    Name Age Dept Month Amount
    AA 22 Finance Jan 1000
    AA 22 Finance Feb 1200
    AA 22 Finance Mar 1250
    BB 25 HR Jan 1100
    BB 25 HR Feb 1100
    BB 25 HR Mar 1120
    CC 23 Finance Jan 1200
    CC 23 Finance Feb 1250
    CC 23 Finance Mar 1370

    Please guide me how to achieve this?

    Monday, January 20, 2020 8:33 PM

Answers

  • Hi

    When you're at the following stage/step with your Table in Power Query:

    Select columns [Name], [Age] and [Dept] as highlighted above > Right-click > Unpivot Other Columns

    By Default, unpivoted columns are named [Attribute] and [Value]

    To avoid a rename Step later you can change the column names now. Edit the Step in the formula bar and change the column names:

    If this solves your problem please mark as answer (can help others) - Thanks

    And as you seem to be new to PQ:


    Monday, January 20, 2020 10:16 PM