As follow up to my previous video on making running totals in Excel’s Power Query (Get and Transform) I show a much simpler solution. Thank you to Wyn Hopkins and Peter Bartholomew.

Special thanks to Gráinne Duggan for an important warning. If we make the running total, sort the source data and then refresh, the running total will turn into a mess.

My Patreon Page: https://www.patreon.com/Data

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

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

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

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

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

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