Главная / Полезное / Расширенный редактор Power Query: полный разбор возможностей

Расширенный редактор Power Query: полный разбор возможностей

Что такое расширенный редактор Power Query и зачем он нужен

Power Query в Excel: для чего это нужно и как работает. Инструкция со скриншотам - изображение номер один
Power Query в Excel: для чего это нужно и как работает. Инструкция со скриншотам — изображение номер один

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

Зачем он нужен на практике:

  • Тонкая настройка шагов загрузки, недоступная через стандартный интерфейс.
  • Копирование логики между отчётами — код можно перенести в другой файл.
  • Отладка: видно, на каком именно преобразовании возникает ошибка.

Осваивать его стоит, когда типовых кнопок перестаёт хватать, а возиться с повторами рутинных действий надоедает.

Отличие расширенного редактора от стандартного интерфейса Power Query

Базовый оконный режим показывает лишь визуальные шаги, тогда как расширенный вариант открывает доступ к программному коду на языке M. В первом случае действия выполняются кнопками и меню, во втором — правкой текстовых формул. Оконный интерфейс удобен для новичков, но ограничен типовыми операциями. Кодовая среда даёт полный контроль: можно копировать фрагменты, менять логику, добавлять условия вручную. При этом изменения, внесённые в коде, мгновенно отражаются в окне предпросмотра — это позволяет сверять результат.

Когда без расширенного редактора не обойтись: типовые сценарии

Инструмент открывается по кнопке в ленте «Главная» или через контекстное меню шага. К нему обращаются, когда стандартные манипуляции мышью упираются в потолок. Например, нужно прописать условие с несколькими вложенными проверками или создать колонку на основе сложной логики. Также без него не обойтись при ручной правке кода после вставки пользовательской функции. Часто он выручает, когда требуется переименовать сразу много шагов или изменить порядок операций, не пересоздавая запрос с нуля.

Как открыть расширенный редактор Power Query

Добраться до этого инструмента несложно, но путь зависит от того, откуда вы стартуете. В Excel 2016 и новее достаточно перейти на вкладку «Данные» и кликнуть по кнопке «Получить данные». После того как вы подключитесь к источнику и попадёте в окно запроса, на верхней панели появится пункт «Расширенный редактор».

В Power BI Desktop алгоритм чуть иной: нужно зайти в «Главная», выбрать «Преобразовать данные», а затем в открывшемся окне найти иконку с одноимённым названием. Оба варианта ведут к одному и тому же окну редактирования кода на языке M.

Пошаговый запуск редактора из Excel и Power BI

Чтобы открыть инструмент в Excel, перейдите на вкладку «Данные» и нажмите кнопку «Получить данные» — в выпадающем меню выберите «Запустить редактор Power Query». В Power BI Desktop путь чуть короче: на вкладке «Главная» кликните по стрелке под кнопкой «Получить данные» и выберите пункт «Запустить редактор запросов». Либо используйте горячие клавиши: в Excel — Alt + PQ, в Power BI — Alt + F12.

После запуска откроется отдельное окно с лентой меню и областью запросов слева. Если вы работаете с несколькими источниками, все они появятся в списке. Для повторного входа в окно редактирования достаточно дважды щелкнуть по названию запроса в панели «Запросы».

Настройка внешнего вида и параметров окна редактора

Интерфейс окна редактирования запросов легко адаптировать под собственные предпочтения. Панель формул включается или скрывается через вкладку «Вид». Там же доступно переключение областей: «Схема запроса» и «Параметры запроса» — их удобно размещать справа или слева, закрепив в нужном положении. Масштаб отображения данных регулируется ползунком в правом нижнем углу. Для комфортной работы с длинными списками столбцов предусмотрена функция закрепления областей — аналог аналогичной опции в Excel. Дополнительно настраивается поведение при обновлении: можно отключить предупреждения о потере несохранённых изменений или включить автоматическую загрузку данных при открытии файла.

Синтаксис языка M в расширенном редакторе

Тренинг \ - изображение номер два
Тренинг \ — изображение номер два

Язык формул Power Query (M) — это функциональный язык, где каждая строка — это выражение, возвращающее значение. В расширенном редакторе вы видите весь запрос целиком, а не отдельные шаги, как в обычном режиме. Синтаксис строится на переменных, функциях и операторах. Например, запись let начинает последовательность шагов, а in указывает, что именно вернуть. Каждый шаг — это имя и выражение через знак равенства. Важно помнить: регистр букв имеет значение, а комментарии начинаются с двойного слэша //. Для новичка код выглядит непривычно, но логика проста — данные преобразуются от шага к шагу, как конвейер.

Базовые конструкции: let, in, переменные и шаги запроса

Любое выражение в редакторе строится по единому каркасу: сначала объявление let, затем последовательность шагов, а завершает всё строка in. Каждый шаг — это именованная переменная, ссылающаяся на предыдущие. Например, сначала фильтруем источник, потом меняем тип столбца, затем добавляем вычисляемое поле. Промежуточные имена видны на панели справа, что упрощает отладку. Важно помнить: порядок строк влияет на результат, а имя шага не должно совпадать с зарезервированными словами.

Типы данных, операторы и обработка ошибок в коде M

В языке M, лежащем в основе Power Query, строгая типизация. Базовые типы: number, text, logical, date, time, datetime, duration, а также сложные — list, record, table. Приведение типов выполняется явно через функции Number.From() или Text.From(), иначе возможны сбои.

Операторы делятся на арифметические (+, -, *, /), сравнения (=, <>, >) и конкатенации (&). Логические условия строятся на and, or, not.

Обработка ошибок строится на конструкциях try ... otherwise и try ... catch. Например:

let
    Source = try Number.From("abc") otherwise 0
in
    Source

Такой подход возвращает 0 вместо ошибки. Для диагностики используйте Error.Record() — он формирует структурированное описание проблемы. Полезно помнить: функция Value.ReplaceType() меняет тип данных на лету, но требует аккуратности — несоответствие формата приводит к исключению.

Таблица соответствия типов:

Тип Пример Особенность
number 42.5 Поддержка NaN и Infinity
text «строка» Неизменяемый, сравнение посимвольно
logical true / false Неявное приведение к числу запрещено

Практические примеры кода для расширенного редактора

Разберём пару типовых сценариев. Допустим, нужно извлечь число из строки вида «Артикул: 4587-Б». В M-коде это решается через Text.Select с условием List.Contains({"0".."9"}).

let
    Source = "Артикул: 4587-Б",
    Digits = Text.Select(Source, {"0".."9"})
in
    Digits

Другой частый случай — объединение таблиц по нескольким колонкам. Здесь удобнее не писать вложенные Table.Join, а использовать Table.NestedJoin, а затем развернуть вложенную таблицу через Table.ExpandTableColumn. Это сокращает количество шагов и упрощает чтение запроса.

Для переименования всех столбцов разом подойдёт Table.TransformColumnNames с функцией замены, например, Text.Replace для удаления пробелов. Такой приём экономит время при работе с выгрузками из внешних систем.

Ручное редактирование шагов: добавление, удаление и изменение порядка

В окне «Настройки запроса» каждое преобразование фиксируется как отдельный пункт. Их можно править вручную, не пересобирая весь конвейер. Чтобы вставить новое действие, кликните по значку шестерёнки рядом с нужной строкой либо выберите пункт «Вставить шаг после». Для удаления ненужной операции достаточно нажать крестик слева от неё — но будьте осторожны: последующие этапы, ссылающиеся на удалённые данные, могут сломаться.

Изменение порядка выполняется простым перетаскиванием строк мышью. Однако помните: последовательность критична, поскольку каждый шаг опирается на результат предыдущего. Перестановка часто приводит к ошибкам, поэтому лучше сначала продублировать запрос и экспериментировать на копии.

Создание пользовательских функций и условных выражений в редакторе

Когда стандартных кнопок перестаёт хватать, приходит время писать собственный код на M. В окне «Расширенный редактор» удобно настраивать не только шаги, но и объявлять функции через let и in. Например, можно задать выражение для пересчёта скидки: (price) => if price > 1000 then price*0.9 else price. Условные конструкции if...then...else легко встраиваются в любой шаг, а созданную функцию потом вызывают в новых запросах. Такой подход сокращает рутинные операции и делает код переиспользуемым.

Работа с несколькими источниками данных и объединение таблиц кодом

Когда данных много, их редко удаётся собрать в одном месте. Чаще приходится комбинировать выгрузки из разных систем. В Power Query это решается через запросы, которые ссылаются друг на друга.

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

Для продвинутых сценариев удобнее писать выражение вручную. Базовый синтаксис выглядит так:

let
    Источник1 = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Источник2 = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Объединение = Table.NestedJoin(Источник1, {"ID"}, Источник2, {"ID"}, "Данные", JoinKind.LeftOuter)
in
    Объединение

После этого столбец с вложенной таблицей разворачивается кнопкой в шапке. Если нужно просто сложить строки друг под другом, применяется Table.Combine — он не требует ключей и работает быстрее.

Полезно помнить: при работе с несколькими источниками стоит сразу задавать типы данных, иначе при объединении могут возникнуть ошибки преобразования.

Типичные ошибки при работе с расширенным редактором

Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel - изображение номер три
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel — изображение номер три

Даже опытные пользователи спотыкаются о мелочи. Чаще всего проблемы возникают из-за невнимательности к синтаксису или непонимания логики языка M.

  • Пропущенная запятая между шагами — самая частая причина отказа в применении.
  • Попытка вручную переименовать шаг, не обновив ссылки на него в последующих выражениях.
  • Игнорирование регистра: в M имена функций чувствительны к нему.

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

Ошибки синтаксиса и как их быстро найти в коде

При работе с формулами в Power Query неизбежны опечатки. Чаще всего проблемы возникают из-за пропущенной запятой, неверного имени функции или незакрытой скобки. Редактор подсвечивает проблемные строки волнистой линией, но этого не всегда достаточно.

Быстрый способ локализовать неисправность — использовать встроенную диагностику. Нажмите «Диагностика» на вкладке «Главная», чтобы увидеть пошаговый лог выполнения. Ошибка будет указана с номером шага и описанием. Также помогает временное комментирование частей кода: выделите фрагмент и нажмите Ctrl+K, C. Так вы сузите круг поиска до конкретного блока.

Если сообщение об ошибке неочевидно, проверьте регистр символов — язык M чувствителен к нему. Например, Table.SelectRows не сработает, если написать table.selectrows.

Проблемы с производительностью и способы оптимизации запросов

Медленная работа редактора обычно связана с избыточной загрузкой данных на этапе предварительного просмотра. Снизить нагрузку помогает несколько простых приёмов.

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

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

Советы по эффективной работе с расширенным редактором Power Query

Чтобы ускорить подготовку данных, держите под рукой шпаргалку по горячим клавишам. Например, сочетание Ctrl+Enter применяет текущий шаг, а Ctrl+Z откатывает неудачное изменение. Полезно также давать осмысленные имена каждому этапу преобразований — так проще отлаживать логику и возвращаться к нужному месту.

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

Не забывайте про кнопку «Закрыть и применить» — она фиксирует результат. А для проверки промежуточных вычислений удобно использовать «Просмотр данных».

Горячие клавиши и шаблоны для ускорения написания кода

Набирать формулы вручную — занятие неблагодарное. Быстрее использовать готовые сниппеты. Например, сочетание Ctrl+Space вызывает подсказки по членам текущего выражения, а F5 запускает выполнение шага. Для вставки стандартных конструкций вроде Table.Group или List.Accumulate удобно держать под рукой собственный файл с заготовками — так называемый сниппет-менеджер. Ниже пара примеров, которые экономят минуты.

  • Ctrl+Shift+L — дублирование строки вниз.
  • Alt+↓ — перемещение текущей строки.
  • Ctrl+K, Ctrl+C — закомментировать выделенный фрагмент.

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

Как использовать справку и сообщество для изучения редактора

Официальная документация Microsoft — первый помощник. В ней описаны все функции, включая тонкости работы с M-языком. Если возникает нестандартная задача, загляните на форумы Stack Overflow и специализированные русскоязычные ветки обсуждений. Там практики делятся готовыми решениями и подсказывают, как обойти типичные ошибки. Полезно также изучать примеры из блогов разработчиков — они часто содержат неочевидные приёмы.

НОВОЕ

ВИДЕОУРОКИ БАЗА КОДА ОНЛАЙН-ТЕСТЫ