Forum Discussion

Woody's avatar
Woody
Copper 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

Sort By
  • Woody's avatar
    Woody
    Copper 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
      Steel 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
        Copper 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:

        BusinessDriversNulled

        PortfolioAnalysesNulled

        PortfolioAnalysisProjectsNulled

        PriorityzationDriversNulled

        ProjectsNulled

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