Введение в Power Query M Code

Оригинал статьи: https://www.howtoexcel.org/m-code/

Power Query - это очень мощный инструмент извлечения и преобразования данных, который поставляется в Excel 2016 (или более поздней версии), Excel для Office 365 и Power BI.

Его можно найти на вкладке "Данные" в разделе "Получение и преобразование данных" ленты.

Это очень мощный и в то же время очень простой в использовании редактор запросов с интуитивно понятным пользовательским интерфейсом для пользователя Excel. Многие шаги преобразования можно легко выполнить с ленты power query editor, и вам не нужно знать никакого кода для очистки и придания формы вашим данным.

За кулисами удобного редактора Excel переводит каждый шаг вашего процесса преобразования из этих ленточных команд на язык кода power query M.

В этом посте вы познакомитесь с основами языка кода power query M и предположим, что вы уже знаете основы power query.

Что такое M Code?

M расшифровывается как data Mash-up, поскольку power query предназначен для подключения к различным источникам данных и их “смешивания”.

M code - это язык, лежащий в основе power query. Когда вы создаете преобразование данных в пользовательском интерфейсе power query editor, Excel записывает соответствующий M-код для запроса.

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

Где вы можете написать код Power Query M?

Если вы хотите начать писать или редактировать M-код, вам нужно знать, где вы можете это сделать. Есть два места, где это возможно, в строке формул или в расширенном редакторе.

Строка формул

Для каждого шага, созданного в пользовательском интерфейсе редактора, вы можете просмотреть соответствующий M-код в строке формул.

Если вы не видите в строке формул, перейти на вкладку View и убедитесь, что параметр Formula Bar отмечен.

Вы можете отредактировать M-код для любого шага запроса, щелкнув по формуле и отредактировав существующий код.

По завершении вы можете принять любые изменения, щелкнув по флажку или нажав Enter. Вы также можете отменить внесенные изменения, нажав на крестик или нажав Esc.

Вы также можете создавать совершенно новые шаги в своем запросе с помощью строки формул, нажав на символ fx рядом со строкой формул. Это создаст новый шаг, который ссылается на предыдущий шаг по имени, а затем вы сможете создать любой M-код, который вам нужен.

Расширенный редактор

В строке формул отображается только M-код для текущего выбранного шага запроса, но в расширенном редакторе вы можете просмотреть и отредактировать M-код для всего запроса.

Вы можете открыть расширенный редактор из двух мест на ленте редактора. На вкладке "Главная" или на вкладке "Вид" нажмите кнопку "Расширенный редактор".

Несмотря на название “расширенный”, редактор является самым простым редактором кода, который вы увидите, и (пока) не содержит никаких функций автозаполнения IntelliSense, подсветки синтаксиса или автоматического форматирования.

Расширенный редактор отобразит имя запроса, покажет M-код запроса и отобразит предупреждение о любых нарушениях синтаксиса в M-коде. Вот и все!

Библиотека стандартных функций

Поскольку M code является функциональным языком, все дело в функциях, а M code поставляется с большой библиотекой предопределенных функций, называемой стандартной библиотекой.

Информацию обо всех доступных функциях стандартной библиотеки можно найти на справочной веб-странице Microsoft Power Query M, включая синтаксис функций и примеры.

Со стандартной библиотекой также можно ознакомиться в редакторе power query, используя ключевое слово #shared.

После ввода в строку формул вы можете просмотреть все доступные функции, нажав на слово Функция справа от названия функции. Вы найдете тот же синтаксис и примеры, что и на справочной веб-странице.

Чувствительность к регистру

Одна из первых вещей, о которой кто-то должен знать при написании M-кода, - это то, что это язык, чувствительный к регистру.

Это означает, что x это не то же самое, что X или abc не то же самое, что ABC. Это верно для любых значений, переменных, функций и т.д.

Выражения и значения в Power Query

Power query - это все о выражениях и значениях.

Выражение - это нечто, что может быть вычислено для возврата значения в power query. 1 + 1 это выражение, которое преобразуется в значение 2.

