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.
- 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
