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

Power Query’s Column by Examples has been Improved

/in /by

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 (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

Power Query: Split columns between digits and non-digits

/in /by

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 Digit to Non-Digit
– from Non-Digit to Digit
– from UpperCase to LowerCase
– from LowerCase to UpperCase

Thus, it’s easy to split a column by the point where a string of letters changes to a number. It’s beautiful!

No more making wild formulas in Excel with the SUBSTITUTE function and Text-to-Columns. NO MORE! Those are officially the olden days!

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

Using a Transformation Table in Power Query’s Fuzzy Merges

/in /by

How can we merge data when New York is listed at NYC, New York City and NY, NY? This is a common problem and there’s a new feature in Excel’s Power Query that can help us solve this.

We can build a Transformation Table in Power Query as part of making a join. This video shows how to use a Transformation Table within a Left Outer Join. We go into the new menu for Fuzzy Merges and insert the magical Transformation Table.

This is new to Excel and Power Query but Transformation Tables have been part of PowerBI for several months.

#TransformationTable
#PowerQueryJoins
#FuzzyMerge

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

Emojis, Excel, Power Query & Dynamic Arrays. It’s true!

/in /by

Did you ever think that you could write a formula with a picture of grapes and have the result be a picture of a train?

It’s possible to add emojis in Excel. But it gets better! This isn’t like adding a picture just to make things pretty. No no. The emojis can be used like text or numbers.

This video shows 8 uses for emojis in Excel:
– Emojis can be used as values (text or numbers)
– Column headers
– Slicers
– Formulas
– Dynamic Arrays. I show both the FILTER and UNIQUE functions.
– Dropdown lists
– Power Query
– Excel Online

Thanks to Frédéric Le Guen for his blogpost that opened these possibilities. See his blogpost here:

Add Emojis to your charts, formulas

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

Power Query: Load to Data Model for Cleansing and Analysis

/in /by

One option when loading a query from Power Query is: Load to Data Model (or create Relationships). In this video that’s what we cover, and why.

In this video we have 3 tables of data and we’d like to answer questions that can only be answered if the data were all in one place.

We could use Power Query but Power Query would require a bunch of queries and isn’t good if we have ad hoc questions. Instead, loading to a Data Model (Relationships) in Power Pivot is the sexy thing to do!

The Data Model will treat the 3 separate tables like a single source of data that we can create one pivot table from.

One thing that’s important with Data Models and relationships. One side of the relationship needs to have all unique values. Example:

We can have Routes: TU-3 and WE-1 multiple times in a table, but we can only match that with a table that has all routes only one time each. More formally: we can have one-to-one relationships and one-to-many. We cannot have many-to-many relationships.

#DataModel
#PivotTable
#SexyData

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

Dynamic Arrays: FILTER Against a List, MATCH function with 2 Criteria

/in /by

We’ve used the dynamic array FILTER function to filter for multiple criteria that will return a lot of data. But what happens when we need to filter for things that don’t fit neatly into a group or category?

In this example we have a list of names and want to filter for specific people in our source data. Using the MATCH function and a helper column, it’s pretty easy to do.

This video also includes a quick summary of Amsterdam Excel Summit 2019 and a preview of Excel Summit South 2019 in Australia:

SumProduct

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

Binning Values

/in /by

Power Query is fantastic, but there’s an ongoing need to do the equivalent of an approximate match like we do when we use VLOOKUP-True to assign categories to values.

This video shows how to assign values by “binning” which is done by using Column by Example and getting help from Power Query. Power Query will build a formula based on how we train it. Once the formula is built, it’ll probably be wrong. HOWEVER! It’s so much easier to modify the formula than it would be to stack up conditions in a conditional column, or write a messy IF statement in Power Query’s M-Code.

This example is good if your categories don’t change. If you do need variable tiers/categories, it’s better to use the method shown in this video: https://youtu.be/EYgKciBr_dg

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