Power Query: Revision Time – Part 2

Welcome to our Power Query blog.  Today, I continue to create a refreshable revision timetable by calculating the number of subjects to be studied.

As my salespeople take a well-earned break, many students here in the UK are preparing for exams in the summer.  To help my own offspring get organised, I volunteered to create a refreshable printable revision timetable.  This is the result:

I needed a list of topics, and to begin with, I created extra entries for topics that required more timeslots:

We agreed on half-hourly slots, and I created a grid so that my daughter could indicate the slots that she wouldn’t be able to revise. 

When I first designed the solution, I included some Excel functions, but since this is a Power Query blog, I will ensure that I only use Power Query functions (apart from some formatting at the end!).

Last time, I converted my data into two [2] Tables: Subjects and Availability.  I extracted Subjects to Power Query.

I created the Availability query by taking  a copy of Subjects, and amending it:

Now I have the subject and slot information, I need to perform some basic calculations.  I could have performed this in Excel, but as this is a Power Query blog, I will be using M here.

The first number I need is the number of subjects.  An easy way to calculate this begins with taking a reference copy of the Subjects query:

Using a reference copy means my new query will be updated if Subjects changes.  I name my new query ‘Subjects_Total’ and select the ‘Group By’ functionality from the Home tab:

I have selected the Advanced options.  Although the defaults in the aggregation section are correct in that I do wish to count rows, I do not want to group them by Subject.  If I hover the cursor to the right of the grouping section, I have more options in Advanced mode:

In particular, I may delete the grouping:

I choose to do this, and then click OK:

To access the number in the cell, I click in the cell and right-click to ‘Drill down’:

This gives me a query consisting of a number:

Calculating the number of slots on Availability will require a difference approach, since I need to consider the values in multiple columns:

I will describe a method to achieve this next time.

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