Столбец из примеров - искуственный интеллект в Power Query
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill) в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас:
Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?
На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искуственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать. Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.
Пример 1. Склейка/нарезка текста
Допустим, мы имеем вот такую "умную" таблицу в Excel с данными по сотрудникам:
Загрузим её в Power Query стандартным образом - кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range).
Предположим, что нам требуется добавить столбец с фамилиями и инициалами для каждого сотрудника (Иванов С.В. для первого сотрудника и т.д.). Чтобы решить эту задачу, можно воспользоваться одним из двух способов:
- щёлкнуть правой кнопкой мыши по заголовку столбца с исходными данными и выбрать команду Добавить столбец из примеров (Add column from examples);
- выделить один или несколько столбцов с данными и на вкладке Добавление столбца выбрать команду Столбец из примеров. Здесь же в выпадающем списке можно уточнить - всё или же только выбранные столбцы нужно анализировать.
Затем всё просто - в появившийся справа столбец мы начинаем вводить примеры желаемых результатов, а встроенный в Power Query искусственный интеллект пытается понять нашу логику преобразований и продолжить дальше самостоятельно:
Вводить правильные варианты можно, кстати, в любые ячейки этого столбца, т.е. не обязательно сверху-вниз и подряд. Также впоследствии можно легко добавлять или убирать из анализа столбцы, используя галочки в строке заголовка.
Обратите внимание на формулу в верхней части окна - именно её создаёт умный Power Query, чтобы получить нужные нам результаты. В этом, кстати, принципиальное отличие этого инструмента от Мгновенного заполнения в Excel. Мгновенное заполнение работает как "черный ящик" - нам не показывают логику преобразований, а просто выдают готовые результаты и мы принимаем их на веру. Здесь же всё прозрачно и всегда можно абсолютно чётко понять, что именно происходит с данными.
Если вы видите, что Power Query "ухватил идею", то можно смело жать на кнопку ОК или сочетание клавиш Ctrl+Enter - будет создан пользовательский столбец с придуманной Power Query формулой. Его, кстати, можно впоследствии спокойно редактировать как обычный созданный вручную столбец (командой Добавление столбца - Пользовательский столбец), щёлкнув по значку шестерёнки справа от названия шага:
Пример 2. Регистр как в предложениях
Если щёлкнуть правой кнопкой мыши по заголовку столбца с текстом и выбрать команду Преобразование (Transform), то можно увидеть три команды, отвечающие за изменение регистра:
Удобно и здорово, но в этом списке, например, лично мне всегда не хватало ещё одной опции - регистр как в предложениях, когда прописной (заглавной) становится не первая буква в каждом слове, а только первая буква в ячейке, а весь остальной текст при этом выводится строчными (маленькими) буквами.
Эту недостающую функцию легко реализовать с помощью искусственного интеллекта Столбца из примеров - достаточно ввести пару-тройку вариантов, чтобы Power Query продолжил в том же духе:
В качестве формулы здесь Power Query использует связку функций Text.Upper и Text.Lower, преобразующих текст в верхний и нижний регистр соответственно и функций Text.Start и Text.Mid - аналогов экселевских функций ЛЕВСИМВ и ПСТР, умеющих извлекать из текста подстроку слева и из середины.
Пример 3. Перестановка слов
Иногда при обработке полученных данных возникает необходимость переставить слова в ячейках в заданной последовательности. Конечно, можно разделить столбец на отдельные колонки-слова по разделителю и склеить потом обратно в заданном порядке (не забыв добавить пробелы), но с помощью инструмента Столбец из примеров всё будет гораздо проще:
Пример 4. Только числа
Ещё одна весьма жизненная задача - вытащить только цифры (числа) из содержимого ячейки. Как и ранее, после загрузки данных в Power Query идём на вкладку Добавление столбца - Столбец из примеров и заполняем пару ячеек вручную, чтобы программа поняла, что именно мы хотим получить:
Бинго!
Опять же, стоит посмотреть в верхнюю часть окна, чтобы убедиться, что Query правильно сгенерировал формулу - в этом случае она содержит функцию Text.Select, которая, как легко догадаться, извлекает из исходного текста заданные символы по списку. Впоследствии этот список, конечно же, можно будет легко отредактировать в строке формул при необходимости.
Пример 5. Только текст
Аналогично предыдущему примеру можно вытаскивать и наоборот - только текст, удаляя все цифры, знаки препинания и т.д.
В этом случае используется уже противоположная по смыслу функция - Text.Remove, удаляющая из исходной строки символы по заданному списку.
Пример 6. Извлечение данных из буквенно-цифровой "каши"
Power Query может помочь и в более тяжелых случаях, когда вам нужно извлечь полезную информацию из буквенно-цифровой каши в ячейке, например, получить номер счета из описания назначения платежа в банковской выписке:
Обратите внимание, что сгенерированная Power Query формула преобразования может получиться весьма сложной:
Для удобства чтения и понимания её можно конвертировать в гораздо более вменяемый вид с помощью бесплатного онлайн-сервиса Power Query Formatter:
Очень удобная штука - респект создателям!
Пример 7. Преобразование дат
Инструмент Столбец из примеров можно применять и к колонкам с датой или датой-временем. При вводе первых цифр даты Power Query услужливо выведет список всех возможных вариантов преобразования:
Так что можно легко преобразовать исходную дату в любой экзотический формат, например "год-месяц-день":
Пример 8. Распределение по категориям
Если мы применяем инструмент Столбец из примеров к столбцу с числовыми данными, то он работает иначе. Предположим, что у нас есть загруженные в Power Query результаты тестирования сотрудников (условные баллы в интервале 0-100) и мы используем следующую условную градацию:
- Мастера - набравшие больше 90
- Эксперты - набравшие от 70 до 90
- Пользователи - от 30 до 70
- Новички - набравшие менее 30
Если добавить к списку столбец из примеров и начать расставлять эти градации вручную, то очень скоро Power Query подхватит нашу идею и добавит столбец с формулой, где вложенными друг в друга операторами if будет реализована логика, очень похожая на то, что нам нужно:
Опять же, можно не дожимать ситуацию до конца, а нажать на ОК и подправить потом пороговые значения уже в формуле - так быстрее:
Выводы
Безусловно, инструмент Столбец из примеров не является «волшебной таблеткой» и, рано или поздно, найдутся нестандартные ситуации или особо запущенные случаи "колхоза" в данных, когда Power Query спасует и не сможет правильно отработать для нас желаемое. Однако, как вспомогательный инструмент - он весьма хорош. Плюс ко всему, изучая сгенерированные им формулы, можно расширять свои познания в функциях языка М, что всегда пригодится вам в будущем.
Ссылки по теме
- Анализ текста регулярными выражениями (RegExp) в Power Query
- Нечёткий текстовый поиск в Power Query
- Мгновенное заполнение в Microsoft Excel