Столбец из примеров - искуственный интеллект в 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