Hello Power Query Users,
I'm looking for a Power Query solution (using the interface as much as possible so I can understand) to extract unique records for each ID based on the Date column.
Keep in mind, the first date is always the ordered date and the second date is always the received date. The raw data is sorted by ID, then Food, then by Date so it'll show if a customer ordered and/or received the item based on the Date column.
For ID 123 the customer ordered milk on 2/5/2024 and received it on 2/7/2024. The same customer ordered eggs on 2/5/2025 but it has not been received (since there is no subsequent paired record), hence the Received Date is empty.
Each pairing record (order & received) will have all of the same values except for the date column, but I don't know how to put the respective dates for each record into its own column. Can any Power Query experts help transform this data to my desired result?
This is my raw data:
| Date | ID | Region | Amount | Group | Food |
|
2/5/2024
|
123
|
East |
$1,000.75
|
1
|
Milk |
|
2/7/2024
|
123
|
East |
$1,000.75
|
1 | Milk |
|
2/5/2024
|
123
|
South |
$100
|
1 | Eggs |
|
2/5/2024
|
888
|
North |
$50
|
5
|
Cookies |
|
2/6/2024
|
888
|
North |
$50
|
5 | Cookies |
|
2/12/2024
|
777
|
West |
$100
|
6 | Apple |
|
2/10/2024
|
123
|
South |
$100
|
3
|
Milk |
|
2/11/2024
|
123
|
South |
$100
|
3 | Milk |
This is what the output result should look like:
| ID | Region | Amount | Group | Food | Ordered Date | Received Date |
| 123 | East | $1,000.75 | 1 | Milk |
2/5/2024
|
2/7/2024
|
| 123 | South | $100 | 1 | Eggs |
2/5/2024
|
|
| 888 | North | $50 | 5 | Cookies |
2/5/2024
|
2/6/2024
|
| 777 | West | $100 | 6 | Apple |
2/12/2024
|
|
| 123 | South | $100 | 3 | Milk |
2/10/2024
|
2/11/2024
|
crossposted at: https://www.mrexcel.com/board/threads/power-query-to-extract-unique-records.1258784/
Allthough you crossposted (without mentioning it) as indicated by Alan, and received quite a few answers, I take it that you were not satisfied as they were either a bit clumsy or just too complicated.
The attached workbook contains a fairly basic solution that is achieved by clicking in the user interface only, be it that you need to type some code that adds custom columns.
Sorry I wasn't aware of the cross posting rule. This is exactly the solution I was looking for. Simple and eloquent. Thanks!
