Forum Discussion
Solved
Removing duplicates from column2 based on column 1
Hi ,
Attached the data below, I want to remove the duplicates and have the desired output. All my data is from the same table.
| Stack Order | Report No |
| 1 | 1110 |
| 1 | 1111 |
| 1 | 1112 |
| 1 | 1113 |
| 2 | 1110 |
| 2 | 1111 |
| 2 | 1112 |
| 2 | 1113 |
| 3 | 1110 |
| 3 | 1111 |
| 3 | 1112 |
| 3 | 1113 |
| 4 | 1110 |
| 4 | 1111 |
| 4 | 1112 |
| 4 | 1113 |
| 5 | 1109 |
| 6 | 1109 |
| 7 | 1109 |
| 8 | 1109 |
| 9 | 1109 |
| 10 | 1107 |
| 11 | 1107 |
| 12 | 1107 |
| 13 | 1107 |
| 14 | 1106 |
| 14 | 1108 |
| 15 | 1105 |
| 16 | 1104 |
| 17 | 1103 |
| 18 | 1103 |
| 19 | 1103 |
| 20 | 1103 |
| 21 | 1106 |
| 21 | 1108 |
Desired Output after filters or dax being applied :
| 1 | 1110 |
| 2 | 1110 |
| 3 | 1110 |
| 4 | 1110 |
Thank you in advance.
Please try the following DAX:
SummaryTable = ADDCOLUMN(VALUES(Table[Stack Order]), "First Report No", CALCULATE(MIN(Table[Report No])))But the same result can also be acheived in PowerQuery, where you can go to Transform > Group By, and group by the Stack Order and Aggregate by the min Report Number
1 Reply
Please try the following DAX:
SummaryTable = ADDCOLUMN(VALUES(Table[Stack Order]), "First Report No", CALCULATE(MIN(Table[Report No])))But the same result can also be acheived in PowerQuery, where you can go to Transform > Group By, and group by the Stack Order and Aggregate by the min Report Number