Forum Discussion

allwynbazil's avatar
allwynbazil
Helper I
3 years ago
Solved

Merging two SharePoint lists in PowerBI

Team,

 

I have two SharePoint lists. One is with Project details and another with weekly progress for each project. The Project table has Project name and Benefits. The Progress table has Project(Lookup from column from Projects table), Date, and Weekly Progress. In Progress Table, there are multiple rows for weekly progress for each project with the date.

 

I would like to merge the tables and show the fields below in a table visual in PowerBI.

  • Project name (from Project Table)
  • Benefits (from Project Table)
  • Weekly Progress(The progress should be for the latest date).  (From Progress Table)

Please help me to achieve the result in PowerBI

 

Regards,

Alwin

 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi allwynbazil 

     

    If both tables have Project column, you can build a relationship between them on Project column, then bring columns from corresponding tables into the table visual. 

     

    For the weekly progress, you can create the following measure

    Latest Progress =
VAR _lastDate = MAX ( 'ProgressTable'[Date] )
RETURN
    CALCULATE (
        MAX ( 'ProgressTable'[Progress] ),
        'ProgressTable'[Date] = _lastDate
    )

     

    Hope this helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi allwynbazil 

     

    If both tables have Project column, you can build a relationship between them on Project column, then bring columns from corresponding tables into the table visual. 

     

    For the weekly progress, you can create the following measure

    Latest Progress =
VAR _lastDate = MAX ( 'ProgressTable'[Date] )
RETURN
    CALCULATE (
        MAX ( 'ProgressTable'[Progress] ),
        'ProgressTable'[Date] = _lastDate
    )

     

    Hope this helps. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • allwynbazil's avatar
      allwynbazil
      Helper I
      3 years ago

      Thanks for looking into the issue v-jingzhang. I fixed this issue using Bookmarks and Selection Options.

      And I used the measure as you suggested to display the latest progress.