Power Query: Revision Time – Part 11

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:

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.

In Part 8, I appended the slot number data to the rest of the TimeTable query.

In Part 9, I removed the duplicate values. 

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:

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:

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

I only need to extract the Subject from the Tables in column Random_Subject:

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:

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.

Now, I can select the columns I wish to keep (Time Slot, Attribute and Slot), and right-click to ‘Remove Other Columns’.

Finally, I need to pivot the Attribute column:

The new columns created will contain the values in Slot.  I need to choose ‘Don’t Aggregate’ from the ‘Advanced options’:

This gives me the timetable I need:

Next time, I will load this query and show how it works.

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