Excel Power Query in Planning

You might have heard before about the Microsoft Excel Power Query. However, have you ever used it in your daily planning tasks? In this article I want to give you some more information about the application of power query in construction planning. Don’t worry, it is very simple and very easy.

What is power query?

I will not talk from a technical point of view. I will talk from a practical and simple point of view. This of power query as your personal assistant. If you have a secretary that will listen to your requests and do it perfectly whenever you ask. I call it FASTY

Following I will give you some examples of the excel query applications in the construction planning field. When you get used to it, I am sure you will use in your daily tasks.

1- Replace values in your sheets:

We all copy data from primavera P6 to use in reports in excel. One common issue is when you copy the data, some blank spaces before the activity Id is there. Using excel power query, you can setup an automatic process to remove blank spaces from a column automatically.

2- Convert P6 data distribution to pivot table

If you are trying to create an excel pivot table to show the weekly, monthly progress for example, you can copy the data from resource assignment; however, the data will be stored in every week/month column. Using power query you can unpivot the columns to store the date and value in 2 columns only where you can perfectly use in your pivot tables.

3- Combine 2 tables:

Using Power Query, we can combine two tables. This is very useful when you are doing cost loading. If you defined each activity with its relevant BOQ item, then you can combine these two tables to create a new table with the activity budget cost.

4- Create Custom Calculated Columns

This is very useful feature. Let’s say you copy some data from Primavera, and you use this data in excel report. However, you need to do some calculations before being able to create your report. Like for example you need to multiply the % completed by certain resource in your schedule. You can do it easily using power query custom calculated columns.

5- Conditional Columns

You can create a column that gets its data from another columns based on column value. Something similar to VLockup. But here, it is much faster for large data and automated. It means whenever your data is changed, the same conditions will apply. No need to drag formulas. I am using this feature in cost control process to define the materials costs, labor costs, equipment costs…etc.

6- Using different columns from different tables

By creating a relationships between your tables, you can create a pivot table using different columns from different tables. I am using it to multiply the actual % completed by activity budget cost materials for example to get the actual cost should be spent. Then I can compare it with the actual spent costs to define the cost variances.

To be honest, the excel power query helped me a lot in my daily tasks as a planning manager. It is amazing when you develop a data model to perform several steps with your data and store it in one query where you can refresh in few seconds to update all your data accordingly. Having such feature encouraged me to create a powerful course for cost control in construction projects using this technique. It is powerful, simple, and easy. You are welcome to check the course from the below link.

Want to learn more?

Check the below link for cost control course where you will learn Excel Power Query and implement in cost control data model. This is an smart way to manage your project costs with minimal efforts.