fbpx

Transform Stacked Column into Tabular Form with Power Query #5

Data comes in countless ways and forms. Sometimes structured, with an SQL database, a table in Excel or several CVS files. The fun with Power Query starts when the delivered format is quite messy. It works to your advantage to be able to handle different scenarios. This post focuses on how to transform data from a single column, but that has a repetitive pattern, into a tabular form. Spoiler, we will use a Modulo column. Below example shows a repetitive pattern with 5 unique categories. After that data for the first category repeats.

Power Query - Index Modulo - 1. -Dataset

To get started:

  • Select the Starting Data -> go to tab Data -> click from Table/Range

With the data in Power Query, you now need a way to get the categories in the columns. Unfortunately, transposing the data does not help. It creates many columns, but still doesn’t group the right data. Another approach is using an Index column. If there is a pattern in the data, it becomes more visible after you add an Index column.

  • Go to Add Column -> click the arrow next to Index Column -> select From 0

With the Index column provided, let’s look at what pattern appear. It becomes clear that the first 5 rows are the shown categories (note that the Index Column starts at 0, so number 4 is actually the 5th value).

Power Query - Index Modulo - 2. Index Column

Modulo Column

If the Index Column would have numbers from 0 to 4 and start over again, that would provide you with a categorization that you can work with. And that’s exactly what Power Query does when creating a Modulo Column based on the Index column. A modulo column takes the values of an existing column, divides them by a specified amount and returns the remainder. To do this:

  • Select column Index
  • Go to tab Add column -> Standard (operation) -> click Modulo
  • Fill in the number 5

The added column now shows a series starting from 0 and going to 4, before repeating the pattern again.

Power Query - Index Modulo - 3. Modulo Column

Pivot Data

With this column, you can now pivot the data. In that way it turns into a tabular form, which is much easier to work with. Do pay attention. When pivoting the Inserted Modulo column, the standard operation Power Query performs is to add up to numbers, or else count text values. In this case, it’s better not to aggregate any data. To do that:

  • select column Inserted Modulo -> click the tab Transform -> click Pivot Column
  • as Values Column specify Data
  • before clicking OK go to Advanced options -> select Don’t Aggregate
Note: Do test what happens if you forget to select Don’t Aggregate. That would present the data in a useless way for this exercise. Yet, it’s good to be aware this is the standard operation.

The result of pivoting the data is that it’s laid out in a tabular form. The biggest issue left is that the data relating to a single row, is scattered over five different rows. In an ideal world there would only be three rows with data.

Power Query - Index Modulo - 5. Pivot results

Cleaning up

To get there, you start out with the Fill Up functionality.

  • Select column 1 -> hold down shift -> left-click on column 4
  • Go to the tab Transform -> select Fill -> press Up

We’re almost there. To keep the three needed rows, you need one more step:

  • Filter out null values from column 0, containing the dates.

The table is now left with three rows. The only thing left is to clean up. Before exporting the data to the excel worksheet:

  • Delete the column Index (as we don’t need it anymore)

Change data type from column:

  • 0 to Date
  • 1 to Whole Number
  • 2 and 3 to Text
  • 4 to Decimal Number.

And rename columns:

  • 0 to Date
  • 1 to Material Nr
  • 2 to Category
  • 3 to Name
  • 4 to Sales

Result

Then load the query to the worksheet.

If you like to follow these steps with the Excel file, have a look here: Example File – Structure Data Using Index Modulo Column.

This post showed a way to handle data that comes in a single column, yet has a repetitive pattern. With only a few steps you turn a single column of data into a tabular form. You won’t need this every day, but the skill might prove handy some day. If you like this skill, make sure to also check out how you can group your data and reach into underlying rows right after.

Thanks for visiting my website. Do share this post with people who may benefit from it. And I would love to read comments with questions or suggestions.

Rick de Groot

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

7 Responses

    • He Omisile! Thanks for commenting. I tried replicating your point, but didn’t bump into any duplicates. The fill up operation was performed on the columns with number 1 to 4. It is column 0, with the dates, where we filter out the null’s. With the steps taken this column does not contain any duplicates. I get the desired results without a remove duplicates operation. Do feel free to include it in your query as it doesn’t hurt.

  1. What when data is not consistent like.
    after sales there is add discount in next row but when 2nd sales came there will not.
    any solution ?

Share Your Thoughts!