cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Project Goal Statement in Project Online with OData

Hi.  I am connecting to Project Online data with OData in Power BI desktop.  There is some additional project data that I need to access that is not returned with OData.  I need to access the 'Project Goal Statement'. This is located on the Project Site page.  How do I go about doing this when it is not returned with the OData feed?

 

Thanks,

Sarah

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Re: Project Goal Statement in Project Online with OData

Hey @ssalisbury1 ,

 

Yes, you can get that information from the default page.  You have to grab the web part / web page URL of that default page.  So, what you are showing is exactly the URL that you grab from.  You want to use that URL you are showing and to insert the ProjectUID and then it should work.  You are correct that this is not a separate "data entity" type of item that you can just get - you have to use that URL and then grab the information field (Project Goal Statement text box).

 

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

View solution in original post

5 REPLIES 5
Highlighted
Impactful Individual
Impactful Individual

Re: Project Goal Statement in Project Online with OData

HI  ssalisbury1,

 

The Project Online API is quite limited for custom items - particularly when you are getting data from the Project Site.  We run into this all of the time when we customize the Issues or Risks page.  The way we have gotten around this is to connect to the SharePoint Project Site URL and then get the data we need from there.  You might get lucky that you have a way to directly link the two tables.  But, when we can't do that, we build a middle table with all of the URL's from the Project Side for the associated Project Site and then the SharePoint side URL as the link between tables.

 

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

 

 
Highlighted
Frequent Visitor

Re: Project Goal Statement in Project Online with OData

Hi @collinq,

 

Thanks for that suggestion!  The issue we are facing is we are looking in all of the sharepoint tables available to us and we still can't find the Project Goal Statement.  For example, for this project below, we have searched all of these available tables, and there is no record of the Project Goal Statement.  The project goal statement is on the Project Site page, and the tables shown below for this project are everything within the Project Site page.

 

When searching the tables returned in the OData feed, we also don't find the Project Goal Statement.

 

2020-06-05_12-56-40.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2020-06-05_13-04-18.png

Impactful Individual
Impactful Individual

Re: Project Goal Statement in Project Online with OData

Hi again @ssalisbury1 ,

 

You will have to build a parameter and then refer to that parameter dynamically in your M code.

 

Param1.png

The easy version of the top of the M code would be (example using "Tasks") where you then would tie in the Site URL:

let
Source = OData.Feed(#"PWA Site URL" & "/_api/ProjectData"),
Tasks_table = Source{[Name="Tasks",Signature="table"]}[Data],

......

 

BUT, I bet you need the ProjectID as well since this will be for each and every project.

You will have to click on a couple of the Project Goal Statements from different sites to get the right URL link and then get that into the M code.  You will have to replace the URL to get to your custom URL.

 

This is an example where I got the data into the query from a custom URL along with the Project data (custom addition on the Project Site) so this will have to modified a bit to get to your Project Goal Statement (not quite sure what I was doing with all of those add/remove columns but I left it in there if it is helpful...probably isn't helpful though  🙂  😞

 

let
Source = OData.Feed(#"PWA Site URL" & "/_api/ProjectData/[en-us]"),
Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
#"Added Custom" = Table.AddColumn(Projects_table, "ScheduleLink", each #"PWA Site URL" & "/project%20detail%20pages/schedule.aspx?projuid=" & [ProjectId]),
#"Spaces Replaced Project Site Link" = Table.AddColumn(#"Added Custom", "ProjectSite", each Text.Replace([ProjectWorkspaceInternalUrl]," ","%20")),
#"Renamed Columns1" =Table.RenameColumns(#"Spaces Replaced Project Site Link",{{"ProjectId", "Project Id"}, {"EnterpriseProjectTypeName", "Enterprise Project Type Name"}, {"ProjectBudgetWork", "Project Budget Work"}, {"ProjectCalendarDuration", "Project Calendar Duration"}, {"ProjectCost", "Project Cost"}, {"ProjectCostVariance", "Project Cost Variance"}, {"ProjectCurrency", "Project Currency"}, {"ProjectDescription", "Project Description"}, {"ProjectDuration", "Project Duration"}, {"ProjectDurationVariance", "Project Duration Variance"}, {"ProjectFinishDate", "Project Finish Date"}, {"ProjectFinishVariance", "Project Finish Variance"}, {"ProjectFixedCost", "Project Fixed Cost"}, {"ProjectIdentifier", "Project Identifier"}, {"ProjectLastPublishedDate", "Project Last Published Date"}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "PWA URL", each Table.AddColumn(#"Renamed Columns", "PWA URL", each #"PWA Site URL")),
#"Removed Columns" =Table.RemoveColumns(#"Added Custom1",{"PWA URL"}),
#"Added Custom2" =Table.AddColumn(#"Removed Columns", "Custom", each #"PWA Site URL"),
#"Renamed Columns2" =Table.RenameColumns(#"Added Custom2",{{"Custom", "PWA URL"}}),

.........

 

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

 

 

 

Not quite 

 

 

 

 

 

 

Highlighted
Frequent Visitor

Re: Project Goal Statement in Project Online with OData

Thanks @collinq .  I understand how to get to those different parts of the project pages using M & parameters.

 

I am trying to access the "default" page for the project.  It seems to be inaccessible via OData or Sharepoint, though.  Is it possible access data from the "default" page for a project?

 

This is an example of a project's default page and where the Project Goal Statement is located.

 

2020-06-05_15-58-35.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I get to the page above by clicking on a project, and then clicking on 'Project Site'. This takes me to the 'default' page, and this is the page I can't find in OData or Sharepoint.

 

2020-06-05_16-05-48.png

 

Highlighted
Impactful Individual
Impactful Individual

Re: Project Goal Statement in Project Online with OData

Hey @ssalisbury1 ,

 

Yes, you can get that information from the default page.  You have to grab the web part / web page URL of that default page.  So, what you are showing is exactly the URL that you grab from.  You want to use that URL you are showing and to insert the ProjectUID and then it should work.  You are correct that this is not a separate "data entity" type of item that you can just get - you have to use that URL and then grab the information field (Project Goal Statement text box).

 

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors