Для написания формулы в Power Query используется язык программирования M. В рамках этой статьи раскрыты основы данного языка программирования, особо не углубляясь в примеры функций и формул, а описаны следующие моменты:
- В чем особенность языка М?
- Как устроен синтаксис формул PQ?
- С чего начать изучения язык Power Query M?
Для аналитика знание этого языка М существенно может облегчить жизнь и работу. Я настоятельно рекомендую вам потратить время на M, потому что есть МНОЖЕСТВО операций, которые вы можете выполнять, применяя этот язык. Но обо всем по порядку.
Что такое язык Power Query M?
Язык программирования M — это очень мощный язык, который заложен в основы Power Query. Любые преобразования данных в этом инструменте от Microsoft будут написаны с помощью языка М. Для многих, такой подход преобразования данных — это нечто страшное. Но как известно: не так страшен черт, как его рисуют. Будем разбираться что к чему.
Язык М — это неофициальное название языка формул Power Query. Полное наименование очень длинное, так что его почти никто не использует. Если кратко M означает Data Mashup [пер. объединение данных] или Data Modeling [пер. моделирование данных].
Где применяется язык М?
В принципе, язык М вполне самостоятелен. Имеет свою структуру и синтаксис, и его можно использовать отдельно с помощью существующих инструментов, однако… В компании Microsoft реализовали применение языка M в удобном встроенном интерфейсе Excel — Power Query. На текущий момент — это вполне себе устоявшаяся рабочая система и довольно достойный инструмент аналитика. В рамках этого инструмента и будет описано применения языка М в данной статье.
Power Query — это инструмент преобразования и подготовки данных, который интегрирован в MS Excel. Если кратко, то PQ — это надстройка для Excel, которая решает 2 основные задачи:
- Импорт данных из любых источников
- Преобразование этих данных в любой нужный нам вид
Причем, сделать это можно лишь раз, а потом только обновлять запросы.
Некоторые особенности языка программирования М
Поскольку язык M является функциональным языком и весь код строится на обширной библиотеке функций, тут не без особенностей.
Первое, что необходимо помнить при написании кода на языке M — это язык чувствителен к регистру. Другими словами, x не то же самое, что X. И это истина для любых переменных, функций и значений.
Из первого вытекает второе, из-за чувствительности к регистру и типу данных вполне вероятно появление ошибок. К примеру, попытка перемножения текстового поля на число, выдаст ошибку.
Однако применение выражения try… или otherwise… можно избежать ошибок изначально заменив их любым значением
Третье, возможность комментировать. Как и во многие языки, в код языка М можно добавлять комментарии двух типов:
- Однострочный комментарий, поставив // перед началом (в данном случае, комментарием становится всё написанное в данной строке)
- Многострочный комментарий обозначается символами /* и */ (всё что помещено между ними будет считаться комментарием)
От особенностей к преимуществам и недостаткам
Плюсы и минусы языка формул М
У любого языка программирования есть как плюсы, так и минусы. Язык M не исключение. Кратко пробежимся по основным из них
Плюсы
- Встроен прямо в Excel — нет необходимости устанавливать дополнительное программное обеспечение.
- Данные в полной безопасности — Query отправляет запрос к исходникам, не изменяя их содержание или структуру.
- Язык Power Query M прост в понимании и изучении за счет большого количества информации и собственной библиотеке с подсказками.
- Доступны различные комбинации шагов для преобразования данных. И они все могут быть правильными.
- Специализируется только для преобразования и обработки данных в Excel
Минусы
Несмотря на то, что язык М довольно функционален, у него есть несколько минусов:
- Язык программирования M не позволит визуализировать данные, даже в простой линейный график
- Не получится произвести сложные статистические расчеты (сумма или среднее пожалуйста, а вот, например, прогноз на основе ряда данных — уже вряд ли)
- Язык M в Power Query не позволит напрямую редактировать значения (только через изменения исходника).
Зачем учить язык программирования М?
Очень часто в работе аналитика приходится крутить исходные данные, стараясь получить крупицы истины. Иногда таких данных может быть много. Данные могут быть в разных источниках и разных форматах. Цель языка M состоит в том, чтобы осуществить преобразование данных быстро и минимальными усилиями.
После обработки и преобразования данных, их можно отправить в модель данных и начать форматировать с помощью DAX, Power BI или просто в сводную со всеми вытекающими. Помимо этого, добавим еще несколько причин, зачем аналитику учить язык программирования М:
- Есть возможность компоновки в единое поле данных из разных источников, не зависимо от количества и форматов файлов
- Автоматизация обновления и подкачки новых данных
- Возможность обработки большого количества строк данных (значительно больше 1 млн. строк)
- Колоссальная экономия времени на преобразование данных
- Можно связывать таблицы между собой по какому-либо ключевому полю
Синтаксис языка программирования М
Синтаксис этого языка прост и всегда имеет два блока программирования: блок выражения LET и блок выражения IN. Вот самый простой синтаксис языка M:
пояснение по блокам:
- Let: определение всех переменных
- In: результат вашего запроса (выход)
По сути это единый сценарий написания запроса на языке М. Единственный момент, когда вы пишете сценарий, вы помещаете зарезервированные слова, такие как let, и все в нижнем регистре. Также ваше имя переменной должно быть одинаковым как в let, так и в разделе.
Конец линии. Строка кода в M продолжаются до того момента пока не поставлен символ конца строки «,». Каждая строка нуждается в запятой для завершения, кроме последней строки перед in
Имена переменных. Имена переменных могут состоять из одного слова, например, Source или в нем могут быть пробелы. Необходимо поместить имя в двойные кавычки («) и поставить хэштег в начале (#). Как-то так:
#»Это имя переменной»
Специальные символы. Имена переменных могут иметь специальные символы, как вы можете видеть ниже. Для наглядности переменная содержит все типы символов и по-прежнему хорошо работает.
Power Query — это пошаговое преобразование. Каждое итерация обычно происходит поэтапно. После совершения какого-либо действия в правой части вы увидите, что каждая переменная образует шаг (см. Параметры запроса)
Есть еще много синтаксиса языка М, которые изначально могут быть не понятны, особенно функции (справочник Microsoft содержит описание на более 700 функций Power Query M). На страницах этого сайта мы еще не раз вернемся к объяснения данного языка. Всё зависит от вашего желания учиться и постигать науку написания кода.
Где учить язык программирования M?
Есть несколько работающих способов изучить язык M:
- Расширенный справочник на сайте Microsoft — по сути это подробный учебник Power Query и языка М. Тут можно либо идти и изучать функции одну за другой, либо искать под конкретный запрос, воспользовавшись поиском. Я лично так и делал, потому как из 700+ функций в повседневной работе требуется максимум 10-20 и перелопачивать все ради небольшой крупицы знаний бессмысленно.
- Прочитать подробную и очень подробную книгу Николая Павлова Скульптор данных в Excel Power Query. Подробное описание книги на странице ТОП100 книг аналитика
- Пройти курс обучения Power Query в специализированной компании, благо таких много на рынке.
Любой новый язык нельзя начать применять, если вы сначала не изучите основы. Мы надеемся, что эта статья поможет вам начать работу с языком М, чтобы создавать более сложные запросы.
