Dear Analyst Episode 25: Structuring data challenge (denormalize data) with Get and Transform

This episode is based on a video and Tweet posted by Mr. Excel (Bill Jelen). Bill discusses an Excel challenge someone emailed him about regarding how to “transform” a badly structured table of data into a structure that makes it easy to do PivotTables and other downstream analysis. Interestingly, I received a ticket from a user at work today about a very similar issue on denormalizing a table of data. Here is the Google Sheets file with the problem for you to follow along with.

The person is trying to convert this table of data (notice the quarters and employee sums across the columns):

The actual data set has many more quarters and employee columns along the top

Into a table like this where the employees are listed in a column with corresponding quarters of data:

Original Tweet and video below:

Wide awake @ 3AM, solving an #Excel data problem, Posted a video with my iffy solution and "alright, how would you do this?". Already have an improvement from @OzExcel, sent at 3AM his time, not having slept yet. Have a go if you wish: https://t.co/PSvf9Y1P6g #ExcelInsomniacs
— Bill Jelen (@MrExcel) February 9, 2020

Get and Transform (Power Query) in Excel

The solution Bill walks through involves the Get and Transform feature in Excel. I’m on a Mac and don’t have Get and Transform since you need to be an Office 365 subscriber and my version of Excel is too old, but the method of transforming the data reminds me a lot of the transformations OpenRefine provides you. Major benefit of OpenRefine is that it’s free. After you unpivot the columns, Bill’s solution involves a bit of text manipulation to get your categories/dimensions to look the way you want.

Personally, I would’ve approached the problem like this user did with using the INDEX and MATCH formulas to pinpoint the row/column of data you need from the original “bad” dataset and getting the data to show up in your clean table:

INDEX(UGLY_RNG,MATCH(CAT),MATCH(EMPLOYEE#)+QTR#)

I didn’t explore this solution entirely, but I prefer solving these data structuring problems with formulas vs. macros and “applied steps” in Get and Transform.

No-Code and keeping your identity small

In the second half of this episode, I mention an episode with Shawn Wang from Netlify on the Software Engineering Daily podcast, and highly recommend checking out a few blog posts related to the talkL: