Перейти к содержимому

Глава 6. Структурированные данные

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М 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}. Элементами списка могут быть структурированные значения. Следующее выражение возвращает список, содержащий текст, список, запись и таблицу:

Список может быть пустым, не содержать элементов: {}.

Подсчитать количество элементов в списке, можно с помощью функции List.Count:

Обзор функций списка вы найдете на сайте powerquery.how.

Операторы списков

Мы кратко познакомились с операторами в главе 4 и показали, какие значения поддерживают те или иные операторы. Сейчас рассмотрим операторы, работающие со списками.

Рис. 6.1а. Операторы, поддерживаемые списками

Равно

Оператор равенства проверяет, идентичны ли значения элементов и порядок их следования:

Оператор равенства работает независимо от того, содержит ли список примитивные или структурированные значения (например, вложенные списки):

Пример использования – сравнение имен столбцов в двух таблицах. При добавлении таблиц с помощью функции Table.Combine объединяются столбцы с одинаковыми именами. Перед добавлением таблиц полезно проверить, совпадают ли в них имена столбцов. Поскольку при проверке равенства важен порядок значений, предварительно отсортируйте имена столбцов:

Запрос 6.1 вернет true, если два списка равны.

Не равно

Оператор не равно проверяет, отличаются ли два списка:

Конкатенация

Оператор конкатенации объединяет два списка, добавляя один список в конец другого:

Альтернатива – функция List.Combine. Она принимает список списков, которые нужно объединить:

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

Предположим, у вас есть следующая строка:

Создайте два списка значений:

В этих двух выражениях используется конструкция диапазона (AB-construct), которую мы обсудим далее в этой главе. Для объединения сгенерированных списков и извлечения значений из строки можно использовать следующий код:

Объединение с null (coalesce)

Оператор объединения с null помещается между двумя значениями: основным (слева) и резервным (справа). Если левое значение не равно null, оно возвращается в качестве результата. Если левое значение равно null, оператор возвращает резервное значение. Например:

Поскольку левое значение (список) не равно null, оператор объединения с null сохраняет его. Однако, если поменять порядок…

… выражение вернет {1, 2, 3}. Такое поведение гарантирует результат, отличный от null, что полезно в сценариях с потенциальными значениями null.

Представьте себе каталог продукции со столбцом Features (Характеристики). Если для некоторых продуктов столбец пуст, вы можете вернуть список характеристик по умолчанию:

Таким образом, встреча со значением null в столбце Features приводит к возврату стандартного списка объектов. Такой подход гарантирует единообразие в описаниях товаров, предоставляя характеристики по умолчанию там, где отсутствуют конкретные значения.

Рассмотрим, как генерировать списки значений.

Способы создания списка

Первый способ создания списка – инициализировать его. Например, можно задать значения различных типов:

Список можно создать с помощью функций-генераторов: List.Dates, List.DateTimeZones, List.DateTimes, List.Durations, List.Numbers, List.Times. Все они принимают три аргумента: начальное значение, количество элементов и приращение, которое добавляется на каждом шаге. Например:

Если вы хотите создать список случайных чисел, используйте функцию List.Random:

В языке 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 в виде списка с помощью следующего выражения:

Рис. 6.2а. Столбец таблицы в виде списка

Этот процесс, известный как выбор поля (field-selection), будет подробно рассмотрен ниже.

С использованием синтаксиса a..b

Cписки можно генерить с помощью оператора a..b. Это особенно полезно, когда требуется создать список последовательных цифр, букв, дат или специальных символов. В своей простейшей форме синтаксис a..b генерирует ряд значений в указанном диапазоне чисел:

Такой список пригодится для циклов, итераций, генерации данных и создания календарей. Конструкцию можно использовать и для создания прерывистого ряда значений:

Функция генерит только возрастающие списки. Попытка создать убывающий список вернет пустой список:

Чтобы создать убывающий список, воспользуйтесь функциями List.Reverse или List.Numbers:

Возрастающий список может включать не только числа, но и буквы:

Но помните, что язык M чувствителен к регистру. Синтаксис…

