• Home
  • Blog
  • Courses
  • Tools
  • Jobs
  • Forums
Have any question?
(00) 201277767728
[email protected]
RegisterLogin

Login with your site account

Lost your password?

Not a member yet? Register now

Register a new account

Are you a member? Login now

Planning Engineer Est.
  • Home
  • Blog
  • Courses
  • Tools
  • Jobs
  • Forums
      • Cart

        0

    Excel in Planning

    • Home
    • Blog
    • Excel in Planning
    • Excel Power Query in Planning

    Excel Power Query in Planning

    • Posted by Hany Ismail, MSC, PMP
    • Categories Excel in Planning, Planning Using Primavera
    • Date October 14, 2020
    • Comments 0 comment

    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 queries in construction planning. Don’t worry, it is very simple and very easy.

    What is a power query?

    I will not talk from a technical point of view. I will talk from a practical and simple point of view. This power query is 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

    Power query in planning

    Following I will give you some examples of excel query applications in the construction planning field. When you get used to it, I am sure you will use it 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 set up an automatic process to remove blank spaces from a column automatically.

    blank
    Replace values in excel

    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 them in your pivot tables.

    blank
    Unpivot data in excel

    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.

    blank
    Combine 2 tables

    4- Create Custom Calculated Columns

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

    blank
    Calculated columns

    5- Conditional Columns

    You can create a column that gets its data from another column 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 the cost control process to define the materials costs, labor costs, equipment costs…etc.

    blank
    Conditional columns

    6- Using different columns from different tables

    By creating 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 that should be spent. Then I can compare it with the actual spent costs to define the cost variances.

    blank

    Following are some applications we used on the Intermediate Planning Course:

    • Calculate the quantity and value of each BOQ item per floor based on floor weight.
    • Creating activities ID based on Master Activity ID.
    • Create Relationships sheet for all floors based on Mater Activity relationships.
    • Create Relationships between Shop drawings approvals and relevant construction activities.
    • Create Relationships between materials delivery and relevant construction activities based on Master ID.
    • Assign Crew hours to activities and calculate the detailed working hours per activity and per trade.

     

    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 a few seconds to update all your data accordingly. Having such a 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 links for the cost control course and Intermediate Planning Course where you will learn Excel Power Query and implement it in the Baseline creation & cost control data model. This is a smart way to manage your project with minimal effort.

    Construction Project Planning Intermediate Level with P6 | Excel | PowerBi

    Cost Control for Construction Projects

     

    Tag:Cost control, excel in planning, P6 to excel, planning, power query, primavera to excel

    • Share:
    Hany Ismail, MSC, PMP
    Hany Ismail, MSC, PMP

    Hany Ismael is the founder and CEO of Planning Engineer Est. in Egypt. He has started his career back in 2003 as a site engineer, technical office engineer, planning engineer, planning manager, and finally planning department manager where he has been involved in several mega construction projects in Egypt and Saudi Arabia. In 2016, he established his own company in Egypt “Planning Engineer Est.” Hany gained his MSc degree in project management from Liverpool University-UK 2013-2016, PMP certified from PMI-USA 2010, and BSc Civil Engineer Tanta University-Egypt 2003. Hany provided more than 3,500 hours of planning and project management training on his website planningengineer.net, YouTube channel, and offline courses since 2011. He enjoys teaching project management in simple and practical way, and he developed several planning tools, techniques and courses.

    Previous post

    How to reduce total float in your schedule
    October 14, 2020

    Next post

    Primavera P6 Calendar 10 Tips and Tricks
    November 18, 2020

    You may also like

    Materials Procurement Scheduling in a Baseline Programme
    Materials Procurement Scheduling in a Baseline Programme
    7 May, 2022
    Remove Actuals using Global Change cover
    Remove Primavera P6 Actuals using Global Change
    24 January, 2022
    Bulk Delete Relationships in P6 cover
    Bulk Delete Relationships in P6
    20 January, 2022

    Leave A Reply Cancel reply

    You must be logged in to post a comment.

    Cart

    0

    Planning Courses

    Cost Control for Construction Projects

    Cost Control for Construction Projects

    $299.00
    Power Bi Monitoring and Controlling with P6

    Power Bi Monitoring and Controlling with P6

    $299.00
    Advanced Planning 3 Courses Package

    Advanced Planning 3 Courses Package

    $499.00
    Data Management and Reporting

    Data Management and Reporting

    $199.00
    Primavera Online Basic Course -Arabic

    Primavera Online Basic Course -Arabic

    $99.00
    Professional Planning Engineer (PPE)

    Professional Planning Engineer (PPE)

    $335.00
    Claims Management and (EOT)

    Claims Management and (EOT)

    $175.00
    Primavera Online Basic Course -English

    Primavera Online Basic Course -English

    $99.00

    Follow Us:

    About Us | Contact Us | Terms of Service | Privacy Policy | Refund Policy

    Copyright ©2012- 2018 planningengineer.net | All materials, unless otherwise noted, were developed originally for Planning Engineer Website. In the event that there is still a problem or error with copyrighted material, the break of the copyright is unintentional and noncommercial and the material will be removed immediately upon presented proof.

    • Planning Courses
    • Planning Jobs
    • Planning Blog
    Posting....