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

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