Power query excel нечеткий поиск

Обновлено: 07.07.2024

У меня есть база данных с большим количеством столбцов, один из которых представляет собой строку с произвольным текстом. На другом листе Excel в книге я настроил таблицу соответствия, чтобы классифицировать строки на основе списков ключевых слов, например:

категория | ключевые слова
домашние животные | собака, кошка, кролик, .
автомобили | Порше, БМВ, Додж, .
.

Цель состоит в том, чтобы поместить в мою базу данных пользовательский столбец, который будет возвращать вышеупомянутую категорию (или категории?), Основываясь на том, какие ключевые слова в списке он может найти в поле описания.

Я думаю, что решение выше и решение от ImkeF не так далеко, но я не нашел способа превратить его в успешный запрос для моего случая. (Я хорошо разбираюсь в Excel, но совершенно незнаком с M и программирую запросы . )

3 ответа

Ориентируемся на размещенные выше ссылки:

M-код для tbl_category: ключевые слова (разделенные запятой) будут разбиты на строки

М-код для tbl_text. Здесь будет добавлен пользовательский столбец под названием «Категория»:

Я наконец-то нашел, как построить запрос, соответствующий моим потребностям, основываясь на ваших шагах выше!

Примечание: я использовал «Метки строк» для замены заголовка столбца 1-го столбца tbl_category для ясности.

Мое решение не такое аккуратное, как хотелось бы (мне пришлось создать второй пользовательский столбец из-за недостатка знаний о том, как вкладывать два шага, чтобы они действовали в одной и той же ячейке), но оно работает отлично!

Так что еще раз спасибо за вашу помощь, Крис . без вашего ведома я не нашел бы этот выход из лабиринта!

Здесь 2-й код изменен:

Только для записи,

После применения к реальным данным запрос больше не работал . выдав ошибку "Мы не можем преобразовать значение null в тип Text". решение было так же просто, как сначала удалить «пустые» ячейки (пустые ячейки, которые были категориями, для которых ключевые слова еще не были определены)!

Когда вы присоединяетсяе столбцы таблицы, вам больше не требуется точное совпадение. Нечеткое соответствие позволяет сравнивать элементы в отдельных списках и присоединяться к ним, если они близко друг к другу. Вы даже можете настроить соответствие допуску или пороговое значение сходства.

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

Нечеткое соответствие поддерживается только при операциях слияния с текстовыми столбцами. Power Query использует алгоритм сходства Jaccard для измерения сходства между парами экземпляров.

Последовательность действий

Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в статьи Создание, изменение и загрузка запроса в Excel (Power Query).

Выберите Главная> объединить > слияние запросов. Вы также можете выбрать слияние запросов в качестве нового. Появится диалоговое окно Слияние с главной таблицей в верхней части.

Выберите столбец, который вы хотите использовать для нечеткого совпадения. В этом примере выбрано имя.

В списке выберите вторичную таблицу, а затем — соответствующий столбец нечеткого совпадения. В этом примере выбрано имя.

Выберите тип join kind. Существует несколько способов присоединиться. По умолчанию используется левое внешнее. Дополнительные сведения о каждом типе объединения см. в этой статьи.

Выберите Использовать нечеткое соответствиедля выполнения слияния , выберите Нечеткие параметры, а затем выберите один из следующих вариантов:

Пороговое значение сходства Указывает на необходимость совпадения двух похожих значений. Минимальное значение 0,00 вызывает соответствие всех значений друг другу. Максимальное значение 1,00 допускает только точные совпадения. Значение по умолчанию — 0,80.

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

Максимальное количество совпадений Управляет максимальным количеством совпадающих строк, которые будут возвращены для каждой входной строки. Например, если вы хотите найти только одну строку для каждой входной строки, укажите значение 1. По умолчанию возвращаются все совпадения.

Таблица преобразования Укажите другой запрос, в который входит таблица сопоставления, чтобы некоторые значения могли быть автоматически сопоставлены в логике сопоставления. Например, при определении таблицы из двух столбцов с текстовыми столбцами "От" и "По" со значениями "Майкрософт" и "MSFT" эти значения будут считаться одинаковыми (оценка сходства — 1,00).

Если вы удовлетворены, выберите ОК. Если нет, попробуйте другие параметры нечеткого слияния, чтобы настроить свой опыт.

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

В главе 18 вы узнали, как перенести в Power Query возможности функций Excel ЕСЛИ() и ЕСЛИОШИБКА(). Перенос других функций из библиотеки условной логики Excel сложнее. Он основан на использовании списков и пользовательских функций.

Рис. 22.1. Исходные данные

Иногда вам будет нужно отфильтровать данные по нескольким критериям. Откройте файл Multi-Column Logic.xlsx (см. рис. 22.1).

Репликация функции Excel ИЛИ()

Допустим вам нужно отфильтровать строки для которых Inventory Item = Talkative Parrot ИЛИ Sold By = Fred. Если это данные Таблицы Excel, можно использовать расширенный фильтр или добавить столбец фильтрации:

Рис. 22.2. Столбец фильтрации на основе логического ИЛИ

Посмотрим, что можно сделать в Power Query. Удалите столбец Filter. Выберите любую ячейку в Таблице. Пройдите по меню Данные –> Из таблицы/диапазона. В редакторе Power Query щелкните правой кнопкой мыши столбец Date –> Тип изменения –> Дата. Поскольку вы не можете фильтровать данные без потери части записей, вам нужно добавить пользовательский столбец и применить формулу для проверки каждой строки.

Добавление столбца –> Настраиваемый столбец. Назовите столбец Match (поиск, совпадение). Вспомните, в главе 18 был описан синтаксис условной логики Power Query:

=if <logical test> then <result> else <alternate result>

К сожалению, в Power Query нет функции ИЛИ(). Напомню, чтобы обратиться к списку функций, кликните на ссылку Сведения о формулах Power Query в нижней части диалогового окна Настраиваемый столбец. Вы окажитесь на странице сайта Microsoft с обзором всех функций Power Query. В категории List functions можно найти функцию List.AnyTrue, которая возвращает ИСТИНА, если хоть одно выражение списка истинно. В документации по функции приведено два примера:

Определяет, является ли хотя бы одно из выражений в списке истинным…

… и возвращает true.

Определяет, является ли хотя бы одно из выражений в списке истинным…

… и возвращает false.

Попробуем использовать эту функцию в качестве теста в пользовательском столбце:

Не забудьте разделить критерии запятыми, а список критериев окружить фигурными скобками, потому что функция List.AnyTrue() в качестве параметра требует список. Нажав Ok, вы увидите, что тест возвращает корректные результаты:

Рис. 22.3. Аналог функции Excel ИЛИ() в Power Query – List.AnyTrue(); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Отфильтруйте столбец Match. Измените имя запроса – pqOR. Главная –> Закрыть и загрузить. Power Query возвращает таблицу, содержащую в общей сложности 88 строк из исходных 332. Если столбец Match вам не нужен, его можно удалить. Поскольку Power Query обрабатывает команды последовательно, удаление столбца Match не приведет к снятию фильтра:

Рис. 22.4. Отобранные по критерии ИЛИ записи в Таблице на листе Excel

Репликация функции Excel И()

Для этих целей в Power Query есть функция List.AllTrue(). Эта функция возвращает истинное значение только в том случае, если каждый логический тест возвращает истинное значение. В Excel щелкните правой кнопкой мыши запрос pqOR –> Дублировать. Переименуйте запрос – pqAND. Если вы удалили столбец Match, удалите этот шаг, чтобы вернуть столбец Match в запрос. Щелкните значок шестеренки рядом с шагом Добавлен пользовательский столбец (для редактирования формулы). Заменить List.AnyTrue на List.AllTrue. Выберите шаг Строки с примененным фильтром. Осталось лишь шесть строк:

Рис. 22.5. Использование функции List.AllTrue() для отбора записей

Хотя в этих примерах мы явно отфильтровали данные на основе столбца Match, самое замечательное в функциях List.AnyTrue и List.AllTrue заключается в том, что вы можете помечать записи без фильтрации. Это добавит вам гибкости при построении более сложной логики, с возможностью сохранения всех исходных данных (чего нельзя получить просто фильтруя столбцы).

Репликация функции Excel ВПР()

Репликация зависит от того, какая версия ВПР/VLOOKUP вам нужна. При поиске точного совпадения репликация может быть получена простым объединением двух таблиц (см. главу 9). Репликация приблизительного соответствия ВПР() требует довольно сложной логики (подробнее о функции ВПР в Excel см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). В примере вы не будете создавать сценарий Power Query с нуля но увидите как он работает. Откройте файл Emulating VLOOKUP.xlsx. В нем есть две таблицы:

Рис. 22.6. Таблица подстановки

Рис. 22.7. Таблица данных

Теперь всё готово, чтобы посмотреть, как это работает. Удалите из таблицы данных (DataTable) все формулы Excel (ячейки В3:D10). Выберите любую ячейку в таблице DataTable –> Данные –> Из таблицы/диапазона. Щелкните правой кнопкой мыши столбец Values –> Удалить другие столбцы:

Рис. 22.8. Запрос готов к использованию функции pqVLOOKUP

Чтобы проверить, работает ли функция PQ VLOOKUP для вас, вы можете попробовать повторить следующую формулу: =VLOOKUP ([Values], BandingLevels, 2, true)

Для этого можно выполнить следующие действия:

Добавление столбца –> Настраиваемый столбец. Назовите столбец 2,True. Используйте формулу:

Рис. 22.9. Репликация VLOOKUP() с четвертым параметром равным true

Снова перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите столбец 3,default. Используйте формулу:

Рис. 22.10. Репликация VLOOKUP() с опущенным четвертым параметром (по умолчанию = true, приблизительное совпадение)

Теперь определите точное совпадение со вторым столбцом таблицы подстановки. Добавление столбца –> Настраиваемый столбец. Назовите его 2,false. Используйте формулу:

Рис. 22.11 Репликация VLOOKUP() с точным совпадением

Несмотря на то, что вы можете использовать эту функцию для эмуляции точного соответствия VLOOKUP(), лучше этого не делать, а воспользоваться объединением таблиц. Завершите запрос. Главная –> Закрыть и загрузить.

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

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

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

Арифметические операции
Возводить в степени, вычислять логарифмы, факториалы, корни
Тригонометрические операции
Округлять до заданных значений
Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

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

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Читайте также: