Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hey @Anonymous ,
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).
Proud to be a Datanaut!
Private message me for consulting or training needs.
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.
Proud to be a Datanaut!
Private message me for consulting or training needs.
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.
Hi again @Anonymous ,
You will have to build a parameter and then refer to that parameter dynamically in your M code.
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"}}),
.........
Not quite
Proud to be a Datanaut!
Private message me for consulting or training needs.
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.
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.
Hey @Anonymous ,
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).
Proud to be a Datanaut!
Private message me for consulting or training needs.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |