Power Query: Revision Time – Part 11
19 June 2024
Welcome to our Power Query blog. Today, I continue to create a refreshable revision timetable by randomising the subject slots I need for the timetable.
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-1715274309.png/9fa4928e14c7422c8be9c83417b61922.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-1715274356.png/2734faf8bb0c4e8f3c8cb772fe0d7ba3.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-1715274377.png/bbef9530a5dd6e6f91072736ed0f9cc7.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-1715274404.png/413d7ef9314ea3ca270945c0b5f5b027.jpg)
I created the Availability query by taking a copy of Subjects, and amending it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1715274421.png/c10f40de073ab43a9f508ece457af593.jpg)
In Part 2, and Part 3, I calculated the number of subjects and the number of slots on Availability.
This gave me both totals:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1715274443.png/2cfbc9eb1a21686e35d2beb4789969e5.jpg)
In Part 4, I calculated how many times each subject will appear in a new query, remembering to round up to whole slots.
This told me the number of slots that each subject should have for my example is three [3]:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1715274461.png/d917c7c59d5b66cf2219a2c9f1650088.jpg)
In Part 5, I created a table where each subject appeared three times (i.e. the number of times given by Subject_Slots).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1715274478.png/2a017b84b1e503112bcfaf1e5561eae2.jpg)
In Part 6, I randomised the order of the slots ready to add to the timetable:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1715274671.png/a8ca9f195c1932f5ffa66e4f4ab0c151.jpg)
In Part 7, I took a reference copy of the query Availability which I called TimeTable and began to transform it so that it is ready to receive the slot data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1715277472.png/32061c33de06fa20fadf085e860e7280.jpg)
In Part 8, I appended the slot number data to the rest of the TimeTable query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1715278202.png/397ea2f7e8ae1320663b671cc2ef24e3.jpg)
In Part 9, I removed the duplicate values.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1715279313.png/020275cd12d7df6385b8bf3895a5d1f5.jpg)
However, as I said in Part 8, I could have transformed the data in TimeTable by merging instead of appending, and I looked at that last week:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image19-1715280085.png/2fd49ab8a55fdf4f67f659a79519e813.jpg)
Back in the TimeTable query, it’s time for me to merge with the Random_Subject query. I choose to ‘Merge Queries’ from the Home tab, and link Slot Number of TimeTable to Index of Random_Subject:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1715280702.png/04fe39e2a2d4a194a055aff4727505b6.jpg)
I am using the default ‘Left Outer’ join – note that I have 42 matches, which is the number of subject slots I have generated. I click ‘OK’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1715280729.png/0072ecb0ffe5e94b26c23d428707484f.jpg)
I only need to extract the Subject from the Tables in column Random_Subject:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1715280747.png/9c119c8af76178a6a0112ac389e6330f.jpg)
Rather than two [2] columns, Value and Subject, I need a single column that either contains ‘x’ or the contents of Subject. If I am always going to use ‘x’ as my unavailable indicator, I could just replace the null values with ‘x’ in Subject. However, strictly speaking, this is hard coding. Instead, I will create a ‘Conditional Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1715280769.png/9473dd16c5df2eaa961633b7d6bf6f0e.jpg)
The logic I have used is that if the value in Subject is null, I populate the new column Slot with the value in Value. Otherwise, I populate the new column Slot with the value in Subject.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1715280789.png/cb109b5d3bedda01644eebe7ddfeea09.jpg)
Now, I can select the columns I wish to keep (Time Slot, Attribute and Slot), and right-click to ‘Remove Other Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image19-1715280808.png/70b1291cac4ee8bd672337768ac5ceb6.jpg)
Finally, I need to pivot the Attribute column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image20-1715280825.png/85e3db8912b31486833f1d5238f6faa8.jpg)
The new columns created will contain the values in Slot. I need to choose ‘Don’t Aggregate’ from the ‘Advanced options’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image21-1715280844.png/67044a30a26f1e5fdb161f250a8b4546.jpg)
This gives me the timetable I need:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image22-1715280863.png/80c94f9ad7ef3b99e61af28d31e64135.jpg)
Next time, I will load this query and show how it works.
Come back next time for more ways to use Power Query!