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: https://ozdusoleil.com My book: Guerrilla Data […]
This came from Leila Gharani Original Video https://youtu.be/OJLfPc9YlqE Then Kevin Lehrbass took it on: The challenge: You have data in a matrix. Locate a specific piece of data and retrieve the name of the column it’s in. This is tougher than you might think. This video is a glimpse of the solutions from Leila (COLUMN, […]
So, you’ve got a bunch of data all in one cell. You need to not only get it out, but it needs to be in rows, stacked in a single column. With this new Get & Transform (Power Query) feature it’s EASY! Split Column by Delimiter into Rows This video has 2 examples: 1. A […]
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 […]
This is real! A non-profit needs to determine which tax receipts have NOT been issued. This is easy to figure out if each donor made one donation. But it’s harder when: – The same person donates multiple times AND – They donate the same amount multiple times. Anne donated $150, $150 and $95. Her receipt […]
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: […]
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 […]
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 […]
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 […]