You have a table with columns OrderID, ProductID, and Quantity. You want to remove duplicates based on OrderID and ProductID but keep the highest Quantity for each pair. Which approach is correct?

hard📝 Scenario Q8 of 15

Power BI - Data Cleaning with Power Query

You have a table with columns OrderID, ProductID, and Quantity. You want to remove duplicates based on OrderID and ProductID but keep the highest Quantity for each pair. Which approach is correct?

AUse DISTINCT on all three columns

BRemove duplicates on Quantity column only

CGroup by OrderID and ProductID, then keep Max of Quantity

DSort by Quantity and remove duplicates on OrderID only

Step-by-Step Solution

Solution:

Step 1: Identify how to keep highest Quantity per pair
Grouping by OrderID and ProductID allows aggregation of Quantity values.
Step 2: Use Max aggregation to keep highest Quantity
Within each group, selecting Max Quantity keeps the highest value.
Final Answer:
Group by OrderID and ProductID, then keep Max of Quantity -> Option C
Quick Check:
Group by + Max aggregation keeps highest Quantity [OK]

Quick Trick: Group by keys, aggregate Quantity with Max [OK]

Common Mistakes:

Removing duplicates on wrong columns
Using DISTINCT on all columns loses max logic
Sorting without grouping misses aggregation

Master "Data Cleaning with Power Query" in Power BI

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More Power BI Quizzes

You have a table with columns OrderID, ProductID, and Quantity. You want to remove duplicates based on OrderID and ProductID but keep the highest Quantity for each pair. Which approach is correct?

Step 1: Identify how to keep highest Quantity per pair

Step 2: Use Max aggregation to keep highest Quantity

Final Answer:

Quick Check:

Want More Practice?