Power Query: Sorting & Merging and ignoring Case-Sensitivity | Excel Days in Bulgaria | My Roadtrip

/in /by

Power Query does some odd things because it’s so case-sensitive.

If you want to sort this list:
iPhone
Ice Cream
USB cable

Power Query will return:
Ice Cream
USB Cable
iPhone

If you need to do a merge with:
De Soto and de Soto
Power Query will not get those matched.

In this video I show you how to use Text.Upper to get the sorting right and Fuzzy Matching and Similarity Threshold to get the merge right.

Thank you to Ed Hansberry for his blogpost that shows details on merging and ignoring case-sensitivity:
https://www.ehansalytics.com/blog/2020/4/27/case-insensitive-merges-in-power-query
Plus! I share with you where I’ve been for the past month and invite you to Excel Days in Bulgaria on 11NOV22.

0:00 Introduction
0:15 Roadtrip Overview
1:05 Excel Days in Sofia Bulgaria
3:03 Power Query: sorting without case-sensitivity
7:16 Power Query: merging and ignoring case-sensitivity

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, good spreadsheet habits, and Excel challenges that comes out every Friday for beginners and every-other-Monday for power users.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 3rd Edition
https://www.mrexcel.com/products/guerrilla-data-analysis-3rd-edition/
My old blog: http://datascopic.net/blog-2-2