Pivot or unpivot Multiple Columns

[ajisharavind_99]

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 

[slorin · Accepted solution]

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]

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

[slorin]

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

[ajisharavind_99]

Thank you very much for your time for explaining the code