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.
https://ozdusoleil.com/wp-content/uploads/2017/08/test-your-knowledge-on-excel-joins-with-this-10-question-quiz-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-09-01 02:00:412020-11-03 09:43:38Test Your Knowledge on Excel Joins with this 10-Question Quiz
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, SUMPRODUCT and INDEX) and Kevin (Array Formulas, Helper Columns, MIN, FIND). Then I show 2 solutions: – Formulas (COUNTIFS, INDEX) – Get & Transform (Unpivot)
https://ozdusoleil.com/wp-content/uploads/2017/08/retrieve-the-correct-column-header-leila-gharani-kevin-lehrbass-excel-challenge-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-08-21 20:11:462020-11-03 09:43:41Retrieve The Correct Column Header (Leila Gharani Kevin Lehrbass Excel Challenge)
https://ozdusoleil.com/wp-content/uploads/2017/08/power-query-split-column-by-delimiter-and-split-into-rows-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-08-07 15:24:202020-11-03 09:43:44Power Query: Split Column by Delimiter and Split into Rows
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 Join? It’s a way of merging data when you need everything on List 1 to be matched with everything on List 2.
A cross join isn’t one of the 6 joins packaged in Get & Transform (Power Query) and isn’t used much. However, the cross join does have a purpose.
This video shows how easy it is to make a cross join but more importantly: – A practical example of when to use a cross join. – Why a cross join was used instead of other joins or Get and Transform (Power Query) features.
In this situation, 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.
https://ozdusoleil.com/wp-content/uploads/2017/08/cross-joins-in-power-query-measuring-student-progress-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-08-02 02:24:052020-11-03 09:43:45Cross Joins in Power Query: Measuring Student Progress
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 for $150 went out. A simple Anti-Join won’t find the second donation of $150.
In this video we use Get and Transform’s (Power Query’s) Anti-Join in a special way to dig out all donation receipts that still need to be issued.
https://ozdusoleil.com/wp-content/uploads/2017/07/the-missing-donation-receipts-using-a-power-query-anti-join-against-multiple-records-and-criteria-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-07-25 19:26:372020-11-03 09:43:48The Missing Donation Receipts: Using a Power Query Anti-Join against Multiple Records and Criteria
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
https://ozdusoleil.com/wp-content/uploads/2017/07/the-6-joins-in-power-query-explained-youtube-thumbnail.jpg480640Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-07-21 02:36:332020-11-03 09:43:51The 6 Joins in Power Query: Explained
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 shopped at just 1 store 8:53 Who’s shopped at 2 stores
THE CHALLENGE My friend Bart in Amsterdam sent a challenge: In a loyalty program there are cardholders and 3 stores.
How can Excel or Power Query (Get & Transform) segment the list of transactions into 8 segments? People who’ve …
1. not made any purchases 2. purchased at all 3 stores 3. only shopped at Trader Joe’s 4. only shopped at Wegmans 5. only shopped at Publix 6. shopped only at Publix and Trader Joe’s 7. shopped only at Publix and Wegmans 8. shopped only at Trader Joe’s and Wegmans
This solution weaves together lots of Power Query features, including: left anti-join, left outer-join, grouping, fill-up, filtering, and conditional columns.
https://ozdusoleil.com/wp-content/uploads/2017/07/segmenting-a-list-into-8-pieces-using-excels-power-query-get-and-transform-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-07-08 05:32:332020-11-03 09:43:52Segmenting a List into 8 Pieces using Excel’s Power Query (Get and Transform)
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 know about creating hyperlinks. Regular references to cells can look like: Sheet3!B5 But hyperlinks need to include a ‘#.” Therefore:
#Sheet3!B5
This video shows how to make the dynamic hyperlink. It’s crazy! We have to use COUNTIF, MATCH, OFFSET, INDIRECT, HYPERLINK and helper columns.
Download the workbook here: http://datascopic.net/hyperlink This video was recorded at Casa de Montecristo by Cigar Inn at 2nd & 54th in New York City.
https://ozdusoleil.com/wp-content/uploads/2017/06/creating-a-variable-hyperlink-in-excel-youtube-thumbnail.jpg480640Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-06-14 07:48:562020-11-03 09:43:54Creating a Variable Hyperlink in Excel
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 Transform) solution using: – Import data from another workbook – Fill Down – Add Custom Column – Grouping with multiple aggregations
https://ozdusoleil.com/wp-content/uploads/2017/05/power-query-summing-data-from-specific-ranges-youtube-thumbnail.jpg7201280Oz du Soleilhttps://ozdusoleil.com/wp-content/uploads/2017/01/oz-logo-v4-300x73.pngOz du Soleil2017-05-27 06:31:452020-11-03 09:43:55Power Query: Summing Data from Specific Ranges
