Обычно все объединения таблиц мы выполняем с помощью кнопок. По умолчанию Power Query использует функцию Table.NestedJoin. Но есть и другая функция - Table.Join. Она обладает несколькими преимуществами.
Пример: в первой таблице - участники акций, во второй – клиенты и суммы оплат. Нужно найти клиентов, которые совершили сделки, но не участвовали в акциях.
Исключим из второй таблицы всех клиентов первой таблицы. Для создания общей таблицы используем "Анти-соединение справа":
Код получившегося шага выглядит так:
- Первый аргумент – имя первой таблицы для слияния.
В нашем примере это #"Таблица акций". - Второй аргумент – список столбцов для объединения из первой таблицы.
В примере выше это {"Клиент"}. - Третий аргумент - имя второй таблицы для слияния.
В нашем примере это #"Таблица оплат". - Четвертый аргумент - список столбцов для объединения из второй таблицы.
В последнем примере это также {"Клиент"}. - Пятый аргумент – название нового столбца, который будет содержать таблицы.
В данном случае он называется "Таблица оплат". - Шестой аргумент – тип соединения.
В нашем последнем случае мы использовали JoinKind.RightAnti.
Table.Join вместо Table.NestedJoin
Table.Join шустрее обрабатывает большие массивы данных и не требует разворачивать вложенные таблицы.
При этом для Table.Join нужны разные названия столбцов в обоих таблицах - это важно. Повторения в названиях столбцов вызовут ошибку при объединении таблиц.
Вернемся к последним таблицам и переименуем столбец «клиенты» во второй таблице, чтобы названия столбцов не совпадали:
Создадим пустой запрос и напишем функцию Table.Join:
- Первый аргумент – имя первой таблицы для слияния.
В нашем примере это #"Таблица акций". - Второй аргумент – список столбцов для объединения из первой таблицы.
В примере выше это {"Клиент"}. - Третий аргумент - имя второй таблицы для слияния.
В нашем примере это #"Таблица оплат". - Четвертый аргумент - список столбцов для объединения из второй таблицы.
В последнем примере это {"Клиенты"}. - Пятый аргумент – тип соединения - JoinKind.RightAnti.
Из плюсов этой функции - вам не нужно добавлять шаг разворота таблицы. При Table.Join все данные сразу разворачиваются и никаких столбцов с вложенными таблицами не создаётся. Поэтому аргумент с названием нового столбца просто пропущен.
Если вы хотите использовать Table.Join, можно с помощью кнопок реализовать стандартную функцию, а затем удалить слово "Nested" и 5-й аргумент (название столбца, содержащего таблицу). Это не требует написания кода вручную - только небольшие изменения.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
При написании кода вручную можно сократить запись функции - вместо типа соединения (в нашем случае 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.
Комментарии