Язык м power query формулы + видео обзор

Язык M для Power Query и Power BI: введение

Язык м power query формулы

Power Query – это запросы, которые может создавать аналитик одной мышкой, указывая системе, куда обратиться и какие действия выполнить. Очень похоже на макросы. Только команды записываются на языке М и больше похожи на формулы Excel: там также есть знак равно, название функции и в скобочках аргументы. Power Query не требует знаний и навыков программиста: код на языке M генерируется автоматически.

Простыми щелчками мыши вы можете творить чудеса и решать почти все задачи, стоящие перед вами. Но иногда бывают такие случаи, когда запрос нужно все-таки поправить. А еще реже – написать полностью вручную. И чтобы развязать вам руки в работе с запросами Power Query, предлагаем вам краткое введение в М на русском языке.

Как в Power Query ввести формулу на языке M

Вы можете писать формулы на языке M в строке формул Power Query, расширенном редакторе или при добавлении настраиваемых (пользовательских) столбцов.

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

Для отображения строки формул перейдите в меню редактора Просмотр и выберите Строка формул.

Язык м power query формулы

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

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

Язык м power query формулы

На рисунке — пример кода запроса, который автоматически сгенерирован Power Query на языке M, по выполненным шагам обращения к данным.

Добавление настраиваемого столбца

Язык м power query формулы

Как устроен язык M

Если вы откроете уже вами созданный запрос в расширенном редакторе, то увидите код, заключенный в конструкции let … in … На самом деле каждый запрос Power Query является одним выражением M, а оператор Let разделяет это выражение на несколько меньших.

Давайте разберемся, как устроен язык М. В языке М есть значения и выражения.

Значения — это значения, такие как число 1 или текстовая строка, или более сложные объекты, такие как таблицы. Также значения могут быть записаны как выражения, но при этом выражениями они не являются, например, 1+1 возвращает значение 2.

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

Ниже приведены типы значений, доступных в М:

Будет создана таблица:
Язык м power query формулы

Внимание! Язык М чувствителен к регистру. Так, например, формула Excel.CurrentWorkbook() сработает, а Excel.currentworkbook() – нет, потому что написана без учета регистра.

Как записать комментарии в тексте запроса

Комментарии в одну строку начинаются с двойной черты //
Комментарии на нескольких строках начинаются и заканчиваются символами /* и */

Источник

Операторы Operators

Язык формул Power Query M включает набор операторов, которые можно использовать в выражении. The Power Query M formula language includes a set of operators that can be used in an expression. Операторы применяются к операндам для формирования символьных выражений. Operators are applied to operands to form symbolic expressions. Например, в выражении 1 + 2 числа 1 и 2 являются операндами, а оператор представлен оператором сложения (+). For example, in the expression 1 + 2 the numbers 1 and 2 are operands and the operator is the addition operator (+).

Значение оператора может меняться в зависимости от типа значений операндов. The meaning of an operator can vary depending on the type of operand values. В этом языке есть следующие операторы: The language has the following operators:

Оператор сложения (+) Plus operator (+)

Выражение ExpressionРавно Equals
1 + 2 1 + 2Суммирование числа: 3 Numeric addition: 3
#time (12, 23, 0) + #duration (0, 0, 2, 0) #time(12,23,0) + #duration(0,0,2,0)Арифметические операции со временем: #time(12,25,0) Time arithmetic: #time(12,25,0)

Оператор комбинирования (&) Combination operator (&)

Функция FunctionРавно Equals
«A» & «BC» «A» & «BC»Объединение теста: «ABC» Text concatenation: «ABC»
<1>& <2, 3> <1>&

Объединение списков: <1, 2, 3>List concatenation:
[ a = 1 ] & [ b = 2 ] [ a = 1 ] & [ b = 2 ]Слияние записей: [ a = 1, b = 2 ] Record merge: [ a = 1, b = 2 ]

Список операторов M List of M operators

Общие операторы, которые применяются к значению NULL, логическому значению, числу, времени, дате, значению datetime, значению datetimezone, длительности, тексту, двоичному значению) Common operators which apply to null, logical, number, time, date, datetime, datetimezone, duration, text, binary)

Оператор OperatorОписание Description
>Больше чем Greater than
>=Больше или равно Greater than or equal
Меньше чем Less than
Меньше или равно Less than or equal
=Равно Equal
<>Не равно Not equal

Логические операторы (в дополнение к общим операторам) Logical operators (In addition to Common operators)

Оператор OperatorОписание Description
or orУсловное логическое ИЛИ Conditional logical OR
и andУсловное логическое И Conditional logical AND
not notЛогическое НЕ Logical NOT

Числовые операторы (в дополнение к общим операторам) Number operators (In addition to Common operators)

Текстовые операторы (в дополнение к общим операторам) Text operators (In addition to Common operators)

Оператор OperatorОписание Description
&Объединение Concatenation

Операторы для списков, записей, таблиц List, record, table operators

Оператор OperatorОписание Description
=Равно Equal
<>Не равно Not equal
&Объединение Concatenation

Оператор поиска записей Record lookup operator

Оператор OperatorОписание Description
[] []Обращение к полям записи по имени. Access the fields of a record by name.

Оператор индексатора списка List indexer operator

Оператор OperatorОписание Description
<>Обращение к элементу списка по его числовому индексу, отсчитываемому от нуля. Access an item in a list by its zero-based numeric index.

Операторы утверждения и совместимости типов Type compatibility and assertion operators

Оператор OperatorОписание Description
is isВыражение «x is y» возвращает true, если тип значения x совместим с y, и false, если они несовместимы. The expression x is y returns true if the type of x is compatible with y, and returns false if the type of x is not compatible with y.
as asВыражение «x as y» утверждает, что значение x совместимо с y в соответствии с применением оператора is. The expression x as y asserts that the value x is compatible with y as per the is operator.

Операторы даты Date operators

Операторы для значений datetime Datetime operators

Операторы для значений datetimezone Datetimezone operators

Операторы длительности Duration operators

Оператор может поддерживать не все сочетания значений. Not all combinations of values may be supported by an operator. Выражения, при вычислении которых возникают неопределенные условия оператора, обрабатываются как ошибки. Expressions that, when evaluated, encounter undefined operator conditions evaluate to errors. Дополнительные сведения об ошибках в M см. в разделе Ошибки. For more information about errors in M, see Errors

Пример ошибки: Error example:

Источник

Кен Пульс и Мигель Эскобар. Язык М для Power Query

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

Ken Puls and Miguel Escobar. M is for (Data) Monkey.[1] The Excel’s Pro’s Definitive Guide to Power Query. – Holy Macro! Books, 2015. – 308 p.

Язык м power query формулы

Скачать заметку в формате Word или pdf, примеры в формате архива

Содержание

Введение: революция в подготовке данных

Анализируя данные, мы, как правило, выполняем следующие действия:

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

Хотя Excel имеет мощный набор инструментов, чтобы выполнить бизнес-анализ, преобразование необработанных данных всегда было проблемой. На самом деле, именно на подготовку данных мы зачастую тратим большую часть времени. До сих пор основные усилия профессионалов Excel по извлечению и подготовке данных были направлены на изучение богатого арсенала формул Excel, языка VBA и SQL-запросов. Два последних метода используются немногими, так как требуют значительных инвестиций времени в их освоение.

Power Query решает проблему подготовки данных. Этот инструмент прост в освоении и имеет интуитивно понятный интерфейс. Каждое действие запоминается, а код при желании можно просмотреть и обновить. Когда дело доходит до импорта, очистки и преобразования данных, вы можете понять Power Query быстрее, чем освоить формулы Excel. С Power Query обрабатывать грязные данные проще, чем с VBA.

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

С Power Query вы можете загружать данные в одно из трех мест: листы Excel, модель данных Power Pivot, Power BI, или ограничиться подключением к источнику данных. Последний пункт может показаться странным, но он означает, что некоторые запросы могут быть использованы в качестве источника данных для других запросов.

Эта книга была написана с использованием Power Query версии 2.24, выпущенной в июле 2015 г.[2] Чтобы установить Power Query в Excel 2010 и Excel 2013 загрузите надстройку с сайта Microsoft. Начиная с Excel 2016 Power Query уже встроен.

Мы много лет изучали Power Query, а теперь и сами обучаем работе в этой программе.

Язык м power query формулы

Рис. 1. Кривая обучения Power Query

В версиях Excel 2010 и Excel 2013 команды Power Query расположены на отдельной вкладка Power Query. Начиная с версии Excel 2016 Microsoft зачем-то сжал все команды в группу Получить и преобразовать данные на вкладке Данные.

Язык м power query формулы

Рис. 2. Размещение команд Power Query в Excel 2016 и более поздних версиях

Оригинальные Excel-файлы, используемые в этой книге, доступны по ссылке.

Глава 1. Основы импорта данных

Бизнес-информация часто хранится в:

Процесс импорта файла с разделителями следует базовому процессу ETL (extract, transform and load – извлечение, преобразование и загрузка). Чтобы импортировать файл создайте новую (пустую) книгу Excel, перейдите на вкладку Данные. В области Получить и преобразовать данные кликните Из текстового/CSV-файла и выберите Ch01-Delimited.txt. Excel запускает окно предварительного просмотра:

Язык м power query формулы

Рис. 3. Окно предварительного просмотра Power Query; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Кликните на кнопке Изменить. Откроется окно редактора Power Query:

Язык м power query формулы

Рис. 4. Окно редактора Power Query

Данные отображаются в табличном формате с заголовками (1). Запрос автоматически получил имя (2), такое же, как и имя файла. В области Примененные шаги перечислены три действия (3). Если вы кликните на первом шаге – Источник, то увидите немного иное представление данных:

Язык м power query формулы

Рис. 5. Данные на первом шаге запроса

Power Query импортировал данные, проанализировал их и автоматически обработал:

Вы можете перемещаться по шагам, чтобы увидеть, что Power Query делал с данными. Чтобы сделать заголовки более читаемыми, их можно изменить. Выберите последний шаг в области Примененные шаги. Если этого не сделать, то ваши манипуляции попытаются записаться за тем шагом, на котором вы находитесь. А это не всегда возможно, и вносит некоторый хаос.

Кликните правой кнопкой мыши на заголовке столбца TranDate, выберите Переименовать, удалите часть заголовка Tran. Переименуйте последний столбец (рис. 6). Если вы внимательно следили за областью Примененные шаги, то заметили, что при переименовании столбца TranDate был создан новый шаг (1). Однако переименование столбца Sum of Amount не привело к созданию еще одного шага. Вместо этого два шага были объединены вместе, что хорошо видно в коде (2). Power Query пытается сохранить эффективность путем слияния подобных операций при построении запроса.

Язык м power query формулы

Рис. 6. Переименование столбцов привело к созданию четвертого шага

Power Query также позволяет изменить тип данных, выбранных автоматически. Если, например, столбцы Account и Dept должны быть отформатированы как текст:

Язык м power query формулы

Рис. 7. Изменение типа данных

Поля Account и Dept будут выровнены влево, указывая на то, что они отформатированы как текст, а не значения, в соответствии со стандартами Excel. В поле Примененные шаги появится пятый шаг – Измененный тип 1.

Язык м power query формулы

Рис. 8. Значения в столбцах Account и Dept выровнены влево

Почему же изменение типа не слились с аналогичным на третьем шаге? Power Query не знает, был ли автоматический третий шаг важным, поэтому реагирует безопасным способом и создает новый шаг. Кстати, если теперь вы попробуете выделить столбец Dept и изменить тип данных назад на Целое число, то Power Query «сообразит», что вы (а не автомат) делаете что-то странное, и предложит отменить предыдущее изменение:

Язык м power query формулы

Рис. 9. Предупреждение, что более раннее действие, возможно является лишним

Присвойте запросу говорящее имя: просто отредактируйте текст в поле Имя. Теперь можно завершить запрос загрузкой. Если кликнуть на кнопку Закрыть и загрузить на вкладке Главная, данные по умолчанию загружаются в Таблицу на лист Excel. Имя Таблицы совпадет с именем запроса. Справа появится панель Запросы и подключения. В ней предоставлена ключевая информация о запросах. Например, количество загруженных строк и обнаруженных ошибок. Из этой панели удобно находить запросы и управлять ими: редактировать, удалять, обновлять.

Язык м power query формулы

Рис. 10. Загрузка запроса в Таблицу на лист Excel

