Pivot and transpose list of text values

[n.kehayova]

Hello board

I am brand new to the boards, and quite new at Power Query, so don't judge if there is an easy fix for what I'm asking:

I have a mapping of tasks, which looks like this:

Team:	Task
Reporting	Do reports
Reporting	Do pretty charts
Analysts	Be smart
Analysts	Analyse stuff
Payroll	Calculate salaries

Etc.

I want to transform this with power query to the following output:

Reporting:	Analysts:	Payroll:
Do report	Be smart	Calculate salaries
Do pretty charts	Analyse stuff

The end purpose of this is to use those 2 lookup tables in a template to link them to dynamic drop downs -> once you select team Reporting, the next menu will give you options to chose only for their tasks.

Can you help me with the transformation (or hint me how to get to my end purpose smarter)? I tried transpose, but it creates 2 separate columns of reporting with 1 task each, and pivoting does not solve this, since I want to keep the text values, not to sum or count them...

 

[Ken Puls]

Welcome to the board. This one actually has a trick to it, but we like basic questions too, so never be too shy to ask.

Here's the trick:
-pull the data into Power Query
-select the Team column
-go to Transform --> Pivot
-choose the Task column as the column to aggregate (if it isn't already) but DO NOT CLICK OK YET
-click the little triangle to expand the advanced options
-choose "Don't Aggregate"
-click OK
-celebrate

 

[n.kehayova]

Halleluah! I knew it will be something simple
Thanks, Ken! Btw, you've got huge fans here in Sofia My office got into Power query ever since that Excel conference you spoke on a couple of months ago Hope seeing you there next time as well!

 

[Ken Puls]

That's awesome! I've already reserved some time in my calendar so that I can make it back again this year.

 

[n.kehayova]

Ok, I spoke too soon.
I am getting the following error:

"Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List"

I got it the 2 times I tried, once with a list of Reporting tasks (around 20), which was expected to get into one column reporting, and once in a list of mixed tasks (6-7 categories with 1 task each, and 1 category "Regular activities" with 3 tasks in it). The second time around the resulting table has only 1 line, and bellow "Regular activities" stands an Error.
The table does not load to the sheet as well. Do you have any ideas?

 

[Bill Szysz]

Try this one.

 

[Ken Puls]

My bad, I missed the bit about multiple entries. It's a bit of a pain that Power Query errors on those. Bill's solution will work much better.

If you need any help in adapting his solution, let us know.

 

[n.kehayova]

Works beautifully, thanks