Merging two SharePoint lists in PowerBI

[allwynbazil]

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

[v-jingzhang · Accepted solution]

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]

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.

[amitchandak]

@allwynbazil , Have you tried Merge in Power Query

Merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query