Excel Power Query: Clear Line-Breaks (A correction to the previous video)

/in /by

Here’s a quick video correcting 2 things from the previous video: Import Data from Web Page and Clear Line-Breaks

Thanks: Wyn Hopkins, Micheal Reynolds and Chris Pope for pointing these out:
1. There was a single carat delimiter that I missed
2. There’s a way in Power Query (Get and Transform) to separate columns by line-feeds and carriage returns.

Have a look! And thanks for being in the battle against Crap Data.

Also, I mention the Amsterdam MVP Excel Summit. It’s 2 days of Excel MVPS teaching the public the greatest about Excel.
It’s 18 and 19 April. Here’s a link for more details:

The Amsterdam Excel Summit

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

Excel Power Query: Import Data from Web Page and Clear Line-Breaks

/in /by

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

Power Query Merging Columns and M Code (managing nulls and excess delimiters)

/in /by

In this video, I go deeper into the issue of clearing excessive delimiters after merging columns in Power Query (Get and Transform).

The previous, related video:

This video describes:
1. Why my hack worked.
I replaced nulls with: ^
Then systematically replaced the ^ symbols. This is much better that merging columns and having lots of delimiters stuck together

2. An M-code solution that Owen Auger helped me with, using
List.RemoveNulls
This is a single step and very clean. However, this solution is in the world of writing code and is great for those who are comfortable with coding.

Check out the previous video to see how this can be handled in native Excel by using the TEXTJOIN function.

Also mentioned in the video; other giants in the world of writing M-code:

Owen Auger
https://owenaugerblog.wordpress.com/
Miguel Escobar
https://www.poweredsolutions.co/blog/
Ken Puls
http://www.excelguru.ca/blog/
Bill Syzyz
https://www.youtube.com/channel/UCx_joCOEjU1KdTpPvu4baYA
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 Merge Columns

/in /by

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

Complex IF Formula Converted to 2-Way Lookup with INDEX-MATCH-MATCH

/in /by

0:00 – what we’re up to
1:21 – looking at the problem
5:12 – a word “going rogue” as an effective style
6:09 – starting the solution
6:29 – using RANDBETWEEN to make up some data
7:14 – making dropdown lists
8:07 – INDEX-MATCH-MATCH making the 2-way lookup
10:00 – summary and final comments

Someone sent me a spreadsheet that had a large complex formula in it. There were 5 IF statements inside each other and some CHOOSE functions.

**NOTE: this video moves fast because the concepts are more important than the how-to.**

This video focuses on the thought process of:
– understanding the formula and user’s needs
– realizing that the formula is using 2 variables to retrieve a piece of data
– the objective is really a 2-way lookup
– INDEX-MATCH-MATCH is what we’ll use to get Excel to retrieve the data.

Another important point is that I don’t try to recreate the person’s data inside the original spreadsheet. I focus on the concept and getting the 2-way lookup working.

This way, it’s not necessary to try to understand the person’s business, terminology, and all the details of how they developed their spreadsheet. Full attention can be devoted to getting the 2-way lookup working. Then, hand it back to the user and let him recreate the mechanism as he needs it.

One thing to appreciate is that the user wrote an effective formula with the IF and CHOOSE functions. This video just shows a cleaner alternative.

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

Referencing Queries with Get & Transform: Segmenting, Cleaning and Appending a Messy Dataset

/in /by

“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

Celebrating 1000 Subscribers to Excel on Fire

/in /by

Thanks for all of the suggestions on what we could do with this CELEBRATION OF 1000 SUBSCRIBERS!

In this video I responded to your suggestions and there’s no Excel here. Surprisingly, more people were interested in “a day in the life.” So, you get a bit of that–including a recipe for cooking alligator.

I hope you enjoy the video! Be in touch, and please comment.

Links that are mentioned:
– Cuss-o-meter

– Excel & Music
Excel, Music, Data, R Kelly & D’Angelo

In The Band Using Excel to study song structure

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 3 Consolidate Workbooks Worksheets Using Power Query

/in /by

In this final video of the 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to consolidate your various Excel workbooks and worksheets using Power Query. Download the Excel workbooks used in this tutorial to practice:
http://myexcelonline.com/blog/wp-content/uploads/2016/06/Data-Cleaning-Power-Query-Training-Video-3.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 Oz du Soleil
03:30 – Consolidate multiple Excel workbooks using Power Query (Get & Transform in Excel 2016)
12:50 – Consolidate multiple Excel worksheets using Power Query (Get & Transform in Excel 2016)

Click on the following links to watch the other training videos:

VIDEO 1: Different Ways to Format Data Using Power Query

VIDEO 2: Format & extract data using Excel Formulas & Excel’s Data Tools

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

Please share, like & leave your comments below.

Thanks much!

Oz

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 2 Clean Extract Data Using Formulas Analytical Tools

/in /by

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