Combine all values within group with delimiter | Excel Power Query 03

Today, we want to combine all values from the same group into one cell. Let’s go.

In our example, we want to combine all publishers. It means that we’re doing an operation reverse to the one in the previous post. This time, we want to go from this table (Fig. 1):

to this table (Fig. 2):

We can do it with Power Query. First, select the table. Then, go to the Data tab and select the From Table/Range command (Fig. 3).

We can see that we have our two columns. The first thing we want to do is group our publishers. Let’s select the Publisher header, then go to the Home tab and then Group By command. In the new window, we can see that we are grouping by the Publisher column. We still need a new aggregation. Let’s change the Operation bar into All Rows and write ‘Series’ in the New column name bar. We cannot change anything in the Column bar. Let’s press OK (Fig. 4).

Now, we have the Publishers group, and in the cells from the Series column, we have all rows from the previous stage. The whole table is in one, single cell (Fig. 5).

However, we don’t need the whole table, which consists of two columns. We only need the Series column. We can change it by modifying the code. Let’s go the View tab, and let’s check whether the Formula bar is ticked. If it is so, we can start modifying the code in the formula bar (Fig. 6).

Looking at the code, we can see that we don’t want to choose underscore for each step. It means that we need only one column. That’s why we must write ‘Series’ in the code (1). Now, let’s delete the type from the code (2) and write ‘type as list’. It’s important to write ‘list’ with a lowercase (Fig. 7). And we see that we have only lists when we click on any of the Series cells.

Now, as Power Query treats values as a list, we can extract them by using the Extract Values option (Fig. 8).

We are extracting the values by combining them with a delimiter. We want to use a comma and a space, so let’s select the Custom option first and then write what we want (Fig. 9). Let’s close it with OK.

Now, we can see that we combined all Series names into one cell for each publisher. We can now go to the Home tab, then Close & Load command, and press the Close & Load to option (Fig. 10).