Leading up to the launch of our Power BI Foundations for Project Controls course, in this post, I will show you how to create a Power BI Cashflow Report. Managing project costs efficiently is crucial for keeping complex projects on track and within budget. Primavera P6 offers robust tools for assigning and tracking costs across timelines, but translating this data into actionable, visual insights can sometimes be a challenge. I will walk you through the process of exporting cost data from Primavera P6 into Excel and then into Power BI. By leveraging these tools, you’ll be able to transform raw cost data into dynamic cashflow reports, helping you make data-driven decisions with ease and precision.
Download your project files here
Exporting Data from Primavera P6 to Excel
- Ensure Cost-Loaded Schedule: Confirm that your Primavera P6 schedule has all necessary costs assigned.
- Export Resource Assignment Data: Navigate to Resource Assignment, select all data, and copy it into an Excel sheet.
- Configure Export Settings:
- Group & Sort: Set to “None”
- Spreadsheet Field: Choose “Budgeted Cost”
- Filters: Omit filters if only costs are assigned
- Columns: Include “Activity ID,” “Activity Name,” and “Resource ID”
Figure 1 – Resource Assignment Layout
- Select Resource Assignment Data: In the resource assignment layout, select all relevant data for export.
- Copy and Paste to Excel: Copy the selected data from Primavera P6 and paste it directly into an Excel sheet for further use.
Figure 2 – Excel File Format - Save the Excel File: After pasting the data, save the Excel file in a designated location for easy access during Power BI import.
Exporting Data from Excel to Power BI
- Open Power BI: Start by opening a blank report in Power BI.
- Import Excel Workbook: In the Home tab, go to Get Data > Excel Workbook, and select your saved Excel file.
- Transform Data: Click on Transform Data to open the Power Query Editor.
- Verify Data in Power Query Editor: Review the imported data in Power Query Editor to ensure it aligns with your reporting requirements.
Figure 3 – Power Query After Import - Clean Up Data: In Power Query Editor, delete any unnecessary rows and columns that contain no data.
- Set Headers: Under the Home tab, select Use First Row as Headers to make the first row of your data the header row.\
Figure 4 -Power Query After Steps 5 and 6 - Unpivot Columns: Select the columns Activity ID, Activity Name, Resource ID, and Spreadsheet Field. Then, under the Transform tab, click on Unpivot Other Columns to restructure your data for better analysis and reporting.
- Adjust Data Types and Rename Columns: Assign the appropriate data type to each column (e.g., date, text, number) to ensure data consistency. Rename columns as needed for clarity and ease of use in reporting.
Figure 5 – Power Query After Steps 7 & 8 - Apply Changes: In the Home tab, click on Close & Apply to load the transformed data into Power BI.
- Choose Visualization Type: In the Report View, select the Line & Clustered Column Chart from the visualization pane.
- Set Chart Axes: Drag Date into the X-Axis and Total Cost into the Y-Axis (Columns) to visualize your cashflow over time.
Figure 6 – Visuals for Step 11 - Format the Chart: After adding Dates and Total Cost, customize the chart’s appearance by adjusting colors, labels, legends, and other formatting options to suit your preferences and improve readability.
Figure 7 – Final Output
Now you know how to use Excel as an intermediary between Primavera P6 and Power BI enables the creation of comprehensive, dynamic, and accurate reports. This process facilitates seamless data transfer and allows for advanced visualization and analysis of project cashflow data in Power BI. Dive deeper into visualization in Power BI and more in our Power BI Foundations for Project Controls on-demand course.
