This video is a 2-for-1. You get the new XLOOKUP and a Full Anti Join in Power Query (Get & Transform). What is a full anti-join? If there was a class held on Monday and Wednesday and you’d like to know who attended one session or the other BUT NOT BOTH, you would need a […]
What if you need the top 5 scores but there’s a 4-way tie for 5th? Well, we need to get all 8 people not just the top 5. There are lots of videos showing how to extract the top X values in a list. But what about tie? This video shows how to use Power […]
After grouping data in Power Query, in this example, the data is exactly how we want it. It’s grouped into the right rows in the column. BUT! It’s in a collapsed column of tables. If we expand, the data will fly apart. So, how do we get a column of data out of a table […]
More XLOOKUP! This video shows how I would have completed a task 15 years ago if I had XLOOKUP available. Here, with a list of course completions, sorted by completion date, we need to calculate the number of days between the first completion and the last completion. And then see if there are any students […]
XLOOKUP is finally here! We have formally declared an end to the VLOOKUP vs. INDEX/MATCH drama. XLOOKUP gives us so much more power and flexibility than VLOOKUP and it’s a single formula unlike INDEX/MATCH which is a nested formula of 2 functions with their own syntax and quirks. This video gives you a small taste […]
Excel Putting it All Together: https://courses.xelplus.com/p/excel-app-from-scratch This video digs into the new Fuzzy Match options in Power Query. In a previous video we looked at names. Here, we look at phone numbers and how Fuzzy Matching attempts to match inconsistent phone numbers. Example: 1 (717) 338-2741 717-388-2741 717.388.2741 7173882741 These are all legitimate variations on […]
There’s a new feature in Excel’s Power Query and it’s the Fuzzy Matching features in the Merge/Joins wizard. Fuzzy Matching or Fuzzy Merging are when you have to match things like: Jen and Jenny Rob and Robert Rick T. Burns and Rick Burns These won’t match at all via regular joins. But the user has […]
A few months ago I compared Excel’s Flash Fill against Power Query’s Add Column by Example. Add Column by Example didn’t fare so well. HOWEVER! I got word that Add Column by Example has been improved. It has and it works great. Check it out. #AddColumnbyExample #PowerQuery #PowerQueryTutorial For an intro to Get & Transform […]
This is a long-time problem with working with data: all of the fields are smashed against each other with no delimiter in between. In this video, names are smashed against phone number. How do we peel them apart? In Power Query there is a brand new feature that splits columns in several ways: – from […]