Продолжаем разбираться с языком M. В прошлой статье мы говорили об основных принципах написания команд. А здесь рассмотрим подробнее, как с помощью языка M в Power Query и Power BI работать со списками, записями, таблицами. А также как создавать собственные функции.
Первую часть статьи можно прочитать по этой ссылке:
Списки в 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.
- 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 }
- 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 - 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( ", " ) )
Получится список:
- 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 | Преобразует запись в таблицу |
Обращение к данным в списках и записях
Записи могут входить в списки. Можно обратиться к элементу в списке с помощью числового индекса, указанного в фигурных скобках { }.
В отличие от обычного Excel, в Power Query порядковые номера записей начинаются с нулевого индекса, а не с первого. Например, индексы 0 и 1 используются для ссылки на 1-ое и 2-ое значение в списке:
[
Продажи =
{
[ Год = 2016, ПерваяПоловина = 1000, ВтораяПоловина = 1100,
Всего = ПерваяПоловина + ВтораяПоловина ],
[ Год = 2017, ПерваяПоловина = 1200, ВтораяПоловина = 1300,
Всего = ПерваяПоловина + ВтораяПоловина ]
},
ВсегоПродажи = Продажи{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.
Создание таблиц
Вы можете создать свою таблицу — без импорта данных из источников, с помощью функций и операторов, указанных ниже.
- #table
С помощью #table можно создать таблицу, указав список имен заголовков и список строк. Например:
#table({"Подразделение", "Продажи"}, {{"Москва", 1000}, {"Омск", 200} })
Создаст таблицу:
С помощью #table также можно задать типы данных:
#table( type table [Подразделение = text, Продажи = number], {{"Москва", 1000}, {"Омск", 200} }
)
- Table.FromRows
Создает таблицу из списка с элементами значений для одной строки. Дополнительно можно указать необязательные наименования столбцов, тип таблицы или число столбцов. Пример:
Table.FromRows( { { 1, "Покупатель 1", "123-4567" }, { 2, "Покупатель2", "987-6543" } }, {"CustomerID", "Name", "Phone"}
)
- 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 ] )
Обращение к данным в таблицах
Для подключения к данным таблиц можно использовать следующие функции языка M.
Формула | Описание |
Excel.CurrentWorkbook | Дает доступ к объектам в текущем файле: форматированные таблицы excel, именованные диапазоны, связи рабочей книги. Например,
обращается к содержимому таблицы SalesTable в текущей книге. |
Excel.Workbook | Выводит запись из листов в книге excel. |
Формулы для работы с данными в таблицах
Большинство операций по обработке данных вы можете выполнить с помощью меню 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 | Помещает таблицу в буфер памяти, изолируя ее от внешних изменений во время оценки. |
Пользовательские функции в Power Query
Функция (function) преобразует набор входящих значений в одно результирующее. Функция записывается путем перечисления параметров функции в круглых скобках ( ), за которыми следует знак перехода => далее указывают выражение, определяющее функцию.
Для выполнения функции ей передают набор входящих параметров.
Например:
[
MyFunction = (x, y, z) => x + y + z, // записали функцию, которая складывает три числа
Result = MyFunction(1, 2, 3) // передали функции цифры 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
В редакторе в режиме просмотра можно увидеть шаги созданного нами запроса:
Шаг 1: MyFunction
Шаг 2: 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пр
Использование одного запроса в качестве источника данных для другого запроса
Вы можете использовать созданный запрос как источник данных в другом запросе, сославшись на его имя. Например, если сохранить записанный выше запрос с именем ПродажиУвеличение5пр, мы можем обратиться к нему:
let
Источник = ПродажиУвеличение5пр
in
Источник
А также использовать более сложные связки, например:
let
Источник = Excel.CurrentWorkbook(){[Name= "продажи"]}[Content],
Отбор = Table.SelectRows(Source, each ([Город] = ПродажиУвеличение5пр[Город]{0}))
in
Отбор
Рекурсивные функции @
Это функции, вызывающие сами себя в тексте с помощью знака @. Например, с помощью рекурсивной функции рассчитан факториал:
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 Desktop и экспериментируйте.
Если вы овладеете этими командами и методами построения формул, вы существенно расширите свой арсенал и навыки по работе с запросами.