Unstack uneven data across columns with #Excel #PowerQuery

Excel Tip - Unstack uneven data with Power Query

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.

Excel Tip - Unstack uneven data with Power Query1

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.

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.