Let’s Celebrate Clean Data and 1000 Subscribers!

/in /by

Thanks to everyone who’s subscribed, commented, sent in questions that became videos. Around this time last year I changed my video production style and you’ve responded surprisingly well.

This channel is about to cross the 1000-subscriber mark. What should we do about that? I would like your suggestions for a video that I can make for you. Let’s make something fun and unusual!

At this moment we’re 27 subscribers away. So, we’ve got a little time to put our heads together and explore ideas.

My book: Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog-2-2

http://datascopic.net/leading-zeros)

Excel’s Get and Transform: Handling Leading Zeros and a Little M-Code

/in /by

(Download the workbook here:

Here’s another video with warning about some things that native Excel handles easier than Get & Transform (Power Query) does, but if we have to do this in Get & Transform, here’s how.

Challenge: handling leading zeros.

Excel is known for clipping off leading zeroes. If you need a the month of March to be: 03
Excel will turn it into: 3

Or, account numbers that are always 6 digits,
Excel will turn 002251
into 2251

This video shows how to force native Excel to recognize leading zeros. Also, the M-Code use of

Text.PadStart

to force Get & Transform to honor leading zeros.

You also see the insertion of steps in a Get and Transform Query, problems with text versus number formatting. Oh! And a few pictures from a recent trip to New York City.

Please, enjoy. This is a 7.5-minute adventure.

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

3 More Warnings re: Get and Transform: Disappearing Headers, Conditional Columns, Strange Duplicates

/in /by

Here’s another video that offers warnings about Excel’s Get and Transform (Power Query).

Get and Transform does a lot of exciting things, but there are some things that are still better done in native Excel, and there unexpected ways you can be stung.

So, rather than how-to, this video warns about ways to get stung if you don’t know better. Read more →

https://www.youtube.com/user/MySpreadsheetLab/videos

Unstack Data that is Separated by Bold Text: Challenge w/Kevin Lehrbass – Formulas, VBA, Power Query

/in /by

Problem: a tall column of data that needs to be unstacked and converted into neat columns and rows.
Twist: the distinction where the list needs to be divided is by bold font.

(Download the workbook:
http://datascopic.net/unstackingboldfont )

Parents’ names are bold. Childrens’ names are regular font.

How does this list get unstacked with the children next to the right parent?

Kevin Lehrbass and I are here to offer several solutions. I open with Excel’s Get and Transform (Power Query). Kevin takes over and shows a formula-based solution and a VBA solution!

Three whole solutions for you! Cleansing this data and getting this stuff unstacked!

Check out Kevin’s channel:

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

3 Warnings about Excel’s Get and Transform Feature

/in /by

Excel’s Get & Transform is a fantastic tool, but there are some warnings that you should be aware of before you get stung. (Watch out for the scorpion)

This video shows 3 ways you can get stung:

3:20 – 6:24
Column headers are hard-coded. If you need a column header to be variable, it’s a bit tricky and requires going into the M Code in Get and Transform’s advanced editor.

6:32 – 6:56
Some things in Get & Transform start counting at 0 instead of 1. You’ll see where I point to column 4 but have to change it to column 3.

7:10 – 8:47
A number and text cannot be put together in the same cell. In this video I want to get a result like: 2000 sqft.

The number has to be formatted as text in order to be combined with ‘sqft.’

Have a look. Let me know if you have questions.
And please subscribe to the channel.

My book: Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog-2-2

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

3 ways to compare and clean lists in Excel: VLOOKUP, MATCH, Get & Transform Left Anti-Join

/in /by

Someone posted an urgent request. Some contacts from a small list had gotten combined with a larger list.

Question: how can I compare the 2 lists and extract the ones that shouldn’t be in the big list?

This video shows 3 ways:
Formulas:
– VLOOKUP
– MATCH and
Get and Transform (Power Query)
– Left Anti-Join

I hope you enjoy. Please subscribe to the channel, and let me know if you have any Excel questions!

My book: Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog-2-2

https://www.youtube.com/watch?v=-jihz5E0X5k)

Adding Line-Breaks in an Excel Cell: Using CHAR and SUBSTITUTE

/in /by

In the previous video I showed how to retrieve Word data and bring it into Excel. However, Nisha in Chicago noticed that the original data was in lists, but I didn’t retain the lists when the data was brought into Excel.

(The original video:

Nisha asked, what it would take to get the data into lists.

In this video I use the functions CHAR and SUBSTITUTE to insert line-breaks. Also, “wrap text” is required so that Excel can display the data in lists.

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

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

Transferring Data from a Word Table and into Excel

/in /by

Data is in tables in Microsoft Word and needs to be moved over to Excel–and retain the table structure.

PROBLEM: copy from Word and into Excel creates a mess. Items that were in the same cell in Word are in different cells in Excel.

The solution is to replace ^p in the document with something that can be used as a delimiter in Excel.

Notice in the video I used zzzz and then changed to a double-pipe as a delimiter. I could have used the double-pipe first, but it’s something I figured out during the editing of this video.

I had tried ** as a delimiter and that caused problems as I eventually realized that asterisks are seen as wildcards and aren’t good as delimiters if you’re going to apply Find/Replace.

My book: Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog-2-2

Excel Challenge (Kevin Lehrbass): Get & Transform to List All Unique Pairs in a Class of 10 Students

/in /by

Once again, Kevin Lehrbass (MySpreadsheetLab) and I are taking on a common challenge and comparing each others’ solutions.

Today’s challenge: if you have 10 students in a class, and would like each student to work with a different student each week, how can you get a list of all of the unique pairs?

My favorite solution that Kevin created uses pivot tables and helper columns. Check out the details of Kevin’s solutions here:

Kevin’s blogpost

Video 00153 All Possible Pairs from List of Names

I present 2 solutions:
1. A Matrix solution that pairs each student.
2. Get & Transform. This uses a cross-join aka Cartesian-join and it’s beautiful! I also show how easy it is to get the unique pairs from 22 students.

This video takes you through a detailed approach to solving this problem in Excel.

ModelOff Global Training Camp – Toronto

Toronto GTC

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