Power Query: Revision Time – Part 2
17 April 2024
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1713193956.png/42f1ea24b25c47ac542c597805873a9e.jpg)
I needed a list of topics, and to begin with, I created extra entries for topics that required more timeslots:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1713193976.png/2c1f230e1f9e3f0bbf351cc97f3ee835.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1713193997.png/36ff5871a92ded76fd430f119d192511.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1713194047.png/64146b75339467e7606f0ca450b4f59e.jpg)
I created the Availability query by taking a copy of Subjects, and amending it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1713194333.png/a26ac35c42115f9d8dd51dcf7c1662ef.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1713194365.png/e68225ca21f8f3413089eb95c49fd586.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1713194594.png/b3cd032e3788eef0d387aa4e8b439191.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1713194612.png/6b3ee4474ef06352285ce9f8f97433b4.jpg)
In particular, I may delete the grouping:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1713194631.png/46dfa84a9c00baf0f0319e5f2e8e155a.jpg)
I choose to do this, and then click OK:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1713194649.png/ec36d956d1bd5fb543a83d7c4247b4c0.jpg)
To access the number in the cell, I click in the cell and right-click to ‘Drill down’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1713194667.png/53b48ec534b205e3e4982be548ebc5c9.jpg)
This gives me a query consisting of a number:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1713194685.png/d2cc82c103607bf36380e4558ea66097.jpg)
Calculating the number of slots on Availability will require a difference approach, since I need to consider the values in multiple columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1713194704.png/60c7dc747fbd5ed1467a933ae4699678.jpg)
I will describe a method to achieve this next time.
Come back next time for more ways to use Power Query!