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

Project Online - Load custom Issues / Risks in PBI

Hi all,

New to PowerBI and I'm struggling with the following. I have a number of projects in a PWA online, and I have gotten the core data from there using the Project Online PowerBi Template app from here

 

The trouble I am having is that the issues and risks lists for each project have additional custom fields that I would like to show in PBI and that aren't part of the template app.

 

It is possible to query these lists using the sharepoint api and hence load the additional data that is missing.

 

Where I am having trouble is loading the data in the actual Risks or Issues table in PBI.

 

Focusing on the Issues table, for each custom field, I would like to add a custom column based on a function that would do these three things.

 

  1. For each issue, lookup the [Project Workspace Internal Url] (the sharepoint url). This value is available in the Projects table. I have managed to achieve this with the following custom column:

 

Custom = (let issueprojectid = [Project Id] in Table.SelectRows(Projects, each [Project Id] = issueprojectid)){0}[Project Workspace Internal Url]

 

  •  For each issue, lookup the issue number id (not guid), because that is the id that can be looked up in the sharepoint api (see next point). I have been able to achieve this by extracting the id into a custom column from the [Item Relative Path Url] which is available in the Issues or Risks table and has this format: "Lists/Issues/1_.000". The ID number is the "1" in that string. I have not been able to write this into a function.
  • Once I have the Issue Project Workspace URL and ID, I need to query the sharepoint site for the custom field, I am able to do so with hardcoded values in the following function:

 

let Source = OData.Feed("ProjectWorspaceInternalURL/_api/web/lists/GetByTitle('Issues')/Items(ID)?$Select=CustomField", null, [Implementation="2.0"])
in Source

 

 

As an alternate approach, I tried to create a custom column for the URL, another with the extracted ID, then finally a column that looked up the custom field value for the issue. Alas, this resulted in an error that stated: "Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination"

 

Any help you could provide in combining the three requirements into a single function would be greatly appreciated. I am simply banging my head against the wall at this point!

 

Thank you

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Project Online - Load custom Issues / Risks in PBI

@LGRD 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Project Online - Load custom Issues / Risks in PBI

@LGRD 

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)