Power Query: Revision Time – Part 6
15 May 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:

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!).
In Part 1, 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:

In Part 2, and Part 3, I calculated the number of subjects and the number of slots on Availability.
This gave me both totals:

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]:

Last time, I created a table where each subject appeared three times (i.e. the number of times given by Subject_Slots).

I now have a list of slots to insert in the timetable. I need a unique identifier for each slot, but I need them to be in a random order. I add a new custom column from the ‘Add Column’ tab:

I have used the M code:
= Number.RandomBetween(1,Slot_Total)
Which gives me a number in the range one [1] to Slot_Total. I could have used any number as the top limit, but to avoid more hard-coding, Slot_Total is a suitable number here.
Now I have some random numbers, I am going to sort the data in ascending order.

The data type of this column is not important as I will be deleting it shortly. Having ordered my data, I create an Index column beginning at one [1] from the ‘Add Column’ tab:

Now I have my data in a random order, I delete Slot Number.

Next time, I will prepare the data from the Availability so that I can add in the slot data.
Come back next time for more ways to use Power Query!