Значение - это отдельный фрагмент данных. Значениями могут быть отдельные значения, такие как числа, текст, логическое, нулевое, двоичное, дата, время, datetime, datetimezone или длительности.

Значения также могут иметь более сложную структуру, чем отдельные значения, такие как списки, записи и таблицы.

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

Отдельные литеральные значения

Значения с одним литералом являются базовым строительным блоком для всех других значений.

• 123.45 это числовое значение.
• "Hello World!" это текстовое значение.
• true является логическим значением.
• null представляет отсутствие значения.

Отдельные внутренние значения

Встроенные значения создаются с использованием различных встроенных функций.

• #time(hours, minutes, seconds)
• #date(years, months, days)
• #datetime(years, months, days, hours, minutes, seconds)
• #datetimezone( years, months, days, hours, minutes, seconds, offset-hours, offset-minutes)
• #duration(days, hours, minutes, seconds)

Например, чтобы сконструировать дату 2018-12-31, вам нужно будет сконструировать ее с помощью #date(2018, 12, 31) встроенной функции.

Структурированные значения

Списки

Список - это упорядоченная последовательность значений.

Вы можете определить список с помощью фигурных скобок. {1, 2, 3} это список, содержащий числа 1, 2 и 3. Поскольку порядок важен, этот список отличается от {3, 2, 1}.

{"Hello", "World"} представляет собой список, содержащий текст "Hello" и "World".

Также возможны списки списков, поэтому {{1, 2}, {3, 4, 5}} это список из двух списков. Первый список содержит число 1 и 2, а второй список содержит числа 3, 4 и 5.

Вы можете создавать последовательные списки, используя формат {x..y}. {2..5} Будет создан список {2, 3, 4, 5}. Это также работает и для текстовых символов. {"a".."d"} создаст список {"a", "b", "c", "d"}.

У вас также может быть список без элементов, {} это пустой список.

Поскольку списки упорядочены, вы можете ссылаться на элементы в списке с нулевым индексным номером. {1, 2, 3}{2} будет оцениваться на 3, поскольку это 2-й элемент в списке (основанный на нулевом индексе).

Записи

Запись - это упорядоченная последовательность полей.

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

Вы можете определить запись с помощью квадратных скобок. [FirstName = "John", Age = 38] это запись с двумя полями. Первое поле в записи имеет название поля FirstName и значение John. Второе поле в записи имеет название поля Age и значение 38.

Также возможны записи записей, [Person = [FirstName = "John", Age = 38]] это запись с одним полем с именем Person и значением поля, которое является записью.

Также возможны пустые записи, [] это пустая запись.

Вы можете ссылаться на значение поля в записи по имени его поля. [FirstName = "John", Age = 38][FirstName] будет вычислен Jphn.

Таблицы

Таблица - это упорядоченная последовательность строк, где каждая строка представляет собой список.

Таблицы могут быть созданы только с использованием встроенной функции. Вы можете создать таблицу с помощью #table() функции из списка заголовков столбцов и списка строк.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) создаст таблицу с 2 столбцами, 3 строками и заголовками столбцов из букв и цифр.

Можно создать пустую таблицу, используя пустые списки в #table() встроенной функции. #table({}, {}) будет создана пустая таблица.

Вы можете ссылаться на любое значение в таблице с индексом строки на основе нуля и названием заголовка столбца. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2}[Letters] будет вычисляться на C, поскольку это 2-я строка (основанная на нулевом индексе) столбца Letters. (на основе нулевого индекса).

Выражения

Выражения - это все, что может быть оценено в значение.

Это относится и к самим значениям. Например, выражение 1 преобразуется в значение 1.

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

Например.

• Выражение 1 + 1 принимает значение 2.
• Выражение 3 > 2 принимает значение true.
• Выражение "Hello " & "World" принимает значение "Hello World".
• Выражение Text.Upper("Hello World") принимает значение "HELLO WORLD".

Операторы

Наряду со стандартной библиотекой, M code также имеет список специальных функций, называемых операторами.

Они принимают ровно два значения (или выражения, которые преобразуются в значение) и возвращают одно значение.

