Do you want to split cells into separate rows and repeat values from other columns? I will show you how.
In our example, we have names of publishers and series. In short, we want to go from this table (Fig. 1):
to this table (Fig. 2):
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).
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).
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).
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) .
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).
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).
As we can see, we went from the table on the left to the table on the right (Fig. 9).
