Hello - I have a table which as mulitple customer entries and the date they ordered
| ID | Category | Date |
| 1 | Food | 21/02/2023 |
| 2 | Food | 22/02/2023 |
| 3 | Food | 23/02/2023 |
| 4 | Food | 24/02/2023 |
| 5 | Food | 25/02/2023 |
| 6 | Food | 26/02/2023 |
| 7 | Food | 27/02/2023 |
| 1 | Drink | 28/02/2023 |
| 2 | Drink | 01/03/2023 |
| 3 | Drink | 02/03/2023 |
| 4 | Drink | 03/03/2023 |
| 5 | Drink | 04/03/2023 |
| 6 | Drink | 05/03/2023 |
| 7 | Drink | 06/03/2023 |
| 9 | Drink | 07/03/2023 |
| 10 | Drink | 08/03/2023 |
What I am aiming for is to have the customers who only ordered food and then a column with teh date they ordered and a column of the date they ordered drink (if they did) eg:
| ID | Food Date | Drink Date |
| 1 | 21/02/2023 | 28/02/2023 |
| 2 | 22/02/2023 | 01/03/2023 |
| 3 | 23/02/2023 | 02/03/2023 |
| 4 | 24/02/2023 | 03/03/2023 |
| 5 | 25/02/2023 | 04/03/2023 |
| 6 | 26/02/2023 | 05/03/2023 |
| 7 | 27/02/2023 | 06/03/2023 |
I did this in the power query editor by creating two tables and usign filter and merge. I also managed to do it using DAX by creating two tables - one for food customers and one for drink customers (I used selectcolumns to allow me to rename fields so I could join) and then merging the two tables using naturalleftouterjoin
As I'm a bit of a novice - is this the best way to do it (and does it matter if I did it in power query editor - which was easier) - any tips appreciated
