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:

Tony 1000001 1000002 Adam 1000009 1000040 1000045 Jimmy 1000005 1000321

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.

1 Answer 1


You could use a Pivot Table to create that output, either via the Excel Data Model/Power Pivot or directly from an Excel Table output by your Power Query.

Just add the Analyst and then ID columns to the Rows well in the PivotTable Fields pane and you will get the output you are after. If you want to output as 2 columns, set the PivotTable's Report Layout = Tabular Form.

I dont think any DAX or M code is needed.

  • Thanks! Not sure how come I didn't think about that... Oct 16, 2018 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.