Power Query: Revision Time – Part 8

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

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

In Part 6, I randomised the order of the slots ready to add to the timetable:

Last week, 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.

Before I can merge this query with Random_Subject, I need to number the slots available. 

As is often the case with Power Query, there are a number of ways I can do this, but I have chosen to filter the data I need to number and then re-merge it with the rest of the query.  To make it easier to keep the order of my data, I will add an index column from the ‘Add Column’ tab before I start:

Next, I filter on Value to remove the rows containing ‘x’:

I may now add another index starting from one [1], which I name Slot Number:

I could merge or append my data to include the other rows that I will need for the timetable.  Before I start, I will rename the steps ‘Added Index’ and ‘Renamed Columns 1’ which I plan to join, to make it easier to follow the process:

I will look at each option: this week I append the data.  In the Home tab, I choose ‘Append Queries’, and choose to append to the current query:

This generates the M code I need.  

I change the code from:

to

This gives me some duplicates:

I will look at one way of dealing with these next week.

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