Язык M в Power Query: как прочитать и безопасно поправить код запроса

Запрос собран кнопками и работает, но в шаге фильтра нужен другой порог, а в источнике переименовали столбец. Справка о Power Query в Excel прямо разрешает изменять и писать свои шаги на языке Power Query M в расширенном редакторе^[2]. Пересобирать запрос с нуля ради одной цифры незачем — достаточно поправить строку кода.

Путь здесь такой. Сначала поймите задачу: что именно в запросе нельзя сделать кнопкой. Затем выберите инструмент — правку существующего шага вместо нового запроса. Освойте основы: let, in, шаги и слово each, попробуйте прочитать код в тренажёре и примените навык к своему запросу. Главный риск — опечатка в регистре: справочник функций не прощает строчную букву там, где нужна прописная.

Ключевые факты: язык M в Power Query

• Язык M — функциональный язык формул Power Query, чувствительный к регистру и похожий на F#^[1].
• Весь код запроса открывается командой Advanced Editor на вкладке View редактора^[3].
• Имя шага с пробелом допускается только в форме #"…"^[4].
• Шаг с each отличается от формулы листа тем, что применяет условие сразу ко всем строкам таблицы.
• Справка Microsoft разрешает писать собственные шаги на языке Power Query M в Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[2].

Что такое язык M в Power Query и как открыть расширенный редактор

Язык M в Power Query описывает каждый шаг запроса текстом внутри конструкции let … in. Справка Microsoft пишет, что любая комбинация данных в Power Query описывается с помощью языка формул Power Query M^[1]. Что умеет язык M сверх кнопок, видно в расширенном редакторе: там можно собрать свой шаг, функцию или условие. Код запроса создают кнопки редактора: удалили столбец — появилась строка, поменяли тип — ещё одна. Русская версия справки переводит let expression как «разрешить выражение» — машинный перевод, ключевое слово в коде остаётся let^[4].

Чтобы открыть весь код, выберите на ленте редактора вкладку View и команду Advanced Editor — так путь описан в английской справке^[3]. В расширенном редакторе английская справка показывает весь существующий код, индикатор синтаксических ошибок и кнопки Done и Cancel^[3].

Редактор и язык M в Power Query встроены в Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[2]. Сам Power Query доступен в Excel для Windows, Excel для Mac и Excel в Интернете^[2]. В Google Таблицах языка M нет: документация перечисляет среди сред Power Query книги Excel, Power BI, Analysis Services и онлайн-службы Microsoft^[1]. Если запросов в книге ещё нет, начните с обзора Power Query и первого запроса из таблицы: к коду имеет смысл переходить, когда запрос уже собран.

Разобравшись с тем, где лежит код, переходим к его устройству — конструкции let … in.

Как читать запрос на языке M: let, in и шаги по порядку

Запрос на языке M состоит из шагов-переменных после let и результата после in^[4]. Каждый шаг — имя, знак равенства и выражение, которое обычно ссылается на предыдущий шаг. Вот запрос с маленькой таблицей заказов и фильтром крупных сумм:

let
    Заказы = Table.FromRecords({
        [Ресторан = "Бриз", Сумма = 54000],
        [Ресторан = "Тарантино", Сумма = 8600],
        [Ресторан = "Сакура", Сумма = 31000]
    }),
    Крупные = Table.SelectRows(Заказы, each [Сумма] > 10000)
in
    Крупные

1. Найдите слово in и имя после него — это шаг, который станет результатом.
2. Прочитайте код шагов после let: «Крупные» берёт таблицу «Заказы» и возвращает строки, где сумма больше порога.
3. Поменяйте порог в коде шага и закройте редактор кнопкой Done.

Имя шага с пробелом записывают только в форме #"имя идентификатора"^[4] — так выглядят шаги, которые создают кнопки русского интерфейса. Между шагами стоит запятая, после последнего шага перед in её нет.

Порядок строк внутри let не равен порядку вычисления. Выражения let вычисляются отложенно, только по мере необходимости^[5]: шаг, от которого результат не зависит, просто не выполнится.

Прочитайте такой код в тренажёре: урок покажет шаги let … in и попросит найти ошибку в условии.

Однако чтение кода — половина дела: функции и условия устроены иначе, чем формулы листа.

Функции, each и if: чем код M отличается от формул листа

Функции языка M работают с целыми таблицами, различают регистр и не похожи на формулы листа. Имена строятся по схеме «Категория.Действие»: Table.SelectRows, Text.Proper. Если набрать имя функции не тем регистром, строка кода не выполнится: справка называет язык чувствительным к регистру^[1].

Функция Table.SelectRows принимает таблицу и условие-функцию и возвращает строки, которые ему соответствуют^[6]; слово each в примере выше и есть такое условие. Выражение if 2 > 1 then 2 + 2 else 1 + 1 по справке возвращает 4^[7], потому что условие истинно. В настраиваемом столбце то же условие пишут для каждой строки:

if [Сумма] >= 30000 then "крупный" else "обычный"

Свою функцию объявляют стрелкой: тело функции следует за символом =>, например (x, y) => x + y^[8]. Справка Microsoft перечисляет десять примитивных типов значений — от Binary и Даты до Текста и Времени^[4].

Остаётся понять, когда в код лучше не лезть вовсе.

Когда язык M не подойдёт

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

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

Перед ручной правкой скопируйте код из Advanced Editor в текстовый файл — так легко вернуть рабочую версию. Индикатор синтаксических ошибок в окне редактора^[3] ловит пропущенную запятую, но не неверный порог: результат всё равно проверяйте на листе. Соседние темы собраны среди всех уроков тренажёра по программированию в Excel.