Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. В главах 4 и 5 мы познакомились со значениями и типами данных. Структурированные значения являются довольно сложными и требуют дополнительного внимания. В отличие от примитивных значений, структурированные могут включать в себя множество других значений. Эта сложность позволяет им хранить ряд примитивных значений или даже других структурированных значений, открывая широкий спектр возможностей для манипулирования данными и их анализа. Важность структурированных значений в языке М такова, что мы посвящаем им целую главу.
Мои комментарии набраны с отступом.
Предыдущая глава Содержание Следующая глава
Скачать заметку в формате Word или pdf
Для лучшего усвоение материала выполняйте упражнения в Power BI Desktop или Excel. Скачайте упражнения к главе 6 с репозитория GitHub.
Структурированные значения можно рассматривать как контейнер, который содержит одно или несколько значений. Контейнеры организованы так, что мы можем выполнять над ними преобразования. Каждое структурированное значение, будь то список, запись, таблица или функция, имеет свои уникальные характеристики и области применения, и понимание того, когда использовать каждое из них, помогает в написании эффективного кода.
Списки
Список содержит последовательность значений любого типа, разделенных запятыми. Элементами списка могут быть примитивные значения (текст, число, дата, …) и структурированные значения (список, запись или таблица).
Почему мы начинаем наш обзор со списков? Дело в том, что списки широко используются в Power Query. Например, при выборе столбца его значения возвращаются в виде списка. Когда функция на вход принимает несколько значений (например, List.Count) или возвращает несколько значений (например, Table.ColumnNames), вы увидите, что эти значения представлены списками. Давайте начнем с того, как создать список.
Создание (или инициализация) списка – это значения, разделенные запятыми, и взятые в фигурные скобки { }. Например, список из трех числовых значений: {1, 2, 3}.
Рис. 6.1. Инициализация списка в Power Query с помощью фигурных скобок { }
Список также может содержать значения разных типов: {1, "Hello", true}. Элементами списка могут быть структурированные значения. Следующее выражение возвращает список, содержащий текст, список, запись и таблицу:
|
1 2 3 4 5 6 |
{ "a", {4,5,6}, [Column1 = 1, Column2 = "v"], #table( { "ID", "Product" }, {{ 1, "Apple" }} ) } |
Список может быть пустым, не содержать элементов: {}.
Подсчитать количество элементов в списке, можно с помощью функции List.Count:
|
1 2 |
List.Count( {1, 2, 3} ) // возвращает 3 List.Count( {} ) // возвращает 0 |
Обзор функций списка вы найдете на сайте powerquery.how.
Операторы списков
Мы кратко познакомились с операторами в главе 4 и показали, какие значения поддерживают те или иные операторы. Сейчас рассмотрим операторы, работающие со списками.
Рис. 6.1а. Операторы, поддерживаемые списками
Равно
Оператор равенства проверяет, идентичны ли значения элементов и порядок их следования:
|
1 2 |
{1, 2, 3} = {1, 2, 3} // возвращает true {1, 2, 3} = {3, 2, 1} // возвращает false |
Оператор равенства работает независимо от того, содержит ли список примитивные или структурированные значения (например, вложенные списки):
|
1 |
{ {1,"a","b"}, 2, 3} = { {1,"a","b"}, 2, 3} // возвращает true |
Пример использования – сравнение имен столбцов в двух таблицах. При добавлении таблиц с помощью функции Table.Combine объединяются столбцы с одинаковыми именами. Перед добавлением таблиц полезно проверить, совпадают ли в них имена столбцов. Поскольку при проверке равенства важен порядок значений, предварительно отсортируйте имена столбцов:
|
1 2 3 4 5 6 7 |
Запрос 6.1 let ColumnNamesTable1 = List.Sort(Table.ColumnNames(Table1)), ColumnNamesTable2 = List.Sort(Table.ColumnNames(Table2)), ColumnsAreEqual = ColumnNamesTable1 = ColumnNamesTable2 in ColumnsAreEqual |
Запрос 6.1 вернет true, если два списка равны.
Не равно
Оператор не равно проверяет, отличаются ли два списка:
|
1 |
{1, 2, 3} <> {3, 2, 1} // возвращает true |
Конкатенация
Оператор конкатенации объединяет два списка, добавляя один список в конец другого:
|
1 |
{1, 2} & {4, 5} // возвращает {1, 2, 4, 5} |
Альтернатива – функция List.Combine. Она принимает список списков, которые нужно объединить:
|
1 |
List.Combine( { {1, 2}, {4, 5} } ) // возвращает {1, 2, 4, 5} |
Объединение списков полезно в различных сценариях. Например, вы хотите выбрать все цифры и буквы из строки (удалить все иные символы). Вместо того чтобы инициализировать списки для отбора вручную, вы можете сгенерировать диапазон значений и объединить их.
Предположим, у вас есть следующая строка:
|
1 |
"inv-1006-!**!-(act)" |
Создайте два списка значений:
|
1 2 |
{ "а".."z" } генерит список строчных букв { "0".."9" } генерит список чисел |
В этих двух выражениях используется конструкция диапазона (AB-construct), которую мы обсудим далее в этой главе. Для объединения сгенерированных списков и извлечения значений из строки можно использовать следующий код:
|
1 2 3 4 5 6 7 8 |
Запрос 6.2 let LowercaseLetters = {"a" .. "z"}, Numbers = {"0" .. "9"}, CombinedCharacters = LowercaseLetters & Numbers, SelectCharacters = Text.Select("inv-1006-!**!-(act)", CombinedCharacters) in SelectCharacters // возвращает "inv1006act" |
Объединение с null (coalesce)
Оператор объединения с null помещается между двумя значениями: основным (слева) и резервным (справа). Если левое значение не равно null, оно возвращается в качестве результата. Если левое значение равно null, оператор возвращает резервное значение. Например:
|
1 |
{ 1, 2, 3 } ?? null // возвращает {1, 2, 3} |
Поскольку левое значение (список) не равно null, оператор объединения с null сохраняет его. Однако, если поменять порядок…
|
1 |
null ?? {1, 2, 3} |
… выражение вернет {1, 2, 3}. Такое поведение гарантирует результат, отличный от null, что полезно в сценариях с потенциальными значениями null.
Представьте себе каталог продукции со столбцом Features (Характеристики). Если для некоторых продуктов столбец пуст, вы можете вернуть список характеристик по умолчанию:
|
1 |
each [Features] ?? {"Feature 1", " Feature 2"} |
Таким образом, встреча со значением null в столбце Features приводит к возврату стандартного списка объектов. Такой подход гарантирует единообразие в описаниях товаров, предоставляя характеристики по умолчанию там, где отсутствуют конкретные значения.
Рассмотрим, как генерировать списки значений.
Способы создания списка
Первый способ создания списка – инициализировать его. Например, можно задать значения различных типов:
|
1 |
{ 1, true, #date( 2023, 12, 31 ) } |
Список можно создать с помощью функций-генераторов: List.Dates, List.DateTimeZones, List.DateTimes, List.Durations, List.Numbers, List.Times. Все они принимают три аргумента: начальное значение, количество элементов и приращение, которое добавляется на каждом шаге. Например:
|
1 2 3 4 |
List.Numbers( 1, 5, 1 ) // возвращает { 1, 2, 3, 4, 5 } List.Numbers( 1, 5, 2 ) // возвращает { 1, 3, 5, 7, 9 } List.Dates( #date(2023,12,31), 5, #duration(1,0,0,0) ) /* возвращает список пяти дат, начиная с 31 декабря 2023 года, увеличивая каждую на день */ |
Если вы хотите создать список случайных чисел, используйте функцию List.Random:
|
1 |
List.Random( 3 ) // возвращает три случайных числа в диапазоне от 0 до 1 |
В языке M много других функций, которые возвращают списки:
- Имена полей. Функции, возвращающие имена полей, найденные в таблицах (ColumnNames, Table.ColumnsOfType) или записях (Record.FieldNames).
- Изменение структурированных значений. Вы можете преобразовать таблицу в список (ToColumns, Table.ToRecord, Table.ToRows или Table.ToList), или измените форму записи (Record.ToList).
- Разбиение текста. При работе со строкой функции Split и Text.SplitAny разделяют строку, на основе разделителя, возвращая результат в виде списка.
Есть также две мощные функции, которые создают список с пользовательской логикой – List.Accumulate и List.Generate. Мы рассмотрим их в главе 13 Итерация и рекурсия.
Ссылка на столбец таблицы
Еще один элегантный способ вернуть список – сослаться на столбец таблицы:
Рис. 6.2. Таблица с тремя столбцами
Если шаг Power Query, возвращающий эту таблицу, называется Source, можно вернуть значения столбца Product в виде списка с помощью следующего выражения:
|
1 |
= Source[Product] |
Рис. 6.2а. Столбец таблицы в виде списка
Этот процесс, известный как выбор поля (field-selection), будет подробно рассмотрен ниже.
С использованием синтаксиса a..b
Cписки можно генерить с помощью оператора a..b. Это особенно полезно, когда требуется создать список последовательных цифр, букв, дат или специальных символов. В своей простейшей форме синтаксис a..b генерирует ряд значений в указанном диапазоне чисел:
|
1 |
{1..10} // возвращает {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} |
Такой список пригодится для циклов, итераций, генерации данных и создания календарей. Конструкцию можно использовать и для создания прерывистого ряда значений:
|
1 2 |
{ 1..3, 11..13 } // возвращает { 1, 2, 3, 11, 12, 13} { -1..1, 5, 8..10 } // возвращает { -1, 0, 1, 5, 8, 9, 10 } |
Функция генерит только возрастающие списки. Попытка создать убывающий список вернет пустой список:
|
1 |
{10..1} // возвращает {} |
Чтобы создать убывающий список, воспользуйтесь функциями List.Reverse или List.Numbers:
|
1 2 |
List.Reverse( {1..10} ) List.Numbers( 10, 10, -1 ) |
Возрастающий список может включать не только числа, но и буквы:
|
1 |
{"а".."f"} // возвращает {"a", "b", "c", "d", "e", "f} |
Но помните, что язык M чувствителен к регистру. Синтаксис…
|
1 |
{"A".." F"} // возвращает список заглавных букв алфавита: {"A", "B", "C", "D", "E", "F"} |
Чтобы понимать, как работает синтаксис a..b, важно знать, что происходит «за кулисами». Когда Power Query генерирует списки, он использует символы Unicode, независимо от того, указали вы число, букву или заглавную букву. Например, если бы вы задали…
|
1 |
{ "Y" .. "a"} |
… то получили …
|
1 |
{ "Y", "Z", "[", "\", "]", "^", "_", "`", "a" } |
Буква "Y" соответствует Юникоду 89, "a" – 98. Убедиться в этом можно, написав:
|
1 2 |
Character.ToNumber( "Y" ) // возвращает 89 Character.ToNumber( "a" ) // возвращает 97 |
Таким образом, чтобы полностью понять последовательность, в которой генерируются символы, полезно обратиться к стандарту Юникода.
Доступ к элементам списка
Доступ к элементам списка известен также как выбор. В языке M для выбора используются те же фигурные скобки, что и при определении списка. И это сбивает с толку. Чтобы извлечь элемент из списка по его позиции, укажите порядковый номер в фигурных скобках после списка. Помните, что списки в M индексируются от нуля, первый элемент в списке имеет индекс 0:
|
1 2 |
{10, 20, 30, 40, 50}{0} // возвращает 10 {10, 20, 30, 40, 50}{4} // возвращает 50 |
Фигурные скобки здесь выполняют двойную роль. Первый набор создает список, второй – определяет индекс значения, которое мы хотим извлечь. Имейте в виду, что оператор выбора всегда содержит одно число – индекс.
Того же результата мы можем добиться с помощью функции List.Range:
|
1 |
List.Range( { 1, 2, 3}, 1, 1 ) // возвращает 2 |
Концепция отбора идет еще дальше. При работе с вложенными списками можно выбирать значения из нескольких слоев:
|
1 2 |
{{1,2,3}, "А", "В"}{0} // возвращает {1, 2, 3} {{ 1,2,3}, "a", "b"}{0}{1} // возвращает 2 |
Что произойдет, если мы попытаемся получить элемент с индексом, которого нет в списке?
|
1 |
{10, 20, 30, 40, 50}{5} |
Power Query вернет Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.
Чтобы предотвратить ошибку, язык M предлагает оператор, который позволяет выбрать необязательный элемент. Добавьте вопросительный знак в конце выражения, пытающегося получить доступ к списку. Если позиции индекса нет в списке, выражение вернет значение null:
|
1 2 3 |
{10, 20, 30, 40, 50}{4}? // возвращает 50 {10, 20, 30, 40, 50}{5} // возвращает Expression error {10, 20, 30, 40, 50}{5}? // возвращает null |
В оригинале: «To prevent this error, the M language offers an operator that instructs optional item selection». На мой взгляд фраза не совсем корректная, так как оператор (?) строго вернет null, и задать иное значение невозможно.
Итак, теперь вы знаете, как создавать списки и получать доступ к их значениям. Изучим, когда списки полезны.
Общие операции со списками
Списки играют важную роль в широком спектре операций. Чтобы проиллюстрировать их использование, давайте рассмотрим несколько реальных примеров, показывающих применимость списков в задачах преобразования данных.
Предположим, вы работаете с таблицей, и хотите добавить столбец, в котором по каждой строке указать максимальное значение, встречающееся в других столбцах. Передайте оцениваемые столбцы, как элементы списка в функцию List.Max:
|
1 |
List.Max( { [Column1], [Column2], [Column3] } ) |
Рис. 6.3. List.Max возвращает максимальное значение по нескольким столбцам
В следующем примере вы хотите оценить, соответствует ли значение в столбце Color одному из трех цветов. В языке M Power Query нет оператора IN, как в SQL или DAX. Однако его можно воспроизвести, предоставив список функции List.Contains:
|
1 2 |
Код 6.3 List.Contains( { "Blue", "Green", "Orange" }, [Color] ) |
Рис. 6.4. List.Contains отлично имитирует оператор IN
Код 6.3 проверяет, содержит ли столбец [Color] хотя бы один из указанных цветов. Это хороший пример того, как списки упрощают условную логику.
Перейдем к следующему сценарию, в котором вы имеете дело со строкой: ISBN: 978-3-16-148410-0 и хотите извлечь цифры с дефисами. Вы можете использовать синтаксис a .. b:
|
1 |
Text.Select( "ISBN: 978-3-16-148410-0", {"0".."9","-"} ) |
Здесь список предоставляет набор символов, который вы хотите извлечь из строки.
Еще один вариант использования списков – предоставление значений для создания таблицы. Например, можно создать таблицу с одним столбцом из трех значений:
|
1 |
Table.FromList( { "Яблоко", "Груша", "Банан" } ) |
Наконец, списки широко используются в функции Table.Group при указании столбцов для группировки и определения агрегатов:
|
1 2 3 4 5 |
Table.Group( Source, {"Column1", "Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}} ) |
Список {"Column1", "Column2"} указывает, по каким столбцам группировать. Список {{"Count", each Table.RowCount(_), Int64.Type}} сообщает имя столбца группировки – Count, вид группировки – считать строки, и тип данных столбца группировки:
Рис. 6.5. Table.Group в аргументах использует списки
Эти примеры показывают роль списков в языке M. Понимание того, как манипулировать списками и использовать их, является важным навыком для того, чтобы улучшить программирование на M.
Как было показано ранее, списки могут содержать любое значение. Какие опции у вас есть при указании типа данных для списка? Об этом мы и поговорим далее.
Присвоение спискам типов данных
Как упоминалось в главе 5, каждое значение имеет связанный с ним тип данных. Это относится не только к примитивным значениям, но и к спискам и их содержимому. Опираясь на знания, полученные в главе 5, подробнее изучим типы данных списков.
Если список содержит значения, принадлежащие одному типу данных, можно указать его при создании столбца. Полученный список можно включить в пользовательский столбец с помощью функции Table.AddColumn. В качестве примера изучим шаги по созданию списка, описанные ранее в этой главе:
Рис. 6.6. Добавление значений списка в столбец без присвоения типа данных
Функция Table.AddColumns не получила никакой конкретной инструкции в отношении типа данных, поэтому она создала столбец List типа any. Как следствие, когда вы разворачиваете столбец List командой Развернуть в новые строки, столбцы остаются типа any:
Рис. 6.7. Без заранее назначенного типа данных, при развертывании столбец остается с типом any
Хорошая новость заключается в том, что вы можете присвоить тип данных значениям списка. Для этого укажите необязательный аргумент columnType функции Table.AddColumn. Для списков, как и для других значений, можно указать любое доступное значение типа данных. Единственное отличие для списков заключается в том, что вы заключаете тип в фигурные скобки. Например, для списка с целыми числами, укажите:
Рис. 6.8. Добавление значений списка в столбец с назначенным типом данных
Когда вы развернете столбец List, он отобразится с типом целое число:
Рис. 6.9. В развернутом виде столбец List поддерживает ранее назначенный тип данных
Указание типа данных избавляет от необходимости посвятить отдельный шаг изменению типа данных столбца.
Мы описали, как действовать при создании нового столбца. Но что делать, если у вас уже есть таблица, содержащая столбец со списками?
Вернемся к ситуации на рис. 6.6 – вы создали столбец List, не определив тип данных элементов списка. Чтобы преобразовать тип столбца со значениями списка можно использовать код…
|
1 2 3 4 5 |
Table.TransformColumns( Source, {{"List", each _ ??_, type {Int64.Type}}} ) |
Важно знать, что функция Table.TransformColumn требует выполнения операции над базовым списком. Если этого не сделать, Table.TransformColumns не присвоит тип данных. Мы применили оператор объединения с null (??) к базовому значению. В данном случае эта операция не нужна, но благодаря ей функция также успешно присвоит тип данных.
Итак, основное преимущество присвоения типа списку заключается в том, что при развертывании списка на новые строки столбцу будет назначен тип данных.
Рассмотрев списки, перейдем к другому типу структурированных данных – записям.
Записи
Запись – это именованный список значений. Если списки – просто упорядоченная коллекция значений, запись связывает имена полей (ключей) со значениями. Это приводит к более сложной, но организованной структуре данных. Запись можно рассматривать как одну строку в таблице, где каждое поле в строке имеет уникальное имя столбца и значение. Как и списки, записи могут содержать значения любого типа: примитивные (текст, числа, даты…) или структурированные (списки, записи, таблицы).
Запись можно создать с помощью оператора инициализации записи. При этом:
- Запись заключается в квадратные скобки.
- За ключом (именем поля) следует знак равенства (=). Записи позволяют указывать имена полей без кавычек.
- Значение поля следует за знаком равенства.
- Пары ключ = значение разделяются запятыми.
Следующий код создает запись с данными о сотруднике:
|
1 |
[ Name = "John Doe", Age = 30, City = "Seattle" ] |
Рис. 6.10. Инициализация записи в Power Query с помощью []
Здесь Name, Age и City – ключи (имена полей), а "John Doe", 30 и "Seattle" – соответствующие значения полей. При создании записи каждое имя поля должно быть уникальным с учетом регистра. Так что следующая запись вернет ошибку Expression.Error: Имя «Age» определено несколько раз:
|
1 |
[ Name = "John Doe", Age = 30, Age = 31 ] |
Имя поля записи можно указывать без кавычек даже если в нем есть пробел. Например:
|
1 |
[ My Name = "John Doe" ] |
Подробнее см. Язык М Power Query. Переменные и идентификаторы.
Запись может содержать примитивные или структурированные значения. Пример записи с вложенным списком и записью:
|
1 2 3 4 |
[ Full Name = "John Doe", Ages = { 20, 30, 49 }, Name = [ Initials = "J", Last Name = "Doe" ] ] |
Рис. 6.11. Запись может содержать примитивные и структурированные значения
Можно создать пустую запись: [ ]. Чтобы подсчитать количество полей в записи, можно использовать функцию Record.FieldCount:
|
1 2 |
Record.FieldCount( [ Name = "John Doe", Age = 30 ] ) // возвращает 2 Record.FieldCount( [] ) // возвращает 0 |
Операторы записи
Записи позволяют использовать следующие операторы:
Рис. 6.11а. Операторы, используемые с записями
Равно
Оператор равенства (=) используется для оценки того, идентичны ли две записи. Язык M считает записи равными, когда:
- Количество имен полей идентично.
- Каждое имя поля существует в обеих записях.
- Порядок следования полей не важен.
- Имена полей и их значения одинаковые.
|
1 2 3 |
[ Name = "John", Age = 32 ] = [ Name = "John", Age = 32 ] // возвращает true [ Name = "John", Age = 32 ] = [ Age = 32, Name = "John" ] // возвращает true [ Name = "John", Age = 32 ] = [ Name = "Jane", Age = 32 ] // возвращает false |
Помните, что сравнение чувствительно к регистру как для имен полей, так и для значений.
Как это можно использовать? В следующем сценарии мы начинаем со списка записей RecordsList, в которых порядок полей отличается. Функция List.Distinct удаляет повторяющиеся записи, а CountRecords – вычисляет количество уникальных записей, оставшихся в списке. Результат (1) показывает, что порядок полей в записях при определении их равенства не имеет значения.
|
1 2 3 4 5 6 7 8 9 10 11 |
Запрос 6.4 let RecordsList = { [ID = 1, Product = "Widget"], [Product = "Widget", ID = 1], [ID = 1, Product = "Widget"] }, UniqueRecords = List.Distinct(RecordsList), CountRecords = List.Count(UniqueRecords) in CountRecords // возвращает 1 |
Не равно
Оператор не равно (<>) служит логической противоположностью оператору равенства. Он возвращает true , если записи не идентичны, и false в противном случае:
|
1 |
[ Name = "John", Age = 32 ] <> [ Name = "Jane", Age = 32 ] |
Результат true, так как поле Name различается в двух записях.
Конкатенация
Оператор конкатенации позволяет объединить две записи в одну:
|
1 2 |
[Name = "John", Age = 32 ] & [ Gender = "Male"] /* возвращает [Name = "John", Age = 32, Gender = "Male"] */ |
Если в объединяемых записях есть повторяющиеся имена полей, операция конкатенации использует значение поля из записи справа для перезаписи значения поля в записи слева:
|
1 |
[ Name = "John", Age = 32 ] & [ Age = 45 ] // возвращает [ Name = "John", Age = 45 ] |
Этого же результата можно достичь с помощью функции Record.Combine, предоставив записи для объединения в виде списка:
|
1 |
Record.Combine({[Name = "John", Age = 32], [Age = 45]}) |
Где это применить на практике? Рассмотрим сценарий, в котором вам нужно получить доступ к данным из API. Процесс обычно начинается с вызова API для получения маркера авторизации. Для этого запроса необходима информация заголовка, которая должна быть предоставлена в виде записи. В следующем примере приведена только заголовочная часть кода, необходимого для запроса авторизации:
|
1 2 3 4 5 6 |
MyHeaders = [ #"Content-Type" = "application/json", #"Api-Client-Identifier" = "ccurr", #"Api-Client-Token" = "11d8373-9y3f-9agw-piwr" ] |
После получения токена аутентификации обычно выполняется последующий вызов API. Для этого вызова часто требуются одни и те же сведения о заголовке. Поскольку рекомендуется не повторяться, код вызова API ссылается на переменную MyHeaders несколько раз. Для второго запроса нам просто нужно обогатить запись вновь полученным токеном авторизации:
|
1 2 3 4 5 6 |
Json.Document( Web.Contents( "https://myAPI.product.com/api/v4/products/", [Headers = MyHeaders & [Authorization = Token]] ) ) |
Оператор конкатенации объединяет запись MyHeaders с записью, содержащей маркер авторизации. Объяснение логики вызова API выходит за рамки этой книги. Однако, если вам интересно, в главе 13 Итерация и рекурсия мы рассмотрим пример вызова API.
Объединение с null (coalesce)
Оператор объединения с null (??) размещается между двумя значениями: основным (слева) и резервным (справа). Оператор вычисляет левое значение, и возвращает его если оно не равно null. В противном случае возвращает правое значение:
|
1 |
null ?? [ Age = 13, Name = "Marc" ] |
Здесь оператор ?? проверяет левое значение. Так как оно равно null, оператор возвращает правое значение – запись [Age = 13, Name = "Marc"]. Оператор объединения с null не часто используется с записями, но активно применяется внутри функций. Допустим вам нужно подсчитать количество полей в записи:
|
1 |
Record.FieldCount( [ Age = 13, Name = "Marc" ] ) // возвращает 2 |
Теперь распространим пример на столбец, содержащий записи и значения null. Цель – подсчитать поля в каждой строке. Для строк с null хорошо бы возвращать 0. Однако функция Record.FieldCount для значения null вернет ошибку: Expression.Error: Не удается преобразовать значение null в тип Record.
Вот где пригодится оператор объединения с null. Если мы обрабатываем столбец с именем myRecords, код будет следующим:
|
1 |
Record.FieldCount( [myRecords] ?? [ ] ) |
Когда функция обнаруживает значение null в столбце myRecords, она возвращает пустую запись. Затем Record.FieldCount подсчитает поля в этой пустой записи, и вернет 0 для значений null. Это предотвратит возникновение ошибок.
Способы создания записи
Создание записи вручную часто является самым простым способом. Например, запись можно инициализировать так:
|
1 |
[ Name = "John Doe", Age = 30, City = "Seattle" ] |
Записи формируются некоторыми функциями. Например:
|
1 2 3 4 5 |
Запрос 6.5 Record.FromList( {"John Doe", 30, "Seattle"}, {"Name", "Age", "City"} ) // возвращает [ Name = "John Doe", Age = 30, City = "Seattle" ] |
|
1 2 3 4 |
Запрос 6.6 Record.FromTable( #table({"Name", "Value"}, {{"Name", "John Doe"}, {"Age", 30}, {"City", "Seattle"}}) ) // возвращает [ Name = "John Doe", Age = 30, City = "Seattle" ] |
Результат обоих запросов идентичен:
Рис. 6.11б. Запись, возвращаемая функцией
Третий подход к получению записи заключается в ссылке на строку таблицы, формально известную как выбор поля. Например, если у вас есть таблица с именем Source, первая строка этой таблицы может быть возвращена в виде записи: Source{0}. В более сложном варианте в функции Table.AddColumn можно сослаться на строку таблицы так, что каждая ячейка в новом столбце вернет запись, состоящую из всех значений строки:
Рис. 6.12. Запись, сформированная функцией Table.AddColumn
Таблицы и их характеристики более подробно мы рассмотрим в следующих разделах.
Power Query предоставляет разные методы и функции для выполнения одной и той же задачи. Выбор между ними во многом зависит от сценария. Знакомство с этими методами поможет вам выбрать наиболее эффективный вариант для создания записей.
Доступ к полям в записи
Записи могут содержать одно или несколько полей и значений. Чтобы получить к ним доступ, необходимо понимать два фундаментальных метода: выбор поля и проекция записи.
Рис. 6.12а. Операторы, используемые для доступа к полям записи
Выбор поля
Чтобы получить доступ к одному полю записи, можно сослаться на ключ, связанный с этим значением. Если вы хотите вернуть значение поля Name из записи:
|
1 |
[Name = "John Doe", Age = 30, City = "Seattle"][Name] |
Того же результата можно достичь, используя функцию Record.Field:
|
1 2 3 4 |
Record.Field( [Name = "John Doe", Age = 30, City = "Seattle"], "Name" ) |
По аналогии со списками можно получить доступ к полю во вложенной записи. Например, в записи поле info само представлено записью. Чтобы узнать пол, укажите:
|
1 |
[Info = [Age = 20, Gender = "Male"]][Info][Gender] |
Основное различие между записью и выбором поля записи лежит в содержимом в квадратных скобках. Определение записи включает имя поля, знак равенства (=) и значение, в то время как выбор поля указывает только имя поля. Чтобы заметить разницу, может потребоваться некоторая практика, но вы привыкнете.
Попытка выбрать поле, отсутствующее в записи, приведет к ошибке:
|
1 |
[Name = "John", Age = 30][City] // Expression.Error: Поле "City" записи не найдено |
Чтобы предотвратить появление этого сообщения, можно использовать необязательный оператор выбора поля (?), чтобы вернуть null вместо ошибки:
|
1 |
[Name = "John", Age = 30][City]? // возвращает null |
В тех же целях можно использовать функцию Record.FieldOrDefault:
|
1 |
Record.FieldOrDefault( [Name = "John", Age = 30], "City", null ) |
Помимо обращения к значению поля, язык М также поддерживают возврат части записи.
Проекция записи
Это процесс создания новой записи с полями выбранными из исходной записи. Перечислите имена полей, которые вам нужны, разделив их запятыми и поместив в дополнительные квадратные скобки:
|
1 2 |
[Ab=1, Bo=2, Ed=3] [ [Ab], [Ed] ] // возвращает [Ab=1, Ed=3] [Ab=1, Bo=2, Ed=3] [ [Ab] ] // возвращает [Ab=1] |
Если вы попытаетесь выбрать несуществующее поле, Power Query вызовет ошибку. Чтобы обойти это, вы можете использовать необязательный оператор проекции записи (?), и он вернет значение null для отсутствующего поля:
|
1 2 |
[Ab=1,Bo=2,Ed=3][[Ab],[Z]] // возвращает ошибку [Ab=1,Bo=2,Ed=3][[Ab],[Z]]? // возвращает [Ab=1, Z=null] |
Это поведение можно воспроизвести с помощью функции Record.SelectFields:
|
1 |
Record.SelectFields([Ab=1,Bo=2,Ed=3], {"Ab", "Z"}, MissingField.UseNull ) |
Общие операции с записями
Во-первых записи используются в качестве структуры для переменных. В DAX переменная начинается с VAR и заканчивается RETURN. Аналог в Power Query – конструкция let .. in. Какое это имеет отношение к записям? Конструкция let .. in в расширенном редакторе по сути является записью. Следующие два кода вернут одно и тоже:
Рис. 6.12б. Переменная, как конструкция let .. in или запись
Зачем используют переменные? По трем причинам:
- Если вам нужно использовать фрагмент кода повторно, сохранение результата выражения в переменной более эффективно.
- Разбиение сложного фрагмента на операции упрощает чтение кода.
- Переменные помогают в устранении неполадок, так как можно возвращать результаты промежуточных вычислений.
Рассмотрим пример – таблицу со столбцом Date, содержащую текстовое значение 31-12-2024. Цель – написать код, который будет динамически указывать, на какой день недели выпадает канун Нового года. Для этого необходимо:
- Преобразовать текстовое значение в дату.
- Получить год и день недели этой даты.
- Создать поле для объединения этой информации.
Это можно сделать вручную, добавив настраиваемый столбец:
|
1 2 3 4 |
"New year " & Text.From( Date.Year( Date.From( [Date] ))) & " is on a " & Date.DayOfWeekName( Date.From( [Date] )) |
Рис. 6.12в. Настраиваемый столбец
Обратите внимание, что эта инструкция дважды преобразует текстовое значение Date в тип данных date. Вместо этого можно использовать запись и работать с переменными:
|
1 2 3 4 5 6 |
[ _Date = Date.From([Date]), _DoW = Date.DayOfWeekName(_Date), _Year = Text.From(Date.Year(_Date)), _Result = "New year " & _Year & " is on a " & _DoW ][_Result] |
Результат не изменится:
Рис. 6.13. Записи являются эффективным способом хранения переменных
Код завершается возвратом поля с именем _Result. Если возникает ошибка, вы можете определить проблемный шаг, заменив последнюю строку другим полем в записи.
Можно даже вернуть всю запись:
Рис. 6.14. Вернув всю запись, можно проверить значения каждой переменной
Это позволит вам увидеть результат каждого поля в записи. Использование конструкции записи особенно полезно для поиска и устранения неисправностей, так как позволяет просматривать значения переменных на каждом этапе.
Ссылка на текущую строку
Еще один сценарий, в котором полезны записи, – это вычисления с текущими значениями строк. Следующая таблица содержит город магазина и продажи по месяцам.
Рис. 6.15. Набор данных для извлечения текущей строки в виде записи
Если вы хотите добавить столбец со средними продажами, вы можете вручную написать:
|
1 |
List.Average( { [January], [February], [March], [April] } ) |
Однако структура записи обеспечивает более динамичный способ ссылки на значения из строки:
|
1 2 3 |
List.Average( Record.ToList( Record.RemoveFields( _, "Store" ) ) ) |
Этот код сначала извлекает все значения из текущей строки в виде записи с помощью символа подчеркивания. Затем функция Record.RemoveFields удаляет поле Store из этой записи. Record.ToList преобразует запись в список, а List.Average усредняет оставшиеся значения.
Параметры функции
Записи также часто используются для предоставления опций в параметрах функции. Например, в Date.ToText можно указать формат вывода и английские региональные настройки:
|
1 2 3 4 |
Date.ToText( #date( 2023, 12, 31 ), [Format = "dd MMM yyyy", Culture = "en-US" ] ) |
Или для немецкого языка:
|
1 2 3 4 |
Date.ToText( #date( 2023, 12, 31 ), [Format = "dd MMM yyyy", Culture = "de-DE" ] ) |
Рис. 6.16. Записи в аргументах функции
Различие – во втором поле записи опций Culture. Используется сокращенное название месяца, соответствующее значению региональной настройки.
Знакомясь с функциями, вы буде часто сталкиваться с записями для указания опций.
Отслеживание промежуточных результатов
Записи также играют важную роль в сохранении промежуточных результатов в сложных функциях. List.Generate – типичный пример функции, использующей записи для вычислений. Эта функция создает список значений на основе начального значения, условия, определяющего, когда следует прекратить создание значений, и функции, описывающей, как создавать каждое последующее значение. Если вам нужно сгенерировать список возрастающих значений от 1 до 5, то без использования записей, может получиться код:
|
1 2 3 4 5 |
List.Generate( () => 1, // начальное значение равно 1 each _ <= 5, // пока значение не достигнет 5 each _ + 1 // на каждом шаге увеличивай значение на 1 ) |
Записи играют важную роль при работе с многовариантными итерациями в List.Generate. Запись может содержать текущее состояние всех переменных, участвующих в итерации. Создав список записей, можно сохранить несколько обязательных значений. В следующем примере создается промежуточный итог путем увеличения значения x на единицу на каждом шаге, а затем добавления этого нового значения к предыдущему значению в переменной RT (для промежуточного итога):
|
1 2 3 4 5 6 |
List.Generate( () => [ x = 1, RT = 1 ], each [x] <= 5, each [ x = [x] + 1, RT = [RT] + [x] + 1 ], each [RT] ) |
Это лишь иллюстрация использования записи. List.Generate – сложная функция, которую мы рассмотрим подробнее в главе 13 при изучении рекурсии и итераций.
Присвоение типа данных записям
Запись – это структурированное значение, способное содержать одно или несколько различных значений. При создании записей Power Query по умолчанию использует тип any, если не указан конкретный тип данных:
Рис. 6.17. Создание столбца MyRecord без присвоения определенного типа данных
При развертывании MyRecord новые столбцы также получат тип данных any:
Рис. 6.18. Развертывание столбца MyRecord, которому не назначен тип данных
Чтобы назначить тип данных записи, укажите его в четвертом аргументе функции Table.AddColumn:
Рис. 6.19. Создание столбца MyRecord с назначенными типами данных
Тогда после развертывания MyRecord, новые столбцы получат ранее назначенный тип:
Рис. 6.20. Развертывание столбца MyRecord сохраняет определенные ранее типы данных
Итак, записи предоставляют структурированный способ хранения данных и управления ими. Они полезны в сценариях, требующих одновременной работы с несколькими связанными полями данных. Помня об этом, мы переходим к последнему структурированному значению – таблицам.
Таблицы
Таблицы занимают центральное место в языке М. По сути, таблица – это структурированное значение, которое упорядочивает данные по строкам и столбцам. Можно сказать, что таблица – это список записей, где каждая запись представляет строку таблицы. Благодаря двумерной форме можно легко просмотреть данные в таблице, и вы обнаружите, что большинство запросов, загружаемых в Power BI или Excel, имеют табличное значение в качестве выходных данных.
Создание таблицы часто выполняется путем вызова функции доступа к данным, как описано в главе 3. Например, при импорте файла Excel или CSV автоматически создается таблица.
Однако для иллюстрации характеристик таблиц в этом разделе используется функция #table, которая создает таблицу вручную. Эта функция принимает два аргумента: список имен столбцов и список списков, где каждый внутренний список образует строку. Например, код…
|
1 2 3 4 5 6 7 8 |
#table( {"ID", "Name", "Country"}, { {1, "John Doe", "USA"}, {2, "Jane Doe", "USA"}, {3, "Jane Doe", "Canada"} } ) |
… возвращает таблицу:
Рис. 6.21. Таблица, созданная с помощью функции #table
Чтобы указать типы данных столбцов, можно применить пользовательский тип таблицы, как описано в главе 5. Рассмотрим, как разные операторы ведут себя с таблицами.
Табличные операторы
Таблицы поддерживают следующие операторы:
Рис. 6.21а. Операторы, поддерживаемые таблицами
Равно
Оператор равенства (=) используется для определения того, одинаковы ли две таблицы как по своей структуре, так и по содержащимся в них значениям. Чтобы две таблицы считались равными, у них должно быть одинаковое количество столбцов и строк, имя каждого столбца доступно в обеих таблицах, ячейки содержат одинаковые значения.
В следующем примере имена столбцов двух таблиц упорядочены по-разному…
|
1 2 3 4 |
#table( {"ID", "Name", "Country"}, {{1, "John Doe", "USA"}, {2, "Jane Doe", "USA"}} ) = #table( {"Name", "Country", "ID"}, {{"John Doe", "USA", 1}, {"Jane Doe", "USA", 2}} ) |
… но выражение вернет true, показывая, что порядок имен столбцов не влияет на равенство таблиц. Ключевым фактором является то, что данные совпадают по значениям и структуре.
Распространенным вариантом использования этого типа сравнения является проверка данных. Предположим, что вы импортировали таблицу бюджета в Power Query. Чтобы обеспечить точность, необходимо убедиться, что загруженные данные соответствуют последнему полученному файлу бюджета. При сравнении двух таблиц с помощью оператора равенства результат true подтверждает, что данные в обеих таблицах идентичны. Это гарантирует, что между старой версией бюджета и последним файлом нет расхождений.
Не равно
Оператор не равно проверяет, отличаются ли две таблицы. Изменим в предыдущем примере в одном месте USA на US:
|
1 2 3 4 |
#table( {"ID", "Name", "Country"}, {{1, "John Doe", "USA"}, {2, "Jane Doe", "USA"}} ) = #table( {"ID", "Name", "Country" }, {{1, "John Doe", "US"}, {2, "Jane Doe", "USA"}}) |
Это выражение возвращает false, так как название страны в третьем столбце первой строки отличается. Оператор не равно работает независимо от того, являются ли сравниваемые значения примитивными или структурированными.
Конкатенация
Оператор конкатенации (&) можно использовать для объединения двух таблиц. Операция добавляет одну таблицу в конец другой:
|
1 2 |
#table({"ID", "Name", "Country"}, {{1, "John Doe", "USA"}}) & #table({"Name", "ID", "Country"}, {{"Jane Doe", 2, "USA"}}) |
Рис. 6.22. Объединение двух таблиц с одинаковыми столбцами
Как можно было ожидать, порядок столбцов в таблицах не имеет значение. Объединение прошло корректно.
Если таблицы имеют разные столбцы, операция слияния заполнит отсутствующие данные значениями null, чтобы обеспечить целостность объединенной таблицы:
|
1 2 |
#table({"ID", "Name", "City"}, {{1, "John Doe", "Texas"}}) & #table({"ID", "Name", "Country"}, {{2, "Jane Doe", "USA"}}) |
Рис. 6.23. При объединении таблиц с разными столбцами добавляются значения null
Еще один полезный сценарий объединения таблиц – использование функции ввода данных. Эта функция позволяет вручную вводить данные и импортировать их в запрос. В редакторе Power Query пройдите Главная –> Ввести данные. Вставить значения в таблицу. Существует ограничение в 3000 ячеек. Чтобы обойти его, создайте несколько таблиц, а затем объедините их.
Хотя это опция поддерживает копипаст, трудно представить, что владея PQ, кто-то захочет вводить таким образом тысячи строк данных.
Вместо конкатенации можно использовать функцию Table.Combine. Она требует, чтобы таблицы были внутри списка:
|
1 |
Table.Combine( { Tablel, Table2 } ) |
Этот метод столь же эффективен и более прозрачен, особенно при работе с несколькими таблицами.
Объединение с null (coalesce)
Оператор объединения с null (??) позволяет вернуть значение по умолчанию, когда основная таблица имеет значение null. Это гарантирует робастность вашим запросам:
|
1 |
null ?? #table( {"ID", "Name"}, {{1, "John Doe"}}) |
Как правило, оператор объединения с null используется в сочетании с таблицами при создании пользовательских функций. Здесь он действует как защита, предотвращая ошибки, которые могут возникнуть из-за пустых входных данных. Предоставляя альтернативную таблицу, когда ожидаемая таблица равна null, вы обеспечиваете устойчивость и надежность пользовательских функций.
Способы создания таблицы
Таблицы – фундаментальное понятие языка M. Этот раздел познакомит вас с методами создания таблиц.
Получение данных из источника
Наиболее распространенным способом создания таблицы является подключение к источнику данных. Например, к файлам Excel или CSV, веб-страницам и базам данных. После подключения к источнику данных Power Query M автоматически создаст таблицу из данных. Например, чтобы создать таблицу из файла Excel, выполните следующие действия:
- В редакторе Power Query пройдите Главная –> Создать источник.
- В окне Получить данные выберите Файл –> Книга Excel.
- В окне навигации выберите файл Excel, к которому хотите подключиться.
- Выберите таблицу или лист для импорта. Нажмите Ok.
Power Query M автоматически создаст таблицу из файла Excel с помощью функций Excel.Workbook и File.Contents.
Ручной ввод данных в функции
Второй способ создания табличных значений – их ручное построение с помощью библиотечных функций, которые позволяют создавать таблицы вручную:
- #table
- FromColumns
- Таблица.FromList
- FromRecords
- FromRows
- FromValue
- FromPartitions
- ToTable
Каждая функция предоставляет свой способ ручного ввода. Наиболее часто для ручного создания таблицы используется #table. Она принимает список имен столбцов в качестве первого аргумента и список списков в качестве второго аргумента для формирования строк таблицы:
|
1 2 3 4 5 6 7 8 |
Код 6.7 #table( {"ProductKey", "Product"}, { {1, "Apple"}, {2, "Prume"} } ) |
Рис. 6.25. Функция #table, создающая таблицу
Другая популярная функция, Table.FromRecords, принимает список записей в качестве входных данных. Эти записи содержат имена столбцов, представленные именами полей записи, и значения строк, представленные значениями полей записи:
|
1 2 3 4 5 |
Код 6.8 Table.FromRecords( { [ ProductKey = 1, Product = "Apple" ], [ ProductKey = 2, Product = "Prume" ] }) |
Результат такой же как и для кода 6.7.
Количество методов для создания таблиц в Power Query огромно, и обсудить здесь все функции не представляется возможным. Тем не менее, чтобы стать экспертом по языку M, необходимо понимать различные методы создания таблиц.
Повторное использование существующих таблиц и запросов
Помимо ручного ввода, есть два других способа добавления таблиц в Power Query: ссылки и дублирование запросов.
Ссылки на таблицы и запросы
Ссылка на таблицу – это процесс указания на выходные данные таблицы или запроса. Такой подход позволяет использовать существующие таблицы и запросы, не изменяя их исходное содержимое. Предположим, что у нас есть запрос с именем Calendar. Выходные данные запроса можно использовать в качестве источника для нового запроса. Кликните правой кнопкой мыши запрос Calendar и выберите Ссылка.
Создастся новый запрос, ссылающийся на запрос Calendar:
Рис. 6.28. Шаг Sourse ссылается на запрос Calendar
Любые обновления исходного запроса будут передаваться в новый запрос. Таким образом, возникает логическая цепочка. Помимо ссылок, вы также можете дублировать таблицы.
Дублирование таблиц и запросов
Дублирование таблиц в Power Query – это создание точной копии существующего запроса. Дублированный запрос имеет те же шаги запроса и содержимое, что и исходный, но функционирует как отдельная сущность:
Рис. 6.30. Операция дублирования запроса копирует шаги запроса
Чтобы дублировать запрос, щелкните правой кнопкой мыши существующий запрос и выберите Дублировать. При тестировании различных подходов к данным рекомендуется дублировать запрос и использовать его в качестве резервной копии. Таким образом, вы всегда можете восстановить исходный запрос в случае сбоя пробной версии.
Использование функции ввода данных
Редактор Power Query предоставляет удобный способ создания таблиц вручную, вводя данные на экране. Для этого пройдите Главная –> Новый запрос –> Введите данные. Появится окно с пустой таблицей, готовой к заполнению. Имена столбцов и значения в ячейках можно редактировать, или вставлять копипаст:
Рис. 6.32. Диалоговое окно Создание таблицы
Чтобы добавить строки, просто щелкните ячейки (выделены серым цветом) в конце последней строки, и появится новая строка. Аналогично, чтобы добавить столбцы, щелкните крайний правый заголовок столбца (также серого цвета), и будет создан новый столбец.
После того, как вы заполнили таблицу данными, нажмите Ok. Окно закроется и данные загрузятся в Power Query в виде новой таблицы.
Рис. 6.33. Двоичный код, сгенерированный операцией Создание таблицы
Операция Создание таблицы сжимает данные в двоичный код.
Доступ к элементам в таблице
Как и в случае со списками и записями, доступ к элементам в таблице является полезным навыком при работе с Power Query. Может потребоваться выбрать один столбец, несколько столбцов, строку или значение одной ячейки. Давайте рассмотрим, как использовать принципы выбора и проекции для таблиц.
Выбор элемента
В контексте таблицы выражение выбора элемента используется для возврата строки. Предположим, что следующая таблица создается на шаге Source.
Рис. 6.34. Таблица для доступа к элементам
Чтобы вернуть первую строку таблицы, введите:
|
1 2 |
Код 6.9 Source{0} // возвращает [ID=1, Name="John Doe", Country="USA"] |
Это идентично использованию функции Table.Range, с указанием, что нужно вернуть только первую строку:
|
1 2 |
Код 6.10 Table.Range( Source, 0, 1 ) |
Не совсем идентично: код 6.9 возвращает запись, а код 6.10 – таблицу.
Как и в случае со списками, попытка получить элемент, для которого запрошенная позиция отсутствует в таблице, приводит к ошибке, которую можно предотвратить с помощью необязательного оператора выбора элемента:
|
1 2 |
Source{3} // Expression.Error: Элементов в перечислении недостаточно для выполнения операции Source{3}? // возвращает null |
Существует еще один способ доступа к строкам. Вместо того, чтобы указывать индекс в фигурных скобках, можно выполнить поиск по одному или нескольким значениям поля. Например, чтобы вернуть вторую строку таблицы (см. рис. 6.34), можно использовать одно из выражений:
|
1 2 |
Source{1} // возвращает вторую строку Source{[ID = 2 ]} // возвращает строку, где ID = 2 |
Вы можете сузить область поиска, указав несколько значений поиска. Расширьте запись поиска дополнительными полями и поместите ее в фигурные скобки:
|
1 |
Source{[ ID = 2, Name = "Jane Doe" ]} |
Важно помнить, что выбор элемента путем предоставления записи должен возвращать уникальную строку. Следующее выражение соответствует двум строкам и вернет ошибку:
|
1 |
Source{[Name ="Jane Doe"]} |
Рис. 6.35. Ошибка, когда выбор элемента возвращает несколько строк
Аналогичное поведение наблюдается в другом выражении, которое ожидает одну строку:
|
1 2 |
Table.SingleRow( Table.SelectRows( Source, each [Name] = "Jane Doe" ) ) |
Если вы хотите вернуть несколько строк, используйте:
|
1 |
Table.SelectRows( Source, each [Name] = "Jane Doe" ) |
Выбор поля
В то время как выбор элемента возвращает строку, оператор выбора поля возвращает значения из столбца в виде списка. Чтобы вернуть значения из столбца Name в виде списка, можно использовать тот же синтаксис, что и для записей…
|
1 |
Source[Name] // возвращает { "John Doe", "Jane Doe", "Jane Doe" } |
… или воспользоваться функцией:
|
1 |
Table.Column( Source, "Name" ) |
Это полезно, если требуется сослаться на все значения столбца для выполнения над ними операции, например, List.Sum. Далее результат суммирования можно использовать в качестве делителя для вычисления процентной доли каждого значения.
Чтобы получить доступ к нескольким столбцам из таблицы используйте проекцию поля. Она позволяет получить таблицу с меньшим числом столбцов:
|
1 |
Source[[Name],[Country]] // возвращает два столбца |
Это похоже на предоставление списка ссылок на столбцы, но вместо того, чтобы окружать столбец фигурными скобками, вы используете квадратные скобки. Этот метод работает как для одного, так и для нескольких столбцов. Преимущество подхода заключается в том, что выходные данные представлены таблицей, а не списком:
Рис. 6.36. Проекция поля возвращает таблицу с меньшим числом столбцов
При выборе несуществующего поля возникает ошибка, но ее можно предотвратить, используя необязательную проекцию поля (?):
|
1 2 |
Source[[Name],[Location]] // возвращает Expression.Error: Столбец "Location" таблицы не найден Source[[Name],[Location]]? // возвращает null в столбце Location |
Рис. 6.37. Необязательная проекция поля возвращает null для отсутствующих столбцов
Это поведение идентично использованию функции Table.SelectColumns с перечислением MissingField.UseNull:
|
1 2 3 4 5 |
Table.SelectColumns( Source, {"Name", "Location"}, MissingField.UseNull ) |
До сих пор мы видели, как выбрать строку в качестве записи (item-selection), и как вернуть один или несколько столбцов из таблицы (field-selection и field-projection). Чтобы вернуть одно значение из таблицы, нужно комбинировать оба понятия. Допустим, вы хотите вернуть значение из третьей строки столбца Name:
|
1 2 |
Source[Name]{2} Source{2}[Name] |
Это пригодится, если вы хотите детализировать одно значение в таблице.
Теперь, когда вы знаете, как создавать таблицы и получать доступ к элементам в них, давайте рассмотрим некоторые ситуации, в которых таблицы полезны.
Общие операции с таблицами
Как правило, таблицы – это то, что мы видим, когда работаем в Power Query. Это кажется очевидным, но вот несколько областей, где часто используются таблицы:
- Вспомогательные таблицы полезны при объединении или добавлении данных. Вы можете их импортировать из файла, базы данных, или создать вручную. Они позволяют обогатить данные (например, географией) в сценариях слияния (аналог ВПР в Excel).
- Таблицы являются наиболее эффективной структурой для проверки данных. Столбцы и строки позволяют видеть много ячеек одновременно. И вы следите за каждым шагом преобразований.
- Интеграция с Power BI и Excel. Преобразования обычно заканчиваются одной или несколькими таблицами, которые вы загружаете в приложение. И уже в приложении выполняете аналитическую работу с очищенными данными.
На момент написания книги существует 117 табличных функций, и это самая большая категория функций в языке M. В последующих главах вы познакомитесь с методами работы наиболее важных табличных функций. В этой главе мы еще рассмотрим назначение типов данных таблиц.
Присвоение типа данных таблицам
Табличные значения – это структурированные значения со сложными типами данных. Они могут содержать другие примитивные или структурированные значения. Тем не менее, вы по-прежнему можете определить типы данных для каждого столбца.
Представьте, что вы создаете таблицу вручную. Например, с помощью функций #table и Table.AddColumn:
Рис. 6.38. Создание табличного значения с использованием функции #table
В соответствии с ожиданиями вновь созданный столбец автоматически получает тип any. При его разворачивании создаются дополнительные столбцы, все с типом данных any. Итак, как назначить таблице тип в процессе создания таблицы?
Это очень похоже на то, как мы определили тип записи, с той лишь разницей, что на этот раз за словом type следует слово table:
Рис. 6.39. Создание столбца таблицы с приписанными типами данных
При развертывании столбца добавляются новые столбцы с назначенными типами данных:
Рис. 6.40. При развертывании столбца таблицы сохраняются типы, назначенные ранее пользовательским type table
Вы можете указать тип таблицы в функции #table. Однако в дальнейшем при создании нового столбца функцией Table.AddColumn наличие типа таблицы в #table не повлияет на тип выходных данных. Только четвертый аргумент функции Table.AddColumn влияет на тип результата:
Рис. 6.41. Тип выходных данных определяется четвертым аргументом функции Table.AddColumn
Видно, что указание типов столбцов внутри #table не повлияло на тип столбца Table (значок соответствует типу any).
Саммари
В этой главе мы рассмотрели списки, записи и таблицы, показав, насколько они важны в языке M. Мы узнали, что структурированные значения служат контейнерами, содержащими одно или несколько примитивных или структурированных значений. Изучение их полезно в самых разных областях. Например, списки и записи часто используются для предоставления нескольких элементов в аргументах функции, что упрощает код. Структуры записей также отлично подходят для создания переменных.
Мы исследовали, как получить доступ к элементам структурированных значений с помощью выбора и проекции. Эти знания помогают понять код, созданный интерфейсом Power Query, а также создавать более читаемый код самостоятельно.
Затем мы рассмотрели создание структурированных значений и работу операторов с ними. В этой главе мы привели интересные примеры кода и сценарии, к которых они работают. Примеры продемонстрировали силу структурированных значений и простоту обращения с ними.
В следующей главе мы сосредоточимся на концепциях М, или, другими словами, на фундаментальных принципах языка М.