SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

As mentioned in previous posts, I said I will post on how to bring in Project Online data with the Roadmap service data in a Power BI Report. We will start off with the Power BI Roadmap report pack I published recently. If you missed it, it can be downloaded from the post below:

https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

With the Power BI Roadmap report set up and loading data from your Roadmap service, we will now edit that Power BI report to bring in Project Online data. Firstly click Get Data > Odata Feed and enter the Project Online Reporting API URL like below:

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

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 TaskId columns in Projects and Tasks queries as these are required to join the Project Online data with the Roadmap data. Once finished you should have at least 9 queries like below:

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

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and task level data from my linked Project Online Projects and Tasks:

It’s that simple, take a look and see what you think.