Power Query: Revision Time – Part 3
24 April 2024
Welcome to our Power Query blog. Today, I continue to create a refreshable revision timetable by calculating the number of slots available.
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-1713259932.png/04af58564982a6c325e004f9659e75a4.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-1713259953.png/1220c1d90ef7e2f0bba8b8fe0f90e133.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-1713259971.png/7c771cf6e6ebc698e79fb6e840addbee.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!).
In Part 1, 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-1713259997.png/187d6db5d5829b5c9a72bf54f34724cd.jpg)
I created the Availability query by taking a copy of Subjects, and amending it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1713260012.png/36a46ef921ae0a9bc94700c2e177c398.jpg)
Last time, I calculated the number of subjects by referencing the Subjects query and grouping the results. This gave me the Subjects_Total query, consisting of a number:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1713260029.png/3d823e4600ef19731807daf123f69216.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/./image7-1713260047.png/d7b513f6cabd7e41774133bb03156fa1.jpg)
I will start by creating a reference query from Availability in the same way that I did for Subjects. I will call it Slot_Total:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1713260062.png/5b9d9fc13c9c9f39ccf6001945a7cb1c.jpg)
To get all the slots available in one column that I may group, I could unpivot the day columns. However, at the moment, the available slots contain the value null:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1713260078.png/b855e28cab19c7add1986307ce17180d.jpg)
If I were to select Time Slot and right-click to ‘Unpivot other columns’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1713260097.png/ef47d1613466be9b50d3d08087d7d060.jpg)
I might think I have all the slots in one column. However, if I look at the filter options, I only have the slots with value ‘x’: the null values have been removed during the unpivot process.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1713260117.png/f14b45ed9a7fb3abb36faccf8edca9d6.jpg)
To rectify this, I need to replace the null values with an empty space. I will insert a step after the Source step. I select all columns by selecting any column heading and using CTRL + A. I access the right-click menu:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1713260138.png/a9d34f3a284972c738311c63bcf5f666.jpg)
I receive a warning telling me I am inserting a step, and I choose to Insert:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1713260155.png/6f31ee4e70f26285a28f6aa174558533.jpg)
In the dialog that appears, I choose to replace any null value with an empty cell:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1713260175.png/b887d6238fff7d3656afce1d71728079.jpg)
I click OK and go to the last step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1713260190.png/27c26f3005b1ff1cf63d3d8b43d056b2.jpg)
The blank values have been included, and I may uncheck the ‘x’ value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1713260216.png/b27db6f2d7cad93ea1828e6a8612f80b.jpg)
This gives me all the available rows, and I may group them and drill down into the total cell using the same method I used last week:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1713260237.png/8deea4945957d056ec34948d9b3c974f.jpg)
Now I have the number of subjects and the number of slots, I can calculate how many times each subject will appear. This is where I will continue next time.
Come back next time for more ways to use Power Query!