Что такое Power Query? Это инструмент, который данные любого вида и из любых источников приводит в формат базы данных. Если вы не знаете, что такое Power Query, срочно посмотрите, что это такое, а затем возвращайтесь к этой статье )
Запросы Power Query – это запросы, которые в Excel и Power BI можно создавать одной мышкой, указывая системе, куда обратиться и какие действия выполнить. Очень похоже на макросы. Только команды записываются на языке М и больше похожи на формулы Excel: там также есть знак равно, название функции и в скобках аргументы. Power Query не требует навыков программиста – код на языке M генерируется автоматически.
Простыми щелчками мыши вы можете творить чудеса и решать почти все задачи, стоящие перед вами. Но иногда бывают такие случаи, когда запрос нужно все-таки поправить. А еще реже – написать полностью вручную. И чтобы развязать вам руки в работе с запросами Power Query, предлагаем краткое введение в язык М.
Как в Power Query ввести формулу на языке M
Вы можете писать формулы на языке M в строке формул Power Query, расширенном редакторе или при добавлении настраиваемых пользовательских столбцов.
Строка формул
Для отображения строки формул в меню Просмотр и поставьте галочку в пункте Строка формул. Чтобы создать новый шаг запроса с помощью строки формул, нажмите кнопку fx в строке формул.
Расширенный редактор
Расширенный редактор запросов в Power Query позволяет полностью написать запрос на языке M, без использования стандартного функционала. Перейдите в меню Просмотр → Расширенный редактор.
На рисунке — пример кода запроса, который автоматически сгенерирован Power Query на языке M.
Добавление настраиваемого столбца
Для создания настраиваемого (пользовательского) столбца перейдите в меню Добавить столбец → Настраиваемый столбец. В открывшемся окне введите необходимые формулы или вычисления.
Как устроен язык M
Если вы откроете уже вами созданный запрос в расширенном редакторе, то увидите код, заключенный в конструкции let … in … Разберемся, как устроен язык М — в нем есть значения и выражения.
Значения — это значения, такие как число 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)
Внимание! Язык М чувствителен к регистру. Например, формула Excel.CurrentWorkbook() сработает, а Excel.currentworkbook() – нет, потому что написана без учета регистра.
Комментарии в запросах
- Комментарии в одну строку начинаются с двойной черты //
- Комментарии на нескольких строках начинаются и заканчиваются символами /* и */
Справка в редакторе Power Query
Введите в строке формул название формулы без скобок (). Появится окно со справкой.
Ниже приведен пример справки для 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 … then … else
Например:
= if [Sales] > 1000 then "более 1000" else if [Sales] > 500 then "более 500" else "500 или меньше"
Оператор let
Выражение let позволяет вычислять набор значений, назначить имена, а затем используется в следующем выражении, которое записывается в in.
Для сложных запросов могут использоваться вложения let, что позволяет сделать запрос более читаемым. Пример:
Это введение в правила языка M. Получается легкая смесь формул Excel и программирования. На самом деле с Excel больше общего, чем кажется вначале. Например, обращение к столбцу BaseLine в записи Data, записанное как:
= Data[BaseLine]
напоминает синтаксис Excel, когда вы ссылаетесь на ячейку A1 на другом листе. Формула будет похожей:
= Лист!A1
Отличие только в том, что после названия листа стоит восклицательный знак. И в том, что в Power Query или в языке M мы обычно ссылаемся не на отдельную ячейку, а на столбец целиком. В остальном запись идентична: таблица аналогична листу, столбец – ячейке. Если рассматривать M в таком ключе, то многие команды становятся интуитивно понятными.
Смотрите продолжение в следующей статье:
Язык M для Power Query и Power BI: работа с данными
Продолжаем разбираться с языком M. Рассмотрим подробнее, как с помощью языка M в Power Query работать со списками, записями, таблицами. А также как создавать собственные функции.
