How to Create a Schedule Dashboard in Power BI using P6 Schedule Data

What is the most common way of looking at a construction schedule?

Do you use a schedule dashboard or special schedule report?

For many, it usually involves going into Primavera 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 the subcontractor? Or by area and trade?

In this post, we will show you how you can use the Gantt chart visual in Power BI to create a schedule dashboard and 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 dashboard metrics

Step 1- Getting schedule data from P6

The first thing we need is to get the schedule data from Primavera 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 progress. You can remove the actuals and progress in Primavera P6 directly before copy and pasting the data to Excel. Check this blog post for the detailed process of how to do so.

I chose to use the Find and Replace tool in Excel after copying and pasting data into Excel. 

The result is 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 the Power BI Desktop and use the “Get Data” from the 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.

Step 2- 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.

?? To continue reading, please click here......