none
_api/ProjectData - how does it decide what Data to present in PowerBI RRS feed

  • Question

  • I am using Power BI to report data from our instance of Project Online. I use the /pwa/_api/ProjectData OData connection to get data into Power BI. The OData feed includes task, resource, project and risk and issue information - so far so good. However no custom risk or issue site column data is obtained using the OData feed - is there any way to understand what /pwa/_api/ProjectData decides to make available in Power BI and if possible to edit this to include the Data that I would like to include in my Power BI reports?

    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Friday, October 23, 2020 11:32 AM

Answers

All replies

  • Hello,

    Open the Power BI Report then open the Power Query editor by clicking the Transform data button on the home ribbon. In the Power Query editor window, click one of the queries then of the right hand side you will see the Applied Steps in the Query Settings panel. I guess there is an applied step that removes other columns. Double click that to see the list of columns, select the additional columns that you want to include. Repeat for the other queries.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, October 23, 2020 1:14 PM
    Moderator
  • Thanks for the suggestion Paul - I took a look and whilst there is a step that removes columns for risks when I inspected it there was no sign of the custom site columns employed in our risk list.

    The site columns that I am looking for are "managed" through site columns from within Site Settings in PWA and are included in a list content type that is used in the risk log for each site - I have to admit I still struggle to understand the ultimate source of some elements of the risk and issues lists in project sites.

    I suspect that because these site columns do not reside in the same location as OOB risk columns like probability and impact that this is the reason why they do not show up when looking for removed columns in the applied step.

    Thanks for the suggestion, it was not something that had occurred to me.


    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Friday, October 23, 2020 1:39 PM
  • Tnanks for the links Paul - I had tried the first approach before but not got very far.

    I tried again using an OData feed as follows:

    https://PROJECTONLINESITEURL/_api/web/lists/GetByTitle('Actions%20Log’)/Items()?$Select=Title,Created

    I get the dialogue box - Unable to Connect - Access to the Resource is forbidden.

    The Actions Log URL includes - /Lists/Actions%20Log/ and the list has columns with the names Title and created.

    I am able to connect to PWA in PBI using OData so I suspect I have not got the URL quite right - what is the significance of the ’ element of the URL in your example?


    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Friday, October 23, 2020 2:48 PM
  • Try the URL in the browser first to ensure the URL is constructed correctly for an example Project Site and that you have access. Then try in Power BI. Please do not copy the examples in the blog as that encodes certain characters incorrectly. In GetByTitle is will be the list name in quotes such as ('Risks').


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, October 23, 2020 3:09 PM
    Moderator
  • Sorry Paul - I am not the most techie of users - I have tried various things to no avail. For example

    https://VALID_URL/sites/pwa/ACCESSIBLE_SITE/_api/web/lists/Risks/Items()Select=Title

    results in my getting the following displayed in the browser

    I guess I am making a rookie error and that once I am over this hurdle I could probably make progress following the rest of your Blog.



    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   


    • Edited by Dominic Moss Friday, October 23, 2020 3:41 PM image
    Friday, October 23, 2020 3:39 PM
  • Hello,

    The URL for Risks would be: {projectSiteUrl}/_api/web/Lists/GetByTitle('Risks')/items


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    • Marked as answer by Dominic Moss Monday, October 26, 2020 3:39 PM
    Friday, October 23, 2020 4:36 PM
    Moderator
  • Thanks for that Pointer - I am making progress and can see the custom site columns for the selected site in the OData in Power BI. I will persist with the rest of your Blog article now that you have helped me over that hurdle.

    With good wishes,

    Dominic

    Microsoft Project Evangelist

    Twitter:   LinkedIn:    Web:   

    Monday, October 26, 2020 3:39 PM