Answered by:
Project Server, Power BI & ODATA

Question
-
Hi
I'm using Power BI to create a suite of repots around our planning data in Project Server (2016/Online).
We use the ODATA connector to connect to the data.
Many of the tables (Assignments, Tasks, Resources) have common columns which can be used to join them and PowerBI gives you the option to expand the columns in subordinate tables without the need to create complex data models.
However when I try and access columns from an associated table I always get the following error:
Wondered if anyone else had seen the same error and if they'd been able to find a solution?
Tuesday, December 12, 2017 1:26 PM
Answers
-
Ahh i see $expand is not implemented in the Project Odata service. You will need to add the other tables in then set up the relationships between the tables or table merges etc.
Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Saturday, February 3, 2018 4:03 PM
Tuesday, December 12, 2017 3:46 PM
All replies
-
Hello,
I'm not able to see the error (screenshot?). Are you able to paste the error text in?
Many Thanks
Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
Tuesday, December 12, 2017 1:58 PM -
Sorry - text error as follows:
DataSource.Error: OData: Unsupported query option encountered: $expand
Details:
DataSourceKind=OData
DataSourcePath=https://xxx.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSetIt doesn't seem to matter which two tables I link I always get the same error when I try the join - I wondered if there was some underlying problem with the version of ODATA...
Tuesday, December 12, 2017 3:23 PM -
Ahh i see $expand is not implemented in the Project Odata service. You will need to add the other tables in then set up the relationships between the tables or table merges etc.
Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Saturday, February 3, 2018 4:03 PM
Tuesday, December 12, 2017 3:46 PM -
Thanks for the speedy reply...I have tried to merge the tables under the query designer with varying levels of success.
If I just link two tables and select a few columns it generally works....however if I then try and pull columns from a third table I get the same error:
DataSource.Error: OData: Unsupported query option encountered: $expand
Details:
DataSourceKind=OData
DataSourcePath=https://xxx.sharepoint.com/sites/UKPWA/_api/Projectdata/AssignmentTimephasedDataSetTuesday, December 12, 2017 4:19 PM -
I would look too use relationships in Power BI between the data sets.
Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
Tuesday, December 12, 2017 5:29 PM -
Hi, best using the a pre-defined .pbit file, pretty much all of the tables and relationships are defined.
Check out the following Power BI template files
Microsoft's https://github.com/OfficeDev/Project-Power-BI-Content-Packs
Paul Mather's https://gallery.technet.microsoft.com/Online-Power-BI-Report-abcb3c3b
and finally
https://gallery.technet.microsoft.com/office/Microsofts-Online-Power-BI-0260454a
Tuesday, December 12, 2017 5:31 PM -
Thanks for the pointers....Wednesday, December 13, 2017 11:47 AM
-
Your welcome, hope it helps you resolve the issue.
Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
Friday, December 15, 2017 9:00 AM