Нечёткий текстовый поиск в Power Query

Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.

Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.

Тренироваться будем на слегка модернизированном примере из прошлой статьи про надстройку Fuzzy Lookup - двух списках, которые нужно объединить в один по совпадению адресов:

Исходные данные

Прежде, чем начнём, обратите внимание на следующие моменты:

  • Точно в этих списках совпадает только один адрес - "Пушкино, Набережная ул., д.61". Все остальные адреса различаются с большей или меньшей степенью разброса.
  • В некоторых адресах переставлены местами слова - например "Ульяновск, Лермонтова ул., д.63" и "улица Лермонтова д.63, г. Ульяновск".
  • В некоторых не хватает части данных - например, нет города в "Сиреневая ул. д.90" во второй таблице.
  • Где-то город с "г.", а где-то без. С улицами - аналогично.
  • Есть адреса уникальные и совершенно ни на что не похожие и ни с чем не совпадающие (Париж и Рио-де-Жанейро в конце каждого списка).
  • Есть адреса с орфографическими ошибками или опечатками внутри слов (Чилябинск, Козань...)

Отдельно хочу отметить проблему с Санкт-Петербургом - этот город может быть записан кучей разных способов. Чтобы учесть этот момент при связывании нам придется заранее сделать специальную таблицу преобразований. Колонки в этой таблице должны строго называться From и To и содержать все возможные варианты наименований (столбец From) и их правильные аналоги (столбец To):

Таблица преобразований

Шаг 1. Грузим исходные данные в Power Query

Сначала, само-собой, нужно загрузить все наши три исходные таблицы в Power Query. Сделать это можно несколькими способами (именованный диапазон, область печати, лист целиком), но самым удобным будет, наверное, преобразование в "умные таблицы" с помощью сочетания клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table).

По умолчанию, каждая умная таблица получает стандартное имя а-ля Таблица1,2... что можно, при желании изменить (но я здесь не буду).

После этого созданную "умную таблицу" можно легко залить в Power Query с помощью кнопки Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если у вас версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

Грузим таблицу в Power Query

В открывшемся окне редактора запросов Power Query можно, в приципе, "допилить" наши данные при необходимости и затем сохранить полученную таблицу как подключение через Главная - Закрыть и загрузить - Закрыть и загрузить в ... (Home - Close&Load - Close&Load to...):

Выгружаем таблицу как подключение

И выбрать в следующем окне опцию Только создать подключение (Only create connection):

Выбор типа импорта

Всё это нужно по очереди проделать со всеми тремя таблицами, чтобы в итоге в правой панели запросов появились все три наши таблицы в режиме подключения:

Все загруженные таблицы в режиме подключения

Всё. Самая скучная часть - позади. Теперь переходим, непосредственно, к слиянию.

Шаг 2. Выполняем объединение

На вкладке Данные (Data) или на вкладке Power Query выбираем команду Получить данные / Создать запрос - Объединить - Объединить (Get Data / New Query - Combine queries - Merge):

Команда объединения запросов в Power Query

Откроется окно слияния:

Окно слияния

В этом окне нужно:

1. Выбрать в выпадающих списках Таблицы 1 и 2, которые мы хотим объединить.

2. Выделить в обеих таблицах столбцы, по которым мы связываем наши списки (колонки Адрес и Место, соответственно).

3. Чтобы увидеть потом не только совпадения, но и отличия и ясно понимать что именно мы нашли, а что нет - выбрать тип соединения Полное внешнее (Full Outer).

4. Включить (самое главное!) флажок Использовать нечеткие соответствия для слияния (Use fuzzy matching to perform the merge). Именно он заставляет Power Query искать не только точные совпадения, но и приблизительные.

Под ссылкой Параметры нечеткого соответствия (Fuzzy matching options) скрывается целый блок дополнительных настроек для нечеткого слияния:

Параметры нечеткого соответствия

