The story behind Power Query

By Michael Garvey, BDIC Programme Officer,  China Joint Colleges Office

I have worked in China-facing roles for my entire UCD career, which stretches back to 2010, including in my current position as Programme and Operations Manager with the China Joint Colleges.  This type of position is certainly a bit different to what a programme manager might do within a traditional UCD school but I believe the practices I have learned can be applied in all contexts.

One such practice is the gathering of data to make more informative reports.  I typically work with information from both within the UCD system and from external sources, including Chinese university partners.  Bringing this information together in a coherent fashion is vital to being able to do my job, and working with the large amounts of data involved means that I need a way of doing this without needing to spend time formatting and combining things together. It’s also important to be able to make this process repeatable and easy to teach to others, and that’s really why I believe Power Query in Excel is so useful.

I started working on the problem of student registration towards the end of 2019, around the middle of semester one. One of the most common occurrences in BDIC was to receive a message from a student describing some sort of issue with their module registration. The frequency of these types of queries and the fact that we were still receiving them mid-way into the semester seemed strange to me, plus the fact that sometimes we didn’t quite know how or why the problem had happened. This seemed to suggest a fundamental issue with the way we managed student registration-we didn’t have any simple means of tracking registration and the dynamic was one of waiting for problems to find us rather than having a good overview of the process and thereby identifying issues before they became problems.

The particular situation of BDIC with regard to registration is in some ways more challenging that for what you might call a ‘traditional’ UCD school and also easier in other respects. Our programmes were spread across a number of Schools and Colleges in UCD, meaning we didn’t have a general  overview of the situation. On the other hand, our students don’t have the option of picking electives or programme streams-we know what they should be doing in each stage. This should mean that if we know what students have already done, we can determine exactly what they should be doing. This requires the combination of reports from academic history, registration, curriculum management and other Infohub sources. In addition, to make our reporting more accessible to our Chinese partners, it would be necessary to add additional information such as Chinese name, BJUT student number and more.

My initial approach to all of this was to learn enough basic coding (Python and VBA in Excel) to be able to combine and sort these reports to get the information I needed. However, this is not the best approach if you want to teach others and set up a system that’s easily repeatable. Power Query offered a way to not only combine and sort reports, but also to find and retain pathways to the information. It’s also easily repeatable-once you have found the information you need and made your report, you just need to refresh your data and run the report again. This also has the advantage of offering you the possibility of retaining the data only for as long as you justifiably need it-you can get rid of the data without having to rework your entire report. And, the process is not very difficult to learn. Essentially, Power Query has made the task of managing registration very straightforward and means we no longer have problems chasing us.

In a broader context, this whole exercise I think shows the importance of having access to the right information and tools. It can often be the case that we know what we would like to do but are not sure whether we have either the correct tools or information to get the job done. Or, it’s possible we don’t know what the possibilities are until we see the information and tools that we do have at our disposal. It’s certainly much easier to manage a process such as registration  when you have a clear overview of the situation. It also means that because I spend much less time solving one particular type of problem, I have more time for other stuff, which I think benefits everyone really.