Do you want to split cells into separate rows and repeat values from other columns? I will show you how.
How to Split column into rows | Excel Power Query 02
In our example, we have names of publishers and series. In short, we want to go from this table (Fig. 1):
Fig. 1 — Source table
to this table (Fig. 2):
Fig. 2 Target table
The task is really simple when using Power Query. We need to go to the Data tab and choose the From Table/Range command (Fig. 3).
Fig. 3 From Table/Range command
It allows us to import our table to Power Query. In our example, we have two columns from which we want to select the Series column and split it into rows. When this column is selected, we need to go to the Home tab and find the Split Column command, then choose the By Delimiter option (Fig. 4).
Fig. 4 By Delimiter option
Power Query will choose a space as the delimiter for us. However, in our case the delimiter is a semicolon and a space. It means that we need to select the Custom option and write the semicolon and a space in the proper bar (Fig. 5).
Fig. 5 Writing the proper delimiter
Then, we should select the Each occurrence of the delimiter radio button. Now, let’s open Advanced options. We can see that columns are selected as the default option. Let’s change it into rows. Now, we can press OK (Fig.6) .
Fig.6 Advanced options
And we have the result. Now, we can go to the Home tab, then to the Close and Load command and choose the Close and Load to option. This way, we can load important data from Power Query to Excel (Fig. 7).
Fig. 7 Data loading
In Excel, we are choosing the Table radio button, then the Existing worksheet and then we have to select the target cell, which in our case is D1. Let’s close it with OK (Fig 8).
Fig. 8 Import Data window
As we can see, we went from the table on the left to the table on the right (Fig. 9).
Today, we want to combine all values from the same group into one cell. Let’s go.
Combine all values within group with delimiter | Excel Power Query 03
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):
Fig. 1 Source table
to this table (Fig. 2):
Fig. 2 Target table
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).
Fig. 3 Going to Power Query
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).
Fig. 4 Group By window
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).
Fig. 5 Whole table in one cell
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).
Fig. 6 Going to the Formula bar
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.
Fig. 7 Code modification
Now, as Power Query treats values as a list, we can extract them by using the Extract Values option (Fig. 8).
Fig. 8 Extracting values
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.
Fig. 9 Delimiter
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).
Fig. 10 Loading data to Excel
Let’s select the Table and the Existing Worksheet radio buttons in the Import Data window (Fig. 11)
Fig. 11 Data importing
We can see that all values within one group are combined into one cell (Fig. 12)
Let’s assume that we are in the future and we sell spaceships. We want to find out what class of a spaceship each country has bought basing on our table of unique spaceship classes.
Combine all unique values within group | Excel Power Query 04
With Power Query, this task is very simple. We have to select our main table, then go to the Data tab and choose the From Table/Range command in order to export our data from Excel to Power Query (Fig. 1).
Fig. 1 Exporting data to Power Query
Since we are interested which country has bought which spaceship, we want to group our data by the country. We have to select the Country column and go to the Home tab (1) and choose the Group By command (2). In the window, we need to choose the Advanced option (3) because we want to add more aggregation. I want to count how many ships each country has bought as well as know the spaceship class names. Since there isn’t any proper operation, let’s choose the Sum option (4), which we will modify later in the code. In the column bar, we’re choosing the Ship class (5). As a final step, let’s write a new name, which is going to be ‘Unique ship classes’ (6) (Fig. 2)
Fig. 2 Data grouping
In the new table, there are some errors in the last column, because we wanted to sum up text, which is impossible. It means that we need to modify our code. We need to go to the View (1) tab and tick the Formula bar checkbox (2). We can see that the formula uses the List.Sum function (3) (Fig. 3)
Fig. 3 Formula bar
What we want to do is combine all values, so let’s change the List.Sum into the Text.Combine function (1). We also need to add a separator, which will be a comma and a space (2) (Fig. 4)
Fig. 4 Code modification
Now, we can see that Power Query has changed its calculations. All ship classes are combined. However, as I said before, we need only unique values without repeats. We just need to add one function, which is the List.Distinct function. (Fig. 5)
Fig. 5 List.Distinct function
Now, we can see there are no repeats. We can go to the Home tab (1) and to the Close & Load command (2) and the Close & Load to option (3) (Fig. 6)
Fig. 6 Going back to Excel
In Excel, we have to remember to select the Table (1) and Existing worksheet (2) radio buttons. Then choose a target cell (Fig. 7)
Fig. 7 Data importing
As we can see, we have counted how many ships each country has bought as well as the product class (Fig. 8)
