Power Query: Revision Time – Part 4
1 May 2024
Welcome to our Power Query blog. Today, I continue to create a refreshable revision timetable by calculating how many times each topic should be included.
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-1713261208.png/36015253d6b1b236b05c278a3b55a193.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-1713261228.png/46803f3bfb2e225e8ec094824cc970f0.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-1713261247.png/ad0b22d368626b748c6730752e4bcfd4.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-1713261264.png/15fe7e192f5a8b7581f8179c191133d4.jpg)
I created the Availability query by taking a copy of Subjects, and amending it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1713261324.png/0b586d1df26dfe0f0a2568a06fbf851c.jpg)
In Part 2, 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-1713261343.png/52f2a8e0f920b18f24f5083ffd6355cc.jpg)
Last time, I calculated the number of slots on Availability by unpivoting some of the data first, as I needed to consider the values in multiple columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1713261366.png/cca394dd6ee4c83a4f9e3b6073ba8f42.jpg)
Solving this, I was then able to group and drill down as I had done for Subjects_Total. I now have both totals:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1713261384.png/600cae60343193a056c8e518c8be419b.jpg)
I can calculate how many times each subject will appear. I create a new blank query by right-clicking in the Queries pane:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1713261403.png/de40b849e35611bd43ad8d32596cf64c.jpg)
I call the new query ‘Subject_Slots’ and I type in a very simple division, using the Intellisense to help me:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1713261434.png/5c8bec6d66237cc5e636fb7089851f03.jpg)
This gives me the answer, here 2.8:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1713261450.png/931f29e4c532029868b86a0f47530f2e.jpg)
However, I am not interested in parts of slots. I need to round up to the nearest whole number, so that I have no empty slots. Since my current query is a number, I have a ‘Number Tools’ menu containing transformations suitable for numbers. On the Transform tab, I have the option to ‘Round Up’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1713261470.png/e084ca9087f55fa9630001bb4bef8f62.jpg)
This tells me the number of slots that each subject should have, in this case, three [3]:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1713261493.png/3fb31e4ba20b85ecbbe9fcbb126820a3.jpg)
Now I know how many times each subject will appear on the timetable; I am ready to allocate the slots. Next week, I will create a query containing a row for each subject slot.
Come back next time for more ways to use Power Query!