Чтобы понимать, как работает синтаксис a..b, важно знать, что происходит «за кулисами». Когда Power Query генерирует списки, он использует символы Unicode, независимо от того, указали вы число, букву или заглавную букву. Например, если бы вы задали…

… то получили …

Буква "Y" соответствует Юникоду 89, "a" – 98. Убедиться в этом можно, написав:

Таким образом, чтобы полностью понять последовательность, в которой генерируются символы, полезно обратиться к стандарту Юникода.

Доступ к элементам списка

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

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

Того же результата мы можем добиться с помощью функции List.Range:

Концепция отбора идет еще дальше. При работе с вложенными списками можно выбирать значения из нескольких слоев:

Что произойдет, если мы попытаемся получить элемент с индексом, которого нет в списке?

Power Query вернет Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.

Чтобы предотвратить ошибку, язык M предлагает оператор, который позволяет выбрать необязательный элемент. Добавьте вопросительный знак в конце выражения, пытающегося получить доступ к списку. Если позиции индекса нет в списке, выражение вернет значение null:

В оригинале: «To prevent this error, the M language offers an operator that instructs optional item selection». На мой взгляд фраза не совсем корректная, так как оператор (?) строго вернет null, и задать иное значение невозможно.

Итак, теперь вы знаете, как создавать списки и получать доступ к их значениям. Изучим, когда списки полезны.

Общие операции со списками

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

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

Рис. 6.3. List.Max возвращает максимальное значение по нескольким столбцам

В следующем примере вы хотите оценить, соответствует ли значение в столбце Color одному из трех цветов. В языке M Power Query нет оператора IN, как в SQL или DAX. Однако его можно воспроизвести, предоставив список функции List.Contains:

Рис. 6.4. List.Contains отлично имитирует оператор IN

Код 6.3 проверяет, содержит ли столбец [Color] хотя бы один из указанных цветов. Это хороший пример того, как списки упрощают условную логику.

Перейдем к следующему сценарию, в котором вы имеете дело со строкой: ISBN: 978-3-16-148410-0 и хотите извлечь цифры с дефисами. Вы можете использовать синтаксис a .. b:

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

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

Наконец, списки широко используются в функции Table.Group при указании столбцов для группировки и определения агрегатов:

