Add Workflow Stages to your Power BI Report

Reporting on Projects by Stage can be a valuable way to track the progress of your portfolio: How many projects do we have in Ideation vs. Execution?  How many projects are approaching Completion?  Unfortunately, several steps are required to be able to get to this data via Project Online OData in Power BI.  Here’s how!

First get Data > OData feed > Enter your URL, followed by /_api/ProjectData

For the sake of the exercise, pull in only two tables: Projects, and ProjectWorkflowStageDataSet

Now, go into Query Editor

Next you’ll add some fields from the Workflow table onto the Projects table.  We’ll do this by Merging queries.  First, select the Projects table query by clicking one on it on the left hand query panel, then click the Merge Queries button in the Combine section of the ribbon.

The Merge window will open, and the Projects table will be on the top.  Select ProjectWorkflowStageDataSet on the bottom, and then highlight the ProjectID field in each table.  You’ll want a Left Outer Join, so that you pull in all Projects, then the corresponding stages for any of those Projects in the workflow table.

Now what will happen is the ProjectWorkflowStageDataSet table will get “stuck” to the right side of the Projects table, so each Project Row will now also contain its applicable Workflow information, as well. But to see that Workflow info, we’ll need to expand that table, and select the fields we want to see. Click the double arrow button in the column heading, then select the column(s) you want – the StageStatus column being the most important one – we will get to that in a moment.

Click the “Select all columns” box to deselect all columns, then check StageName, and StageStatus.

From the StageStatus column, you will need to filter out all values except for 1, 2, and 3 – these values represent the current stage (can only be one of the three) in which the project resides. You can find more information on that in this Microsoft Docs page.The easiest way to do this would be to first filter out one of them, then click on the gear in the applied step so you can then modify that filter to manually remove the others even if they’re not currently on the list. You do this to “future proof” the report so that if there ARE ever any that have those values, they will be included.

Then click the gear in the filter applied step:

Then use the Advanced selection and the Add Clause button until your filter looks like this:

Now you can Close and Apply your query changes, and you’ll be able to report on your Stage Name for each Project. Happy Reporting!