NOTE: This video isn’t about the details. It’s the thought process. So, please don’t worry if you don’t catch everything.

Download here: http://datascopic.net/messiest

Subscriber, eCabinets Tips and Tricks asked about the most bizarre spreadsheet I’ve ever faced, and what I did to untangle it.

‘Bizarre’ can be measured a lot of ways. I chose this example because it almost beat me. I almost threw up my hands and accepted defeat. It was too big to even try to handle manually.

I’ve faced spreadsheets that had far more complexity, but I could see the solution and it was just a matter of plowing through. But this one … it took a week to find a way to even get started.

It was a spreadsheet that had 2000 rows and over 600 columns. The data in the columns were all out of order, and needed to be moved in groups of 15. My job was to get them all in order.

Two things made a solution possible:
1. HLOOKUP (this is the only time I’ve ever used HLOOKUP. Ever.)
2. Making a small, simple model of the problem that could then be scaled out to the real data.

#2 is what I really want to stress: when a task is overwhelming, slow down. Make a small model. Clear away the noise and just focus on developing a solution.

eCabinets Tip and Tricks
https://www.youtube.com/user/ecabinetstips
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Power Query has Clean and Trim features, but sometimes those don’t work with line-breaks.

1:49 Importing the data from the Wikipedia page
2:29 Unpivot
3:13 Split columns
3:35 Explanation of the problem of the data being on multiple lines
4:30 Clean & Trim (they don’t work)
4:48 Setting up the data to
5:36 #(lf) to expose the line-feeds
6:22 Split columns
6:50 Unpivot

This video imports data from a Wikipedia page and cleanses it. The problem is each cell has a tall column of data that needs to be parsed. To do this, watch how we have to get crafty because it’s necessary to handle this with M code.

We have to use #(lf)

Along the way, you’ll see:
Unpivot,
Replace Values
Trim and Cleanse
And lots of fire.

Contact me:
oz1.depot@gmail.com

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns and the “gotchas” that you need to know about.

0:49 Description of the 3 warnings
1:09 Showing the problem
2:00 Problem 1: The source columns are gone
2:17 Problem 2: The columns are out of order
2:50 Merge properly
3:09 Problem 3: Excess delimiters
3:29 A hack for replacing the excess delimiters
5:12 Using TEXTJOIN to do the work in native Excel

The problems:
1. The source columns disappear after you merge. If you need the source columns to do other calculations or transformations, you’ll need to duplicate those columns, then merge the duplicates.

2. The final order after merging depends on the order that you highlighted the columns. In this video, I highlighted column 3 first, and then 1, 2 and 4. Yup! The data merged out of order.

3. If you have blank cells, Power Query will add a delimiter any way. And those delimiters are tricky to get rid of! I show you a hack on how to clean those out.

To compare, I show you how to do the same thing in native Excel by using TEXTJOIN

Thanks again to:
Ernie Johnson and
Puneet Gogia ExcelChamps.com

Contact me:
oz1.depot@gmail.com

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

“Reference a Query” is a feature in Excel’s Get and Transform, and it’s not obvious what it does. From the outside it looks the same as Duplicate Query.

This video shows an example of when you’d reference a query and how the feature is used.

Here, we received a dataset that looks like 2 datasets that had been stuck together. They need to be separated, cleansed 2 different ways and then put back together.

“Referencing the Query” allows the user to segment and cleanse the data while remaining connected to the source data in case updates are made.

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My website: https://ozdusoleil.com