Power Query: Group Dynamics
10 October 2018
Welcome to our Power Query blog. This week, I look at how to calculate a group share.
This week’s blog looks at how to achieve a group share calculation in Power Query, in the same way that I can perform one in Excel. This is an exercise in how to manipulate data in Power Query; it is not how I would actually attempt to do this calculation, since it is much easier to do this in Excel or PowerPivot
I have the following data. I am interested in finding out which groups make up most owned items. I want to see the percentage of owned or outsourced items for each group.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
In Excel I can create an additional column and enter the formula
=[@[Items_in_Group]]/SUMIFS([Items_in_Group],[Owned/Outsourced],[@[Owned/Outsourced]])
which calculates the percentage of outsourced or owned items that are in the current group.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I can see that ‘Floor’ items make up 40% of the owned items.
I want to perform the same calculation in Power Query, so I create a new query ‘From Table’ from the ‘Get & Transform’ section on the ‘Data’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I start by grouping by my Owned/Outsourced column. This will allow me to make my first step - to calculate the total number of items in each group:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I use a basic group by statement, where I sum Items_In_Group. This will simplify my data so that I can see the number of items Owned and Outsourced.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I know that step ‘Changed Type’ shows me all the details, and ‘Grouped Rows’ shows me the total items owned and outsourced. One method I can use to get to my final calculation is to merge these steps by merging ‘Table2’ with itself (I will show another method later).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I choose the first column each time, and select the left outer join.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
From the M code generated for this step, I can see that the ‘Grouped Rows’ step has been merged with itself.
= Table.NestedJoin(#"Grouped Rows",{"Owned/Outsourced"},#"Grouped Rows",{"Owned/Outsourced"},"Grouped Rows",JoinKind.LeftOuter)
I can edit this step so that I merge the ‘Changed Type’ step instead.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I have now taken my ‘Changed Type’ step with all the details and merged the ‘Grouped Rows’ step. I only want Total Items Owned or Outsourced from the ‘Grouped Rows’ step, so I expand the table and pick this column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
As usual, I don’t want to ‘Use original column name as prefix’!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
Now I have the two key values for my percentage calculation, I can create a custom column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I divide the number of items in the group by the items owned or outsourced to see which group is mostly made up of owned and outsourced items respectively. I could also do this using the ‘Divide’ option from the ‘Standard’ part of the ‘From Number’ section.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image12.png/a1537847463e660a31158c8032525438.jpg)
In the same way I did for the Excel method, I convert my new column to type ‘Percentage’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image13.png/917da985be13220165c8d2823e95344f.jpg)
My calculations are consistent with the Excel method.
I can achieve the same result in another way. I can use M code if I don’t want to merge my queries. Instead of using the ‘Grouped Rows’ step above, I create a custom column which will carry out the grouping for me.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M code I have used is:
= Table.AddColumn(#"Sorted Rows", "Items_Owned_Outsourced", each Table.Group(Table2, "Owned/Outsourced", {"Total", each List.Sum([Items_in_Group])}))
The part of this which creates my grouped total is Table.Group(Table2, "Owned/Outsourced", {"Total", each List.Sum([Items_in_Group])})
I have grouped by Owned/Outsourced and summed Items_in_Group within this. This gives me a table very similar to the one created by the merging of the query with itself, which I can expand.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
As before, I can create my custom (or divide) column and convert it to a percentage. This time, I will use the divide functionality.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image16.png/d082e3477129350b8a2a589156028e63.jpg)
This gives me a new Division column which I can rename.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
Having converted this to the right data type, my data is consistent with the other results.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/10-oct/97/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
Come back next time for more ways to use Power Query!