08 Apr 2021

## aggregating text part 2

Haven’t I done this before? It was almost identical except for two details.

### Previous Post

Why didn’t I remember that I’d solved this and created a post?

Who knows but there are two important differences I’ll review in this post:

1. preferred formula solution
2. power query variation

### 1. Formula Solution

The person I helped preferred a simple solution with a couple of quick manual steps:

1. create unique ID list =UNIQUE(\$A\$4:\$A\$13)
2. concatenate Codes =TEXTJOIN(“, “,TRUE,IF(\$A\$4:\$A\$13=\$L4,\$B\$4:\$B\$13,””))
3. copy/paste formula above as values
4. text to columns to split the codes

Q: Why not fully automate it?

A: It’s a quick one time task. Fully automating could take more time.

That’s fair. Now I’ll examine a Power Query solution variation.

### Power Query Variation

In my previous post I found this tip that modifies the M code in the advanced editor using Text.Combine function.

This variation uses Text.Combine using only the menu (no manual M code changes).

Step 0

Step 1

The magic is Operation ‘All Rows’ in the Group By below.

When I group by ID and use ‘All Rows’ in new column ‘AllCodes’, M creates a table in each row.

Step 2

I don’t need the entire table for each ID (row). I just need the values from column Code. The Custom Column allows me to extract these values into a list.

Tip: click to the right of text ‘List’ to see the values in bottom left.

Step 3

In the column headers press the double arrows, select ‘Extract Values’.

We end up with this:

Step 4

I did a couple of cleanup steps and then split column ‘Code 1’ by delimiter (comma).

Close and Load to a new sheet. Yes it’s fully automated but it does take time to learn this approach.

### Review

Simple or Robust?

Business users with a 1 time task may prefer a formula solution despite a couple of manual steps.

Data nerds like me prefer to invest time to fully automate and learn something new along the way.

Pitfall

I did get confused in Step 2. What am I supposed to extract and from where?

Answer was [AllCodes][Code] (column Code that’s inside the table in column AllFields).

I always try to review what I’ve learned so I don’t forget for the next time it comes in handy (hence making this post to practice!).

I’m a Data Analyst from Markham Ontario Canada (near Toronto). I need to keep my Excel skills up to date for my job but I also find that playing around in Excel helps pass the time during Covid.

1. Oyekunle SOPEJU says:

Hi, Kevin.
Thank you very much for sharing.
Here is another Formula Solution in Office 365 ( Dynamic Array ) . It’s one step less. No need to do text to column.
1. Create unique ID list = UNIQUE( \$A\$4:\$A\$13 )
2. Filter Codes = TRANSPOSE( FILTER( \$B\$4:\$B\$13,\$L4=\$B\$4:\$B\$13 ) )
3. Copy & paste formula above.

My regards

1. Kevin Lehrbass says:

Thanks for the solution Oyekunle! Good to hear from you. I hope you’ve been well.