Today we are going to discuss about numerous methods in Excel to Unpivot data.
Our input data is presented in a cross-tabulated or pivoted format, which can make the process of data analysis quite challenging.
Unpivoting refers to the transformation of data from a pivoted table format into a columnar format, which is better suited for data analysis.
Excel Formula Method:
Single cell formula - Office 365 Version.
Formula in J2 cell,
=LET(z,B2:H7,m,TOCOL(DROP(TAKE(z,1),,1)),t,DROP(z,1,1),p,DROP(TAKE(z,,1),1),
REDUCE({"Product","Month","Sales"},p,LAMBDA(a,v,IFERROR(VSTACK(a,
HSTACK(v,m,TOCOL(INDEX(t,MATCH(v,p,0),)))),v))))
Here, we refer the range B2:H7 once and the formula takes Month Name, Product Name and the Table of sales values excluding month and product.
Name 'z' in formula refers to the range B2:H7.
For Month,
Variable name is 'm',
=TOCOL(DROP(TAKE(z,1),,1))
TAKE(z,1) this will take first row from our range,
={"Product","Jan","Feb","Mar","Apr","May","Jun"}
To exclude "Product", we drop 1 column, then TOCOL helps us to convert this into vertical array.
={"Jan";"Feb";"Mar";"Apr";"May";"Jun"}
For Table which contains only sales value,
Variable name is 't',
=DROP(z,1,1)
We can simply drop first row and first column.
For Product,
Variable name is 'p',
=DROP(TAKE(z,,1),1)
We take first column and then drop first row,
={"A";"B";"C";"D";"E"}
Now we can get into REDUCE Function,
=REDUCE(initial_value,array,function)
In initial value argument, feed headers of our desired result,
{"Product","Month","Sales"}
Product list 'p' is our array which we are going to iterate,
in function argument use LAMBDA with 2 variables,
a -> State,
is the result of each iteration.
v -> Current,
is the value what we are iterating.
=HSTACK(v,m,TOCOL(INDEX(t,MATCH(v,p,0),)))
Here in first iteration, we simply do horizontal stacking of
v which is Product 'A',
m is vertical array of months,
third argument extracts matching row of sales values and converts this into vertical array,
so, output after our first iteration is,
IFERROR function helps us in error handling, which will replace errors to our current value of iteration as shown below.
Then VSTACK function helps us to stack output of each iteration vertically. Then we will get our desired result.
Power Query Method:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
in
Unpivot
This isn't just a part of our code; it represents the entire code.
When it comes to data transformation, this is the power of M-code.
We simple extract the table into power query editor,
then Unpivot columns other than "Product" column.
We can write M-code directly with Table.UnpivotOtherColumns function or
we can simply use user interface as shown below.
Excel User Interface Method:
Select anywhere inside our input range and use shortcut key ALT + D +P,
Old pivot table wizard step 1 of 3,
Select option button for 'Multiple consolidation ranges' and Click Next.
Old pivot table wizard step 2a of 3,
Select option button for 'I will create page fields' and then click Next.
Old pivot table wizard step 2b of 3,
Select the range and click on Add and then click Next.
Old pivot table wizard step 3 of 3,
Select the option button for new worksheet or Existing worksheet based on your preference and select the cell where we want to insert the pivot table, then click Finish.
In pivot table once you double click on Grand Total, separate sheet will be inserted with transformed columnar table in proper format.
Comentários