The Majesty of DUPLICATE QUERY in Power Query

/in /by

Duplicate Query in Power Query is underused and under-discussed. This video shows one way to use Duplicate Query to save from doing a lot of work over and over again.

This video also shows split column; split column non-digit to digit; a little M-code. There’s great stuff to keep you from having to work so hard to get cooperation from your data.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Creating Fake Data in Excel: random dollar amounts & dates using RANDARRAY & TEXT

/in /by

To do training we need fake data (dummy data) to work with. In this video I show how to use RANDARRAY and the TEXT function to create a list of dummy transactions. This solution also takes advantage of Dynamic Arrays and the new calculation engine behind Excel. We also generate a list of fake dates.

Power Query Tips & Techniques Course
https://www.linkedin.com/learning/excel-power-query-tips-and-techniques-office-365/what-you-should-know?u=2125562
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
#DynamicArrays
#RANDARRAY
#FakeDataGenerator

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Full Anti-Join and the New XLOOKUP with “if not found”

/in /by

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 full anti-join. One way to do this would be to do a left anti-join and then a right anti-join, then append the results.

Another way, as I show in this video, is to append the 2 datasets, create an inner join, then use an anti-join to isolate the people who are in one group or the other but not both.

XLOOKUP!
WOW! This was revised within the last 24 hours to include a 6th argument for: “if not found.”

This is exciting because a lot of times we’ll do a lookup and anticipate results where the lookup value isn’t found. The fix is to wrap a lookup formula in the IFNA function. No more!

The 6th argument, “if not found” is a valuable feature because we no longer need the extra step of wrapping IFNA around the formula. And the 6th argument is optional.

#FullAntiJoin
#PowerQuery
#InnerJoin
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Use Power Query to Get Top X Scores and Tie Scores

/in /by

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 Query and consider tie scores.

You’ll see Power Query, drill down, duplicate query, and a little a little M-coding in order to get this done.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Extract a column of data from a Power Query table

/in /by

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 and keep the data in the appropriate rows and cells?

We have to use the Power Query function: Text.Column

In this video you’ll see
– Camera Tool
– using a line-feed as a delimiter
– how Table.Column is used
– Grouping in Power Query
– Extract values from a list

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

XLOOKUP: Search from the bottom and top of a list

/in /by

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 who took more than the allowed 90 days.

This is complicated by 2 issues:
1. People taking the courses out of order.
2. Some people, not all, are required to take course 3A.

VLOOKUP can do the easy part and retrieve the first completion date for each person. XLOOKUP can do that. But XLOOKUP can also do the harder part of starting a search at the bottom of the list, and work upward. This is really exciting and shows how XLOOKUP is indeed the replacement for VLOOKUP.

Also in this video, we use dynamics arrays, the UNIQUE function, to extract the names of the students. UNIQUE, XLOOKUP! We’re really doing some Excel folks!

#XLOOKUP
#UNIQUE
#DynamicArrays

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

XLOOKUP and the XLOOKUP Perth Peace Summit

/in /by

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 of XLOOKUP. Also, this includes footage of the emergency XLOOKUP Peace Summit where fellow Excel MVP, Wyn Hopkins, and I signed a Declaration of Peace, ending the VLOOKUP vs INDEX/MATCH squabbling.

XLOOKUP will live on!

#XLOOKUP
#VLOOKUP
#INDEXMATCH

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Fuzzy Matching: Matching Inconsistent Phone Numbers in Power Query

/in /by

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 the same phone number. Without fuzzy matching in Excel or Power Query, to match these we’d have to clean them up and get them consistent before attempting a merge, join or match.

Power Query’s fuzzy matching does a good job but it takes some fiddling with the threshold percentage. But there’s a warning! Fuzzy matching is based on percentages and likelihood. It can get a lot right and it can get a lot wrong. BE CAREFUL!

#LeftOuterJoin
#FuzzyMatching
#Matching

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

The New Fuzzy Matching Feature in Excel’s Power Query

/in /by

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 to be careful with the Fuzzy Matching because it’s based on a percentage of similarity. Check out the video. You’ll see.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2