Power BI Cashflow Reporting – Exporting Data from Primavera P6

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

1. Ensure Cost-Loaded Schedule: Confirm that your Primavera P6 schedule has all necessary costs assigned.
2. Export Resource Assignment Data: Navigate to Resource Assignment, select all data, and copy it into an Excel sheet.
3. Configure Export Settings:
   1. Group & Sort: Set to “None”
   2. Spreadsheet Field: Choose “Budgeted Cost”
   3. Filters: Omit filters if only costs are assigned
   4. Columns: Include “Activity ID,” “Activity Name,” and “Resource ID”

      

4. Select Resource Assignment Data: In the resource assignment layout, select all relevant data for export.
5. Copy and Paste to Excel: Copy the selected data from Primavera P6 and paste it directly into an Excel sheet for further use.

   

6. 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

1. Open Power BI: Start by opening a blank report in Power BI.
2. Import Excel Workbook: In the Home tab, go to Get Data > Excel Workbook, and select your saved Excel file.
3. Transform Data: Click on Transform Data to open the Power Query Editor.
4. Verify Data in Power Query Editor: Review the imported data in Power Query Editor to ensure it aligns with your reporting requirements.

   

5. Clean Up Data: In Power Query Editor, delete any unnecessary rows and columns that contain no data.
6. Set Headers: Under the Home tab, select Use First Row as Headers to make the first row of your data the header row.\

   

7. 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.
8. 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.

   

9. Apply Changes: In the Home tab, click on Close & Apply to load the transformed data into Power BI.
10. Choose Visualization Type: In the Report View, select the Line & Clustered Column Chart from the visualization pane.
11. Set Chart Axes: Drag Date into the X-Axis and Total Cost into the Y-Axis (Columns) to visualize your cashflow over time.

    

12. 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.

    

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.