Excel Power Query Data Cleansing Part 2 Clean Extract Data Using Formulas Analytical Tools

In this 2nd of a 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to clean and extract data using Excel formulas and Excel’s built in analytical tools.

This training is for beginners & intermediate Excel users and can be applied to Microsoft Excel 2003, 2007, 2010, 2013 & 2016 versions.

Download the Excel workbooks used in this tutorial to practice:
http://myexcelonline.com/blog/wp-content/uploads/2016/09/Data-Cleansing-Training-Video-2-Workbooks.zip
You will learn the following topics:

Intro from John Michaloudis
03:55 – Proper Function
05:00 – Trim Function
08:55 – Text Function
13:30 – Left Function
15:05 – Right Function
17:00 – Substitute Function
18:55 – Replace Function
20:45 – Convert Text to Numbers – TIP!!!
22:50 – Number Formats
24:00 – Find Blank Cells With Color
25:35 – Remove Duplicates
27:30 – Text to Column – Split Names
28:30 – Flash Fill

View the rest of this training series here:

VIDEO 1: Different Ways to Format Data Using Power Query

VIDEO 3: Consolidate Workbooks & Worksheets using Power Query

WATCH OUR FREE WEBINAR ON POWER QUERY & DATA CLEANSING NOW: http://www.myexcelonline.com/107-21.html

Please share, like & leave your comments below.

Thank You,

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

Excel Power Query Data Cleansing Part 1 Different Ways to Format Data Using Power Query

In this 1st of a 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to format your messy data using Power Query. Download the Excel workbooks used in this tutorial to practice:
http://myexcelonline.com/blog/wp-content/uploads/2016/06/Power-Query-Data-Cleansing-Excel-Workbooks.zip
This training is for beginners & intermediate Excel users and can be applied to Microsoft Excel 2010, 2013 & 2016 versions.

You will learn the following topics:

Intro from John Michaloudis & Oz du Soleil
12:10 – Intro to Power Query (Get & Transform in Excel 2016)
16:30 – Trim leading & trailing spaces
20:30 – Format “text” Dates & Values using Excel v Power Query
24:15 – Parse URLs using Excel v Power Query
27:55 – Transform & automate reports from an ERP system (e.g. Oracle, SAP, QuickBooks) into a flat Excel file

Watch the rest of the training videos here:
VIDEO 2: Format & extract data using Excel Formulas & Excel’s Data Tools
https://www.youtube.com/watch?v=ngnkPcKYRK4
VIDEO 3: Consolidate Workbooks & Worksheets using Power Query

WATCH OUR FREE WEBINAR ON POWER QUERY (GET & TRANSFORM) & DATA CLEANSING NOW: http://www.myexcelonline.com/107-21.html

Please share, like & leave your comments below.

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

Let’s Celebrate Clean Data and 1000 Subscribers!

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

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

(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

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

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

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

3 Warnings about Excel’s Get and Transform Feature

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

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

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

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

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