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

 

(Download my Excel file here)

 

Task

 

 

 

 

 

 

 

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).

Menu Only Solution Steps:

Step 0

Load data into Power Query.

 

 

 

 

 

 

 

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!).

 

About Me

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.