Scenario: In the OrdersData table, some OrderIDs appear multiple times with different dates. We need to create a new table that keeps only the most recent order for each OrderID.
Here we have a sample table called OrdersData.
| OrderID | Customer | OrderDate | Amount |
|---|---|---|---|
| 101 | Aarav | 2023-01-10 | 500 |
| 101 | Aarav | 2023-02-05 | 600 |
| 102 | Priya | 2023-03-12 | 400 |
| 103 | Rohit | 2023-01-20 | 700 |
| 103 | Rohit | 2023-02-15 | 750 |
| 104 | Meera | 2023-03-01 | 300 |
Solution:
-
Sort table by OrderDate descending.
-
Use Remove Duplicates on OrderID.
-
Power Query keeps the first record (latest one).
Let’s go to Power Query Editor, and you can see here we have table called ‘OrdersData’.
Now, we will create a separate table that contains only the latest date record for each OrderID, removing any duplicates.
Next, write the following Power Query M code
Table.Distinct(
Table.Sort(OrdersData, {{"OrderDate", Order.Descending}}),
{"OrderID"}
)
Explanation
1. Table.Sort(OrdersData, {{“OrderDate”, Order.Descending}})
-
Takes the table OrdersData.
-
Sorts it by the column OrderDate in descending order (latest date first).
-
Example:
-
If OrderID 101 has orders on 2023-01-10 and 2023-02-05, the row with 2023-02-05 comes first.
-
2. Table.Distinct(… , {“OrderID”})
-
Removes duplicates based on the OrderID column.
-
Since the table is already sorted by latest OrderDate, the first occurrence of each OrderID is the most recent one.
- Keeps only one row per OrderID (the latest).
Also Read..
