Intelligent Use of Tech: Power Query

In this mini-series, we are sharing some of the learning from our team who have been testing and trialling new applications as part of our Intelligent Use of Tech forums. This week we focus on Power Query where Nathan Bilton has taken the next step from using Excel to exploring the Power Query function.

Here are Nathan’s thoughts.

The Basics

Power Query is a default plugin to Excel and is a tool used for data extraction, loading and transformation. You can use it to gather, clean and import data into excel, as well as automating the sorting process, without using code, so that you don’t have to spend time or effort manually inputting the same requests every time you upload data. This means you can gather data from a wide range of sources and use this in Excel without the need to convert the different file types yourself.

The benefit of Power Query to those who use data from multiple sources is the time you save by not having to manually making changes to clean the data and get it into the structure you need. Instead, you programme Power Query with a set of rules as to how you would like your data sorting and, once those parameters are established, it will automate the cleaning process whilst also preserving the data in its original form.

My Experience

Since joining Quantik, I have been introduced to the concept of data science and encouraged to build on my interest in this area. Whilst I don’t consider myself to be an expert, I am an avid learner, and I am keen to integrate that learning into my day-to-day duties as a Senior QS.

I have really enjoyed my exploration of Power Query because there are so many occasions, as a quantity surveyor, where I need to pull information from various sources and spend time cleaning that data so that I can use it for the thing I need.

The importance of clean data has become even more apparent through this learning journey. I work with multiple clients so data can come to me in different formats, from different systems, with different preferences as to the format of the output. Power Query allows me to quickly and easily remove errors, blanks and irrelevant data so that I can get on with focusing on the output.

As an example, I used Power Query to merge three data sets relating to three different projects quickly. They were civils projects so there was similarity in the data structure, as the work items were generally consistent, but there were differences in the activity types. I needed to profile the work performed over time and macro analyse the quantities, and Power Query helped me achieve this much quicker than reviewing each data set manually.

At Figure 1, you can see that I had statistics telling me whether there were errors or empty cells to resolve.

Figure 1

The benefit of being able to format data in a Power Query table and add data of the same data type does come with a challenge in that all the feed-in document / data need to be created in a similar manner otherwise the tool doesn’t know what data to pull (or I haven’t yet worked out how to overcome this issue).

This has been a hindrance when working with data provided to me in different formats however, when it comes to new issues, it has helped me focus on preparing templates which capture the data that I need and ensure that everyone uses those templates without exception.

Opportunities for Use

I have started to explore how this clean data can be used in Power BI to produce information that can be discussed with clients. I have trialled Power BI’s use using some dummy data and have been impressed with the way visuals can be created instantly. I can see benefits here where I am talking to a client about an issue and, if they ask questions, I can immediately re-work the data to present in a way that they understand. I am keen to build on this knowledge and see what I can achieve by adding in predictive analytics and what-if scenarios.

In my exploration of Power Query, and then Power BI, I have been wondering how else these tools could be used to reduce non-value add data processing type work.

The most obvious opportunity is project financial reporting where you often use multiple data sources to create a snapshot picture for management to review. I can see how my time would be more efficient if I was using Power Query each month to automate the data cleaning process, rather than going through that laboriously every month.

Then when it comes to the management review, being able to instantly work the data to answer a question would be very powerful. You often get different stakeholders in those meetings with different needs, as some want lots of detail and others just want a high-level picture. Using Power BI as a presentation tool would allow me to answer those questions at the time and allow the meeting to be focused on the key issues.

Could we even see those meetings become more frequent than a monthly process, because we have removed all of the manual work?

As a further consideration, could these tools help at the interface between contractors and subcontractors where they can both see the immediate impact of an issue and there is less disagreement because both sides are working with the same data. Would this allow more of a focus on mitigating risks, rather than arguing about who is at fault?

Final Reflections

Excel is a staple of the QS diet, and you find those who use it well tend to flourish in the early stages of their career because they can yield insights from data much quicker than others, and those insights inform and improve decision making.

I see Power Query being the next logical step on that journey because it allows us to remove so much wasted time and effort cleaning up polluted data sets so that we can get the insights that we need. We can do it in a more controlled and auditable way which means we are not searching around a spreadsheet trying to spot and resolve errors.

I do imagine Power BI becoming a go to tool for construction projects, particularly for meetings regarding time and cost performance involving multiple disciplines. The data comes into Power BI in a structured way and everyone in the meeting can explore the data and work it in a way that gets them the answers relevant to their role.

In the next mini-series, we will cover the six technical competencies that an RICS APC candidate will need to demonstrate if they are sitting their assessment. The series should help those who are looking to sit their assessment in session two this year, might inspire those who have been thinking about taking the assessment, will be a helpful reminder to those who are already MRICS, and will assist those who are counsellors in signing-off their candidates.

Keep an eye out for that and, in the meantime, enjoy the rest of your week!

Bringing The science of Quantik® to you

p.s. If anything in this article, or any of our articles, resonates with a challenge you are experiencing, check out our ‘Bringing The science of Quantik® to you’ initiative where we offer a tailor-made session aimed at helping you tackle your project or business challenge (link below).

<Form: https://form.jotform.com/241425931072350>

Back to articles