#Project Roadmap #PowerBI report pack with #AzureBoards data #PPM #ProjectManagement #MSProject #Office365 #PowerPlatform #Dynamics365 #CDS #Odata #AzureDevOps

March 16, 2019 at 5:38 pm | Posted in Administration, App, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , , , , , ,

This post follows on from my last post where I posted about using Azure DevOps Azure Boards in Project Roadmap, in case that you missed it here is the link: https://pwmather.wordpress.com/2019/03/15/project-roadmap-azuredevops-azureboards-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-cds/

In this post we will cover combining Azure Board data into the Roadmap Power BI report pack I released. Here is the blog on the default Roadmap Report pack if you haven’t seen that yet: https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

I covered a similar topic the other week but for combining Project Online data here: https://pwmather.wordpress.com/2019/03/08/project-roadmap-powerbi-report-pack-with-projectonline-data-ppm-projectmanagement-msproject-office365-powerplatform-dynamics365-cds-odata/

With the Power BI Roadmap report set up and loading data from your Roadmap service which includes linked items from Azure Boards, we will now edit that Power BI report to bring in Azure Boards data. Firstly click Get Data > Odata Feed and enter the Azure DevOpps OData API URL like below:

https://analytics.dev.azure.com/organizationName/_odata/v1.0/

image

For details on the Azure DevOps OData API in Power BI, see this article: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/access-analytics-power-bi?view=azure-devops

Click OK and sign in as required. In the Navigator window select Projects and WorkItems plus other tables as required:

image

Click Edit to load the Power Query editor. Edit the queries as needed, such as removing columns, remaining columns etc. but ensure you leave the ProjectId and WorkItemId columns in Projects and WorkItems queries as these are required to join the Azure Boards data with the Roadmap data. Once finished you should have at least 9 queries like below:

image

Click Close and Apply in the Power Query editor. Set up the relationships between the Projects table and RoadmapRowLinks and WorkItems table and RoadmapItemLinks:

image

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and work item level data from my linked Azure Boards Projects and Work Items:

image

It’s that simple!

Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: