Convert unevenly spaced list to table [Data from Hell]

Posted on by

Introducing Data from Hell:

Watch out, its data from hell. In this new video series, we are going to look at some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.

For our first installment, let’s reshape unevenly spaced list of values to a table.

Unevenly Spaced List -> Tabular format

Let’s say you have a list with some values. They are separated by blank cells. You want to convert this to a table, so related values are on same row. But the table can have any number of columns depending on how big the chunks are.

Something like this:

d4h-unevenly-spaced-list-to-table-power-query

Enter Power Query (Get & Transform Data)

You guessed right. This data is too much out of whack for formula based solution. So let’s reshape it using Power Query aka Get & Transform Data.

The process goes like this:

  1. Load the data in to query editor
  2. Replace nulls (blank cells) with something that is not part of your data, like %^%
  3. Using Text.Combine, concatenate all values to a single value, separated by some symbol that is not part of data, like =
  4. Convert this single value to a table and split by the symbol used in step 2.
  5. Calculate number of = symbols in each row using Text.Length and Text.Replace functions in to a new column
  6. Figure out the maximum of this new column as a variable. Name it as MaxCols
  7. Remove the extra column created in step 5
  8. Split the first column by delimiter set in step 3 with number of columns set to MaxCols.
  9. Load data back to Excel.

Data from Hell – Convert unevenly spaced list to table – Power Query tutorial

As this process requires a bit of tinkering with Power Query functions and a few false starts, I made a tutorial. I woke at 4AM to record this. Just kidding, I woke up because my nose was all blocked up and couldn’t go back to sleep. So I opened up Excel for some play and recorded this.  Check it out below.

You can also watch this video on our YouTube Channel.

Download Example Workbook

Click here to download the example workbook for this Power Query tutorial. Try reshaping the data yourself first, you will learn a lot about PQ and how to use it for your advantage.

More Power Query Recipes for you

Power Query (or Get & Transform Data as it is called in newer versions of Excel) is an incredibly powerful tool to extract, reshape and merge your data sets. Check out below tutorials and recipes for more.