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).
Sometimes, you need to create a running total in pivot tables. How to do it properly?
Running Total in Pivot Table
Let’s start with creating a pivot table. Select one cell from our data and go to the Insert tab and select the Pivot Table command. A Pivot table from table or range window will appear, where we have our range. We need to select the Existing worksheet radio button and write the location, which, in our case, will be cell F1. Let’s click the OK button (Fig. 1).
Fig. 1 Creating a pivot table
And there we have it. In our pivot table we need to drag the Date to the Rows labels.
Fig. 2 Dragging the date
Now, depending on the Excel version, we have an option of Group by dates. I’m going to group our date by months and years (Fig. 3).
Fig. 3 Grouping
We have our table. Now, I want to add Income so let’s press the Income checkbox (Fig. 4).
Fig. 4 Income checkbox
Now, I want to have the income and the running total for the income, so let’s drag the Income two times to the Values area. We can see that we have Sum of Income and Sum of Income 2, where we actually want to have our running total (Fig. 5).
Fig. 5 Income and Income 2
Let’s right click any value from Income 2, then select the Show Values as and then the Running Total In option (Fig. 6).
Fig. 6 Selecting the Running Total option
Now, we have to decide whether we want to base our running total on Date or Years Field. Let’s take Date (Fig. 7).
Fig. 7 Date Field
Now, we can see that in the Income 2 column we have bigger numbers. Let’s format them by pressing any cell in the column and choosing the Number Format option (Fig. 8).
Fig. 8 Number Format option
In the Format Cell window, let’s choose the Currency category, in Decimal places let’s write 0 and press the OK button. Let’s do the same in the Sum of Income column (Fig. 9).
Fig. 9 Format cell window
Now, we can see that we are working with money. In each month we have bigger and bigger numbers which means that we have the running total in this column. We can change the name of Sum of Income 2 into Running Total. Since we are using the Date Field as our reference point, we have the running total in 2020. In 2021 Excel counts from the start, which means that we have the running total only for 2021. The same is with 2022. If we change the Date field into the Years field in the Show Values as (Running Total) window, we will have the same value in the first year and in the Sum of Income column (Fig. 10).
Fig. 10 Years
However, in the next year, we have the the sum from January 2021 and January 2020. In February, we have the sum from February 2021 and 2020. In 2022, we have sums from three Februaries (Fig. 11).
Fig. 11 Sums from three Ferbuaries
Let’s drag Manager into the columns header (Fig. 12).
Fig. 12 Dragging Manager into headers
By doing so, we can show value from the Manager’s perspective, which means a horizontal view.
Fig. 13 Horizontal view
However, this type of data isn’t a proper one to show it this way, so let’s get back by pressing Ctrl + Z two times and see that we can also create a percent running total by going to the % Running Total In option and choose the Date Field (Fig. 14).
Fig. 14 Percentage Running Total
Now, we can see the values as percentage (Fig. 15).
