Joining two tables out of one table -best way

neilmc — Thu, 02 Mar 2023 08:24:48 GMT

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

Re: Joining two tables out of one table -best way

wdx223_Daniel — Thu, 02 Mar 2023 08:46:18 GMT

In Power Query

NewStep=Table.Pivot(PreviousStepName,{"Food","Drink"},"Category","Date",each _{0}?)

in dax

just put the customer id on the row, and category on the column, then put the measure on the value area

Measure=MAX(Table[Date])

topic Joining two tables out of one table -best way in DAX Commands and Tips

Joining two tables out of one table -best way

Re: Joining two tables out of one table -best way