Using Power Query, you can create a Pivot Table. Here is the Mcode for that excercise.
Excel 2016 (Windows) 32 bit
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
NAME |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
2 |
Bill |
Bananas |
Lemons |
Oranges |
Cherries |
|
|
|
3 |
Craig |
Apples |
Cherries |
|
|
|
|
|
4 |
Martha |
Lemons |
Cherries |
Bananas |
Oranges |
Mango |
Papaya |
grapes |
5 |
Mike |
Cherries |
Pears |
Lemons |
Bananas |
|
|
|
6 |
Sally |
Pears |
Lemons |
|
|
|
|
|
7 |
Terry |
Apples |
Oranges |
Pears |
|
|
|
|
8 |
Victor |
Oranges |
Bananas |
Apples |
|
|
|
|
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
Bookmarks