Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello People,
I have the data on the Power BI desktop. Where there are duplicate rows as below.
I need to remove the duplicates but want to keep the one with the minimum price.
I tried to remove the duplicates in the query editor, but it removes the one with minimum price.
Particular | Date | Price | Merged |
ABC | 10/8/2021 | $40.00 | ABC - $40.00 |
ABC | 10/11/2021 | $39.00 | ABC - $39.00 |
Any help will be appreciated!
Solved! Go to Solution.
Hi @AbbasG
You can try this method.
First , sort Particular , then sort price by ascending. This is make sure the min price to be the first under the same Particular
And then use the remove duplicate row to remove. Because this remove duplicates rows will all ways keep the first one.
Or you can try dax to reach that. Try column like the following :
Column =
VAR _MIN =
MINX(
FILTER( 'Table', [Particular] = EARLIER( Table_DAX[Particular] ) ),
[Price]
)
RETURN
IF( _MIN = [Price], 1, BLANK() )
And select the 1
I put the pbix file in the end you can reference.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AbbasG
You can try this method.
First , sort Particular , then sort price by ascending. This is make sure the min price to be the first under the same Particular
And then use the remove duplicate row to remove. Because this remove duplicates rows will all ways keep the first one.
Or you can try dax to reach that. Try column like the following :
Column =
VAR _MIN =
MINX(
FILTER( 'Table', [Particular] = EARLIER( Table_DAX[Particular] ) ),
[Price]
)
RETURN
IF( _MIN = [Price], 1, BLANK() )
And select the 1
I put the pbix file in the end you can reference.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@AbbasG , refer if this topic can help
https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |