legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,214
- Office Version
- 365
- Platform
- Windows
Hi,
I'm looking for a Power Query solution to extract unique records based on the Date, ID, Amount, and Food columns. 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 paired record). The first date is always the ordered date and the second date is always the received date. The 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.
To get the unique records I need to use the ID, Amount, and Food 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:
This is what the result should look like:
I'm looking for a Power Query solution to extract unique records based on the Date, ID, Amount, and Food columns. 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 paired record). The first date is always the ordered date and the second date is always the received date. The 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.
To get the unique records I need to use the ID, Amount, and Food 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 | $100 | 1 | Milk |
2/7/2024 | 123 | West | $100 | 2 | Milk |
2/5/2024 | 123 | East | $100 | 3 | Eggs |
2/5/2024 | 888 | West | $50 | 5 | Cookies |
2/6/2024 | 888 | East | $50 | 3 | Cookies |
2/12/2024 | 777 | West | $100 | 5 | Apple |
2/10/2024 | 123 | East | $100 | 3 | Milk |
2/11/2024 | 123 | West | $100 | 5 | Milk |
This is what the result should look like:
ID | Amount | Food | Ordered Date | Received Date |
123 | 100 | Milk | 2/5/2024 | 2/7/2024 |
123 | 100 | Eggs | 2/5/2024 | Not Received |
888 | 50 | Cookies | 2/5/2024 | 2/6/2024 |
777 | 100 | Apple | 2/12/2024 | Not Received |
123 | 100 | Milk | 2/10/2024 | 2/11/2024 |
Last edited: