Entries by Oz du Soleil

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 […]

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 […]

Cross Joins in Power Query: Measuring Student Progress

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 […]

The 6 Joins in Power Query: Explained

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: […]

Segmenting a List into 8 Pieces using Excel’s Power Query (Get and Transform)

WARNING: this is a fast-paced overview. Don’t worry if you can’t follow every step. The detailed workbook can be downloaded here: http://datascopic.net/3Stores8Segments 0:29 Overview 2:35 Demonstration of the solution 3:17 How the solution/model works 3:45 Figuring out who HAS NOT made any purchases 4:30 Who’s made purchases at all 3 stores? 7:04 Figuring out who […]