Запрос может показать наличие ошибок (цифра 1 на рис. 10). Не переживайте: это означает, что региональные настройки не соответствуют формату данных в файле, подготовленных для США. В частности, дата представлена в формате ММ.ДД.ГГГГ. Поэтому Power Query используя российские региональные настройки, счел, что значение 12.13.2009 является ошибкой, и не загрузил данные в Excel. Позже мы покажем, как решить эту проблему.

В будущем вы можете обновить данные одним из следующих методов:

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

Если вы закрыли панель Запросы и подключения, ее можно снова отобразить пройдя по меню Данные –> Запросы и подключения.

Подключение к табличным данным в Excel. Из открытой книги Excel можно импортировать данные, хранящиеся на листе, в именованном диапазоне, или в динамическом именованном диапазоне. В первом случае данные могут храниться не в Таблице, а в табличном формате (рис. 11). Данные содержатся в файле с примерами Excel Data.xlsx.

Язык м power query формулы

Рис. 11. Импортируемые данные находятся в табличном формате

Чтобы отправить эти данные в Power Query кликните на любую ячейку в диапазоне (1 на рис. 12), перейдите на вкладку Данные (2) и нажмите кнопку Из таблицы/диапазона (3).

Язык м power query формулы

Рис. 12. Поместить данные из таблицы в запрос Power Query

Excel откроет окно Создание таблицы (рис. 13), и предложит подтвердить 1) границы таблицы и 2) включает ли набор данных заголовки. После внесения изменений (если таковые требуются) и подтверждения, Excel превратит выбранный диапазон в Таблицу и откроет окно редактора Power Query. Если на листе данные уже содержались в Таблице, Excel сразу запустит редактор Power Query.

Язык м power query формулы

Рис. 13. Окно Создание таблицы

Обратите внимание (рис. 14):

Язык м power query формулы

Рис. 14. Небольшие отличия в первых шагах работы Power Query

При нажатии кнопки Закрыть и загрузить Excel создаст таблицу на новом листе, используя имя запроса. Однако это имя конфликтует с именем существующей таблицы в Excel. Тогда к имени новой таблицы будет добавлено подчеркивание и число (рис. 15). Чтобы управлять процессом наименования, прежде чем нажать кнопку Закрыть и загрузить измените имя таблицы в окне редактора Power Query с Таблица1 на FromTable. И лишь затем нажмите кнопку Закрыть и загрузить.

Язык м power query формулы

Рис. 15. Данные из запроса загружаются на новый лист

Поскольку новая таблица ничем не отличается от ранее имевшейся в Excel, то такое действие не имеет самостоятельного смысла. Мы показали эту процедуру просто для иллюстрации того, как подключиться и загрузить данные из таблицы Excel.

Подключение к именованным диапазонам. Если вы не хотите, чтобы стиль таблицы применялся к вашим данным, можете подключиться к диапазону Excel. В качестве примера используйте лист NamedRange приложенного файла Excel. Данные в нем идентичны данным в предыдущем примере (см. рис. 11). Чтобы подключиться к именованному диапазону сначала определите именованный диапазон, а затем создайте новый запрос.

Итак, выберите ячейки A5:F42 (рис. 16), перейдите в поле Имя, введите Date, нажмите Enter.

Язык м power query формулы

Рис. 16. Определение именованного диапазона

После определения имени, диапазон можно выбрать с помощью стрелки раскрывающей список имен (рис. 17). Независимо от того, где вы находитесь в книге, вы перейдете на этот лист, и данные в названном диапазоне будут выбраны.

Язык м power query формулы

Рис. 17. Доступ к данным через имя диапазона

Убедитесь, что выбран весь именованный диапазон и его имя отображается в поле Имя. Создайте новый запрос, пройдя по меню Данные –> Из таблицы/диапазона. Power Query не создаст Таблицу из исходных данных, а будет ссылается на именованный диапазон. Интерфейс Power Query выглядит так же, как при импорте файлов с разделителями, а не как при подключении к таблице Excel. Одной из особенностей Таблиц Excel является то, что они имеют строку заголовка. Для именованного диапазона заголовок необязателен. Power Query подключается к источнику данных и выполняет его анализ, чтобы выяснить, как обрабатывать данные. Подобно текстовым файлам с разделителями, он определяет строку, которая выглядит как заголовки, повышает ее и применяет типы данных к столбцам.

