You can use the Project Online Power BI Content Pack template to report on the Project for the web data contained in your Dataverse. You can also customize and expand the the template's capabilities by changing the file to suit your organization’s needs.   

For instructions on connecting to your data sources and installing this template, see Connect to Project for the web data through Power BI desktop.  

The template provides connections, or queries, for all the base entities of Project for the web, including Projects, Tasks, Bookable Resources, Project Buckets, Project Team, and System User. It also provides the connections required to retrieve data from the Roadmap service (Roadmap, Roadmap Items, and Roadmap Item Links), and a version is available that includes Project Web App (PWA) connections for complete portfolio reporting scenarios. All template versions are available on GitHub at aka.ms/ProjectReports.

The template provides an example of how Power BI and Project for the web can work together to provide powerful insights into project portfolios.  

This template file was created to help you customize your organization's report content and expand insights into different aspects of portfolios. As more functionality is added to Project for the web, the reports will be customized further to offer additional visibility into the data managed by the new features.

It’s often tempting to query for and add all data from a source, with the thought that you might use it in your report later. However, depending on the size of your data set, querying for all data might significantly impact the performance of the report. The more data the report needs to load, the longer the load and refresh can take, and the longer the visualizations may take to render on your screen.  

We recommend that you focus on the current reporting needs for your organization or specific reporting scenarios, and optimize your queries to retrieve only the data needed to support your visualizations.  

The Project Online Power BI Content Pack template is an example of how to optimize queries for the topic being explored. Some columns that are useful for other topics have been excluded. You can extend the Project entity in the Dataverse in PowerApps by adding more columns. For information on how to do this, see How to create and edit columns. Those columns can be added into the queries to expand or change the focus of the Power BI report.

Example: Exploring location and program columns

  1. On the ribbon, select Edit Queries, then in the Power Query Editor, under Queries, select Projects.

  2. On the right side, under Query Settings, next to Removed Other Columns, select the gear icon.

    The Power BI Query editor with numbered sections to show how columns are being added. Number 1 shows Projects under Queries; number 2 shows which listings to remove under Applied Steps and number 3 shows custom fields that an organization has added to its Projects entity.

  3. In the Choose Columns dialog box, search for any custom columns that you’d like to add to the report. In this example, we added the cr512_location and cr512_program columns from the list of custom columns the organization had in their Projects entity. You can add custom columns or additional built-in columns.  This is an example of how to choose columns to add in query editor. The columns being added in this example are called cr512_location and cr512_program.

  4. In the Power Query Editor window, select Close & Apply, and then select Refresh on the main Power BI ribbon.

Adding the columns in this example allowed the creation of the following charts that show the project count by program and location.  

This is a chart showing the project count by location and program.

Another example would be to use the built-in column createdon to show a count of projects created by month.

If you’re developing a report and want to look at additional customized project management artifact data as it relates to the projects in the system, you can add new data tables by querying for custom Dataverse entities. The template design has made it easy to bring in new entities and connect them to Projects in your data model.

  1. Select Edit Queries.

  2. Under Queries, right-click the Project Bucket query, and then select Duplicate.

    The Query editor  window with Project Bucket and Duplicate selected in the Queries pane. Project Bucket appears in the Name field under PROPERTIES. The Renamed Columns listing is highlighted in the APPLIED STEPS pane.

  3. For the newly duplicated query currently named Project Bucket (shown in the image above), in the Query Settings pane, under APPLIED STEPS, delete the steps for Renamed Columns and Removed Other Columns.

  4. Again under APPLIED STEPS, next to Navigation, select the settings gear.The query editor with Issues selected in the Queries pane. The number 2 appears next to the word Issues which is listed in the Name field under PROPERTIES, and the number 1 appears next to Navigation, which is selected under APPLIED STEPS in the Query Settings pane.

  5. Select the entity to which you want to navigate. In our example we’re selecting an entity labeled cr512_issues, which in this example is a custom entity in the Dataverse that stores issue-related data for our projects.

  6. Rename your query in the Query Settings pane in the Name box.  Here we’ve renamed the query "Issues". 

  7. To choose the columns you’d like to keep, on the Home tab, select Manage Columns > Choose Columns

    Note: You can also rename your columns to something a little more user friendly (this will help your reports look more professional and improve the Q&A feature in the Power BI Service).

  8. In the Power Query Editor window, select Close & Apply.

  9. Navigate to your Model view and create relationships as needed with your new entity and the Projects entity.

Note: The relationships you create will depend on your specific scenario. In this example, we have connected the Issues table to the Projects table (Project ID in Projects to cr512_msdyn_project in Issues).

The Projects panel on the left has Project ID selected. The Issues panel on the right has cr512_msdyn_project selected. There are horizontal lines extending from each panel. The line on the left is a bit lower than the line on the right. Between the two lines if a box with an arrow pointing up. These lines with the arrow helps to show that a connection is being established between the two tables represented by the two panels.

Using the additional data and relationships, you can create a doughnut chart using Projects, Project Name and Issues, Count of cr512_issueid. The chart shows that the two projects with the most issues are the Automated Tracking Sensor project and the Power BI Migration project, each with a count of 4 issues.

A circular chart in the center has names of projects appearing along the outside of the circle. Each project name has a number associated with it to show the number of issues it has. Alon the top right of the circle are two projects that each have the number 4 next to them. The names of these projects and the number associated with them have a rectangular border around them.

Alternatively, the Auto Detect relationships feature can often find new relationships in your data. On the ribbon of the main Power BI screen, select Manage Relationships, and then select the Auto Detect button at the bottom. Power BI will discover the new data relationships in the report. When complete, it will display how many new relationships it found now that new data and columns have been added. You’ll want to review these new relationships to ensure that the resulting data model is what you expect.  

As you add more connections (queries) to the report, you may choose to allow the Power BI service to provide you with insights from your dataset.  

Additionally, pinning visualizations to a dashboard can lead to using Q&A in the Power BI service to get additional insights from the dataset.  

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×