Home

Using PowerBI to extract MSProject data

DerfNotwen
Occasional Visitor

I am using Power BI to extract and report on data from MS Project Online (odata). I am trying to generate a single report that, currently, is being performed from 2 PBI extracts and then combining the data into an Excel Pivot, as follows

Extract 1 – from Timesheet Lines
- ActualWorkBillable
- PeriodEndDate
- ProjectName
- TaskName
- TimesheetOwner
Extract 2 – from Assignments
- ResourceName
From Resources
- ResourceStandardRate (believe this is a custom field, containing the hourly rate)

The data from both extracts is then exported to Excel and a Vlookup used to match the TimesheetOwner to the ResourceName and obtain the hourly rate. This is then used to calculate the cost (ActualWorkBillable multiplied by ResourceStandardRate)

I have tried creating a New Column and using the Related approach (see below), which sort of works but only calculates the cost for some of the entries, however not for the “Top Level” task entries

DailyCost=Related([Resources[ResourceStandardRate])*[ActualWorkBillable]

Selecting other fields, while giving results for all entries, yields really unusually high totals.

 

Is there a way to create a single query to extract all the data I need ?


TIA

1 Reply

I'm moving your question to the Excel community for better visibility.

 

Also note that there is a separate PowerBI community you may want to ask your question in: https://community.powerbi.com/

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies