Table.Join, JoinKind, нечёткое слияние: объединение таблиц в Power Query

Table.Join, JoinKind, нечёткое слияние: объединение таблиц в Power Query

Обычно все объединения таблиц мы выполняем с помощью кнопок. По умолчанию Power Query использует функцию Table.NestedJoin. Но есть и другая функция - Table.Join. Она обладает несколькими преимуществами.

Пример: в первой таблице - участники акций, во второй – клиенты и суммы оплат. Нужно найти клиентов, которые совершили сделки, но не участвовали в акциях.

Исключим из второй таблицы всех клиентов первой  таблицы. Для создания общей таблицы используем "Анти-соединение справа":

Код получившегося шага выглядит так:

  1. Первый аргумент – имя первой таблицы для слияния. 
    В нашем примере это #"Таблица акций".
  2. Второй аргумент – список столбцов для объединения из первой таблицы. 
    В примере выше это {"Клиент"}.
  3. Третий аргумент - имя второй таблицы для слияния. 
    В нашем примере это #"Таблица оплат".
  4. Четвертый аргумент -  список столбцов для объединения из второй таблицы. 
    В последнем примере это также {"Клиент"}.
  5. Пятый аргумент – название нового столбца, который будет содержать таблицы. 
    В данном случае он называется "Таблица оплат".
  6. Шестой аргумент – тип соединения. 
    В нашем последнем случае мы использовали JoinKind.RightAnti.

Table.Join вместо Table.NestedJoin

Table.Join шустрее обрабатывает большие массивы данных и не требует разворачивать вложенные таблицы. 

При этом для Table.Join нужны разные названия столбцов в обоих таблицах  - это важно. Повторения в названиях столбцов вызовут ошибку при объединении таблиц.

Вернемся к последним таблицам и переименуем столбец «клиенты» во второй таблице, чтобы названия столбцов не совпадали: 

Создадим пустой запрос и напишем функцию Table.Join:

  1. Первый аргумент – имя первой таблицы для слияния. 
    В нашем примере это #"Таблица акций".
  2. Второй аргумент – список столбцов для объединения из первой таблицы. 
    В примере выше это {"Клиент"}.
  3. Третий аргумент - имя второй таблицы для слияния. 
    В нашем примере это #"Таблица оплат".
  4. Четвертый аргумент -  список столбцов для объединения из второй таблицы. 
    В последнем примере это {"Клиенты"}.
  5. Пятый аргумент – тип соединения - JoinKind.RightAnti.

Из плюсов этой функции - вам не нужно добавлять шаг разворота таблицы. При Table.Join все данные сразу разворачиваются и никаких столбцов с вложенными таблицами не создаётся. Поэтому аргумент с названием нового столбца просто пропущен. 

Если вы хотите использовать Table.Join, можно с помощью кнопок реализовать стандартную функцию, а затем удалить слово "Nested" и 5-й аргумент (название столбца, содержащего таблицу). Это не требует написания кода вручную - только небольшие изменения. 

При написании кода вручную можно сократить запись функции - вместо типа соединения (в нашем случае JoinKind.RightAnti) можно указывать число от 0 до 5. Каждому типу соответствует своё число: 
1 - Left Outer; 
2 - Right Outer; 
3 - Full Outer; 
4 - Left Anti; 
5 - Right Anti; 
0 - Inner Join.

Нечеткое соответствие в Power Query

Бывают случаи, когда значения ключевых столбцов совпадают не полностью. 
При слиянии подобных таблиц вам нужно использовать нечеткое соответствие.

Пример: первая таблица – товары с остатками у заказчика, вторая таблица – товары с остатками у поставщика. Наименования товаров у заказчика и поставщика не всегда совпадают. Для нового заказа нужно составить единую таблицу только по товарами, которые есть у поставщика.

Для решения этой задачи выберем тип слияния "Полное внешнее", чтобы видеть все совпадения и отличия:

Ставим галочку «Использовать нечеткие соответствия для слияния». Именно этот параметр заставляет Power Query искать не только точные совпадения, но и приблизительные. 

Далее раскрываем треугольник напротив «Параметры нечеткого соответствия»:

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

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

Игнорировать регистр - при включении этого флажка, Power Query перестанет различать прописной и строчный текст.

Сопоставление путем объединения текстовых фрагментов - при поиске соответствий будет производиться проверка на переставление слов внутри текста.

Максимальное число совпадений  - ограничение количества найденных вариантов. Если одному адресу в первой таблице соответствует несколько похожих адресов во второй, то можно ограничить количество найденных вариантов. 

Таблица преобразования  - позволяет учесть разные варианты написания текста (задавать необязательно). 

Выполнив все настройки, нажмём на ОК и развернём вторую таблицу с помощью двойной кнопки:

Сделав набольшие преобразования, получаем удобную таблицу:

Заключение

Теперь вы знаете, что помимо Table.NestedJoin существует функция Table.Join, которая работает быстрее и не требует разворачивать таблички после объединения. Давайте столбцам в объединяемых таблицах разные названия если используете Table.Join. 

Не нужно вручную прописывать Table.Join - проще создать объединение кнопками, удалить из функции слово "Nested" и убрать (5-й) аргумент с названием столбца.

Для объединения таблиц, где значения совпадают не полностью, используйте нечеткое соответствие. У него есть ряд настроек - с их помощью можно добиться требуемого качества объединения.

И не забывайте, что вместо JoinKind можно указывать цифры. Это удобнее, чем прописывать тип соответствия вручную. Наиболее частые варианты: 1 - Left Outer, 3 - Full Outer, 0 - Inner Join.

Комментарии