Продолжаем разбираться с языком M. В прошлой статье мы говорили об основных принципах написания команд. А здесь рассмотрим подробнее, как с помощью языка M в Power Query и Power BI работать со списками, записями, таблицами. А также как создавать собственные функции.
Первая часть статьи: Язык M для Power Query: введение
Списки в Power Query
Список (list) – упорядоченная последовательность значений.
Фигурные скобки { и } обозначают начало и конец списка.
Примеры списков:
{1, 2, 3} – список из чисел,
{ 1 .. 365 } – список из чисел от 1 до 365,
{ 1, 5 .. 8, 11 } – числа 1, 5, 6, 7, 8, 11,
{ } – пустой список,
{ {1, 2, 3}, { 4 .. 6 } } – список из нескольких списков.
Список можно преобразовать в таблицу в меню Средства для списков → Преобразование → В таблицу.
Функции для создания списков
С помощью следующих функций вы можете создать списки в Power Query.
1. List.Numbers
Возвращает список чисел для заданного исходного значения, количества значений и значения приращений.
Синтаксис:
List.Numbers ( start, count, optional increment )
start – исходное значение в списке (number),
count – количество значений, которое требуется создать в списке (number),
optional increment (необязательно) – шаг увеличения (number).
Пример: список из 10 чисел, начинающийся с 1, с шагом увеличения +2
= List.Numbers ( 1, 10, 2 )
Получится список { 1, 3, 5, 7, 9, 11, 13, 15, 17, 19 }
2. List.Dates
Возвращает список дат с заданным числом значений, начиная с даты начала, с указанным шагом приращения #duration.
List.Dates ( start, count, step )
start – дата начала (тип date),
count – количество дат в списке (number),
step – шаг приращения (duration).
Пример: создание списка из пяти дат, начиная с 31.12.2011, с шагом роста в один день.
= List.Dates ( #date(2011, 12, 31), 5, #duration(1, 0, 0, 0) )
Получится список:
31.12.2011
01.01.2012
02.01.2012
03.01.2012
04.01.2012
3. Table.ToList
Преобразует таблицу в список с помощью функции объединения для каждой строки таблицы.
Table.ToList ( table, optional combiner )
table – таблица для преобразования (тип table),
optional combiner – функция, которая применяется к строке таблицы для создания единичного значения (nullable function).
Пример:
= Table.ToList (
Table.FromRows ( {
{ Number.ToText (1), "Иванов", "123-4567" },
{ Number.ToText (2), "Петров", "987-6543" },
{ Number.ToText (3), "Сидоров", "543-7890" } } ),
Combiner.CombineTextByDelimiter( ", " )
)
Получится список:
4. Table.Column
Возвращает в виде списка столбец данных из указанного столбца таблицы.
Table.Column ( table, column )
table – таблица (тип table),
column – столбец (text).
Пример:
= Table.Column (
Table.FromRecords (
{ [ CustomerID = 1, Name = "Иванов", Phone = "123-4567" ],
[ CustomerID = 2, Name = "Петров", Phone = "987-6543" ],
[ CustomerID = 3, Name = "Сидоров", Phone = "543-7890" ] } ),
"Name" )
Получится список:
Функции обработки списков
К спискам можно применить функции – определить минимальное, максимальное значение, число строк и так далее.
| Функция | Описание |
| List.Average | Возвращает среднее значение для элементов в списке. Результат вычисления будет с тем же типом данных, что и значения в списке. |
| List.Count | Определяет число элементов в списке. |
| List.Max | Возвращает максимальное значение в списке (или необязательное значение, если список пуст). |
| List.Min | Возвращает минимальное значение в списке (или необязательное значение, если список пуст). |
| List.Mode | Определяет элемент, который чаще всего появляется в списке. Если несколько элементов появляются одинаково часто, выбирается последний из них. |
| List.Product | Рассчитывает произведение чисел в списке, отличных от null. |
| List.Select | Возвращает список значений из заданного списка, которые удовлетворяют условию отбора. |
| List.Sort | Сортирует список по необязательным указанным критериям. |
| List.StandardDeviation | Возвращает оценку стандартного отклонения значений в списке. |
| List.Sum | Возвращает сумму всех значений в списке, отличных от null. |
Записи в Power Query
Запись (record) – это упорядоченная последовательность полей, где каждое поле имеет имя и одно значение.
Примеры записей:
[ x = 1, y = 2 ]
[ firstname = "Иван", lastname = "Иванов" ]
[ Sales2016 = 1000, Sales2017 = 1100, TotalSales = Sales2016 + Sales2017 ]
[ ] – пустая запись
Запись можно преобразовать в таблицу в меню Средства для записей → Преобразовать → В таблицу.
Формулы для создания записей — Record.FromList, Record.FromTable.
Например, запись
= Record.FromList ( {1, 2}, {"a", "b"} )
– то же самое, что и
= [ a = 1, b = 2 ]
Функции обработки записей
Для анализа данных записей можно использовать функции Power Query, например:
| Функция | Описание |
| Record.FieldCount | Определяет число полей в записи |
| Record.FieldNames | Возвращает названия полей в записи в виде текста |
| Record.ReorderFields | Изменяет порядок полей в записи |
| Record.ToTable | Преобразует запись в таблицу |
Обращение к данным в списках и записях
Записи могут входить в списки. К элементу в списке можно обратиться с помощью числового индекса, указанного в фигурных скобках { }. В Power Query порядковые номера записей начинаются с нулевого индекса, а не с первого. То есть индекс 0 используется для ссылки на 1-ое значение, а индекс 1 — на 2-ое значение в списке:
[ Продажи =
{ [ Год = 2016, квартал1 = 1000, квартал2 = 1100, Всего = квартал1 + квартал2 ],
[ Год = 2017, квартал1 = 1200, квартал2 = 1300, Всего = квартал1 + квартал2 ] },
ВсегоПродажи = Продажи{0}[Всего] + Продажи{1}[Всего] ]
Так выглядит результат выполнения этого шага запроса:
Если вы используете индекс, которого нет в списке, например Продажи{3}[Всего], то будет возвращена ошибка. Но если добавить оператор ? в конец выражения — Продажи{3}[Всего]?, вместо ошибки вы получите пустое значение null.
Примеры обращения к элементам списков:
{1, [A=2], 3} {1}? — результат [A=2]
{true, false} {2}? — null
Примеры обращения к записям:
[A=1, B=2] [B] — результат 2
[A=1, B=2] [C]? — null
[A=1, B=2] [[B]] — [B=2]
[A=1, B=2] [[B], [C]]? — [B=2,C=null]
Таблицы в Power Query
Работая с Power Query, в большинстве случаев вы взаимодействуете с таблицами. Чаще всего именно к таблицам вы подключаетесь, обрабатываете и возвращаете как результат выполнения запроса. Давайте разберемся, как создать таблицу, обратиться к её данным и преобразовать с помощью языка M.
В Power Query можно подключиться к таблицам или создать с помощью функций и операторов, приведенных ниже.
1. #table
С помощью #table можно создать таблицу, указав список имен заголовков и список строк. Например:
= #table ( {"Подразделение", "Продажи"}, {{"Москва", 1000}, {"Омск", 200} } )
создаст таблицу:
С помощью #table также можно задать типы данных:
= #table ( type table [Подразделение = text, Продажи = number], { {"Москва", 1000}, {"Омск", 200} } )
2. Table.FromRows
Table.FromRows создает таблицу из списка с элементами значений для одной строки. Дополнительно можно указать наименования или число столбцов, тип данных, например:
= Table.FromRows (
{ { 1, "Покупатель 1", "123-4567" }, { 2, "Покупатель2", "987-6543" } },
{"CustomerID", "Name", "Phone"} )
3. Table.FromRecords
Создает таблицу из списка записей. Пример:
= Table.FromRecords (
{ [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"] } )
Дополнительно можно задать тип данных:
= Table.FromRecords ( { [CustomerID=1, Name="Bob"] },
type table[ CustomerID = Number.Type, Name = Text.Type ] )
Формулы для работы с данными в таблицах
Большинство операций по обработке данных вы можете выполнить с помощью меню Power Query. Ниже приведены некоторые примеры функций, с помощью которых можно проанализировать данные таблиц.
| Функция | Описание |
| Table.RowCount | Определяет количество строк в таблице. |
| Table.ColumnCount | Определяет количество столбцов в таблице. |
| Table.Group | Группирует строк таблицы по значениям в указанном ключевом столбце для каждой строки. Для каждой группы создается запись, содержащая ключевые столбцы и их значения вместе со всеми агрегированными столбцами. |
| Table.Sort | Сортирует таблицу, используя список из одного или нескольких имен столбцов и необязательного параметра comparisonCriteria |
| Table.FindText | Выбирает из таблицы строки с указанным текстом. Если текст не найден, возвращается пустая таблица. |
| Table.First | Возвращает первую строку из таблицы или необязательное значение, если таблица пуста. |
| Table.LastN | Возвращает последние строки таблицы в зависимости от указанного значения. Если значение – число, то будет возвращено соответствующее число строк. Если указано условие, то строки возвращаются в порядке возрастания до тех пор, пока не будет встречена строка, не соответствующая условию. |
| Table.Repeat | Возвращает таблицу со строками, повторенными указанное число раз. |
| Table.Column | Возвращает указанный столбец данных из таблицы в виде списка. |
| Table.PromoteHeaders | Назначает первую строку таблицы в качестве новых заголовков столбцов. |
| Table.Max | Определяет наибольшую строку в таблице, исходя из критериев. |
| Table.Min | Определяет наименьшую строку в таблице, исходя из критериев. |
| Table.Buffer | Помещает таблицу в буфер памяти, изолируя ее от внешних изменений во время оценки. |
Обращение к данным в таблицах
Для подключения к данным таблиц можно использовать следующие функции языка M.
| Формула | Описание |
| Excel.CurrentWorkbook | Дает доступ к объектам в текущем файле: форматированные таблицы excel, именованные диапазоны, связи рабочей книги. Например, = Excel.CurrentWorkbook(){[Name= "Sales"]}[Content]обращается к содержимому таблицы Sales в текущей книге. |
| Excel.Workbook | Выводит запись из листов в книге excel. |
Пользовательские функции в Power Query
Функция (function) преобразует набор входящих значений в одно результирующее. Функция записывается c перечислением параметров в круглых скобках ( ), далее следует знак перехода => а затем выражение, определяющее функцию.
Для выполнения функции ей передают набор входящих параметров. Пример функции, которая складывает три числа:
= [ MyFunction = (x, y, z) => x + y + z,
Result = MyFunction (1, 2, 3) ]
Запишем это же выражение как запрос в расширенном редакторе с помощью оператора let и зададим типы данных.
let
MyFunction = (x as number, y as number, z as number) as number => x + y + z,
Result = MyFunction(1, 2, 3)
in
Result
В редакторе можно увидеть шаги созданного запроса:
В примере выше передали функции входные параметры и тут же вычислили результат. Запишите тот же запрос без передачи ему исходных параметров, и назовите его MyFunction:
let
MyFunction = (x as number, y as number, z as number) as number
=> x + y + z
in
MyFunction
Этот запрос будет иметь тип данных function — функция, и далее его можно будет использовать в любом другом запросе в книге. Например, в следующем запросе создан столбец «сумма», который суммирует продажи с помощью созданной функции MyFunction:
let
Источник = Excel.CurrentWorkbook(){[Name= "продажи"]}[Content],
Суммирование = Table.AddColumn ( Источник, "сумма", each MyFunction ( [1 квартал], [2 квартал], [3 квартал] ) )
in
Суммирование
Упрощение выражений с each и _
В предыдущем запросе при суммировании мы использовали оператор each для обозначения того, что каждое значение в указанных столбцах таблицы должно быть просуммировано. Оператор each и знак нижнего подчеркивания _ используются в запросах для упрощения. Так, например, записанные ниже выражения эквивалентны:
each _ + 1
(_) => _ + 1
В примере ниже с помощью функции Table.TransformColumns и оператора each, увеличиваем на 1,05 значение в столбце «3 квартал»:
let
Источник = Excel.CurrentWorkbook(){[Name= "продажи"]}[Content],
Увеличение5пр = Table.TransformColumns(Источник, {"3 квартал", each _ * 1. 05})
in
Увеличение5пр
Рекурсивные функции @
Это функции, вызывающие сами себя в тексте с помощью знака @. Например, с помощью рекурсивной функции рассчитан факториал:
let
Factorial = (x) =>
if x = 0 then 1 else x * @Factorial(x — 1),
Result = Factorial(3) // Факториал 3! = 1 · 2 · 3 = 6
in
Result
Рекурсивные функции могут использоваться для создания сложных расчетов.
Вот краткие инструкции по использованию языка M. Чтобы освоить их и понять, просто открывайте Power Query в Excel или в Power BI и экспериментируйте. Если вы овладеете этими командами и методами построения формул, вы существенно расширите свой арсенал и навыки по работе с запросами.
