Excel, GEEK, OData, Power BI, Project Online
by Allan Rocha, MVP

How to Create a Resource Demand Dashboard using Project Online, Excel and Power BI

Let’s create and access a simple but effective Resource Demand Dashboard using:

  • Project Online Engagements data;
  • Power Query
  • Office Apps
  • Pivot Tables in Excel
  • Power BI

First of all, you must have a Project Online account and some Engagements data.

  • Engagement is the new feature in Project Online to support a full Resource Management capability.
    I’m talking about a model and giving a full demo in the video below.

Let’s get started then!

  • Make sure you have Resource Engagements data

  • Open Excel
  • I’m using the Excel 2016, which contains Power Query built in
  • In case you are using Excel 2013, you just need to download the Power Query here: Microsoft Power Query for Excel
  • Create a blank workbook

  • Access the tab Data (Power Query in Excel 2013)
  • Create a New Query from Other Sources
  • Then select From OData Feed

  • Access your Project Online OData feed by typing the following URL:
  • https://[TenantName].sharepoint.com/sites/pwa/_api/ProjectData

  • in the Navigator window
  • select the table: Engagements
  • Click on the arrow next to Load
  • Select Load To…

  • In the Load To window
  • Select Only Create Connection
  • Check Add this data to the Data Model
  • Click Load

  • You will see the Workbook Queries panel

  • Create your own layout, but remember the Excel Online limitations.
  • Select any cell
  • Click on Insert à Pivot Table
  • In the Create PivotTable windows select Use this Workbook´s Data Model
  • Select Existing Worksheet
  • Click OK

  • Drag and drop the fields below to the following areas:
  • ProjectName
    à ROWS
  • ProposedWork
    à VALUES (Sum)

  • Access the Insert tab and click on People Graph
  • People Graph Is a cool Office App, which will allow us visualize this data better

  • In the People Graph’s Data settings, click on Select your data

  • Select our Pivot Table area, like below
  • Click on Create

  • Repeat the steps below and create a new Pivot Table
  • Replace ProjectName by ResourceName (Role/RBS/Skills)
  • I’m using only TOP 10 most demanded Roles, but it’s up to you

  • You can also add some slicers to make your analysis even better

  • Feel free to add as many slicers as you want!

  • You can also be creative and try different layouts!

  • Upload your file to SharePoint Online or OneDrive for Business
  • You will have a great report with full Excel Online functionalities
  • Below you can see when we are using the slicers
  • I want to check all the demand only for one specific project

  • Import your file to Power BI
  • Open the report directly in Power BI

  • If you have the Power BI Pro license you can schedule data refresh hourly
  • But even with the FREE version of Power BI you can schedule data refresh daily

Hope you like!