Forum Discussion
Solved
Help in Data modelling
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
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
