Пример столбца — искусственный интеллект в Power Query

Одно из самых просматриваемых видео на моем канале YouTube — это видео о Flash Fill в Microsoft Excel. Суть этого инструмента в том, что если вам нужно как-то преобразовать исходные данные, то вам просто нужно начать вводить в соседний столбец тот результат, который вы хотите получить. После нескольких введенных вручную ячеек (обычно достаточно 2-3) Excel «поймет» логику необходимых вам преобразований и автоматически продолжит набранное вами, выполняя за вас всю монотонную работу:

Квинтэссенция эффективности. Волшебная кнопка «сделай это правильно», которую мы все так любим, верно?

На самом деле аналог такого инструмента есть в Power Query — там он называется Столбец из примеров (Столбец из примеров). По сути, это небольшой искусственный интеллект, встроенный в Power Query, который может быстро учиться на ваших данных, а затем преобразовывать их. Давайте подробнее рассмотрим его возможности в нескольких практических сценариях, чтобы понять, где он может нам пригодиться в реальных задачах.

Пример 1. Наклеивание/вырезание текста

Допустим, у нас есть такая «умная» таблица в Excel с данными о сотрудниках:

Загрузите его в Power Query стандартным способом – кнопкой Из таблицы/диапазона таб Данные (Данные — из таблицы/диапазона).

Предположим, нам нужно добавить столбец с фамилиями и инициалами для каждого сотрудника (Иванов С.В. для первого сотрудника и т.д.). Для решения этой проблемы можно использовать один из двух способов:

• щелкните правой кнопкой мыши по заголовку столбца с исходными данными и выберите команду Добавить столбец из примеров (Добавить столбец из примеров);

• выберите один или несколько столбцов с данными и на вкладке Добавление столбца выбрать команду Столбец из примеров. Здесь в раскрывающемся списке можно указать, нужно ли анализировать все или только выбранные столбцы.

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

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

Обратите внимание на формулу вверху окна — это то, что создает умный Power Query, чтобы получить нужные нам результаты. В этом, кстати, и состоит принципиальное отличие этого инструмента от Мгновенное заполнение в Экселе. Мгновенное заполнение работает как «черный ящик» — оно не показывает нам логику преобразований, а просто дает готовые результаты, и мы принимаем их как должное. Здесь все прозрачно и всегда можно абсолютно четко понять, что именно происходит с данными.

Если вы видите, что Power Query «уловил идею», то можете смело нажимать кнопку OK или сочетание клавиш Ctrl+Enter – будет создан пользовательский столбец с формулой, изобретенной Power Query. Кстати, его потом можно будет легко редактировать как обычный столбец, созданный вручную (командой Добавление столбца – Пользовательский столбец), щелкнув значок шестеренки справа от названия шага:

Пример 2: Падеж как в предложениях

Если щелкнуть правой кнопкой мыши по заголовку столбца с текстом и выбрать команду трансформация (Преобразование), то вы увидите три команды, отвечающие за изменение регистра:

Удобно и круто, но в этом списке, например, мне лично всегда не хватало еще одного варианта — падежа как в предложениях, когда заглавной буквой (прописной) становится не первая буква в каждом слове, а только первая буква в ячейке, и остальная часть текста при Это отображается строчными (маленькими) буквами.

Эту недостающую функцию легко реализовать с помощью искусственного интеллекта. Столбцы из примеров — просто введите пару вариантов Power Query, чтобы продолжить в том же духе:

В качестве формулы здесь Power Query использует набор функций. Текст.Верхний и Текст.Нижний, преобразуя текст в верхний и нижний регистр соответственно, и функции Текст.Старт и Текст.Средний – аналоги функций Excel LEFT и PSTR, умеющие извлекать из текста подстроку слева и посередине.

Пример 3. Перестановка слов

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

Пример 4: Только цифры

Еще одна очень важная задача — вытащить из содержимого ячейки только числа (числа). Как и раньше, после загрузки данных в Power Query переходим на вкладку Добавление столбца – Столбец из примеров и заполняем пару ячеек вручную, чтобы программа поняла, что именно мы хотим получить:

Бинго!

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

Пример 5: только текст

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

В этом случае используется уже противоположная по смыслу функция — Text.Remove, которая удаляет символы из исходной строки по заданному списку.

Пример 6: Извлечение данных из буквенно-цифровой каши

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

Обратите внимание, что формула преобразования, созданная Power Query, может быть довольно сложной:

Для удобства чтения и понимания его можно преобразовать в гораздо более вменяемый вид с помощью бесплатного онлайн-сервиса. Форматер Power Query:

Очень удобная штука – респект создателям!

Пример 7: Преобразование дат

Инструмент Столбец из примеров также может применяться к столбцам даты или даты и времени. Когда вы вводите первые цифры даты, Power Query отображает список всех возможных вариантов преобразования:

Таким образом, вы можете легко преобразовать исходную дату в любой экзотический формат, например «год-месяц-день»:

Пример 8: Категоризация

Если мы воспользуемся инструментом Столбец из примеров к столбцу с числовыми данными это работает по-другому. Предположим, у нас есть результаты тестов сотрудников, загруженные в Power Query (условные оценки в диапазоне 0–100), и мы используем следующую условную градацию:

• Мастера – те, кто набрал более 90 баллов.
• Эксперты – от 70 до 90 баллов.
• Пользователи – от 30 до 70
• Новички – те, кто набрал меньше 30.

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

Опять же, можно не дожимать ситуацию до конца, а нажать на OK а потом подкорректировать пороговые значения уже в формуле – так быстрее:

Выводы

Конечно, инструмент Столбец из примеров не является «волшебной таблеткой» и рано или поздно возникнут нестандартные ситуации или особо запущенные случаи «колхоза» в данных, когда Power Query выйдет из строя и не сможет вычислить то, что мы хотим правильно для нас. Однако как вспомогательный инструмент он очень хорош. К тому же, изучая сгенерированные им формулы, вы сможете расширить свои знания о функциях языка М, что всегда пригодится в будущем.

• Анализ текста с помощью регулярных выражений (RegExp) в Power Query
• Нечеткий текстовый поиск в Power Query
• Быстрая заливка в Microsoft Excel