Adding Calculated Row / Calculated Items Using Power Query

Following are the Revenue and Cost of Revenue of 3 Giant technology Companies for 3 years

ENTITY ITEMS 2016 2017 2018
APPLE Revenue         215,639,000         233,715,000         182,795,000
APPLE Cost of Revenue         131,376,000         140,089,000         112,258,000
GOOGLE Revenue            90,272,000            74,989,000            66,001,000
GOOGLE Cost of Revenue            35,138,000            28,164,000            25,691,000
MICROSOFT Revenue            85,320,000            93,580,000            86,833,000
MICROSOFT Cost of Revenue            32,780,000            33,038,000            27,078,000

Your Objective is to Compute the Gross Profit (Revenue – Cost of Revenue) for each Company and Add it as a row to the Original Table. Following is the Final Output

ENTITY ITEM  2016  2017  2018
APPLE Revenue   215,639,000   233,715,000   182,795,000
APPLE Cost of Revenue   131,376,000   140,089,000   112,258,000
APPLE Gross Profit      84,263,000      93,626,000      70,537,000
GOOGLE Revenue      90,272,000      74,989,000      66,001,000
GOOGLE Cost of Revenue      35,138,000      28,164,000      25,691,000
GOOGLE Gross Profit      55,134,000      46,825,000      40,310,000
MICROSOFT Revenue      85,320,000      93,580,000      86,833,000
MICROSOFT Cost of Revenue      32,780,000      33,038,000      27,078,000
MICROSOFT Gross Profit      52,540,000      60,542,000      59,755,000

Power Query with its powerful Unpivot and Pivot features makes it very easy to do such stuff

Step#1: Load the Table in the Power Query

In Excel 2016…Go to Data Tab>>>Get & Transform>>> From Table

In previous versions of Excel you can use the “Power Query” tab

Picture Below shows this

Step2: Unpivot the Year Columns so that we have a single set of VALUES for pivoting in the next step

Select the year columns>>>Go to Transform tab >>>UNpivot

The picture below shows this

We get the YEARs unpivoted  into a single Column. We rename this Column to YEARS. This is shown below

Step3: Pivot the Items Column so that we can compute the difference between Revenue and Cost of Revenue

Why? Because Power Query functions use Column references…..Unlike Excel there is no way to select a ROW or a cell

You might be thinking why do we not use Excel instead…..The answer is that …With Excel you can do it for sure…..but what if there were 500 countries. This will take you a substantial amount of time subtracting Cost of Revenue from Revenue for each and every entity. Power Query will do this in less than a minute

Select the ITEMS column>>> Go to Transform tab>>> PIVOT>>> Use Value Column as VALUES…………Picture below shows this

You will get the following

Step4: Now You can easily add a Column Gross Profit

Go to “Add Column” tab>>> Custom Column……..Picture below shos this

Step5: Now you can Unpivot back the ITEM Columns

select the REVENUE/Cost of Revenue/Gross Profit Columns>>>>UNPIVOT

Step # 6: Final Step… You can Pivot back the YEARS column……..

Select the YEAR Column>>>Transform Tab>>> Pivot using VALUE column as VALUES

You will get the desired final output

Step#6 is not recommended though….. if you intend to use pivot tables with your final data Step #5 should be the final step

Click here to Download Excel file  and see the steps in the Query Editor and play with them

Share This: