What is the most common way of looking at a construction schedule? For most, it usually involves going into P6 and adjusting groupings and applying filters (sometimes very lengthy and complex filters that become hard to follow). Once the set of activities are selected, a single view (layout) is generated and the schedule is printed to a PDF.
But what if you want to look at the schedule by subcontractor? Or by area and trade?
In this post, we will go through how to use the Gantt chart visual in Power BI to visualize schedule data from P6. Using Power BI, we will:
- Get schedule data from P6
- Create relationships between activities and activity codes
- Use the Gantt chart visual and create measures for simple schedule metrics
Getting schedule data from P6
The first thing we need is to get schedule data from P6. For this dashboard example, I utilized a construction schedule for an apartment project and selected some key data fields that will be helpful for our dashboard.
I selected all activities in P6 and pasted them into an Excel spreadsheet (Control A + Control C). The next step was to clean the start and finish dates by removing the actuals (the “A” in the dates) and constraint marks (the “*” in the dates). There are a few different ways to remove the actuals and constraints – I chose to use the Find and Replace tool in Excel.
The result is both the start and finish date columns can be formatted as dates. If the actual and constraint marks were not excluded, then Power BI would read the start and finish columns as text data and not allow for time intelligence analysis.
After saving and closing the Excel schedule data file, we open Power BI desktop and use the “Get Data” from Excel connection. From there, find the Excel file containing the schedule data, select the activities tab, and then hit “transform data” to go to the Query Editor.
The Query Editor, also known as Power Query, is where the majority of time is spent prepping and transforming data for analysis. In the Query Editor, you can perform a lot of functions that were usually done in Excel such as:
- Trimming leading and trailing spaces (Text.Trim)
- Concatenate multiple columns to make a unique ID
- Standardizing cost codes to be 6 digits (Text.PadStart)
One useful transformation is filling down rows in the Query Editor. For example, exporting an activity code list like a project-level WBS can be transformed to include each WBS level as a column.
This same process can be repeated for adding more activity codes from P6 and into Power BI. The main benefit is this allows you to slice and dice your schedule data the same way as in P6, but using powerful visuals.
Creating relationships between activities and activity codes
The real magic in Power BI is the ability to relate tables to each other – activity codes such as area or subcontractor can be linked to a list of activities.
Bill Jelen, founder of MrExcel.com, says Power Pivot (the engine for Power BI) is the best new feature to happen to Excel in the last 20 years. Power Pivot takes you from making Pivot Tables in Excel to making dynamic dashboards in Power BI. It supercharges your analysis and allows you to create endless smart formulas.
For this example, we will create relationships between the activities tables and the following “lookup” tables:
- Project WBS
- CSI Cost Codes
- Calendar
One of the great things I learned was from Rob Collie in his book was that filters flow downhill. This was an important concept I learned with how to visually organize lookup tables and data tables, which made data modelling easier to learn with a clean and organized structure.
The relationships created for this dashboard are one-to-many, where the lookup tables contain a column of unique values and the data table contains many instances of the same unique list of values. The relationships for this model include:
- Project WBS – Linked the P6 code value to the [Project WBS] field in the P6 Schedule Data table (data table)
- CSI Cost Codes – Linked the P6 code value to the [CSI Code] activity code field in the P6 Schedule Data table (data table)
Creating these relationships allow you to link data sets together without having to use a VLOOKUP and combine multiple tables. By linking lookup tables to data tables, you can create formulas that compare things like budget vs. actuals, or last month vs. this month. What took hours or days to produce (what-if analysis for management) can now be reduced to sometimes under an hour.
Use the Gantt chart visual and create measures for simple schedule metrics
After getting the data and creating relationships in a data model, the final (and very fun) piece is to create visuals. The ability to display data and quickly generate insights is very valuable, especially for many in project management.
Some of the visuals in this example dashboard include:
- Gantt chart
- Card
- Slicer
The most common way to view construction schedules is through the use of the Gantt chart, where P6 schedules are generated and emailed to various stakeholders. In Power BI, the same activities can be viewed using a Gantt chart and can be analyzed by various “slicers”.
Slicers are most commonly attributes that relate to the data (activities) contained within data tables. For example, activity codes like WBS, Trade, and CSI Code can be used as slicers to dynamically view specific sets of activities. A few views (layouts) that can be seen in the Gantt chart:
- All “Construction Summary” activities
- All “Construction Summary” activities that are Wood Framing
- All “Division 03 Concrete” activities
- All “Division 03 Concrete” activities for Building West
In Power BI, you can slice and dice data in many ways to help you analyze schedules and gain insights. Creating measures (summarizations like sums, averages, counts) allows you to analyze data that changes based on the slicers you select.
For example, I created two measures (Start MIN, Finish MAX) that find the earliest start date and latest finish date contained in the P6 Schedule Data table. These measures are dynamic and will display different start and finish dates if you select Trade = “Civil Underground” or CSI Division = “05 Metals”.
Measures are extremely powerful and allow you to generate insights across a variety of dimensions. Many simple measures can be created to view schedule data from P6 in Power BI:
- Count critical activities
- Count activities for a 3-week look-ahead
- Compare baseline dates to actual dates
Making charts is one of the most fun parts in Power BI and there are many amazing dashboards that you can see from the Power BI community. Creating simple measures to interact with schedule data from P6 is truly powerful and helps everyone quickly identify key metrics.
Power BI in the construction industry
In this post we went over how to create a Gantt chart in Power BI using schedule data from P6. We looked at how to get data from P6, how to create relationships to activity code tables, and how to create measures to summarize data in visuals.
Creating charts and being able to view activities from the schedule based on trade or WBS opens new avenues in project controls. Many construction companies are now utilizing Power BI to create a variety of dashboards – from tracking safety to schedule and cost metrics at the project level.
Are your projects or companies using Power BI or another business intelligence tool? What are some of the dashboards you have seen?
AI Alice Artificial Intelligence blog Construction Construction Management Construction Technology Dashboards Data Lagrange Lagrangeppm news P6 Power BI Power Query Primavera program managament Project Controls Project Controls Academy Project Management Reporting Scheduling webinar
Hey! very useful, but would you have a template to provide some guidance ?
Love it! Thank you helping us setup our enterprise reporting!
Love it! Thank you helping us setup our enterprise reporting!
Hey!
very useful, but would you have a template to provide some guidance ?