Merge rows where ID is the same

[PeterStuhr]

Hi all,

I have this table:

As you can see we have a duplicate in "CompanyID3". What I would like to achieve is to merge the 2 rows where "CompanyID3" = 13087, where the one with an ID in "CompanyID1" is the winner. Meaning - is there a duplicate in ID3, then the one with value in ID1 (if any) wins.

Is it possible?

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create this calculated column:

Column = 
var id3 = 'Table'[Companies.CompanyID3]
var check = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Companies.CompanyID3]=id3))
return
IF(check=2&&'Table'[Companies.CompanyID1]=BLANK(),0,1)

Then apply it to the original table visual, the result shows:

Hope this can help.

Best Regards,

Giotto Zhi

[MFelix]

Hi @PeterStuhr ,

Check the post below believe that you can make some similar option for your data.

https://community.powerbi.com/t5/Desktop/Remove-duplicate-rows-based-on-max-value-of-a-different-col...