Арифметика

M code поставляется с базовыми арифметическими операторами, которые вы ожидаете и к которым привыкли в обычном Excel +, -, * и /. Они позволят вам складывать, вычитать, умножать и делить значения соответственно.

Они могут использоваться с различными другими типами значений, отличными от просто чисел. Например, вы можете добавить продолжительность к дате.

#date(2018,12,25) + #duration(7, 0, 0, 0) будет оцениваться до 2019-01-01.

Сравнение

Вы можете сравнивать значения в M-коде, используя операторы сравнения <, >, <= >=, = <>.

• x < y будет оценено как true, если x меньше y .
• x > y будет оценено как true, если x больше, чем y .
• x <= y будет оценено как true, если x меньше или равно y .
• x >= y будет оцениваться как true, если x больше или равно y .
• x = y будет оценено как true, если x равно y .
• x <> y будет оценено как true, если x не равно y .

Они могут использоваться с различными типами значений. Например, вы можете сравнить два списка с помощью оператора equal.

{1,2,3,4} = {1,2,3} будет оценено как false, поскольку списки не совпадают.

Объединение и слияние

Вы можете объединять текст и списки, записи и таблицы с помощью оператора амперсанд &.

Например.

• "Hello " & "World" будет оцениваться на "Hello World".
• {1,2,3} & {3,4,5} будет оцениваться на {1,2,3,3,4,5}.

Логический

Вы можете выполнять операции над логическими значениями (или выражениями, которые преобразуются в логические значения) с помощью операторов not, and и or.

• not x будет оцениваться как true, когда x равно false.
• x and y будет оцениваться как true, когда оба значения x и y равны true.
• x or y будет оцениваться как true, когда либо x, либо y равны true.

Комментирующий код

Как и следовало ожидать от любого языка программирования, к вашему коду можно добавлять комментарии.

В M code возможны два типа комментариев. Однострочные комментарии и многострочные комментарии.

Комментарии в одну строку

M code goes here
M code goes here //This is a single line comment
M code goes here

Однострочный комментарий можно создать, поставив перед комментарием два символа косой черты //. Все, что находится в той же строке перед этим, будет интерпретировано как M-код, все, что после него, будет интерпретировано как комментарий.

Многострочные комментарии

M code goes here /*This is a comment
on multiple lines*/ M code goes here

Многострочный комментарий можно создать, поместив комментарий между символами /* и */. Все, что находится за пределами этих символов, будет интерпретировано как M-код. Все, что находится между ними, будет интерпретировано как комментарий.

Оператор Let

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

let
a = 1,
b = 2,
c = a + b
in
c

Это выражение состоит из трех выражений, которые вычисляются после let инструкции.

Каждое выражение разделяется запятой, за исключением последнего перед in выражением. В этом примере все выражение let and in будет равно 3.

let
c = a + b,
b = 2,
a = 1
in
c

Возможно, вы думаете, что выражения в let инструкции должны отображаться в том порядке, в котором они должны быть вычислены. Но это не так!

Приведенный выше код абсолютно корректен и также будет оценен до 3. Программа оценки M code автоматически вычислит необходимый порядок вычислений на основе зависимостей выражений.

Очевидно, что пользователю легче читать M-код, если он написан в порядке вычисления, но есть и другое преимущество.

Выражения будут отображаться в виде отдельных шагов в окне "Applied Steps". При записи не по порядку выражения будут отображаться как один объединенный шаг.

let
a = 1,
b = 2
in
a + b

Вы также можете вычислять выражения внутри in части let... in... выражения.

Имена переменных

let
#"Is this a variable name? Wow!!!" = 1 + 1
in
#"Is this a variable name? Wow!!!"

Вы можете присвоить своим выражениям практически любое имя, используя #"" символы. Вы даже можете использовать пробелы и другие специальные символы.

Использование зарезервированных ключевых слов - единственное исключение.

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

Выражение each

Выражение each является сокращением для объявления функций, принимающих один параметр с именем _ (подчеркивание).

