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

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

/in /by

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!

/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