none
Project Server, Power BI & ODATA RRS feed

  • 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

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
    Moderator
  • 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/AssignmentTimephasedDataSet

    It 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

    Tuesday, December 12, 2017 3:46 PM
    Moderator
  • 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/AssignmentTimephasedDataSet

    Tuesday, 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
    Moderator
  • 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 


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Tuesday, December 12, 2017 5:31 PM
    Moderator
  • 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
    Moderator