Power Query: Revision Time – Part 5

Welcome to our Power Query blog.  Today, I continue to create a refreshable revision timetable by creating a list of all 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 respectively.

This gave me both totals:

Last time, I calculated how many times each subject will appear in a new query, remembering to round up to whole slots. 

This tells me the number of slots that each subject should have for my example is three [3]:

I am ready to allocate the slots.  My goal is to create a table with each subject appearing three times.  I will then allocate a random number to each subject slot so that I may put it into the timetable.  I could approach this in different ways.  I have decided to create a table based on the number of slots for each subject which I will then merge with the Subjects query.

I start with a new blank query by right-clicking in the Queries pane and choosing the appropriate menu path:

I call the new query Random_Subject.  I am going to use list functionality to create a simple list from one [1] to Subject_Slots: 

The M code I have used is:

This gives me a list, which I need to convert to a Table so that I may merge it:

I take the defaults to create the Table.  To allow me to merge with the Subjects query, I need something to link to.  I create a Custom Column from the ‘Add Column’ tab:

This very simple column, which I have called ‘Link’, has a value of zero [0] for every row. 

Before I go to the Subjects query to add a similar column, I select both columns and change the data type to ‘Whole Number’:

Now, I add a ‘Link’ column to Subjects:

Back in Random_Subject, I choose to ‘Merge Queries’ from the Home tab:

I use the column Link to merge Random_Subject and Subjects.  I take the default ‘Left Outer’ join, since that will link each row in Random_Subject to matching rows in Subjects, which will be all of them.  In this example, because the rows all have the same value for the Link columns, any joins other than ‘Left Anti’ and ‘Right Anti’ will give me the data that I need.

I choose to expand the Subjects column, choosing only the column Subject:

I Click OK, and then select the new column Subject, and right-click to ‘Remove Other Columns’:

I have a list of all the slots I need for the timetable, but I need to find a way to randomly insert them into the timetable.  This is where I will continue next week…

Come back next time for more ways to use Power Query!