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:

1. Project Online Engagements data;

2. Power Query

3. Office Apps

4. Pivot Tables in Excel

5. Power BI

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

1. 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!

1. Make sure you have Resource Engagements data

1. Open Excel

2. I’m using the Excel 2016, which contains Power Query built in

3. In case you are using Excel 2013, you just need to download the Power Query here: Microsoft Power Query for Excel

4. Create a blank workbook

1. Access the tab Data (Power Query in Excel 2013)

2. Create a New Query from Other Sources

3. Then select From OData Feed

1. Access your Project Online OData feed by typing the following URL:

2. https://[TenantName].sharepoint.com/sites/pwa/_api/ProjectData
   

1. in the Navigator window

2. select the table: Engagements

3. Click on the arrow next to Load

4. Select Load To…

1. In the Load To window

2. Select Only Create Connection

3. Check Add this data to the Data Model

4. Click Load

1. You will see the Workbook Queries panel

1. Create your own layout, but remember the Excel Online limitations.

2. Select any cell

3. Click on Insert à Pivot Table

4. In the Create PivotTable windows select Use this Workbook´s Data Model

5. Select Existing Worksheet

6. Click OK

1. Drag and drop the fields below to the following areas:

2. ProjectName à ROWS

3. ProposedWork à VALUES (Sum)

1. Access the Insert tab and click on People Graph

2. People Graph Is a cool Office App, which will allow us visualize this data better

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

1. Select our Pivot Table area, like below

2. Click on Create

1. Repeat the steps below and create a new Pivot Table

2. Replace ProjectName by ResourceName (Role/RBS/Skills)

3. I’m using only TOP 10 most demanded Roles, but it’s up to you

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

1. Feel free to add as many slicers as you want!

1. You can also be creative and try different layouts!

1. Upload your file to SharePoint Online or OneDrive for Business

2. You will have a great report with full Excel Online functionalities

3. Below you can see when we are using the slicers

4. I want to check all the demand only for one specific project

1. Import your file to Power BI

2. Open the report directly in Power BI
   

1. If you have the Power BI Pro license you can schedule data refresh hourly

2. But even with the FREE version of Power BI you can schedule data refresh daily

Hope you like!

#GEEK #OData #PowerBI #ProjectOnline