Forum Discussion

Woody's avatar
Woody
Brass Contributor
Jun 02, 2025

Project Online - Business Drivers matrix table in Power BI

Hi!

After classifying our ongoing projects we could get to this matrix table in Project Online:

There are 6 Business Drivers where each project contributes on a rating scale (None, Low, Moderate, Strong, Extreme)

I am stuck on Power BI to find the relation between each Project, the rating scale and each Business Driver to be able to build the same matrix table in Power BI:

Any help would be much appreciated!

Business Intelligence
Online
PPM
Project
Project Server
Reporting

6 Replies

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

    Many thanks!

    With step 2, I merged as New table PortfolioAnalysisProjects with PortfolioAnalysis on AnalysisId and I kept the following columns (PrioritizationId column does not exist yet):

    ProjectId
    ProjectName
    AnalysisId
    AbsolutePriority
    AnalysisName
    Priority
    Analysys (Record)

     

     

    I can see PrioritizationId is in the Analysys (Record) column:

     


    But when trying to expand only for PrioritizationId this is the error I get:

     

     

    So I cannot continue to merge with PrioritizationDrivers on PrioritizationId.

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


      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

       

      • 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 03, 2025

     

    Here is my take on this given the limited info I have about your model and other requirements.  If nothing else this may give you some ideas on how to accomplish your end goal.

    I would build a new table in Power Query as follows.

    1. Load All Tables:
      • Project
      • PortfolioAnalysisProject
      • PortfolioAnalysis
      • PrioritizationDriver
      • BusinessDriver
    2. Merge Queries:
      • Start with Project
      • Merge as New table with PortfolioAnalysisProject on ProjectId
      • Merge with PortfolioAnalysis on AnalysisId
      • Merge with PrioritizationDriver on PrioritizationId
      • Merge with BusinessDriver on BusinessDriverId
    3. Select and Keep Only:
      • Project[ProjectName]
      • BusinessDriver[DriverName] (A–F)
      • PrioritizationDriver[DriverValue] (None–Extreme)
      • Name new table BuinessDriverMatrix?? and establish relationship to Project table
    4. Pivot the Table:
      • Select DriverName column → Pivot Column
      • Use DriverValue as the Values Column
      • In the pivot settings, choose Don’t Aggregate (or use “Max” if needed)

Resources