The data is transformed into an Excel table and loaded into Power Query. It looks like this:
(I don't understand why the table doesn't work. After a few searches I realize that the site does not support table markdown)
| Name | ID |Analyst |
|Name1 | 1000001 | Tony |
|Name2 | 1000002 | Tony |
|Name3 | 1000045 | Adam |
|Name4 | 1000009 | Adam |
|Name5 | 1000321 | Jimmy |
|Name6 | 1000040 | Adam |
|Name7 | 1000005 | Jimmy |
ID is unique.
I'd like to create a pivot table into this form:
I do not need to keep the
Name field, and it's not necessary to sort the table by Analyst or by Spot id. I'm looking forward to use a few lines of M code to do this, but found nothing suitable for my cause.
BTW please also let me know if it's doable in Power Pivot with DAX. I know how to do this in VBA, quite trivial, but I need to teach the non-programmers how to do this with DAX/M, and it's embarrassing as I don't them them either.