Power Query: Revision Time – Part 3

Welcome to our Power Query blog.  Today, I continue to create a refreshable revision timetable by calculating the number of slots available.

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:

Last time, I calculated the number of subjects by referencing the Subjects query and grouping the results.  This gave me the Subjects_Total query, consisting of a number:

Calculating the number of slots on Availability will require a difference approach, since I need to consider the values in multiple columns:

I will start by creating a reference query from Availability in the same way that I did for Subjects.  I will call it Slot_Total:

To get all the slots available in one column that I may group, I could unpivot the day columns.  However, at the moment, the available slots contain the value null:

If I were to select Time Slot and right-click to ‘Unpivot other columns’:

I might think I have all the slots in one column.  However, if I look at the filter options, I only have the slots with value ‘x’: the null values have been removed during the unpivot process.

To rectify this, I need to replace the null values with an empty space.  I will insert a step after the Source step.  I select all columns by selecting any column heading and using CTRL + A.  I access the right-click menu:

I receive a warning telling me I am inserting a step, and I choose to Insert:

In the dialog that appears, I choose to replace any null value with an empty cell:

I click OK and go to the last step:

The blank values have been included, and I may uncheck the ‘x’ value:

This gives me all the available rows, and I may group them and drill down into the total cell using the same method I used last week:

Now I have the number of subjects and the number of slots, I can calculate how many times each subject will appear.  This is where I will continue next time.

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