How to surface Project Online resources added to a project but have no assignments in Power BI

[David_Howe]

In Project Online, we add resources from our enterprise resource pool to projects using the "build team" functionality.  Once the team is built for a project we may or may not assign some or all of those resources to tasks in the project.  I want to create a Power BI report that shows all of the resources on a project team regardless of whether they have assignments or not.  I can get the resources that have assignments, but I cannot figure out how to surface the resources that are on the project team but do not have assignments.  

[#2]

Hello @David_Howe ,

That data is not available in the Odata Reporting APIs, it is available in the CSOM REST API though, for example:
{pwaUrl}/_api/ProjectServer/Projects('{projGuid}')/ProjectResources

Replace {pwaUrl} with your PWA URL and {projGuid} with the project GUID.

Paul

[#3]

Thanks, Paul. So, If I want to surface that in a Power BI report, which type of data source would I choose under "Get Data" to use that CSOM REST API?

[#4]

Hello @David_Howe ,

An OData connection in Power BI / Power Query used to work so try that.

Paul

[#5]

Thanks again, Paul. I tried using the Odata connection but keep getting the following error: "Unable to connect. Details: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value."
If I remove the '{ProjGuid'}/ProjectResources from the end of URL and just use {pwaUrl}/_api/ProjectServer/Projects(), I get the full Projects table, but then if I try to expand the ProjectResources table, I get the same error. Any ideas what may be causing this?

[#6]

Hello @David_Howe ,

expands wont work for these endpoints. If you add a hard coded URL such as 

http://PWAURL.com/_api/ProjectServer/Projects('569-34543534-34534-23432')/ProjectResources 

Does that work?

[#7]

Unfortunately, no. I tried that first and that is when I got the error message so I tried getting the whole table and expanding from the query editor and got the same error message.

[#8]

As an update, I tested with a different Project Online instance and it worked. I was able to get the whole Projects table using http://PWAURL.com/_api/ProjectServer/Projects() then expand the ProjectResources table in the query editor. It works in one instance but not the other. Both are configured the same way as far as I can tell.

[#9]

Hello @David_Howe ,

That's good to know the expand works on one instance. For the other instance is there an error on the expand in Power BI? It might be data related.

Paul

[#10]

Yes, Paul. The error message is as follows: Unable to connect. Details: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value

[#11]

Hello @David_Howe ,

That's a tricky one, probably something you'd need to raise with Microsoft via your M365 admin portal to see if it is something that can be resolved in the data. It might be a certain resource type that is missing some data to enable the Odata expand.

Paul

[#12]

Ok, great. Will do. Thanks!