Forum Discussion
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 |
| 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?
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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
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
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 ShenIf 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.