let
Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}),
#"Added Custom" = Table.AddColumn(Source, "Double", each 2*[Numbers])
in
#"Added Custom"

В этом примере вы создаете новый столбец, который умножает столбец с числами на 2 для each строки.

let
Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}),
#"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_[Numbers])
in
#"Added Custom"

Вы можете создать тот же запрос, используя синтаксис подчеркивания, который семантически эквивалентен each инструкции. Оба запроса будут работать одинаково.

Операторы If Then Else

M code довольно скуден по сравнению с другими языками, когда дело доходит до логических выражений.

Не доступны инструкции select case или loop. Доступно только if... then... else... выражение.

if [logical expression to test]
then [do this when true]
else [do this when false]

Синтаксис прост и похож на большинство других языков программирования.

Все это может отображаться в одной строке или может быть представлено отдельными строками для удобства чтения.

Попробуйте другие инструкции

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

let
Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}),
#"Added Custom" = Table.AddColumn(Source, "Product", each try [Number]*[Number and Text] otherwise 0)
in
#"Added Custom"

Ошибок можно избежать, используя try... otherwise... выражение.

Это позволит избежать ошибок в результатах вашего запроса и позволит вам заменять ошибки любым значением или выражением.

Функции

Функция - это преобразование набора значений параметров в значение. Наряду с функциями стандартной библиотеки, M code позволяет создавать собственные функции.

let
Product = (x,y) => x * y,
Result = Product(2,3)
in
Result

Этот запрос определяет функцию, которая умножает два числа. Затем запрос вызывает и оценивает функцию со значениями 2 и 3, которая получает значение 6.

Функции с дополнительными параметрами

Существует два типа параметров функции: обязательный параметр и необязательный параметр.

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

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

let
Product = (x, optional y) => if y is null then x else x * y,
Result = Product(2)
in
Result

Эта функция имеет необязательный второй аргумент. Затем запрос вызывает и оценивает функцию, используя только требуемый параметр со значением 2, который оценивается как 2.

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

Рекурсивные функции

Также возможно написать функцию, которая ссылается на саму себя, используя @ оператор определения области видимости.

let
Fibonacci = (n) =>
if n = 1
then 1
else
if n = 2
then 1
else @Fibonacci(n-1) + @Fibonacci(n-2),
Result = Fibonacci(7)
in
Result

Последовательность Фибоначчи является примером функции, которая определяется рекурсивно.

Следующее число в последовательности определяется как сумма двух предыдущих чисел. Итак, чтобы получить n-е число, вам нужно знать (n-1)-е и (n-2)-е числа.

Эта функция найдет n-е число Фибоначчи путем сложения (n-1)-го и (n-2)-го чисел Фибоначчи.

Результат запроса равен 13, поскольку 13 является 7-м числом Фибоначчи.

Вот еще несколько полезных примеров, в которых вы можете использовать рекурсивные функции для репликации функции TRIM в Excel для удаления лишних пробелов между словами или массового поиска и замены значений на основе списка.

Функции запроса

Приведенные выше примеры определяли функцию в запросе, затем вызывали и оценивали функцию в запросе.

Также возможно создать запрос, который является функцией (a query function) и может вызываться и оцениваться из других запросов.

let FunctionResult = (Argument1, Argument2,...) =>
let
/*M code to evaluate in the function goes here*/
in
Result
in
FunctionResult

Это общий формат, необходимый для создания функции запроса.

Обратите внимание, вам понадобится let... in... оператор внутри let... in... оператора функции запроса, чтобы выполнить несколько шагов.

Выводы

Power query - отличная функция в Excel, которая может помочь вам автоматизировать и упростить импорт и преобразование данных.

Вы можете многое сделать, используя только графический интерфейс "укажи и щелкни", даже не прикасаясь к M code.

Но по мере того, как ваши требования становятся более сложными, может возникнуть необходимость отредактировать созданный вами M-код или написать свой собственный с нуля.

Любой новый язык программирования нечитаем, если вы сначала не освоите основы. Мы надеемся, что это руководство поможет вам освоиться с M code, чтобы вы могли начать создавать более сложные запросы.