Entries by Oz du Soleil

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

Creating a Variable Hyperlink in Excel

This is cool! The problem: 11 worksheets and hundreds of codes Objective: the ability to type a code and be taken directly to that code wherever it is in the workbook. It’s like the Find/Select feature in Excel, the user wanted to stay in the worksheet and minimize use of the ribbon. One thing to […]

Power Query: Summing Data from Specific Ranges

This is a CRAZY one! Kevin Lehrbass presented this problem: A tall stack of mini data ranges that need to be totaled. BUT! Only total the ranges that say “TOTAL.” Ignore the ranges that are labeled anything else. (Kevin’s solution: https://www.youtube.com/watch?v=oomf8bfcFEw) Kevin’s video shows various formula solutions. My video shows a Power Query (Get and […]