Имя запроса по умолчанию – это имя именованного диапазона. Опять же, хороший стиль –изменить имя, иначе Excel сам добавит символы, чтобы избежать дублирования имен.

Динамические именованные диапазоны. Таблицы Excel автоматически расширяются по вертикали и горизонтали по мере добавления новых данных. Это замечательная особенность Таблиц. Но, с другой стороны, Таблицы автоматически форматируются. Можно создать динамический именованный диапазон, который расширяется по мере роста данных.

На листе Dynamic Excel-файл вы найдете еще одну копию исходных данных. Настройте динамический именованный диапазон, который будет расширяться при добавлении новых записей:

Язык м power query формулы

Рис. 18. Создание динамического диапазона

Теперь вы можете ссылаться на этот именованный диапазон в Power Query, но поскольку это динамический диапазон, вы не можете выбрать его в поле Имя в Excel. А раз вы не можете выбрать его, как вы можете поместить его в Power Query? Трюк в том, чтобы создать пустой запрос, а затем указать Power Query, к какому диапазону вы хотите подключиться. Пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос (рис. 19). Откроется окно редактора Power Query. Если строка формул не отображается, перейдите на вкладку Просмотр и установите флажок Строка формул.

Язык м power query формулы

Рис. 19. Создание пустого запроса

В строке формул введите: =Excel.CurrentWorkbook(). Нажмите Enter. Отобразится таблица со списком всех объектов этой книги Excel, к которым можно подключиться:

Язык м power query формулы

Рис. 20. Список всех объектов, которые Power Query видит в текущей книге Excel

Кликните на поле Table в строке DynamicRange. Power Query загрузит динамический диапазон:

Язык м power query формулы

Рис. 21. Динамический диапазон в редакторе Power Query

Если посмотреть на перечень примененных шагов, то можно увидеть, что Power Query

Вам осталось присвоить запросу имя FromDynamicRange, кликнуть на кнопку Закрыть и загрузить, чтобы поместить данные из запроса на новый лист Excel.

Общие замечания. Если это возможно, предпочитайте решения на основе Таблиц Excel. Они проще в настройке и последующем обслуживании. К динамическим диапазонам обращайтесь если только вы точно понимаете, помечу не подходят Таблицы.

[1] Язык назвали М, в частности, потому, что буквы с A по L уже были заняты)) А название книги отсылает к английскому букварю, в котором букве М соответствует изображение обезьянки – M is for Monkey

Язык м power query формулы

[2] Перевод и скриншоты выполнены в Excel 2019 и Excel 365 на 64-разрядной версии 2.61. – Прим. Багузина

[3] Указанная формула поддерживает добавление новых строк, но не новых столбцов. Еще больше возможностей доступно с использованием функции СМЕЩ.

Источник

Видео

Power Query Продвинутый №1. Введение в Язык М 1

Power Query Продвинутый №1. Введение в Язык М 1

Power Query: Зачем нужно понимать язык M?

Power Query: Зачем нужно понимать язык M?

Power Query Tips&Tricks - Язык М (M Language)

Power Query Tips&Tricks - Язык М (M Language)

Лучшие практики по работе с PowerQuery язык M

Лучшие практики по работе с PowerQuery язык M

Power Query Продвинутый №2. Введение в Язык М 2

Power Query Продвинутый №2. Введение в Язык М 2

Power Query: знакомство с языком M

Power Query: знакомство с языком M

Power Query: нестандартная группировка с применение языка M (по мотивам вопроса ученицы)

Power Query: нестандартная группировка с применение языка M (по мотивам вопроса ученицы)

Формулы+Сводные vs. Power Query (разбор задачи с собеседования)

Формулы+Сводные vs. Power Query (разбор задачи с собеседования)

Power Query Продвинутый №3. Уровни владения Языком М

Power Query Продвинутый №3. Уровни владения Языком М

Power Query Язык М №1. Простые выражения, let

Power Query Язык М №1. Простые выражения, let
Поделиться или сохранить к себе:
Добавить комментарий

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