Список {"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, не определив тип данных элементов списка. Чтобы преобразовать тип столбца со значениями списка можно использовать код…

Важно знать, что функция Table.TransformColumn требует выполнения операции над базовым списком. Если этого не сделать, Table.TransformColumns не присвоит тип данных. Мы применили оператор объединения с null (??) к базовому значению. В данном случае эта операция не нужна, но благодаря ей функция также успешно присвоит тип данных.

Итак, основное преимущество присвоения типа списку заключается в том, что при развертывании списка на новые строки столбцу будет назначен тип данных.

Рассмотрев списки, перейдем к другому типу структурированных данных – записям.

Записи

Запись – это именованный список значений. Если списки – просто упорядоченная коллекция значений, запись связывает имена полей (ключей) со значениями. Это приводит к более сложной, но организованной структуре данных. Запись можно рассматривать как одну строку в таблице, где каждое поле в строке имеет уникальное имя столбца и значение. Как и списки, записи могут содержать значения любого типа: примитивные (текст, числа, даты…) или структурированные (списки, записи, таблицы).

Запись можно создать с помощью оператора инициализации записи. При этом:

  • Запись заключается в квадратные скобки.
  • За ключом (именем поля) следует знак равенства (=). Записи позволяют указывать имена полей без кавычек.
  • Значение поля следует за знаком равенства.
  • Пары ключ = значение разделяются запятыми.

Следующий код создает запись с данными о сотруднике:

Рис. 6.10. Инициализация записи в Power Query с помощью []

Здесь Name, Age и City – ключи (имена полей), а "John Doe", 30 и "Seattle" – соответствующие значения полей. При создании записи каждое имя поля должно быть уникальным с учетом регистра. Так что следующая запись вернет ошибку Expression.Error: Имя «Age» определено несколько раз:

Имя поля записи можно указывать без кавычек даже если в нем есть пробел. Например:

Подробнее см. Язык М Power Query. Переменные и идентификаторы.

Запись может содержать примитивные или структурированные значения. Пример записи с вложенным списком и записью:

Рис. 6.11. Запись может содержать примитивные и структурированные значения

Можно создать пустую запись: [ ]. Чтобы подсчитать количество полей в записи, можно использовать функцию Record.FieldCount:

Операторы записи

Записи позволяют использовать следующие операторы:

Рис. 6.11а. Операторы, используемые с записями

Равно

Оператор равенства (=) используется для оценки того, идентичны ли две записи. Язык M считает записи равными, когда:

  • Количество имен полей идентично.
  • Каждое имя поля существует в обеих записях.
  • Порядок следования полей не важен.
  • Имена полей и их значения одинаковые.

Помните, что сравнение чувствительно к регистру как для имен полей, так и для значений.

Как это можно использовать? В следующем сценарии мы начинаем со списка записей RecordsList, в которых порядок полей отличается. Функция List.Distinct удаляет повторяющиеся записи, а CountRecords – вычисляет количество уникальных записей, оставшихся в списке. Результат (1) показывает, что порядок полей в записях при определении их равенства не имеет значения.

Не равно

Оператор не равно (<>) служит логической противоположностью оператору равенства. Он возвращает true , если записи не идентичны, и false в противном случае:

Результат true, так как поле Name различается в двух записях.

Конкатенация

Оператор конкатенации позволяет объединить две записи в одну:

Если в объединяемых записях есть повторяющиеся имена полей, операция конкатенации использует значение поля из записи справа для перезаписи значения поля в записи слева:

Этого же результата можно достичь с помощью функции Record.Combine, предоставив записи для объединения в виде списка:

Где это применить на практике? Рассмотрим сценарий, в котором вам нужно получить доступ к данным из API. Процесс обычно начинается с вызова API для получения маркера авторизации. Для этого запроса необходима информация заголовка, которая должна быть предоставлена в виде записи. В следующем примере приведена только заголовочная часть кода, необходимого для запроса авторизации:

После получения токена аутентификации обычно выполняется последующий вызов API. Для этого вызова часто требуются одни и те же сведения о заголовке. Поскольку рекомендуется не повторяться, код вызова API ссылается на переменную MyHeaders несколько раз. Для второго запроса нам просто нужно обогатить запись вновь полученным токеном авторизации:

Оператор конкатенации объединяет запись MyHeaders с записью, содержащей маркер авторизации. Объяснение логики вызова API выходит за рамки этой книги. Однако, если вам интересно, в главе 13 Рекурсия и итерация мы рассмотрим пример вызова API.

Объединение с null (coalesce)

Оператор объединения с null (??) размещается между двумя значениями: основным (слева) и резервным (справа). Оператор вычисляет левое значение, и возвращает его если оно не равно null. В противном случае возвращает правое значение:

Здесь оператор ?? проверяет левое значение. Так как оно равно null, оператор возвращает правое значение – запись [Age = 13, Name = "Marc"]. Оператор объединения с null не часто используется с записями, но активно применяется внутри функций. Допустим вам нужно подсчитать количество полей в записи:

Теперь распространим пример на столбец, содержащий записи и значения null. Цель – подсчитать поля в каждой строке. Для строк с null хорошо бы возвращать 0. Однако функция Record.FieldCount для значения null вернет ошибку: Expression.Error: Не удается преобразовать значение null в тип Record.

Вот где пригодится оператор объединения с null. Если мы обрабатываем столбец с именем myRecords, код будет следующим:

Когда функция обнаруживает значение null в столбце myRecords, она возвращает пустую запись. Затем Record.FieldCount подсчитает поля в этой пустой записи, и вернет 0 для значений null. Это предотвратит возникновение ошибок.

Способы создания записи

Создание записи вручную часто является самым простым способом. Например, запись можно инициализировать так:

Записи формируются некоторыми функциями. Например:

Результат обоих запросов идентичен:

Рис. 6.11б. Запись, возвращаемая функцией

Третий подход к получению записи заключается в ссылке на строку таблицы, формально известную как выбор поля. Например, если у вас есть таблица с именем Source, первая строка этой таблицы может быть возвращена в виде записи: Source{0}. В более сложном варианте в функции Table.AddColumn можно сослаться на строку таблицы так, что каждая ячейка в новом столбце вернет запись, состоящую из всех значений строки:

Рис. 6.12. Запись, сформированная функцией Table.AddColumn

Таблицы и их характеристики более подробно мы рассмотрим в следующих разделах.

Power Query предоставляет разные методы и функции для выполнения одной и той же задачи. Выбор между ними во многом зависит от сценария. Знакомство с этими методами поможет вам выбрать наиболее эффективный вариант для создания записей.

Доступ к полям в записи

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

Рис. 6.12а. Операторы, используемые для доступа к полям записи

Выбор поля

Чтобы получить доступ к одному полю записи, можно сослаться на ключ, связанный с этим значением. Если вы хотите вернуть значение поля Name из записи:

Того же результата можно достичь, используя функцию Record.Field:

По аналогии со списками можно получить доступ к полю во вложенной записи. Например, в записи поле info само представлено записью. Чтобы узнать пол, укажите:

Основное различие между записью и выбором поля записи лежит в содержимом в квадратных скобках. Определение записи включает имя поля, знак равенства (=) и значение, в то время как выбор поля указывает только имя поля. Чтобы заметить разницу, может потребоваться некоторая практика, но вы привыкнете.

Попытка выбрать поле, отсутствующее в записи, приведет к ошибке:

Чтобы предотвратить появление этого сообщения, можно использовать необязательный оператор выбора поля (?), чтобы вернуть null вместо ошибки:

В тех же целях можно использовать функцию Record.FieldOrDefault:

Помимо обращения к значению поля, язык М также поддерживают возврат части записи.

Проекция записи

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

Если вы попытаетесь выбрать несуществующее поле, Power Query вызовет ошибку. Чтобы обойти это, вы можете использовать необязательный оператор проекции записи (?), и он вернет значение null для отсутствующего поля:

Это поведение можно воспроизвести с помощью функции Record.SelectFields:

Общие операции с записями

Во-первых записи используются в качестве структуры для переменных. В DAX переменная начинается с VAR и заканчивается RETURN. Аналог в Power Query – конструкция let .. in. Какое это имеет отношение к записям? Конструкция let .. in в расширенном редакторе по сути является записью. Следующие два кода вернут одно и тоже:

Рис. 6.12б. Переменная, как конструкция let .. in или запись

Зачем используют переменные? По трем причинам:

  • Если вам нужно использовать фрагмент кода повторно, сохранение результата выражения в переменной более эффективно.
  • Разбиение сложного фрагмента на операции упрощает чтение кода.
  • Переменные помогают в устранении неполадок, так как можно возвращать результаты промежуточных вычислений.

Рассмотрим пример – таблицу со столбцом Date, содержащую текстовое значение 31-12-2024. Цель – написать код, который будет динамически указывать, на какой день недели выпадает канун Нового года. Для этого необходимо:

  1. Преобразовать текстовое значение в дату.
  2. Получить год и день недели этой даты.
  3. Создать поле для объединения этой информации.

Это можно сделать вручную, добавив настраиваемый столбец:

Рис. 6.12в. Настраиваемый столбец

Обратите внимание, что эта инструкция дважды преобразует текстовое значение Date в тип данных date. Вместо этого можно использовать запись и работать с переменными:

Результат не изменится:

Рис. 6.13. Записи являются эффективным способом хранения переменных

Код завершается возвратом поля с именем _Result. Если возникает ошибка, вы можете определить проблемный шаг, заменив последнюю строку другим полем в записи.

Можно даже вернуть всю запись:

Рис. 6.14. Вернув всю запись, можно проверить значения каждой переменной

Это позволит вам увидеть результат каждого поля в записи. Использование конструкции записи особенно полезно для поиска и устранения неисправностей, так как позволяет просматривать значения переменных на каждом этапе.

Ссылка на текущую строку

Еще один сценарий, в котором полезны записи, – это вычисления с текущими значениями строк. Следующая таблица содержит город магазина и продажи по месяцам.

Рис. 6.15. Набор данных для извлечения текущей строки в виде записи

Если вы хотите добавить столбец со средними продажами, вы можете вручную написать:

Однако структура записи обеспечивает более динамичный способ ссылки на значения из строки:

Этот код сначала извлекает все значения из текущей строки в виде записи с помощью символа подчеркивания. Затем функция Record.RemoveFields удаляет поле Store из этой записи. Record.ToList преобразует запись в список, а List.Average усредняет оставшиеся значения.

Параметры функции

Записи также часто используются для предоставления опций в параметрах функции. Например, в Date.ToText можно указать формат вывода и английские региональные настройки:

Или для немецкого языка:

Рис. 6.16. Записи в аргументах функции

Различие – во втором поле записи опций Culture. Используется сокращенное название месяца, соответствующее значению региональной настройки.

Знакомясь с функциями, вы буде часто сталкиваться с записями для указания опций.

Отслеживание промежуточных результатов

Записи также играют важную роль в сохранении промежуточных результатов в сложных функциях. List.Generate – типичный пример функции, использующей записи для вычислений. Эта функция создает список значений на основе начального значения, условия, определяющего, когда следует прекратить создание значений, и функции, описывающей, как создавать каждое последующее значение. Если вам нужно сгенерировать список возрастающих значений от 1 до 5, то без использования записей, может получиться код:

Записи играют важную роль при работе с многовариантными итерациями в List.Generate. Запись может содержать текущее состояние всех переменных, участвующих в итерации. Создав список записей, можно сохранить несколько обязательных значений. В следующем примере создается промежуточный итог путем увеличения значения x на единицу на каждом шаге, а затем добавления этого нового значения к предыдущему значению в переменной 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, которая создает таблицу вручную. Эта функция принимает два аргумента: список имен столбцов и список списков, где каждый внутренний список образует строку. Например, код…

… возвращает таблицу:

Рис. 6.21. Таблица, созданная с помощью функции #table

Чтобы указать типы данных столбцов, можно применить пользовательский тип таблицы, как описано в главе 5. Рассмотрим, как разные операторы ведут себя с таблицами.

Табличные операторы

Таблицы поддерживают следующие операторы:

Рис. 6.21а. Операторы, поддерживаемые таблицами

Равно

Оператор равенства (=) используется для определения того, одинаковы ли две таблицы как по своей структуре, так и по содержащимся в них значениям. Чтобы две таблицы считались равными, у них должно быть одинаковое количество столбцов и строк, имя каждого столбца доступно в обеих таблицах, ячейки содержат одинаковые значения.

В следующем примере имена столбцов двух таблиц упорядочены по-разному…

… но выражение вернет true, показывая, что порядок имен столбцов не влияет на равенство таблиц. Ключевым фактором является то, что данные совпадают по значениям и структуре.

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

Не равно

Оператор не равно проверяет, отличаются ли две таблицы. Изменим в предыдущем примере в одном месте USA на US:

Это выражение возвращает false, так как название страны в третьем столбце первой строки отличается. Оператор не равно работает независимо от того, являются ли сравниваемые значения примитивными или структурированными.

Конкатенация

Оператор конкатенации (&) можно использовать для объединения двух таблиц. Операция добавляет одну таблицу в конец другой:

Рис. 6.22. Объединение двух таблиц с одинаковыми столбцами

Как можно было ожидать, порядок столбцов в таблицах не имеет значение. Объединение прошло корректно.

Если таблицы имеют разные столбцы, операция слияния заполнит отсутствующие данные значениями null, чтобы обеспечить целостность объединенной таблицы:

Рис. 6.23. При объединении таблиц с разными столбцами добавляются значения null

Еще один полезный сценарий объединения таблиц – использование функции ввода данных. Эта функция позволяет вручную вводить данные и импортировать их в запрос. В редакторе Power Query пройдите Главная –> Ввести данные. Вставить значения в таблицу. Существует ограничение в 3000 ячеек. Чтобы обойти его, создайте несколько таблиц, а затем объедините их.

Хотя это опция поддерживает копипаст, трудно представить, что владея PQ, кто-то захочет вводить таким образом тысячи строк данных.

Вместо конкатенации можно использовать функцию Table.Combine. Она требует, чтобы таблицы были внутри списка:

Этот метод столь же эффективен и более прозрачен, особенно при работе с несколькими таблицами.

Объединение с null (coalesce)

Оператор объединения с null (??) позволяет вернуть значение по умолчанию, когда основная таблица имеет значение null. Это гарантирует робастность вашим запросам:

Как правило, оператор объединения с 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. Она принимает список имен столбцов в качестве первого аргумента и список списков в качестве второго аргумента для формирования строк таблицы:

Рис. 6.25. Функция #table, создающая таблицу

Другая популярная функция, Table.FromRecords, принимает список записей в качестве входных данных. Эти записи содержат имена столбцов, представленные именами полей записи, и значения строк, представленные значениями полей записи:

Результат такой же как и для кода 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. Таблица для доступа к элементам

Чтобы вернуть первую строку таблицы, введите:

Это идентично использованию функции Table.Range, с указанием, что нужно вернуть только первую строку:

Не совсем идентично: код 6.9 возвращает запись, а код 6.10 – таблицу.

Как и в случае со списками, попытка получить элемент, для которого запрошенная позиция отсутствует в таблице, приводит к ошибке, которую можно предотвратить с помощью необязательного оператора выбора элемента:

Существует еще один способ доступа к строкам. Вместо того, чтобы указывать индекс в фигурных скобках, можно выполнить поиск по одному или нескольким значениям поля. Например, чтобы вернуть вторую строку таблицы (см. рис. 6.34), можно использовать одно из выражений:

Вы можете сузить область поиска, указав несколько значений поиска. Расширьте запись поиска дополнительными полями и поместите ее в фигурные скобки:

Важно помнить, что выбор элемента путем предоставления записи должен возвращать уникальную строку. Следующее выражение соответствует двум строкам и вернет ошибку:

Рис. 6.35. Ошибка, когда выбор элемента возвращает несколько строк

Аналогичное поведение наблюдается в другом выражении, которое ожидает одну строку:

Если вы хотите вернуть несколько строк, используйте:

Выбор поля

В то время как выбор элемента возвращает строку, оператор выбора поля возвращает значения из столбца в виде списка. Чтобы вернуть значения из столбца Name в виде списка, можно использовать тот же синтаксис, что и для записей…

… или воспользоваться функцией:

Это полезно, если требуется сослаться на все значения столбца для выполнения над ними операции, например, List.Sum. Далее результат суммирования можно использовать в качестве делителя для вычисления процентной доли каждого значения.

Чтобы получить доступ к нескольким столбцам из таблицы используйте проекцию поля. Она позволяет получить таблицу с меньшим числом столбцов:

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

Рис. 6.36. Проекция поля возвращает таблицу с меньшим числом столбцов

При выборе несуществующего поля возникает ошибка, но ее можно предотвратить, используя необязательную проекцию поля (?):

Рис. 6.37. Необязательная проекция поля возвращает null для отсутствующих столбцов

Это поведение идентично использованию функции Table.SelectColumns с перечислением MissingField.UseNull:

До сих пор мы видели, как выбрать строку в качестве записи (item-selection), и как вернуть один или несколько столбцов из таблицы (field-selection и field-projection). Чтобы вернуть одно значение из таблицы, нужно комбинировать оба понятия. Допустим, вы хотите вернуть значение из третьей строки столбца 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, а также создавать более читаемый код самостоятельно.

Затем мы рассмотрели создание структурированных значений и работу операторов с ними. В этой главе мы привели интересные примеры кода и сценарии, к которых они работают. Примеры продемонстрировали силу структурированных значений и простоту обращения с ними.

В следующей главе мы сосредоточимся на концепциях М, или, другими словами, на  фундаментальных принципах языка М.

1 комментарий для “Глава 6. Структурированные данные”

  1. Уведомление: Глава 7. Концепция M

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *