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 […]
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 […]
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 […]
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. […]
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 […]
Here it is! My Paqui One-Chip Challenge video. This chip is HOT! You’ll see. #onechipchallenge #sweeps 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 […]
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
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 […]