Здесь:

  • Порог подобия (Similarity Threshold) - дробный коэффициент (от 0 до 1), определяющий, насколько строгого соответствия вы требуете при сборке. При значении этого коэффициента равном единице, Power Query будет искать, фактически, только точные совпадения. При значениях близких к нулю сильно возрастает вероятность ошибки. Имеет смысл путем 2-3 попыток подобрать максимально большое значение (т.е. максимально строгий поиск), но при котором находятся все (или большинство) результатов.
  • Игнорировать регистр (Ignore case) - по умолчанию Power Query учитывает регистр при поиске, т.е. различает Москва и МОСКВА, например. Включение этого флажка позволяет избавиться от регистрочувствительности при слиянии.
  • Сопоставление путем объединения текстовых фрагментов (Match by combining text parts) - в переводе на человеческий язык означает, что при поиске соответствий будет производится проверка на переставление слов внутри текста (помните Ульяновск и ул.Лермонтова?)
  • Если одному адресу в первой таблице соответствуте несколько похожих адресов во второй (это особенно актуально при низких значениях порога подобия), то можно ограничить количество найденных вариантов - за это отвечает параметр Максимальное число совпадений (Maximum number of matches).
  • Чтобы учесть разные варианты написания Санкт-Петербурга - укажем нашу третью таблицу как Таблицу преобразования (Transformation Table).

Выполнив все настройки, нажмём на ОК и развернём в появившемся окне Power Query вторую таблицу с помощью кнопки в шапке (флажок Использовать исходное имя столбца как префикс можно снять):

Разворачиваем вложенные таблицы

В результате получим что-то похожее на:

Результат нечеткого слияния

Как видите, все адреса нашли свои аналоги, кроме уникальных Парижа и Рио-де-Жанейро, в паре с которыми появились ячейки с null, т.е. пустотой.

Шаг 3. Пишем свою М-функцию подобия

В принципе, на этом можно было бы и остановиться, но, вот, лично меня во всей этой истории смущает один момент: как определить, насколько хорошо Power Query нашёл соответствие для каждого адреса? Представьте, что вам нужно объединить подобным образом таблицы по несколько тысяч строк - вероятность ошибки при таком объеме данных уже ощутимая. Как понять, где Power Query отработал нечёткое слияние хорошо (текст совпадает почти точно), а где стоит проверить совпадение вручную и, возможно, внести правки?

Вот если бы был (помечтаем!) в наших данных столбец, где указывался бы коэффициент подобия найденных адресов, наглядно иллюстрирующий точность подбора! Как бы это упростило поиск подозрительных вариантов!

К сожалению, я не нашел в Power Query встроенных инструментов для подобного :( Однако, мы можем своими силами реализовать похожую штуку, написав собственную функцию подобия двух текстовых строк на встроенном в Power Query языке М (за идею огромное спасибо и поклон в пояс Андрею VG с нашего форума).

Делаем следующее:

1. На вкладке Данные выбираем команду Получить данные / Создать запрос - Из других источников - Пустой запрос (Get Data / New Query - From other sources - Blank query).

2. В открывшемся окне редактора запросов жмем на Главной (Home) или на вкладке Просмотр (View) кнопку Расширенный редактор (Advanced Editor).

3. В появившемся окне удаляем всё, что там есть по-умолчанию и копируем-вставляем туда М-код нашей функции:

(text1 as text, text2 as text) as number =>
let
    text1 = Text.Upper(text1),
    text2 = Text.Upper(text2),
    matching_chars = List.Count(List.Intersect({Text.ToList(text1), Text.ToList(text2)})),
    average_length = (Text.Length(text1) + Text.Length(text2)) / 2,
    coef =  matching_chars / average_length
in
    coef

Выглядеть это всё должно, в итоге, вот так:

Код М-функции подобия в Power Query

Если интересны детали, то эта функция:

  • переводит обе текстовых строки в заглавные буквы функцией Text.Upper, чтобы избежать регистрочувствительности
  • разбирает исходные строки на отдельные символы функциями Text.ToList
  • ищет количество совпадений символов функциями List.Intersect и List.Count и помещает его в переменную matching_chars
  • вычисляет среднюю длину исходных текстовых строк с помощью функций Text.Length и помещает результат в переменную average_length
  • делит число совпадений на среднюю длину, чтобы получить коэффициент подобия

Само-собой, эта логика отличается от той, что использует Power Query при поиске соответствий (а как именно это делает Power Query - знают только разработчики в Microsoft). Однако, в подавляющем большинстве реальных случаев, наша функция со своей задачей отлично справляется - проверено на опыте.

После нажатия на Готово в правой панели окна Power Query можно переименовать нашу функцию, дав ей более наглядное имя (например, КоэфПодобия вместо Запрос1).

Теперь осталось применить её к нашим данным. Выберем на вкладке Добавление столбца команду Вызвать настраиваемую функцию (Add Column - Invoke Custom Function) и введём ее аргументы в открывшемся окне:

Вводим аргументы М-функции

После нажатия на ОК мы, наконец, получим желаемое - столбец, где будет виден числовой коэффициент подобия, наглядно отображающий качество подбора наших адресов:

Столбец с вычисленным коэф. подобия

Щёлкнув правой кнопкой мыши по заголовку получившегося столбца, можно выбрать команду Заменить ошибки (Replace Errors) и легко заменить получившиеся Error в Париже и Рио-де-Жанейро на нули. Ну, а затем отсортировать нашу таблицу по убыванию по столбцу коэффициентов и выгрузить обратно в Excel уже знакомой командой Главная - Закрыть и загрузить (Home - Close&Load):

Таблица с результатами нечеткого слияния в Excel

Точные совпадения в начале списка проблем не составят, а вот строки в конце списка, возможно, потребуют вашего внимания и "доработки напильником". В любом случае, такой вариант слияния мне кажется более надежным.

P.S. А у меня в Excel такого нет!

Для всех, кто после прочтения этой статьи немедленно рванёт в свой Excel проверять наличие нечёткого поиска в Power Query, ещё разок хочу уточнить:

  • У вас должен быть Office 365 по подписке, а не Office 2013, 2016, 2019 и т.д. К сожалению, политика Microsoft на данный момент такова, что только подписчики Office 365 получают все последние плюшки и нововведения типа неточного поиска, динамических массивов, новой функции ВПР (ПРОСМОТРX) и т.п.
  • У вас должны быть установлены все последние обновления. Имейте ввиду, что в некоторых компаниях IT-службы намеренно тормозят и откладывают установку обновлений Office, т.к. они могут нарушать функционирование других программ (связки Excel c ERP и т.п.)
  • Обновления рассылаются всем пользователям Office 365 волнами в течение нескольких недель. Неточный поиск появился у меня на одном компьютере после обновлений в конце прошлого года, а на другом - уже в начале этого. Если прямо сейчас у вас ещё нет этой функции - подождите и всё будет :)

