Insert a Line-Feed Between Every 3rd Character Using Excel’s Power Query

/in /by

This video responds to a question: how can you add a line-feed between every 30th character.

This was a bit intimidating as I wondered WHY would someone’s data be in such a condition? But I thought of an example where area codes might be all smashed together, with no other way to split them apart except for knowledge that they’re always 3 digits long.

In this video, you see Power Query (Get & Transform), split by number of characters, insert line-feed.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
Thumbnail image taken by Marina Green Photography

HOME

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

Pants on Fire Storytelling Show: Oz du Soleil learns the danger of playing cards with strangers

/in /by

Pants on Fire is a storytelling event in Portland, OR where the audience hears seven outrageous stories, and one is a lie. Here, I tell the true story of playing cards on a city bus with spree killer Alton Coleman.

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

Aggregating Joins in Power Query: the mysteries

/in /by

This video explores Aggregating Joins. These are strange because they can be handy. However, as we see in the video, sometimes Grouping or regular joins are better solutions. Still, you should know that Aggregating Joins is available to you.

I’m interesting in knowing your thoughts on this. Do you use Aggregate Joins a lot? Whats’t he deal?

Thumbnail image taken by Marina Green Photography

HOME

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

Test Your Knowledge on Excel Joins with this 10-Question Quiz

/in /by

My past few videos have covered Joins and other methods for merging data in Microsoft Excel using Get & Transform (Power Query).

Now, take this 10-question quiz. Test your knowledge on working with Joins. See if you’re ready to battle Crap Data and help get this world’s data clean.

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

Retrieve The Correct Column Header (Leila Gharani Kevin Lehrbass Excel Challenge)

/in /by

This came from Leila Gharani
Original Video https://youtu.be/OJLfPc9YlqE
Then Kevin Lehrbass took it on:

The challenge:
You have data in a matrix. Locate a specific piece of data and retrieve the name of the column it’s in.

This is tougher than you might think.

This video is a glimpse of the solutions from
Leila (COLUMN, SUMPRODUCT and INDEX) and
Kevin (Array Formulas, Helper Columns, MIN, FIND).
Then I show 2 solutions:
– Formulas (COUNTIFS, INDEX)
– Get & Transform (Unpivot)

Take a look!

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: Split Column by Delimiter and Split into Rows

/in /by

So, you’ve got a bunch of data all in one cell. You need to not only get it out, but it needs to be in rows, stacked in a single column.

With this new Get & Transform (Power Query) feature it’s EASY!
Split Column by Delimiter into Rows

This video has 2 examples:
1. A plain example
2. A real world example

You’ll also see a left outer join … and a sword.

Download the file here:
http://datascopic.net/columns2rows
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

Cross Joins in Power Query: Measuring Student Progress

/in /by

In this video we’re measuring student progress against program requirement. A full outer join is used as a final step. However, the cross join is needed so that we can identify where students haven’t done ANY work in a category. The full outer join won’t pick up data that doesn’t exist.

What is a Cross Join? It’s a way of merging data when you need everything on List 1 to be matched with everything on List 2.

A cross join isn’t one of the 6 joins packaged in Get & Transform (Power Query) and isn’t used much. However, the cross join does have a purpose.

This video shows how easy it is to make a cross join but more importantly:
– A practical example of when to use a cross join.
– Why a cross join was used instead of other joins or Get and Transform (Power Query) features.

In this situation, we’re measuring student progress against program requirement. A full outer join is used as a final step. However, the cross join is needed so that we can identify where students haven’t done ANY work in a category. The full outer join won’t pick up data that doesn’t exist.

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

The Missing Donation Receipts: Using a Power Query Anti-Join against Multiple Records and Criteria

/in /by

This is real!
A non-profit needs to determine which tax receipts have NOT been issued. This is easy to figure out if each donor made one donation. But it’s harder when:
– The same person donates multiple times AND
– They donate the same amount multiple times.

Anne donated $150, $150 and $95.
Her receipt for $150 went out. A simple Anti-Join won’t find the second donation of $150.

In this video we use Get and Transform’s (Power Query’s) Anti-Join in a special way to dig out all donation receipts that still need to be issued.

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

The 6 Joins in Power Query: Explained

/in /by

You’ve seen the Joins in other videos, but here is a fun overview of all 6 joins in Excel’s Get & Transform (Power Query)

TIMELINE
2:39 Creating Queries
3:47 Full Outer Join
6:00 Left Outer Join
7:18 Right Outer Join
8:45 Inner Join
9:51 Right Anti Join
11:24 Left Anti Join
13:06 Something Crazy

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