If you’ve ever tried bringing a Primavera P6 export into Power BI, you know the struggle. I’ve been there too! Working in Project Controls and reporting on project schedules, I’ve lost count of how many P6 extracts I’ve had to wrestle with. No matter the industry, Infrastructure, Real Estate, Construction, P6 is everywhere, but its exports? Not exactly reporting-friendly.
Those endless date columns, scattered data, and ever-changing formats make it tough to get the insights you need. But don’t worry, I’ve got you! In this post, I’ll show you how to clean up and transform P6 data using Power Query, turning it into something structured, dynamic, and actually useful for reporting. Let’s dive in!
The Challenge: Working with Time-Phased Data
The P6 extract often comes in a wide format, where each column represents a different reporting period (e.g., months or weeks). A sample of the dataset may look like this:
The challenge here is that every new reporting period (e.g., forecast updates or baseline revisions) may introduce additional columns. This structure makes it difficult to filter, group, and analyse data effectively in Power BI if no transformation is done.
Power Query to the Rescue!
Power Query, the data transformation engine in Power BI, offers a range of tools to reshape, clean, and prepare data for analysis. In this case, the function ‘Unpivot’ plays a key role in converting the wide format of P6 exports into a long format, making it much easier to analyse trends over time and create dynamic reports.
Unpivoting is the process of transforming multiple columns into rows. In our case, each date column (e.g., 01/01/2024, 01/02/2024) will become a single “Attribute” column with corresponding values. The values scattered across the Date columns are also converted into one single column, ‘Value’.
Steps to Unpivot in Power BI:
- Load the P6 extract into Power Query.
- Select all columns except the ones that are Dates (Project Code, Activity Number, Activity Name, Activity Type, Monthly Cost Type).
- Click on Transform > Unpivot Columns > Unpivot Other Columns.
- Rename the new column headers:
- “Attribute” → “Month”
- “Value” → “Cost”
- Convert the “Month” column to a Date data type.
By using ‘Unpivot Other Columns’ instead of directly selecting all the Date columns, you can future-proof your query. This method ensures that any new columns added due to schedule delays are automatically included in the unpivot operation.
Now, our dataset looks like this:
Pivoting: Reshaping Data for Reporting
Sometimes, after unpivoting our data, we might need to pivot certain fields to create more structured tables. Pivoting is the opposite of unpivoting, it means we’re converting rows back into columns. Think of pivoting as a way to reorganize our data so it’s easier to work with. In our example, our dataset includes two different cost categories: ‘Planned Value’ and ‘Earned Value’, it is easier to have these as separate columns instead of all jumbled together.
Steps to Pivot:
- Select the “Monthly Cost Type” column.
- Click Transform > Pivot Column.
- Choose “Cost Value” as the values column.
- Set the aggregation to Don’t Aggregate.
Now, our final table is structured like this:
Why This Matters for Project Controls
By transforming the P6 extract into a structured format, we unlock the ability to create powerful visualisations. With the cleaned data, we can:
- Easily generate S-Curves to track planned vs. actual progress
- Use dynamic slicers to analyze project activities over time
- Integrate multiple data sources for richer project insights
Power Query is a game-changer for working with P6 data in Power BI. By unpivoting time-phased data and applying structured transformations, we make our project reporting more dynamic, scalable, and insightful. If you’re struggling with messy P6 extracts, try these steps in Power BI, and let me know how it goes!
I will soon publish a YouTube video showing how to do what I’ve described in this article. Stay tuned! And don’t forget to subscribe to my YouTube channel @AccessibleBI to make sure you receive notifications and get more content on project reporting.
Want more Power BI insights for Project Controls? Subscribe to Smart Frames for more articles and tutorials. Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!
