– Мммм, Power Query!
Вот такие слова часто раздаются на курсах и семинарах по Excel и Power BI. Что такое Power Query? Это инструмент, который данные любого вида и из любых источников приводит в формат базы данных. Если вы не знаете, что такое Power Query, срочно посмотрите видео на нашем сайте и затем возвращайтесь к этой статье )
Power Query – это запросы, которые может создавать аналитик одной мышкой, указывая системе, куда обратиться и какие действия выполнить. Очень похоже на макросы. Только команды записываются на языке М и больше похожи на формулы Excel: там также есть знак равно, название функции и в скобочках аргументы. Power Query не требует знаний и навыков программиста: код на языке M генерируется автоматически.
Простыми щелчками мыши вы можете творить чудеса и решать почти все задачи, стоящие перед вами. Но иногда бывают такие случаи, когда запрос нужно все-таки поправить. А еще реже – написать полностью вручную. И чтобы развязать вам руки в работе с запросами Power Query, предлагаем вам краткое введение в М на русском языке.
Полное описание языка M доступно на сайте Microsoft, но пока только на английском языке.
Как в Power Query ввести формулу на языке M
Вы можете писать формулы на языке M в строке формул Power Query, расширенном редакторе или при добавлении настраиваемых (пользовательских) столбцов.
Строка формул
Для отображения строки формул перейдите в меню редактора Просмотр и выберите Строка формул.
Чтобы создать новый шаг запроса с помощью строки формул, нажмите кнопку fx.
Расширенный редактор
Расширенный редактор запросов в Power Query позволяет полностью написать запрос на языке M, без использования стандартного функционала. Перейдите в меню Просмотр -> Расширенный редактор.
На рисунке — пример кода запроса, который автоматически сгенерирован Power Query на языке M, по выполненным шагам обращения к данным.
Чтобы создать новый пустой запрос, в меню Excel перейдите на вкладку Данные -> Получить данные -> Из других источников -> Пустой запрос.
Добавление настраиваемого столбца
Для создания настраиваемого (пользовательского) столбца перейдите в меню Добавить столбец -> Настраиваемый столбец. В открывшемся окне введите формулу столбца на языке M.
Как устроен язык M
Если вы откроете уже вами созданный запрос в расширенном редакторе, то увидите код, заключенный в конструкции let … in … На самом деле каждый запрос Power Query является одним выражением M, а оператор Let разделяет это выражение на несколько меньших.
Давайте разберемся, как устроен язык М. В языке М есть значения и выражения.
Значения — это значения, такие как число 1 или текстовая строка, или более сложные объекты, такие как таблицы. Также значения могут быть записаны как выражения, но при этом выражениями они не являются, например, 1+1 возвращает значение 2.
Выражение — это формула, используемая для построения значений. Выражение может быть сформировано с использованием синтаксических конструкций. Пример:
if 2 > 1 then 2 else 1
Ниже приведены типы значений, доступных в М:
- Простые значения (primitive value) – числовые, логические, текст или null. Например:
123 — число,
true — логическое значение,
null – отсутствие данных, null
- Список (list) – упорядоченная последовательность значений. Фигурные скобки { и } обозначают начало и конец списка. Пример:
{1, 2, 3} – список из чисел.
- Запись (record) – это упорядоченная последовательность полей, где каждое поле имеет имя и одно значение (любого типа). Пример:
[ A = 1, B = 2, C = 3 ]
- Таблица (table) – набор значений в виде строк и именованных столбцов. Нет определенного синтаксиса для создания таблицы, ее можно создать или обратиться к ней с помощью нескольких стандартных функций.
Например:
#table( {"A", "B"}, { {1, 2}, {3, 4} } )
Будет создана таблица:
- Функция (function) преобразует набор входящих значений в одно результирующее. Функция записывается путем перечисления параметров функции в круглых скобках ( ), за которыми следует знак перехода => далее указывают выражение, определяющее функцию.
(x, y) => (x + y) / 2
Внимание! Язык М чувствителен к регистру. Так, например, формула Excel.CurrentWorkbook() сработает, а Excel.currentworkbook() – нет, потому что написана без учета регистра.
Как записать комментарии в тексте запроса
Комментарии в одну строку начинаются с двойной черты //
Комментарии на нескольких строках начинаются и заканчиваются символами /* и */
Стандартные формулы языка М: справка в редакторе Power Query
Нажмите кнопку fx в строке формул и введите название формулы без скобок (). Появится окно со справкой по этой формуле. Ниже приведен пример справки по формуле Excel.Workbook
Весь перечень формул можно вывести, написав в строке формул = #shared
Появится список формул. Если щелкнуть по формуле, в нижней части экрана появится справочная информация. Из списка можно выбрать нужную формулу и тут же добавить ее в запрос.
Идентификаторы, операторы и ключевые слова
Вы можете указывать имена для обозначения значений – идентификаторы. Если в идентификаторе есть пробел, то он указывается с помощью символа # и в кавычках, например:
[ #"2016 Sales" = 1000,
#"2017 Sales" = 1100,
#"Total Sales" = #"2016 Sales" + #"2017 Sales" ]
Для обращения к данным записей (record) используются идентификаторы с квадратными скобками [ ]. Пример:
[ Data = [ Base Line = 100, Rate = 1.8 ],
Progression = Data[Base Line] * Data[Rate] ]
В языке M есть зарезервированные последовательности символов, которые нельзя использовать как операторы, это:
and as each else error false if in is let meta not otherwise or section shared then true try type #binary #date #datetime #datetimezone #duration #infinity #nan #sections #shared #table #time
Также есть стандартные операторы и знаки пунктуации:
, ; = < <= > >= <> + - * / & ( ) [ ] { } @ ! ? => .. ...
Логические выражения if
Логические выражения записываются как if…then…else
Например:
if [Sales] > 1000 then "более 1000" else if [Sales] > 500 then "более 500" else "500 или меньше"
Оператор let
Выражение let позволяет вычислять набор значений, назначить имена, а затем используется в следующем выражении, которое записывается в in.
Например:
let
Sales2017 =
[ Year = 2017, FirstHalf = 1000, SecondHalf = 1100,
Total = FirstHalf + SecondHalf ], // 2100
Sales2018 =
[ Year = 2018, FirstHalf = 1200, SecondHalf = 1300,
Total = FirstHalf + SecondHalf ] // 2500
in
Sales2017[Total] + Sales2018[Total] // 4600
Для сложных запросов могут использоваться вложения let, что позволяет сделать запрос более читаемым.
Например:
let
AreaСalculation = (x, y) =>
let
Area = x * y,
DoubleArea = Area * 2
in
DoubleArea
in
AreaСalculation
Это введение в правила языка M. Получается легкая смесь формул Excel и программирования. На самом деле с Excel больше общего, чем кажется вначале. Например, обращение к столбцу Base Line в записи Data, записанное как:
= Data[Base Line]
напоминает синтаксис Excel, когда вы ссылаетесь на ячейку A1 на другом листе. Формула будет похожей:
=Лист!A1
Отличие только в том, что после названия листа стоит восклицательный знак. И в том, что в Power Query, или в M мы обычно ссылаемся не на отдельную ячейку, а на столбец целиком. В остальном запись идентична: таблица аналогична листу, столбец – ячейке.
Если рассматривать M в таком ключе, то многие команды становятся интуитивно понятными, что мы с вами дальше и увидим.
Смотрите продолжение в следующей статье:
Язык M для Power Query и Power BI: работа с данными
Продолжаем разбираться с языком M. Рассмотрим подробнее, как с помощью языка M в Power Query работать со списками, записями, таблицами. А также как создавать собственные функции.