Forum Discussion

RodFromm's avatar
RodFromm
Iron Contributor
Jun 06, 2025

Re: Project Online - Business Drivers matrix table in Power BI


Try this and see if you can extract the PrioritizationId from the Analysis record.  I don't have this functionality enable in my PWA so you may have to fiddle with it a bit to make it work.

Steps to Create and Use the fxGetPrioritizationIdByAnalysisId Function

  1. Create the Function Query
    • In Power Query, create a new blank query.
    • Name it fxGetPrioritizationIdByAnalysisId.
  2. Add the Function Code
    • Copy and paste the provided code into the fxGetPrioritizationIdByAnalysisId query.
  3. Apply the Function in Your Data
    • Go to the PortfolioAnalysisProjects query.
    • Click Add Column > Invoke Custom Function.
  4. Configure the Function Invocation
    • Set the new column name to: PrioritizationID.
    • Choose the function query: fxGetPrioritizationIdByAnalysisId.
    • For the parameter, select the AnalysisId column.
  5. Finish
    • Click OK to apply the function and generate the new column

 

let
    GetSelectedFieldsFromAnalysis = (analysisId as text) =>
        let
            url = "https://<your PWA URL>/_api/ProjectData/PortfolioAnalysisProjects('" & analysisId & "')/Analysis",
            result = OData.Feed(url, null, [Implementation = "2.0"]),
            record = result{0},
            selectedFields = Record.SelectFields(record, {"PrioritizationID", "AnalysisName", "CreatedDate"})  // Add more fields as needed
        in
            selectedFields
in
    GetSelectedFieldsFromAnalysis

 

3 Replies

  • Woody's avatar
    Woody
    Brass Contributor
    Jun 06, 2025

    The function worked like this, taking into account there is only one analysis done, so all projects have the same AnalysisId:

    let
    GetSelectedFieldsForEachProject = (ProjectId as text) =>
        let
            url = "https://mycompany.sharepoint.com/sites/pwa/_api/ProjectData/PortfolioAnalysisProjects(AnalysisId=guid'4734f66d-b33f-f011-b02b-00155d7c9042',ProjectId=guid'" & ProjectId & "')/Analysis",
            result = OData.Feed(url, null, [Implementation = "2.0"]),
            selectedFields = Record.SelectFields(result, {"PrioritizationId", "AnalysisName", "CreatedDate"})
        in
            selectedFields
in
    GetSelectedFieldsForEachProject

     

    The following are the five tables with all private company data removed:

    https://1drv.ms/x/c/82e81f538612a159/EYUzGHmjzQJOiIh0tmXiKe4BeclO5873PXywIFOjFGiu6A?e=jC61bV

    https://1drv.ms/x/c/82e81f538612a159/ES6U63BJnkxDoFQ22oCEagwB3MHHGqULpasnd59HM06zVQ?e=lk4wEm

    https://1drv.ms/x/c/82e81f538612a159/EXsY3tuEL6NHhgwl2q1yJTkBmwwSdA9LhHHiaPCtmF1ndw?e=v1bWUK

    https://1drv.ms/x/c/82e81f538612a159/EVreqh2jAw1Jp6f3KpcUUj4BKkknPJq-g3NmUZ2H6fVTmg?e=4g5Lmx

    https://1drv.ms/x/c/82e81f538612a159/ETiSJCziIPVPnk_owthsW3gB_j5JNgXiebng5t_c7HWFUA?e=YcY60f

    I am not able to find the relationship between each Project, the Impact Level and the Business Driver to create that matrix table. I believe there is no connection.

    • RodFromm's avatar
      RodFromm
      Iron Contributor
      Jun 06, 2025

      Based on your spreadsheets the relationships appear to be present, but they don't let you access the table/fields that contains the Business Driver values: Extreme, Low, Strong, etc.  Wish I could offer additional suggestions, but we do not have this feature enabled.

      • Woody's avatar
        Woody
        Brass Contributor
        Jun 06, 2025

        Appreciate you taking a shot at it 👏

Resources