Forum Discussion

ajisharavind_99's avatar
ajisharavind_99
Advocate I
3 years ago
Solved

Pivot or unpivot Multiple Columns

Hi Everyone,  

I am very new to Power Query, and I am looking for a solution to pivot or unpivot data into tabular format.

I believe someone can very quickly help me.

Note: The prgrs break down name will change ID by ID.

Sample Excel file 

 

Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hi,

     

    = Table.FromRows(
       List.TransformMany(
          Table.ToRows(Previous_Step),
          each {0..6},
          (x,y) => {x{0}, x{1+y}, x{8+y}, x{15+y}, x{22+y}, x{29+y}, x{35+y}, x{42+y}}
          ),
       type table [ID = text, Prgrs Breack Down = text, Actual = number, Approved Plan = number, Current Plan= number, Int Plan = number, QPIT VAR = number, Var = number]
    )

     

    Stéphane 

4 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi,

     

    = Table.FromRows(
       List.TransformMany(
          Table.ToRows(Previous_Step),
          each {0..6},
          (x,y) => {x{0}, x{1+y}, x{8+y}, x{15+y}, x{22+y}, x{29+y}, x{35+y}, x{42+y}}
          ),
       type table [ID = text, Prgrs Breack Down = text, Actual = number, Approved Plan = number, Current Plan= number, Int Plan = number, QPIT VAR = number, Var = number]
    )

     

    Stéphane 

    • ajisharavind_99's avatar
      ajisharavind_99
      Advocate I
      3 years ago

      slorin Thank you very much 🙂 working well , Colud you explain how this code is working (for my understanding)

      • slorin's avatar
        slorin
        Super User
        3 years ago

        Hi,

         

        Table.ToRows(Previous_Step)

        --> x = each row of Previous_Step (x is a list)

         

        each {0..6} 

        --> y = 0 ,  y = 1, .... y = 6

         

        (x,y) => {x{0}, x{1+y}, x{8+y}, x{15+y}, x{22+y}, x{29+y}, x{35+y}, x{42+y}}

        x = first row and  y = 0 

         {x{0} <=> ID, x{1+0} <=> StageDesc1, x{8+0}<=>Actual1, x{15+0}<=>AprvdPlan1 ...}

         

        next x = first row and y = 1

        { x{0} <=> ID, x{1+1} <=> StageDesc2, x{8+1}<=>Actual2, x{15+1}<=>AprvdPlan2 ...}

        ...

        List.TransformMany split all rows into 7 lists ( y = 0 to y = 6)

         

        and Table.FromRows build a table with this list of list

         

        Stéphane