Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kellyylx
Helper I
Helper I

Group duplicates based on different matching columns

Hi, I have this unsorted table of names below and I want to group duplicates based on different duplication matches. Any unique values will be in group 0.

 

name   address
ACurrie Street 
BMary Road Block 4    
CStadium 6
ACurrie Street 
GLuck Ave 
DStanley Road
DStanley Rd
EChuan Road
FGeorgia Ave 4
HKhatib Street
IPeace Road
GLuck Ave 
G

Luck Avenue 

 

Expected Output:

nameaddressdup group by name only     dup group by name and address
ACurrie Street 11
BMary Road Block 4    00
CStadium 600
ACurrie Street 11
GLuck Ave 32
DStanley Road20
DStanley Rd20
EChuan Road00
FGeorgia Ave 400
HKhatib Street00
IPeace Road00
GLuck Ave 32
GLuck Avenue 30

 

How can i do this in power bi?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi All
Firstly  HotChilli thank you for your solution!
And @kellyylx   ,We can use the rank function to help you achieve this.

 

dup group by name only(1) = RANK
(DENSE,
FILTER(ALL('Table'),
CALCULATE(COUNT('Table'[name]),FILTER(ALLSELECTED('Table'),'Table'[name]=MAX('Table'[name])))>=2),
ORDERBY('Table'[name],ASC),
DEFAULT)
dup group by name only (2) = RANK(DENSE,ALL('Table'),ORDERBY([dup group by name only(1)],ASC BLANKS LAST),DEFAULT)
dup group by name only(3) = 
VAR A=MAXX(ALL('Table'),'Table'[dup group by name only (2)])
RETURN 
IF(
    'Table'[dup group by name only (2)]>=A,
    0,
    'Table'[dup group by name only (2)])

 

For your second requirement, we just need to add adress to the constraints, and everything after that is the same as (2), (3).

 

 

dup group by name and address(1) = RANK
(DENSE,
FILTER(ALL('Table'),CALCULATE(COUNT('Table'[name]),ALLSELECTED('Table'),'Table'[name]=MAX('Table'[name])&&'Table'[address]=MAX('Table'[address]))>=2),
ORDERBY('Table'[name],ASC),
DEFAULT)

 

vxingshenmsft_0-1724981076298.png

If in doubt, check out the pbix file I uploaded and I'd be honoured if I could help you out!

 

Best regards,
Community Support Team_ Tom Shen

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 



View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi All
Firstly  HotChilli thank you for your solution!
And @kellyylx   ,We can use the rank function to help you achieve this.

 

dup group by name only(1) = RANK
(DENSE,
FILTER(ALL('Table'),
CALCULATE(COUNT('Table'[name]),FILTER(ALLSELECTED('Table'),'Table'[name]=MAX('Table'[name])))>=2),
ORDERBY('Table'[name],ASC),
DEFAULT)
dup group by name only (2) = RANK(DENSE,ALL('Table'),ORDERBY([dup group by name only(1)],ASC BLANKS LAST),DEFAULT)
dup group by name only(3) = 
VAR A=MAXX(ALL('Table'),'Table'[dup group by name only (2)])
RETURN 
IF(
    'Table'[dup group by name only (2)]>=A,
    0,
    'Table'[dup group by name only (2)])

 

For your second requirement, we just need to add adress to the constraints, and everything after that is the same as (2), (3).

 

 

dup group by name and address(1) = RANK
(DENSE,
FILTER(ALL('Table'),CALCULATE(COUNT('Table'[name]),ALLSELECTED('Table'),'Table'[name]=MAX('Table'[name])&&'Table'[address]=MAX('Table'[address]))>=2),
ORDERBY('Table'[name],ASC),
DEFAULT)

 

vxingshenmsft_0-1724981076298.png

If in doubt, check out the pbix file I uploaded and I'd be honoured if I could help you out!

 

Best regards,
Community Support Team_ Tom Shen

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 



Thank you! this is exactly what i need!

Can you explain to me how the If statement works in dup group by name only works? I dont understand what the VAR A is.



HotChilli
Super User
Super User

dup group by name only - This is an arbitrary group number?

Why does name G have group 3? Shouldn't it have been allocated to 2 if we are working down the list? and D would be group 3

HotChilli
Super User
Super User

Something seems off in the sample table.

Example: Stanley Road  shows as 2 in 'dup group by name only' - there's only one Stanley Road in the data.
Currie Street shows as 1 in 'dup group by name only' - there's two in the data.

the values are the group number of duplicated records and not the count

 

if we look at the dup group by name only column, the two A belongs to group 1, D's belong to group 2 and G's belong to group 3

 

if we look at the dup group by name and address column, the two 'A - Currie Street' belongs to group 1, and the two 'G - Luck Ave' belongs to group 2 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.