This is a continuation of the previous post, in which I showed you how to unstack two columns of data by using Pivot Table as a helper, and together with a couple of excel tricks.  As mentioned, that approach is only good for one-off as the result is static.  When you expect the data to be changed frequently; or will have new data added to it on regular basis, you would absolutely look for a more robust solution that can be updated easily.  With Power Query, a simple act of Refresh will bring you the result in a flash. 

Perhaps, you have read articles or videos for similar problem.  In most cases, the source data is of fixed pattern, i.e. it is always composed of stacks of fixed number of rows.  In that case, we can add an Index Column, followed by adding modulo column in order to identify the “column” to be pivoted.  However, when the source data consists of stacks of different rows, i.e. some stacks have 3 rows, some stacks have 4 rows, or 5 rows and so on, we will need to find a way to get the helper column for Pivot.

What’s more challenging is... what if the data can be spread all over the rows in any order, like the screenshot below.

In the following video, I will explain to you the logic behind the Power Query Magic involved in this approach.  Although we will need to add two custom columns with M functions, we can ride on the UI of Power Query to generate the functions we need.

In short, the steps involved in Power Query are

1. Group By [Team] by using operation “All Rows”
2. Add Custom Column to sort the [Name] in the resulting Tables (optional step, if you want the result to be sorted)
3. Add Custom Column to add Index column to the resulting Table in previous step
4. Remove other columns
5. Expand Tables
6. Pivot column

Let’s watch it in action:

English VO

Cantonese VO

Note: Please turn on CC for English subtitles.

You may download a Sample File to follow along.

Hope you like it. 🙂

If you have alternate approach to solve this problem, please share by leaving comments.