The Madness of How to Make Running Totals in Power Query

This video is a wild one! To make running totals in native Excel is pretty easy. However, in this video, you will see the adventure for making Running Totals in Power Query (Get and Transform).

You will see some M Coding in Power Query, Lists, List.Range, List.Sum, custom columns, and putting an entire table inside a single column.

In Excel we can work at the cell level and that’s what makes running totals easy. But, in Power Query, we have to work on entire rows or columns. So, there are a lot of steps to force Power Query to give us what we want.

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: http://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

Using Power Query to Import Names To Make Dummy Data

I maintain a file of dummy data and I describe the 2 reasons in this video. But where does the data come from?

Here, I show how to import and clean data from this web page of 200 top Brazilian first names.
http://www.studentsoftheworld.info/penpals/stats.php3?Pays=BRA
Once the names are imported, the names have to be fixed using Power Query’s “Add Column by Example.” This makes it easy to correct names that are ALLCAPS or all lowercase.

The result is over 20 Million combinations of names for when it’s necessary to make tutorials and test solutions.

I also mention Excel Weekend 4 that’s happening in January 2018 in São Paulo Brazil. Learn more about it here:
http://www.excelweekend.com.br/
For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: http://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 PowerQuery Instinct With This 5 Question Quiz

Download the workbook here:
http://datascopic.net/GTInstinct
LET’S WHOOP CRAP DATA!

This 5-question quiz is designed to expose you to Crap Data and test your instinct for what Power Query (Get and Transform) features you’ll need for victory.

The solutions aren’t in this video because we’re focused on INSTINCT–what you think you’ll need before you get started. However, the solutions are in the workbook that you can download here: http://datascopic.net/GTInstinct

Website: http://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

How to Split Columns with Power Query with Variable Delimiters

It’s easy to split columns by delimiter but there’s a problem if you need to automate the parsing.

Get & Transform (Power Query) hard-codes a number the first time you create the query, and if that number is exceeded, new data will just disappear.

This video shows how to set up a query so that all future data will be integrated. This involves creating a list of lists in Get and Transform then converting it back to a table.

There is one trick and I learned it from DataChant:

Website: http://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

Paqui One-Chip Challenge 2017

Here it is! My Paqui One-Chip Challenge video.
This chip is HOT! You’ll see.

#onechipchallenge
#sweeps

Website: http://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

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

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: http://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

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

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: http://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

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

Website: http://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

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: http://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