Power Query: Solid Summary

Welcome to our Power Query blog. This week, I look at a use for grouping.

I have some data for the rental income for my imaginary tent hire business. I want to see what seasonal variation I have.

I start by using ‘From Table’ on the ‘Get & Transform’ section of the ‘Data’ tab.

I take the defaults as I want to use the headings.

I will use the ‘Group By’ functionality in the ‘Transform’ tab.

I need to group by Department to get the totals for the year.

The ‘Basic’ grouping is not detailed enough, as I want to create an Average Income column and a Rows column.

When I select ‘OK’, I get one row.

The Rows column contains a table, which I expand.

This will give me a table with my original information, and an average income column.

I add a ‘Custom Column’ from the ‘Add Column’ tab.

This gives me the deviation from the average.

I can now filter on this column to see when I get a deviation of more than 20, whichever currency it may be in.

This gives me the dates when my income is maximised.

I make most money at Christmas, Easter and the New Year. Time to focus my salespeople on the lucrative summer market next!

Come back next time for more ways to use Power Query!