Power Query: Revision Time – Part 1
10 April 2024
Welcome to our Power Query blog. Today, I begin to create a refreshable revision timetable by extracting the inputs.
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1711722123.png/8e68412786dd353df0505cefeb5ac548.jpg)
Let’s have a look at what I put together. I started off with the inputs. I needed a list of topics, and to begin with, I created extra entries for topics that required more timeslots:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1711722151.png/536475727f15c9ea63d433aec238ce75.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1711722174.png/ad3d6c961ea78670656385d1698b517e.jpg)
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!).
Before I extract my data, I am going to convert it into two [2] Tables, which will allow me to name the Tables before I extract them. I may use ‘Convert to Table’ from the Insert tab, or CTRL + T. In each case, I check the regions and indicate that my Tables will have headers.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1711722217.png/7a7ea50f8aa1cb04163658f3cc1e053f.jpg)
I name the Tables Subjects and Availability respectively.
Next, I click somewhere in the Subjects table, and on the ‘Get & Transform Data’ section of the Data tab, I choose to extract data ‘From Table/Range’. I use the icon indicated in my version of Excel: yours might look slightly different.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1711722251.png/cb706336f1e006f1fd5b1ea901637e50.jpg)
In the Power Query editor, I see the query Subjects. Note that another benefit of converting the data to a Table before I extracted it, is that it is extracted as a Table and not a list.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1711722293.png/1e1939053968cc9991ca191e56494cf0.jpg)
I could go back to the workbook and repeat this process for the other Table, but instead I will take a duplicate copy of the Subjects query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1711722312.png/f01bf6529d93e274b57a4d5e4038246c.jpg)
I call the new query Availability:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1711722333.png/377f4bac739589666f93da18075f1a80.jpg)
I delete the ‘Changed Type’ step, and look at the Source step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1711722351.png/ff85b0a24bd48aa42a3adb1fb3b02863.jpg)
The M code is:
= Excel.CurrentWorkbook(){[Name="Subjects"]}[Content]
If I change the Name parameter to ‘Availability’, I will pick up the second Table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1711722385.png/6d0d62fba9183136a6afc48df024efc3.jpg)
Since I have chosen to detect types in the settings, another ‘Changed Type’ step has been generated, but I clearly need to change Column1:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1711722414.png/04383ce3e856ddc79c2f1e1e36d397ea.jpg)
Since I know this is a time, I change the data type to time by clicking on the number icon in the heading:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1711722432.png/908dd15f4d930e70ba4b3ec0f3a746f2.jpg)
I am prompted to include this transformation in the existing ‘Changed Type’ step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1711722449.png/f9c9e74ab6ba765ac61fa136c9c43ff6.jpg)
I click ‘Replace current’ and rename Column1. My data is ready to be transformed next week:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1711722473.png/cd4df343bc2e4a4de924fd1ddfdd1207.jpg)
Come back next time for more ways to use Power Query!