Help in Data modelling

[JS]

Hello. 

I need some help with data modelling here! I have the table below - 

Buyer	Green Apple	Red Apple	Orange	Pineapple	Kiwi	Strawberry
James	1	0	0	0	0	0
James	0	0	0	0	1	0
Mary	0	1	0	0	0	0
Mary	0	0	0	0	0	1
Mary	1	0	0	0	0	0
John	0	1	0	0	0	0
John	0	0	1	0	0	0
John	0	0	0	0	1	0

I would like to convert the table to what is shown below - 

Buyer	Green Apple	Red Apple	Orange	Pineapple	Kiwi	Strawberry
James	1	0	0	0	1	0
Mary	1	1	0	0	0	1
John	0	1	1	0	1	0

Is there any quick way to get this done. Any help or advise would be grateful! 

Thanks

JS

[DAX0110 · Accepted solution]

Yes, it can be done easily in Power Query:

(1) unpivot all the non-Buyer columns, so that you get a table with 3 columns: Buyer, Fruit, Qty

(2) group by both Buyer and Fruit columns, summing over Qty

(3) finally, pivot the Fruit and Qty columns

[JS]

Thank you!!!! Worked perfectly!!!!