Group duplicates based on different matching columns

[kellyylx]

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
A	Currie Street
B	Mary Road Block 4
C	Stadium 6
A	Currie Street
G	Luck Ave
D	Stanley Road
D	Stanley Rd
E	Chuan Road
F	Georgia Ave 4
H	Khatib Street
I	Peace Road
G	Luck Ave
G	Luck Avenue

Expected Output:

name	address	dup group by name only	dup group by name and address
A	Currie Street	1	1
B	Mary Road Block 4	0	0
C	Stadium 6	0	0
A	Currie Street	1	1
G	Luck Ave	3	2
D	Stanley Road	2	0
D	Stanley Rd	2	0
E	Chuan Road	0	0
F	Georgia Ave 4	0	0
H	Khatib Street	0	0
I	Peace Road	0	0
G	Luck Ave	3	2
G	Luck Avenue	3	0

How can i do this in power bi?

[Anonymous · Accepted solution]

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)

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.

[kellyylx]

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]

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]

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.

[kellyylx]

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