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:
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:
- Load the data in to query editor
- Replace nulls (blank cells) with something that is not part of your data, like %^%
- Using Text.Combine, concatenate all values to a single value, separated by some symbol that is not part of data, like =
- Convert this single value to a table and split by the symbol used in step 2.
- Calculate number of = symbols in each row using Text.Length and Text.Replace functions in to a new column
- Figure out the maximum of this new column as a variable. Name it as MaxCols
- Remove the extra column created in step 5
- Split the first column by delimiter set in step 3 with number of columns set to MaxCols.
- 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.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?