Ссылки по теме




13.04.2020 16:31:53
При сравнении двух списков из 1000 строк каждый путём написания М-функции подобия. Excel 2010 "уходит" в бесконечный пересчёт.
10.05.2020 12:12:26
Стал пользовать в Power BI Desktop, использую только из за регистра.
Но это просто супер возможность!!!!!!!

Николай Спасибо!!!
14.12.2020 13:31:33
Если интересно попробовать этот инструмент, а Office 365 не установлен, можно установить Power BI, в нем есть нечеткий поиск.
30.06.2021 12:30:25
А вот, кстати, да. Спасибо за ценное уточнение, Дмитрий!
P.S. Еще бы Power BI умел потом результаты выгружать в исходном виде (не сводной!) в Excel - цены бы ему не было :)
24.12.2020 11:38:14
У меня нет Office 365 и, соответственно, нет возможности использовать вариант неточного совпадения.
Применив М- функцию КоэффПодобия, она определила только точные совпадения, по приближенным вывел ошибку "Error".
Не сработал, жаль
14.01.2021 17:04:51
Николай, добрый день!
Спасибо!
Как всегда на Высоте!

я правильно понимаю, что если у меня Office 365, но нет кнопки "Использовать нечеткие соответствия", то надо ждать обновление?
30.06.2021 12:29:32
Совершенно верно. Текущую версию можно посмотреть через Файл - Учетная запись
29.04.2021 07:31:01
Добрый день!
При нечетком сопоставлении двух столбцов (9000 и 60000 строк соответственно) запрос не выполняется. PQ уходит в бесконечный пересчет, нет ли каких-либо советов, что может быть не так? если сократить первый из диапазонов до 500 строк, то все нормально выгружается.  
Добрый день, будет ли работать в оффис 2019? спс
30.06.2021 12:28:19
Точно нет - нужен либо Office 365 (обновляемый ежемесячно по подписке), либо дожидаться выхода Office 2022 ¯\_(ツ)_/¯
Жаль) А похожее в 2019 есть что нибудь?
05.07.2021 17:06:08
Есть надстройка Fuzzy Lookup
10.01.2023 12:45:25
в 2019 вполне успешно использую:
Table.FuzzyNestedJoin(#"step1", {"Column1"}, source, {"Column1"}, "source", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5])
 
21.10.2021 08:57:13
Николай, уроки хороши, великий труд!
Подскажите пожалуйста, может кто-то из коллег сталкивался с таким вопросом, офис 2019 нет пункта "галочки" использовать нечеткие соответствия ?
29.06.2022 16:04:25
Тут есть интересный баг или фича, например, числа: 558 и 585 имеют 100% совпадение, т.к. перестановка символов не учитывается.
Наверх