Глава 8. Работа с вложенными структурами

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Вложенные структуры очень распространены и относятся к иерархической организации данных, когда таблица, запись или список содержатся в другом структурированном значении. К распространенным источникам данных, поддерживающим вложенные структуры, относятся реляционные базы данных, JSON и XML. Существует широкий спектр M функций, которые возвращают структурированные значения. Очень важно понять, как обращаться с такими структурами.

Мои комментарии набраны с отступом.

Предыдущая глава Содержание Следующая глава

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

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

Переход к программированию
Преобразование значений в таблице
Работа со списками
Работа с записями
Работа с таблицами
Работа со смешанными структурами

Чтобы извлечь максимальную пользу из этой главы важно понимать концепции, рассмотренные в главе 6 Структурированные значения. Рекомендуем вам повторять примеры в редакторе Power Query. Выполняя сценарии, вы получите более глубокое их понимание. Исходные файлы включены в репозиторий GitHub.

Переход к программированию

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

Начало работы

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

При взаимодействии с запросом через пользовательский интерфейс создается код M, который можно просмотреть в строке формул или в расширенном редакторе. Каждое действие, выполняемое пользователем, присваивается идентификатору или переменной в выражении let. Однако у пользовательского интерфейса есть ограничения. Во-первых, он не предоставляет доступ ко всем функциям, доступным в языке M, а во-вторых, он не всегда справляется с преобразованием вложенных структур. Означает ли это, что вы должны начать активно программировать? Не обязательно. Мы расскажем о методике, которая облегчает работу с вложенными структурами. Но сначала вам нужно будет разобраться с детализацией (Drill Down).

Общие сведения о детализации

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

let
  t = Table.FromRows(
    {
      {"Question", "Response1", "Response2", "Response3"},
      {"Overall quality?", "High", "Medium", "Low"},
      {"Ease of use?", "Good", "Average", "Poor"},
      {"Would you recommend?", "Yes", "No", "No"}
    }
  ),
  Source = Table.FromRecords(
    {[Survey = "a", Results = t], [Survey = "b", Results = t], [Survey = "c", Results = t]},
    type table [Survey = text, Results = table]
  )
in
  Source

let

t = Table.FromRows(

{

{"Question", "Response1", "Response2", "Response3"},

{"Overall quality?", "High", "Medium", "Low"},

{"Ease of use?", "Good", "Average", "Poor"},

{"Would you recommend?", "Yes", "No", "No"}

}

Source = Table.FromRecords(

{[Survey = "a", Results = t], [Survey = "b", Results = t], [Survey = "c", Results = t]},

type table [Survey = text, Results = table]

)

Source

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

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

Получить все значения из столбца можно из контекстного меню или с помощью ленты.

Из контекстного меню. Выберите интересующий вас столбец. Например Results. Щелкните правой кнопкой мыши по заголовку столбца. В контекстном меню выберите Детализация. Это добавит новый шаг к запросу. Для реализации второго метода удалите этот шаг, например, щелкнув x перед именем шага в поле Примененные шаги.

Рис. 8.1. Детализация столбца таблицы

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

Рис. 8.1а. Преобразование таблицы в список

Удалить шаг Results, чтобы продолжить экспериментировать.

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

Из контекстного меню. Выберите интересующую вас ячейку. Например, первую ячейку в столбце Results. Щелкните правой кнопкой мыши по пустому месту в этой ячейке (1). В контекстном меню выберите Детализация (Drill Down). Удалите шаг Results.

Выбор структурированного значения. Если ячейка содержит структурированное значение (в нашем случае это значение Table) кликните на него левой кнопкой мыши. Будет получено значение этой ячейки. Удалите шаг Results.

Рис. 8.2. Детализация ячейки таблицы

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

Пользовательский интерфейс предоставляет эффективный способ создания кода. Даже опытные программисты M используют интерфейс. Однако преобразование вложенных таблиц требует перехода от интуитивно понятного метода «укажи и щелкни» к методам ручного кодирования. Этот ручной подход очень важен, так как пользовательский интерфейс Power Query, хотя и надежен, имеет ограничения. В частности, при работе с вложенными структурами пользовательский интерфейс может отображать только имя их литерального типа, например Table, Record или List. Это обобщенное представление может сделать процесс применения и проверки преобразований громоздким и неэффективным. Тем не менее, если вы понимаете поток выражений М, выражение let и детализацию, вы задействуете весь потенциал пользовательского интерфейса.

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

Рис. 8.3. Параметры загрузки данных в Power Query

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

Правой кнопкой мыши щелкните пробел в ячейке (1), как показано на рис. 8.2, и в контекстном меню выберите Добавить как новый запрос (Add as New Query). Появится новый запрос с именем Results, в котором выводится значение таблицы из выбранной ячейки.

Правой кнопкой мыши щелкните имя нового запроса в области Запросы и в контекстном меню снимите галку Включить загрузку (в Excel этой опции нет). Запрос является вспомогательным для написания кода M для преобразования всех вложенных таблиц.

Перейдите на вкладку Преобразование и кликните Транспонировать.

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

Дважды щелкните заголовок первого столбца и переименуйте его в Respondents.

Три предыдущих действия добавили новые шаги, которые отображаются в области Примененные шаги. Первый называется Транспонированная таблица. Пришло время изучить код M. Откройте расширенный редактор. Так как эта переменная содержит пробел, используется идентификатор в кавычках: #»Транспонированная таблица» (строка 18):

Рис. 8.4. Часть кода M

Скопируйте этот идентификатор и все, что за ним следует до конца кода. Закройте расширенный редактор и вернитесь к исходному запросу (см. рис. 8.2). Пройдите Добавление столбца –> Настраиваемый столбец. В диалоговом окне Настраиваемый столбец в области Настраиваемая формула столбца введите let, а затем вставьте код из буфера обмена.

Следующий шаг является наиболее важным: замените имя входной таблицы или значения для преобразования. Полезно знать, что большинство табличных функций принимают таблицу в качестве первого аргумента. Изучив код на рисунке 8.5, мы видим, что Results1 нуждается в замене. Выделите и удалите Results1. Вставьте Results из области Доступные столбцы, дважды щелкнув его:

Рис. 8.5. Замена ссылки на таблицу на первом шаге преобразования

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

Рис. 8.6. Предварительный просмотр одной из преобразованных вложенных таблиц

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

Также можно создать собственную функцию преобразования из вспомогательного запроса. Этот процесс описан в главе 9 Параметры и пользовательские функции.

Методы многоступенчатого преобразования значений

При преобразовании данных часто требуется применить несколько преобразований, чтобы получить правильное значение. Какие варианты доступны для того, чтобы лучше всего справиться с этим? Пришло время выделить четыре различных метода, которые обрабатывают многоступенчатое преобразование значений. У каждого есть свои плюсы и минусы.

Вложенные функции

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

Выражение let

Используя let мы преследуем две задачи: разбить сложные преобразования на более мелкие; создать переменные для хранения значений, востребованных в различных местах кода. Такая модульность делает код более организованным и часто более простым для понимания. Помните, что после предложения in можно вернуть одну из переменных или другое выражение.

Выражение записи (record)

Оно очень похоже на выражение let. Основное различие между let и record заключается в том, что record более гибкая. Выражение записи особенно полезно при проверке, устранении неполадок и отладке, поскольку можно быстро изменить возвращаемое значение на всю запись, выборку полей или значение одного поля.

Пользовательские функции

Написание кода в свободной форме, как и создание пользовательских функций с нуля, требует понимания языка M, однако при создании пользовательской функции из другого запроса потребность в глубоких знаниях M не так велика. Более того, функцию можно создать из вспомогательного запроса просто выбрав опцию Создать функцию в контекстном меню запроса. Об этом и многом другом рассказывается в главе 9 Параметры и пользовательские функции. Основными преимуществами пользовательских функций являются возможность повторного использования, стандартизация и более чистый код за счет делегирования.

Преобразование значений в таблицах

Преобразование значений является фундаментальным навыком и часто критически важной частью процесса очистки, подготовки и преобразования данных перед анализом. Язык M включает в себя функции для преобразования столбцов, строк, полей записей и списков таблицы. Когда дело доходит до преобразования структурированных значений в таблице, вы, скорее всего, столкнетесь с Table.AddColumn, Table.TransformColumns и Table.ReplaceValue. Каждая из них имеет свои преимущества и варианты использования.

Многие функции стандартной библиотеки принимают функции в качестве аргументов. Часто эти функции являются унарными, т.е. принимают один аргумент. Выражение each относится к этой категории; это сокращение для объявления неявной функции, которая принимает один параметр, обозначаемый подчеркиванием (_). Когда вы встречаете each, полезно знать, что формула, следующая за ним, будет выполняться для каждого элемента в списке или строки в таблице, и подчеркивание предоставляет доступ к текущему элементу. Подробнее см. главу 9 Параметры и пользовательские функции.

Table.AddColumn

Выбирая Добавление столбца –> Настраиваемый столбец, вы вызывает функцию Table.AddColumn. Это позволяет постепенно выстраивать сложную логику, создавая новые значения на основе существующих. Т.е., при создании новых столбцов входные значения из других столбцов таблицы остаются нетронутыми. Это гарантирует, что вы всегда сможете отследить и сравнить результаты с исходными данными. Иногда пользовательские столбцы для промежуточных вычислений не нужны в окончательном выводе. Рекомендуется удалить все ненужные столбцы перед загрузкой запроса в модель данных.

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

let
  Source          = Table.FromRows({{"Power", "BI"}, {"Power", "Query"}}),
  getAllRowValues = Table.AddColumn(Source, "Step1", each Record.ToList(_))
in
  getAllRowValues

let

Source = Table.FromRows({{"Power", "BI"}, {"Power", "Query"}}),

getAllRowValues = Table.AddColumn(Source, "Step1", each Record.ToList(_))

getAllRowValues

Рис. 8.6а. Результат шага 1

Перейдите Добавление столбца –> Настраиваемый столбец. Настройте диалоговое окно:

Рис. 8.7. Диалоговое окно Настраиваемый столбец с областью формул и доступными столбцами

Выражение применяется к каждой строке таблицы. Такой пошаговый подход полезен при проектировании сложных преобразований. Однако важно помнить о потенциальном влиянии на производительность, особенно при работе с большими наборами данных. Добавление большого количества столбцов в большой набор данных может повлиять на загрузку предварительного просмотра, время выполнения запроса и общую производительность. Таким образом, удаление шага 1, который теперь является избыточным, считается лучшей практикой. Имейте в виду, что даже если столбец не является частью окончательных выходных данных, вы все равно можете просмотреть его, выбрав предыдущий шаг в области Примененные шаги.

Table.TransformColumns

Функция Table.TransformColumns может быть вызвана из интерфейса, например, при изменении формата столбца. Рассмотрим данные EmployeeData:

let
  Source = Table.FromRows(
    {{101, "john", "prince", 50000}, {102, "alice", "wonder", 60000}, {103, "bob", "bever", 55000}},
    type table [EmployeeID = number, FirstName = text, LastName = text, Salary = number]
  )
in
  Source

let

Source = Table.FromRows(

{{101, "john", "prince", 50000}, {102, "alice", "wonder", 60000}, {103, "bob", "bever", 55000}},

type table [EmployeeID = number, FirstName = text, LastName = text, Salary = number]

)

Source

Выделите столбцы FirstName и LastName, удерживая нажатой клавишу CTRL (или клавишу Shift, так как столбцы соседние). Перейдите Преобразование –> Формат –> Каждое Слово С Прописной:

Рис. 8.8. Действия в интерфейсе, использующее Table.TransformColumns

Эти действия инициируют преобразование, использующее функцию Table.TransformColumns:

Table.TransformColumns(
    Запрос1,
    {
        {"FirstName", Text.Proper, type text},
        {"LastName", Text.Proper, type text}
    }
)

Table.TransformColumns(

Запрос1,

{

{"FirstName", Text.Proper, type text},

{"LastName", Text.Proper, type text}

}

)

Запрос1 – это таблица для преобразования. Второй параметр функции – это список списков для каждого столбца в формате { column name, transformation } или { column name, transformation, new column type }.

Полный синтаксис функции:

Table.TransformColumns(
    table as table,
    transformOperations as list,
    optional defaultTransformation as nullable function,
    optional missingField as nullable number,
) as table

Table.TransformColumns(

table as table,

transformOperations as list,

optional defaultTransformation as nullable function,

optional missingField as nullable number,

) as table

Однако, если вы имеете дело с очень широкой таблицей и вам нужно применить одно преобразование ко всем столбцам, управление длинным списком transformOperations может стать громоздким. Вместо этого можно указать defaultTransformation, которое будет применяться ко всем столбцам таблицы. Для этого оставьте список transformOperations пустым, вставьте запятую после него и укажите функцию преобразования по умолчанию для преобразования всех столбцов:

Table.TransformColumns(
    Source,
    {},
    each Text.Proper( Text.From(_))
)

Table.TransformColumns(

Source,

{},

each Text.Proper( Text.From(_))

)

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

Table.TransformColumns(Source,
    {
        {"EmployeeID", each _, type number},
        {"Salary", each _, type number}
    },
    Text.Proper
)

Table.TransformColumns(Source,

{

{"EmployeeID", each _, type number},

{"Salary", each _, type number}

Text.Proper

)

Здесь мы исключили столбцы EmployeeID и Salary из преобразования Text.Proper по умолчанию. Для этих двух столбцов мы сохранили значения, назначив тип number.

Формула по умолчанию упростилась, поскольку не требуется превращать значения в текст (значения в столбцах FirstName и LastName и так текстовые).

Было: each Text.Proper( Text.From(_))

Стало: Text.Proper

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

List.Accumulate(
   List.Select(Table.ColumnNames(Source), each Text.EndsWith(_, "Name")),
   Source,
   (s, a) => Table.TransformColumns(s, {a, each Text.Proper(_), type text})
)

List.Accumulate(

List.Select(Table.ColumnNames(Source), each Text.EndsWith(_, "Name")),

Source,

(s, a) => Table.TransformColumns(s, {a, each Text.Proper(_), type text})

)

Следующий код работает ничуть не хуже:

List.Accumulate(
   List.Select(Table.ColumnNames(Source), each Text.EndsWith(_, "Name")),
   Source,
   (s, a) => Table.TransformColumns(s, {a, Text.Proper, type text})
)

List.Accumulate(

List.Select(Table.ColumnNames(Source), each Text.EndsWith(_, "Name")),

Source,

(s, a) => Table.TransformColumns(s, {a, Text.Proper, type text})

)

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

Table.TransformColumns(
  Source,
  List.Combine(
    {
      List.Transform({"FirstName", "LastName"}, each {_, each Text.Proper(_), type text}),
      List.Transform({"Salary"}, each {_, each Text.From(_), type text})
    }
  )
)

Table.TransformColumns(

Source,

List.Combine(

{

List.Transform({"FirstName", "LastName"}, each {_, each Text.Proper(_), type text}),

List.Transform({"Salary"}, each {_, each Text.From(_), type text})

}

)

Здесь содержимое столбцов FirstName и LastName преобразует в правильный текст, а содержимое столбца Salary – в текст. Важно отметить, что если вы указываете более одного списка transformOperations для любого столбца, возникнет ошибка. И вы не можете получить доступ к другому столбцу, кроме того, который подвергается преобразованию.

Table.ReplaceValue

Функция Table.ReplaceValue обладает широкими возможностями настройки и позволяет точно манипулировать данными. Ее можно вызвать через интерфейс. Мы повторно используем данные EmployeeData, так что убедитесь, что они находятся в исходном состоянии. Другими словами, оставьте только шаг Source.

Допустим, мы хотим заменить фамилию bever на brown. Выделите столбец LastName, пройдите Преобразование –> Замена значений –> Замена значений. Настройте диалоговое окно:

Рис. 8.9. Диалоговое окно Замена значений

После подтверждения генерируется код M:

Table.ReplaceValue(
    Source,
    "bever",
    "brown",
    Replacer.ReplaceValue,
    {"LastName"}
)

Table.ReplaceValue(

Source,

"bever",

"brown",

Replacer.ReplaceValue,

{"LastName"}

)

Функция Table.ReplaceValue вызывается со следующими аргументами:

Source – таблица, подлежащая преобразованию
bever – старое значение для поиска и замены
brown – новое значение
ReplaceValue – функция-заменитель стандартной библиотеки М, которая заменяет все содержимое ячейки новым значением
{"LastName"} – аргумент columnsToSearch – список имен столбцов, в которых следует искать старое значение

Подробнее о синтаксисе функции Table.ReplaceValue см. powerquery.how

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

Table.ReplaceValue(
    Source,
    each [LastName],
    each if [EmployeeID]=103 then "brown" else [LastName],
    Replacer.ReplaceValue,
    {"LastName"}
)

Table.ReplaceValue(

Source,

each [LastName],

each if [EmployeeID]=103 then "brown" else [LastName],

Replacer.ReplaceValue,

{"LastName"}

)

При замене значений в нескольких столбцах можно расширить список columnsToSearch новыми именами столбцов. Однако важно понимать, что значения в этих столбцах должны совпадать с указанным значением oldValue или условием, чтобы произошла замена. Рассмотрим следующее изменение, в котором столбец FirstName был включен в список столбцов:

Table.ReplaceValue(
    Source,
    each [LastName],
    each if [EmployeeID]=103 then "brown" else [LastName],
    Replacer.ReplaceValue,
    { "FirstName", "LastName"}
)

Table.ReplaceValue(

Source,

each [LastName],

each if [EmployeeID]=103 then "brown" else [LastName],

Replacer.ReplaceValue,

{ "FirstName", "LastName"}

)

Если бы для EmployeeID = 103 столбец FirstName содержал bever, он был бы изменен на brown.

Хотя это не самый интересный с практической точки зрения пример, давайте продолжим. Допустим, нужно заменить FirstName и LastName для EmployeeID = 103 на brown. Вы можете создать пользовательскую замену:

Table.ReplaceValue(
    Source,
    each [EmployeeID]=103,
    "brown",
    (x, y, z)=> if y then z else x,
    {"FirstName", "LastName"}
)

Table.ReplaceValue(

Source,

each [EmployeeID]=103,

"brown",

(x, y, z)=> if y then z else x,

{"FirstName", "LastName"}

)

Четвертый аргумент функции Table.ReplaceValue представляет собой пользовательскую функцию, определенную с тремя параметрами: текущее значения (currentValue), старое значение (oldValue) и новое значение (newValue). Эти параметры, хотя и имеют произвольные имена, играют ключевую роль в определении поведения операции замены.

Функция работает, оценивая, выполняется ли условие, заданное oldValue (y). Если это условие выполняется, что означает, что критерий удовлетворен, функция возвращает newValue (z). В противном случае она сохраняет текущее значение (currentValue) (x) ячейки. Такой условный подход позволяет контролировать процесс замены, гарантируя, что изменения вносятся только при соблюдении определенных критериев. Подробнее о заменах мы поговорим в главе 11 Сравнение, замена, комбинирование, разделение.

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

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

Работа со списками

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

Преобразование списка

На каждый элемент в списке можно ссылаться по его индексу – числу, которое отражает отсчитываемую от нуля позицию в списке. Первый элемент имеет индекс 0, за ним следует второй элемент с индексом 1 и т. д. Метод, называемый доступом к элементу (item access), предоставляет доступ к каждому элементу списка, по индексу в фигурных скобках, { }.

List.Transform

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

List.Transform( {1, 4, 9}, Number.Sqrt ) // возвращает {1, 2, 3}

1	List.Transform( {1, 4, 9}, Number.Sqrt ) // возвращает {1, 2, 3}

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

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

Запрос EmployeeData
let
    Source = Table.FromRows(
        {
            {101, "john", "prince", 50000},
            {102, "alice", "wonder", 60000},
            {103, "bob", "bever", 55000}
        },
        type table [
            EmployeeID=number,
            FirstName=text,
            LastName=text,
            Salary=number
        ]
    )
in
    Source

Запрос EmployeeData

let

Source = Table.FromRows(

{

{101, "john", "prince", 50000},

{102, "alice", "wonder", 60000},

{103, "bob", "bever", 55000}

type table [

EmployeeID=number,

FirstName=text,

LastName=text,

Salary=number

]

)

Source

Запрос 8.1
let
    colNames = Table.ColumnNames( EmployeeData ),
    requestPositions = { 1, 2 },
    getColNames = List.Transform(
        requestPositions,
        each colNames{_}
    )
in
    getColNames // возвращает список { FirstName, LastName }

Запрос 8.1

let

colNames = Table.ColumnNames( EmployeeData ),

requestPositions = { 1, 2 },

getColNames = List.Transform(

requestPositions,

each colNames{_}

)

getColNames // возвращает список { FirstName, LastName }

Возвращаются имена столбцов для второго и третьего столбцов таблицы.

List.Zip

Еще одна полезная функция при работе со списками – List.Zip. Ее нельзя вызвать в пользовательском интерфейсе. Функция принимает список списков в качестве единственного аргумента и возвращает новый список со списками, в которых элементы из всех входных списков объединены на основе их общей позиции в индексе:

let
    list1 = {"A", "B", "C"},
    list2 = {1, 2, 3, 4, 5},
    zippedList = List.Zip({list1, list2})
in
    zippedList

let

list1 = {"A", "B", "C"},

list2 = {1, 2, 3, 4, 5},

zippedList = List.Zip({list1, list2})

zippedList

Первый результирующий список включает «A» из list1 и 1 из list2, которые оба находятся в позиции индекса 0. Этот процесс продолжается для всех элементов:

Рис. 8.10. Входные и результирующие списки List.Zip

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

Рис. 8.10а. Содержание вложенных списков

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

Запрос 8.2
let
    myTable = Table.FromRecords({
        [ID=1, Name="Alice"], [ID=2, Name="Bob"]
    }),
    oldNames = Table.ColumnNames(myTable),
    newNames = {"Identifier", "FullName"},
    zippedNames = List.Zip({oldNames, newNames}),
    renamedCols = Table.RenameColumns(myTable, zippedNames, MissingField.Ignore),
    Custom1 = {  "{ID, Identifier}", "{Name, FullName}" }
in
    Custom1

Запрос 8.2

let

myTable = Table.FromRecords({

[ID=1, Name="Alice"], [ID=2, Name="Bob"]

}),

oldNames = Table.ColumnNames(myTable),

newNames = {"Identifier", "FullName"},

zippedNames = List.Zip({oldNames, newNames}),

renamedCols = Table.RenameColumns(myTable, zippedNames, MissingField.Ignore),

Custom1 = { "{ID, Identifier}", "{Name, FullName}" }

Custom1

Шаг zippeNames берет элемент из oldNames и элемент из входного списка newNames и формирует новый список списков, каждый из которых содержит старое имя столбца, за которым следует новое имя столбца.

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

let
    myTable = Table.FromRecords({
        [Type = "nested table", Value=
            Table.FromRecords({
                [ID=1, Name="Alice"], [ID=2, Name="Bob"]
            })
        ]}),
    NestedRename = Table.TransformColumns( myTable, {{"Value", each
    let
        oldNames = Table.ColumnNames(_),
        newNames = {"Identifier", "FullName"},
        zippedNames = List.Zip({oldNames, newNames}),
        renamedCols = Table.RenameColumns(_, zippedNames)
    in
        renamedCols, type table}}
    )
in
    NestedRename

let

myTable = Table.FromRecords({

[Type = "nested table", Value=

Table.FromRecords({

[ID=1, Name="Alice"], [ID=2, Name="Bob"]

})

]}),

NestedRename = Table.TransformColumns( myTable, {{"Value", each

let

oldNames = Table.ColumnNames(_),

newNames = {"Identifier", "FullName"},

zippedNames = List.Zip({oldNames, newNames}),

renamedCols = Table.RenameColumns(_, zippedNames)

renamedCols, type table}}

)

NestedRename

Во вложенном выражении let переменные запроса 8.2 повторно используются для преобразования всех вложенных таблиц в столбце Value.

Извлечение элемента

Помимо доступа к элементам, есть всего несколько функций, которые извлекают элементы списка: List.First, List.Last, List.Single и List.SingleOrDefault. Если входной список пуст, то List.First и List.Last возвращают null, а List.SingleOrDefault – значение по умолчанию. К сожалению List.Single в этом случае возвращает ошибку. Рассмотрим следующее выражение записи:

[
    myEmptyList = {},
    listFirst = List.First(myEmptyList),
    listLast = List.Last(myEmptyList),
    listSingle = List.Single( myEmptyList ),
    singleOrDefault = List.SingleOrDefault(myEmptyList),
    singleOrDefault2 = List.SingleOrDefault(myEmptyList, 99),
    myNonEmptyList = {1, 2, 3},
    listFirst2 = List.First(myNonEmptyList),
    listLast2 = List.Last(myNonEmptyList),
    listSingle2 = List.Single(myNonEmptyList),
    singleOrDefault3 = List.SingleOrDefault(myNonEmptyList, 99)
]

[

myEmptyList = {},

listFirst = List.First(myEmptyList),

listLast = List.Last(myEmptyList),

listSingle = List.Single( myEmptyList ),

singleOrDefault = List.SingleOrDefault(myEmptyList),

singleOrDefault2 = List.SingleOrDefault(myEmptyList, 99),

myNonEmptyList = {1, 2, 3},

listFirst2 = List.First(myNonEmptyList),

listLast2 = List.Last(myNonEmptyList),

listSingle2 = List.Single(myNonEmptyList),

singleOrDefault3 = List.SingleOrDefault(myNonEmptyList, 99)

]

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

Рис. 8.11. Доступ к элементам списка с помощью M функций

Обратите внимание, что указание списка с более чем одним элементом вызовет ошибку как в List.Single, так и в List.SingleOrDefault. Эти функции кажутся неинтересными, но они пригодятся в сочетании с другими функциями M, которые изменяют размер или фильтруют списки.

Изменение размера списка

Несколько функций могут изменять размер списка, не трогая содержащиеся в нем элементы. Эти функции создают новый список с меньшим или большим количеством элементов на основе смещения или количества: List.FirstN, List.RemoveLastN, List.Skip и List.Repeat. В этом разделе мы поговорим о двух других функциях: List.Range и List.Alternate.

List.Range

Функция List.Range возвращает подмножество элементов списка. Как и большинство функций списка, она не может быть вызвана из пользовательского интерфейса.

List.Range(
    list as list,
    offset as number,
    optional count as nullable number
) as list

List.Range(

list as list,

offset as number,

optional count as nullable number

) as list

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

let
    Source = Table.FromColumns(
        {
           {1..5},
           {987, 645, 843, 754, 398}
        }, type table
        [i = number, Amount = number]
    ),
    listAmount = Source[Amount]
in
    listAmount

let

Source = Table.FromColumns(

{

{1..5},

{987, 645, 843, 754, 398}

}, type table

[i = number, Amount = number]

listAmount = Source[Amount]

listAmount

Запрос возвращает список на основе значений столбца Amount. Несмотря на то, что идентификатор шага называется listAmount, на панели Примененные шаги он отображается как Навигация.

Рис. 8.12. Исходный список на основе значений столбца Amount

Выражение listAmount будет использовано для вычисления нарастающих итогов. В главе 13 Итерация и рекурсия мы представим более эффективный метод вычисления промежуточных итогов. Но в некоторых сценариях List.Range может быть неплохой альтернативой. Функция дает простой метод создания списка для суммирования.

Пойдем дальше. Кликните f_Х рядом со строкой формул, чтобы вставить шаг. В строке формул удалите listAmount и вставьте код:

Table.AddColumn(
    Source,
    "simple RT prep",
    each List.Range( listAmount, 0, [i] ), type list
)

Table.AddColumn(

Source,

"simple RT prep",

each List.Range( listAmount, 0, [i] ), type list

)

В области Примененные шаги переименуйте шаг в simpleRunningTotal. [i] – номер текущей строки.

Рис. 8.13. Список в третьей строке содержит первые три значения столбца listAmount

Значение в столбце i соответствует количеству элементов, содержащихся в каждом списке. Верхняя строка содержит список с одним элементом, а нижняя – со всеми пятью.

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

Table.AddColumn(
        Source,
        "reverse RT prep",
        each List.Range( listAmount, [i]-1 ), type list
)

Table.AddColumn(

Source,

"reverse RT prep",

each List.Range( listAmount, [i]-1 ), type list

)

В области Примененные шаги переименуйте шаг в reverseRunningTotal. Первый аргумент остался прежним, а вот второй мы сделали динамическим, сославшись на столбец [i], который является индексом, начинающимся с 1. Чтобы получить значение, отсчитываемое от нуля, мы вычли 1. Нет необходимости указывать третий аргумент, так как нам нужны все оставшиеся элементы списка:

Рис. 8.14. Список во второй строке содержит все значения, кроме первого

Теперь, давайте посмотрим, как вернуть значение текущей и следующей строки. Кликните f_Х рядом со строкой формул, чтобы вставить шаг. В строке формул удалите reverseRunningTotal и вставьте код:

Table.AddColumn(
    Source,
    "current and next",
    each List.Range( listAmount, [i]-1, 2 ), type list
)

Table.AddColumn(

Source,

"current and next",

each List.Range( listAmount, [i]-1, 2 ), type list

)

В области Примененные шаги переименуйте шаг в getCurrentAndNext. Первый и второй аргументы остались прежними, а третий мы сделали статичным и равным 2.

Рис. 8.15. Получение текущей и следующей строки

Список в последней пятой позиции вернет один элемент.

Можно также получить значения текущей и предыдущей строк. Для этого требуется обработка ошибок, о которой вы узнаете в главе 12. Кликните f_Х рядом со строкой формул. В строке формул удалите getCurrentAndNext и вставьте код:

Table.AddColumn(
    Source,
    "previous and current",
    each try List.Range(listAmount, [i] - 2, 2) otherwise {null, [Amount]},
    type list
)

Table.AddColumn(

Source,

"previous and current",

each try List.Range(listAmount, [i] - 2, 2) otherwise {null, [Amount]},

type list

)

Конструкция try <expression> otherwise <alternative> используется для обработки ошибок. Она позволяет безопасно выполнять выражения, которые могут вызвать ошибки, и предоставляет альтернативные значения или действия в случае возникновения ошибки. Здесь <expression> – выражение, которое может вызвать ошибку, <alternative> – значение или выражение, которое будет использоваться в случае ошибки.

Для первой строки (i = 1) выражение [i]-2 вернет -1. Такого индекса нет, поэтому выражение each<…> вернет список {null, [Amount]}.

В области Примененные шаги переименуйте шаг в getPrevAndCurrent.

Рис. 8.16. Получение значений Amount из предыдущей и текущей строк

При использовании List.Range в табличном пространстве требуется столбец индекса, чтобы сделать смещение (и/или счетчик) динамическими. С List.Range тесно связаны List.RemoveRange и List.InsertRange. Синтаксис List.RemoveRange:

List.RemoveRange(
   list as list,
   index as number,
   optional count as nullable number,
) as list

List.RemoveRange(

list as list,

index as number,

optional count as nullable number,

) as list

index, отсчитываемый от 0 указывает, с какой позиции удалять; count, отсчитываемый от 1 говорит, сколько элементов удалить.

Синтаксис List.InsertRange:

List.InsertRange(
   list as list,
   index as number,
   values as list,
) as list

List.InsertRange(

list as list,

index as number,

values as list,

) as list

index указывает, с какой позиции начать вставлять элементы; values – список вставляемых значений.

List.Alternate

Синтаксис:

List.Alternate(
   list as list,
   count as number,
   optional repeatInterval as nullable number,
   optional offset as nullable number,
) as list

List.Alternate(

list as list,

count as number,

optional repeatInterval as nullable number,

optional offset as nullable number,

) as list

List.Alternate создает новый список, содержащий элементы исходного списка, чередуя элементы с заданным интервалом и количеством пропускаемых элементов:

list – исходный список, из которого будут браться элементы;

offset – количество начальных элементов, которые будут извлечены, отсчитывается от 0;

repeatInterval – интервал (шаг), с которым будут извлекаться элементы.

count – количество элементов, пропускаемых (отбрасываемых) на каждом шаге.

Взгляните на эту запись:

[
    myList = {"a", 1, "b", 2, "c", 3},
    numerals = List.Alternate( myList, 1, 1 ),
    letters = List.Alternate( myList, 1, 1, 1 )
]

[

myList = {"a", 1, "b", 2, "c", 3},

numerals = List.Alternate( myList, 1, 1 ),

letters = List.Alternate( myList, 1, 1, 1 )

]

Рис. 8.17. Возвращаемые значения для каждой переменной в записи

Чтобы извлечь цифры мы пропускаем один элемент (count = 1) через 1 (repeatInterval = 1), начиная пропуск с первого элемента (offset опущен). Чтобы извлечь буквы мы пропускаем один элемент (count = 1) через 1 (repeatInterval = 1), начиная пропуск со второго элемента, т.е., первый оставляем (offset = 1, отсчитывается от 0).

Фильтрация списка

Как и при изменении размера списков, функции выбора и фильтрации уменьшают количество элементов в списке, но не по позиции, а на основе значения элемента списка. К функциям фильтрации относятся: List.RemoveNulls, List.FindText и List.Select.

List.FindText

… выбирает элементы списка, содержащие подстроку из второго аргумента. Обратите внимание, что второй аргумент принимает только значение типа text и функция выполняет сопоставление с учетом регистра:

let
    myList = {"a", 1, "b", 2, "c", 3, "ba"},
    findText = List.FindText( myList, "a")
in
    findText // возвращает {"a", "ba"}

let

myList = {"a", 1, "b", 2, "c", 3, "ba"},

findText = List.FindText( myList, "a")

findText // возвращает {"a", "ba"}

List.Select

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

[
  myList       = {"a", 1, "b", 2, "c", 3, "ba"},
  selectByType = List.Select(myList, each _ is number),
  selectIsOdd  = List.Select(myList, each try Number.IsOdd(_) otherwise false),
  selectByLen  = List.Select(myList, each try Text.Length(_) >= 2 otherwise false)
]

[

myList = {"a", 1, "b", 2, "c", 3, "ba"},

selectByType = List.Select(myList, each _ is number),

selectIsOdd = List.Select(myList, each try Number.IsOdd(_) otherwise false),

selectByLen = List.Select(myList, each try Text.Length(_) >= 2 otherwise false)

]

Сохраняются только те элементы, для которых функция выбора возвращает true:

Рис. 8.18. Значения, возвращаемые для каждой переменной в записи

Важно помнить, что списки могут содержать значения любого типа. Поэтому, если вы не можете гарантировать тип значения каждого элемента списка, разумно включить обработку ошибок – конструкцию try … otherwise.

Более продвинутый сценарий использования List.Select рассматривается в главе 11.

Преобразования в список

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

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

Запрос EmployeeData
let
    Source = Table.FromRows(
        {
            {101, "john", "prince", 50000},
            {102, "alice", "wonder", 60000},
            {103, "bob", "bever", 55000}
        },
        type table [
            EmployeeID=number,
            FirstName=text,
            LastName=text,
            Salary=number
        ]
    )
in
    Source

Запрос EmployeeData

let

Source = Table.FromRows(

{

{101, "john", "prince", 50000},

{102, "alice", "wonder", 60000},

{103, "bob", "bever", 55000}

type table [

EmployeeID=number,

FirstName=text,

LastName=text,

Salary=number

]

)

Source

Взглянув на таблицу, легко увидеть основные строительные блоки: заголовки, они же имена полей записи type table [ … ]; данные, представленные в коде списком списков. Каждая коллекция элементов может быть преобразована в список. Мы рассмотрим их по порядку в следующих разделах.

Имена столбцов

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

[
    tblColNames = Table.ColumnNames( EmployeeData ),
    recFieldNames = Record.FieldNames( EmployeeData{0} ),
    tblColumnsOfType = Table.ColumnsOfType( EmployeeData, {Text.Type})
]

[

tblColNames = Table.ColumnNames( EmployeeData ),

recFieldNames = Record.FieldNames( EmployeeData{0} ),

tblColumnsOfType = Table.ColumnsOfType( EmployeeData, {Text.Type})

]

Рис. 8.19. Полученные имена столбцов таблицы / полей записи

Один столбец

Следующая запись показывает как получить все значения столбца в виде списка:

[
    drilldown = EmployeeData[FirstName],
    tblColumn1 = Table.Column( EmployeeData, "FirstName" ),
    tblColumn2 = Table.Column( EmployeeData,
        Table.ColumnNames( EmployeeData ){1} ),
    tblToColumns1 = Table.ToColumns( EmployeeData){1},
    tblToList1 = Table.ToList( EmployeeData[[FirstName]] )
]

[

drilldown = EmployeeData[FirstName],

tblColumn1 = Table.Column( EmployeeData, "FirstName" ),

tblColumn2 = Table.Column( EmployeeData,

Table.ColumnNames( EmployeeData ){1} ),

tblToColumns1 = Table.ToColumns( EmployeeData){1},

tblToList1 = Table.ToList( EmployeeData[[FirstName]] )

]

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

Рис. 8.20. Получение значений одного столбца

Все столбцы

Получение всех столбцов таблицы вернет список списков:

[
    tblToColumns = Table.ToColumns( EmployeeData )
]

[

tblToColumns = Table.ToColumns( EmployeeData )

]

Последовательность вложенных списков соответствует порядку столбцов таблицы:

Рис. 8.21. Получение значений всех столбцов

Все строки

Методы и функции для получения значений из строк:

[
  tblToRows = Table.ToRows(EmployeeData),
  tblToList = Table.ToList(
    Table.TransformColumnTypes(
      EmployeeData,
      List.Transform(Table.ColumnNames(EmployeeData), each {_, type text})
    )
  ),
  tblToRecords = Table.ToRecords(EmployeeData),
  recFieldValues = Record.FieldValues(EmployeeData{0}),
  recToList = Record.ToList(EmployeeData{0})
]

[

tblToRows = Table.ToRows(EmployeeData),

tblToList = Table.ToList(

Table.TransformColumnTypes(

EmployeeData,

List.Transform(Table.ColumnNames(EmployeeData), each {_, type text})

)

tblToRecords = Table.ToRecords(EmployeeData),

recFieldValues = Record.FieldValues(EmployeeData{0}),

recToList = Record.ToList(EmployeeData{0})

]

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

Рис. 8.22. Получение значений строк

Другие операции

Существуют и другие распространенные операции, например разделение и объединение:

[
    tblSplit = Table.Split( EmployeeData, 1),
    lst = Table.ToRows( EmployeeData),
    lstCombine = List.Combine( lst ),
    lstSplit = List.Split( lstCombine, Table.ColumnCount(EmployeeData))
]

[

tblSplit = Table.Split( EmployeeData, 1),

lst = Table.ToRows( EmployeeData),

lstCombine = List.Combine( lst ),

lstSplit = List.Split( lstCombine, Table.ColumnCount(EmployeeData))

]

Рис. 8.23. Полученные значения строк

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

Одновременное развертывание списков в нескольких столбцах

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

Рис. 8.24. Таблица, содержащая списки в нескольких столбцах

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

let
    myTable = Table.FromRecords({
        [   Type = "set 1",
            ID = {1, 2},
            Name = {"Alice", "Bob"}
        ],
        [   Type = "set 2",
            ID = {3, 4},
            Name = {"Sam", "Kate"}
        ]
    }),
    combinedLists = Table.AddColumn(myTable, "Combined", each
        Table.FromColumns( {[ID], [Name]}, {"ID", "Name"}), type table [ID=Int64.Type, Name=text]
    ),
    cleanUpColumns = Table.SelectColumns( combinedLists, {"Type", "Combined"}, MissingField.UseNull ),
    expandAllLists = Table.ExpandTableColumn(cleanUpColumns, "Combined", {"ID", "Name"}, {"ID", "Name"}),
    fxToTable = ( twoCols as list ) as table =>
        Table.FromColumns(twoCols, {"ID", "Name"}),
    noAddCols = Table.FromList(
        Table.ToList( myTable, each
            {_{0}} & { fxToTable({_{1}, _{2}}) }
        ),
        each _,
        type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]
    )
in
    noAddCols

let

myTable = Table.FromRecords({

[ Type = "set 1",

ID = {1, 2},

Name = {"Alice", "Bob"}

[ Type = "set 2",

ID = {3, 4},

Name = {"Sam", "Kate"}

]

}),

combinedLists = Table.AddColumn(myTable, "Combined", each

Table.FromColumns( {[ID], [Name]}, {"ID", "Name"}), type table [ID=Int64.Type, Name=text]

cleanUpColumns = Table.SelectColumns( combinedLists, {"Type", "Combined"}, MissingField.UseNull ),

expandAllLists = Table.ExpandTableColumn(cleanUpColumns, "Combined", {"ID", "Name"}, {"ID", "Name"}),

fxToTable = ( twoCols as list ) as table =>

Table.FromColumns(twoCols, {"ID", "Name"}),

noAddCols = Table.FromList(

Table.ToList( myTable, each

{_{0}} & { fxToTable({_{1}, _{2}}) }

each _,

type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]

)

noAddCols

Разберем код. myTable создает таблицу из трех столбцов. Столбец Type содержит текстовое значение, а столбцы ID и Name – списки, как показано на рис. 8.24. Чтобы избежать нежелательного дублирования при развертывании этих двух столбцов, мы будем рассматривать списки как значения столбцов и объединять их вместе, чтобы сформировать таблицу.

combinedList добавляет столбец с именем Combined в myTable. Этот шаг мы выполним в интерфейсе, пройдя Добавление столбца –> Настраиваемый столбец. Вызывается функция Table.AddColumn и отображается диалоговое окно:

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

Мы дали имя столбцу и вставили формулу. Мы использовали функцию Table.FromColumns, которой передали значения из столбцов ID и Name на основе синтаксиса: {[ID], [Name]}. Table.FromColumns вернет таблицу с двумя столбцами. В качестве второго аргумента мы предоставили список с новыми именами столбцов: {"ID", "Name"}.

После нажатия OK мы дополнили код, автоматически созданный интерфейсом, присвоив тип значению таблицы – type table [ID=Int64.Type, Name=text].

cleanUpColumns. Столбцы со списками потеряли актуальность. Мы выбрали их и прошли Главная –> Удалить столбцы –> Удалить столбцы. При этом была вызвана функция Table.SelectColumns, чтобы сохранить столбцы Type и Combined. Затем руками вставили необязательный параметр MissingField равный перечислению MissingField.UseNull. Это защитит запрос от ошибки, если указанного в списке поля нет. Для всех отсутствующих полей функция вернет null.

expandAllLists извлекает все значения из вложенных таблиц столбца Combined. Для выполнения операции кликните на значке с двумя боковыми стрелками в заголовке столбца Combined.

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

    fxToTable = ( twoCols as list ) as table =>
        Table.FromColumns(twoCols, {"ID", "Name"}),
    noAddCols = Table.FromList(
        Table.ToList( myTable, each
            {_{0}} & { fxToTable({_{1}, _{2}}) }
        ),
        each _,
        type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]
    )

fxToTable = ( twoCols as list ) as table =>

Table.FromColumns(twoCols, {"ID", "Name"}),

noAddCols = Table.FromList(

Table.ToList( myTable, each

{_{0}} & { fxToTable({_{1}, _{2}}) }

each _,

type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]

)

Обновите переменную после предложения in, заменив expandAllLists на noAddCols. Нажмите Готово. Хотя в данный момент вам не нужно полностью понимать этот код, мы разберем его для дальнейшего использования.

Создается пользовательская функция под названием fxToTable, которая предназначена для вызова на значениях из столбцов 2 и 3, которые соответствуют столбцам ID и Name из myTable. Ее синтаксис соответствует выражению пользовательского столбца, разобранному выше на шаге combinedList.

Шаг noAddCols более сложный и выполняет несколько операций. Начнем с внутреннего выражения:

(а) Table.ToList(myTable, each {_{0}} & {fxToTable({_{1}, _{2}})})

1	(а) Table.ToList(myTable, each {_{0}} & {fxToTable({_{1}, _{2}})})

Оно преобразует myTable в список списков значений строк, применяя функцию к значениям каждой строки. Из таблицы, показанной на рис. 8.24, видно, что мы берем значение из первого столбца и помещаем его в список: {_{0}}. Амперсанд & позволяет нам объединить этот список с другим списком, содержащим таблицу. Эта таблица создается путем вызова пользовательской функции fxToTable на значениях второго и третьего столбцов: {fxToTable({_{1}, _{2}})}.

(b) Table.FromList(…, each _)

1	(b) Table.FromList(…, each _)

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

(c) type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]

1	(c) type table [Type=text, Combined=table [ID=Int64.Type, Name=text]]

Table.FromList также позволяет нам указать столбцы для новой таблицы; мы предоставили тип таблицы для определения имен и типов столбцов.

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

Выравнивание многослойных вложенных списков с непостоянной структурой

Вложенные списки могут содержать ценные данные, которые требуют распаковки или выравнивания для дальнейшего анализа. Часто для выравнивания многослойных вложенных списков используется рекурсия. Однако мы представим альтернативный способ достижения того же результата. Метод включает использование функций Json.FromValue и Text.FromBinary, для создания строки для манипуляции, как это демонстрируется в пользовательской функции flattenList. Не беспокойтесь, если вы не знакомы с пользовательскими функциями. Они будут рассмотрены в следующей главе.

let
    inputList =
        {
            {"Product A", {"Product A1", "Product A2"}},
            {{"Product A1", "Product A2"}, {{"Product B"}}},
            {"Product C", {"Product C1", "Product C2", {"Product C2a", {{"Product C2b"}}}}, "Product D"},
            {"Product B", {{{"Product A", {{"Product A1", "Product A2"}}}}}},
            {"Product C", {"Product C1", "Product C2"}, {}}
        },
    myTable = Table.FromColumns({inputList},
        type table[to flatten = list]
    ),
    flattenList = (myList as list) as list =>
    let
        string = Text.FromBinary( Json.FromValue( myList )),
        cleanString = Text.Trim( Text.Remove( string, {"[", "]"}), "," ),
        convertToList = Splitter.SplitTextByDelimiter(",")(cleanString)
    in
        convertToList,
    InvokedOnList = flattenList( inputList ),
    InvokedOnTable = Table.TransformColumns( myTable,
        {{ "to flatten", flattenList, type list }}
    )
in
    InvokedOnTable

let

inputList =

{

{"Product A", {"Product A1", "Product A2"}},

{{"Product A1", "Product A2"}, {{"Product B"}}},

{"Product C", {"Product C1", "Product C2", {"Product C2a", {{"Product C2b"}}}}, "Product D"},

{"Product B", {{{"Product A", {{"Product A1", "Product A2"}}}}}},

{"Product C", {"Product C1", "Product C2"}, {}}

myTable = Table.FromColumns({inputList},

type table[to flatten = list]

flattenList = (myList as list) as list =>

let

string = Text.FromBinary( Json.FromValue( myList )),

cleanString = Text.Trim( Text.Remove( string, {"[", "]"}), "," ),

convertToList = Splitter.SplitTextByDelimiter(",")(cleanString)

convertToList,

InvokedOnList = flattenList( inputList ),

InvokedOnTable = Table.TransformColumns( myTable,

)

InvokedOnTable

Разберем этот код:

flattenList – пользовательская функция, которая принимает входные данные типа списка и создает выходное значение типа списка. Тело функции содержит выражение let:

string использует функцию FromValue для создания JSON-представления входного значения списка, которое преобразует каждое значение списка в массив JSON, заключая его в квадратные скобки [ ]. Функция Text.FromBinary преобразует этот JSON в значение текстового типа.
cleanString использует выражение Remove( string, {"[", "]"}) для удаления всех квадратных скобок, по сути, удаляя все уровни списка. Выходная строка может заканчиваться запятой в случаях, когда окончательный вложенный список был пустым. Чтобы защитить нашу функцию, эта возможная конечная запятая должна быть удалена: Text.Trim( …, "," ).
convertToList вызывает SplitTextByDelimiter(",") для получения функции, которая вызывается с переменной cleanString для получения значения типа списка.

InvokedOnList иллюстрирует, как пользовательская функция flattenList может быть вызвана для переменной inputList.

InvokedOnTable показывает, как можно использовать пользовательскую функцию flattenList в Table.TransformColumns для преобразования каждого значения строки в столбце to flatten.

После вызова функции flattenList остается только один список, содержащий текстовые значения. Однако, если какие-либо текстовые значения в этих многоуровневых вложенных списках содержат квадратные скобки, операция cleanString удалит их; это ограничение, о котором вы должны знать.

Язык M включает в себя широкий спектр функций для работы со списками. В этом разделе мы описали только малую часть, чтобы дать представление об их возможностях. Основное внимание мы уделили демонстрации методов преобразования, извлечения, изменения размера и выбора элементов списка, которые были проиллюстрированы примерами кода. Далее мы рассмотрим, как работать с записями.

Работа с записями

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

Преобразование записей

На каждое значение поля в записи можно ссылаться по имени поля – уникальному идентификатору в записи. Метод, известный как выбор поля, предоставляет доступ к каждому значению поля, используя имя в квадратных скобках [ ]. Функция Record.TransformFields изменяет значения полей записи. Однако она играет гораздо более скромную роль по сравнению с Table.TransformColumns, которая подробно описана в разделе Преобразование значений в таблицах. Преобразование значений по столбцам встречается чаще, чем изменение полей по строкам. В результате использование функции Record.TransformFields является более редким.

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

let
    rec = [
        EmployeeID = "102",
        FirstName = "Alice",
        LastName = "Wonder",
        Salary = "6000.0"
    ],
    recTransformFields = Record.TransformFields( rec,
        {
            {"EmployeeID", Number.FromText},
            {"Salary", each Number.FromText(_, "en-US")}
        }
    ),
    recMerge = rec &
        [
            EmployeeID = Number.FromText(rec[EmployeeID]),
            Salary = Number.FromText(rec[Salary], "en-US")
        ],
    recCombine = Record.Combine(
        {
            rec,
            [
                EmployeeID = Number.FromText(rec[EmployeeID]),
                Salary = Number.FromText(rec[Salary], "en-US")
            ]
        })
in
    recCombine

let

rec = [

EmployeeID = "102",

FirstName = "Alice",

LastName = "Wonder",

Salary = "6000.0"

recTransformFields = Record.TransformFields( rec,

{

{"EmployeeID", Number.FromText},

{"Salary", each Number.FromText(_, "en-US")}

}

recMerge = rec &

[

EmployeeID = Number.FromText(rec[EmployeeID]),

Salary = Number.FromText(rec[Salary], "en-US")

recCombine = Record.Combine(

{

rec,

[

EmployeeID = Number.FromText(rec[EmployeeID]),

Salary = Number.FromText(rec[Salary], "en-US")

]

})

recCombine

Рис. 8.27. Значение rec до модификации

Переменные recTransformFields, recMerge и recCombine дают один и тот же результат (сравни с рис. 8.28), хотя и используют разные методы. recTransformFields использует библиотечную функцию Record.TransformFields. Список transformOperations состоит из списков в формате {имя поля, преобразование}, по одному для каждого преобразуемого поля, без возможности указания типа. У функции нет defaultTransformation, зато имеется необязательный параметр missingField.

Рис. 8.28. Значение rec после модификации

На шагах recMerge и recCombine сначала ссылаются на исходную запись (левый операнд), а затем на вновь созданную запись (правый операнд). Новая запись содержит два поля с именами полей из исходной записи, что позволяет перезаписать значения этих полей во время операции объединения / слияния.

Извлечение значения поля

В М существуют две функции, Record.Field и Record.FieldOrDefault, которые извлекают значение поля из записи. У функций нет параметра missingField. Функция Record.Field вернет ошибку, если переданная во втором аргументе текстовая строка не соответствует ни одному из имен полей в записи. Record.FieldOrDefault вернет null или значение по умолчанию, если оно было указано:

[
    rec = [
        EmployeeID = "102",
        FirstName = "Alice",
        LastName = "Wonder",
        Salary = 6000
    ],
    recField = Record.Field( rec, "FirstName"),
    recField2 = Record.Field( rec, "FirstNames"),
    recFieldOrDefault = Record.FieldOrDefault( rec, "FirstName"),
    recFieldOrDefault2 = Record.FieldOrDefault( rec, "FirstNames"),
    recFieldOrDefault3 = Record.FieldOrDefault( rec, "FirstNames", "Unknown")
]

[

rec = [

EmployeeID = "102",

FirstName = "Alice",

LastName = "Wonder",

Salary = 6000

recField = Record.Field( rec, "FirstName"),

recField2 = Record.Field( rec, "FirstNames"),

recFieldOrDefault = Record.FieldOrDefault( rec, "FirstName"),

recFieldOrDefault2 = Record.FieldOrDefault( rec, "FirstNames"),

recFieldOrDefault3 = Record.FieldOrDefault( rec, "FirstNames", "Unknown")

]

Рис. 8.29. Record.Field и Record.FieldOrDefault в действии

В начале этой главы мы объяснили, что выражение записи похоже на выражение let. Приведенный пример – хорошая иллюстрация этой идеи. Изучите многоступенчатый процесс преобразования содержимого в столбце Value:

let
    tbl = #table(type table[Value=number], {{100}}),
    transform = Table.TransformColumns( tbl,
        {
            { "Value", each
                [
                    a = Number.E,
                    b = Number.RoundAwayFromZero(a, 0),
                    c = _ * b
                ][c], type number
            }
        }
    )
in
    transform

let

tbl = #table(type table[Value=number], {{100}}),

transform = Table.TransformColumns( tbl,

{

{ "Value", each

[

a = Number.E,

b = Number.RoundAwayFromZero(a, 0),

c = _ * b

][c], type number

}

)

transform

Конкретное вычисление или преобразование здесь не важно. Мы хотим показать процесс генерации значения и передачи этого вновь созданного значения для использования в других выражениях. Доступ к полю применяется к выражению записи путем добавления имени поля в квадратных скобках. Здесь возвращается значение поля [c].

Устранить неполадки с вложенным выражением записи очень просто – верните всю запись, закомментировав выбор поля и приписанный тип (при наличии). Например так: выделите фрагмент кода [c], type number и нажмите Alt + Shift + A, чтобы превратить его в комментарий.

Рис. 8.30. Возврат всей записи для отладки кода

Изменение размера записей

В дополнение к оператору объединения и проекции, существуют четыре функции, которые могут изменять размер записи: Record.RemoveFields, Record.AddField, Record.SelectFields и Record.Combine. Мы используем их для создания новой записи и изменения количества полей.

Рис. 8.31. Новые значения записей, содержащие меньшее или большее количество полей

[
    rec = [
        EmployeeID = "102",
        FirstName = "Alice",
        LastName = "Wonder",
        Salary = "6000.0"
    ],
    recRemoveFields = Record.RemoveFields( rec, {"LastName"}),
    recRemoveFields2 = Record.RemoveFields( rec, {"LastNames"}, MissingField.Ignore ),
    recAddField = Record.AddField( rec, "fxGreeting", ()=> "Hi!" ),
    recAddField2 = Record.AddField( rec, "fxGreeting", ()=> "Hi!", true ),
    recCombine = Record.Combine({rec, [fxGreeting = ()=> "Hi!"]}),
    recSelectFields = Record.SelectFields( recAddField2, {"fxGreeting"} ),
    recSelectFields2 = Record.SelectFields( recAddField2, {"fxGreetings"}, MissingField.UseNull )
]

[

rec = [

EmployeeID = "102",

FirstName = "Alice",

LastName = "Wonder",

Salary = "6000.0"

recRemoveFields = Record.RemoveFields( rec, {"LastName"}),

recRemoveFields2 = Record.RemoveFields( rec, {"LastNames"}, MissingField.Ignore ),

recAddField = Record.AddField( rec, "fxGreeting", ()=> "Hi!" ),

recAddField2 = Record.AddField( rec, "fxGreeting", ()=> "Hi!", true ),

recCombine = Record.Combine({rec, [fxGreeting = ()=> "Hi!"]}),

recSelectFields = Record.SelectFields( recAddField2, {"fxGreeting"} ),

recSelectFields2 = Record.SelectFields( recAddField2, {"fxGreetings"}, MissingField.UseNull )

]

Record.RemoveFields и Record.SelectFields имеют необязательный параметр missingField. Record.AddField имеет необязательный параметр delay. Если delay отсутствует, вычисляется определение функции и возвращается значение function. Если указать delay = true, то функция вызывается для оценки и возврата значения функции, как показано на рис. 8.31.

Фильтрация записей

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

[
    rec = [
        EmployeeID = "102",
        FirstName = "Alice",
        LastName = "Wonder",
        Salary = 6000
    ],
    fxFieldsOfType =
        (r as record,
         t as type
        ) as record => [
            findFields = List.Select(
                Record.FieldNames(r),
                each Value.Is(Record.Field(r, _), t)
            ),
            getRecord = Record.SelectFields(
                r,
                findFields
            )
        ][getRecord],
    invokedCF1 = fxFieldsOfType(rec, Number.Type ),
    fxFieldNameStartsOrEndsWith =
        (
            r as record,
            t as text,
            optional startsWith as logical,
            optional ignoreCase as logical
        ) as record => [
            lookAtFunction = if startsWith ?? true
                then Text.StartsWith
                else Text.EndsWith,
            ignoreCase = if ignoreCase ?? true
                then Comparer.OrdinalIgnoreCase
                else Comparer.Ordinal,
            findFields = List.Select(
                Record.FieldNames(r),
                each lookAtFunction(_, t, ignoreCase)
            ),
            getRecord = Record.SelectFields(
                r,
                findFields
            )
        ][getRecord],
    invokedCF2 = fxFieldNameStartsOrEndsWith(rec, "name", false )
]

[

rec = [

EmployeeID = "102",

FirstName = "Alice",

LastName = "Wonder",

Salary = 6000

fxFieldsOfType =

(r as record,

t as type

) as record => [

findFields = List.Select(

Record.FieldNames(r),

each Value.Is(Record.Field(r, _), t)

getRecord = Record.SelectFields(

findFields

)

][getRecord],

invokedCF1 = fxFieldsOfType(rec, Number.Type ),

fxFieldNameStartsOrEndsWith =

(

r as record,

t as text,

optional startsWith as logical,

optional ignoreCase as logical

) as record => [

lookAtFunction = if startsWith ?? true

then Text.StartsWith

else Text.EndsWith,

ignoreCase = if ignoreCase ?? true

then Comparer.OrdinalIgnoreCase

else Comparer.Ordinal,

findFields = List.Select(

Record.FieldNames(r),

each lookAtFunction(_, t, ignoreCase)

getRecord = Record.SelectFields(

findFields

)

][getRecord],

invokedCF2 = fxFieldNameStartsOrEndsWith(rec, "name", false )

]

Пользовательская функция fxFieldsOfType позволяет выбрать значения полей указанного типа. Она принимает в качестве входных данных значение записи и тип. Тело функции содержит выражение записи с переменными findFields и getRecord.

findFields использует функцию Record.Field для получения значений полей записи и передачи их в функцию Value.Is одно за другим. При этом оценивается, совместим ли тип значения с типом аргумента (t). Если совместим (значение равно true), List.Select сохраняет имя поля, предоставленное функцией Record.FieldNames(r), в противном случае имя поля опускается.

getRecord использует функцию Record.SelectFields для выбора всех полей из записи (r), которые прошли проверку совместимости и имена которых перечислены в списке findFields. [getRecord] осуществляет выбор поля в выражении записи, чтобы вернуть значение из поля getRecord.

invokedCF1 иллюстрирует, как пользовательская функция fxFieldsOfType может быть вызвана для записи rec, извлекая значения для полей с типом Number.Type.

Пользовательская функция fxFieldNameStartsOrEndsWith отбирает имена полей, начинающиеся или заканчивающиеся подстрокой, по умолчанию игнорируя регистр. Функция принимает четыре параметра (два обязательных и два необязательных) и возвращает значение записи: r – запись, t – текстовая подстрока, startsWith и ignoreCase – логические параметры, по умолчанию имеющие значение true. Тело функции содержит выражение записи.

lookAtFunction – условный оператор, принимает startsWith, объединяет его с null (??), по умолчанию присваивает значение true, и возвращает Text.StartsWith, если значение startsWith не указано или true, и Text.EndsWith если значение startsWith = false.

ignoreCase работает аналогичным образом, возвращая OrdinalIgnoreCase, если ignoreCase не указано или true, и Comparer.Ordinal если ignoreCase = false.

findFields использует функцию List.Select для сохранения всех имен полей из списка, предоставленного функцией Record.FieldNames, для которых примененная функция lookAtFunction(_, t, ignoreCase) возвращает значение true.

invokedCF2 дает пример вызова пользовательской функции fxFieldNameStartsOrEndsWith путем предоставления записи (rec) и строки («name») и установки необязательного startsWith (false).

Преобразования в запись

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

Преобразование строки таблицы в запись

Обращение к одной строке таблицы возвращает запись. Это можно увидеть и без использования кода M. Создайте пустой запрос и сошлитесь на таблицу EmployeeData, введя =EmployeeData в строке формул. Слева от таблицы есть номера строк:

Рис. 8.31а. Номера строк

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

При получении строки помните, что позиционные индексы в M начинаются с нуля. Чтобы получить вторую запись, нужно применить оператор позиционного индекса {1}:

Рис. 8.32. Выбор одной строки таблицы возвращает запись

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

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

После нажатия OK сгенерируется выражение:

Table.AddColumn(Источник, "rec", each _)

1	Table.AddColumn(Источник, "rec", each _)

В таблицу будет добавлен новый столбец, содержащий в каждой строке вложенную запись.

Запись из таблицы

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

Эта операция встречается при создании записи для поиска и замены. Рассмотрим запрос:

let
    Attributes = Table.FromRows(
        {
            {"Colour", "Red", "X1"},
            {"Colour", "White", "X2"},
            {"Colour", "Blue", "X3"},
            {"Colour", "blue", "X3"}
        }, type table[Attribute=text, Value=text, Name=text]
    ),
in
    Attributes

let

Attributes = Table.FromRows(

{

{"Colour", "Red", "X1"},

{"Colour", "White", "X2"},

{"Colour", "Blue", "X3"},

{"Colour", "blue", "X3"}

}, type table[Attribute=text, Value=text, Name=text]

Attributes

Рис. 8.32б. Исходная таблица с атрибутами

Чтобы создать запись подстановки из таблицы, для начала нужно оставить в таблице два столбца. Первый столбец должен содержать уникальные текстовые значения, которые будут использоваться в качестве имен полей записи. Это столбец Name. Второй столбец должен содержать значения полей. Они могут быть любого типа. Это столбец Value. В записи подстановки имя поля выступает в качестве текста для сопоставления, а значение поля – в качестве соответствующего значения замены.

Удерживая нажатой клавишу Ctrl, выделите столбцы в следующем порядке: сначала столбец Name (1), затем столбец Value (2). Пройдите Главная –> Удалить столбцы (3) –> Удалить другие столбцы (4):

Рис. 8.33. Порядок выбора столбцов и удаление других столбцов

Выберите столбец Name, пройдите Преобразование –> Столбец cведения. Настройте диалоговое окно:

Рис. 8.34. Столбец сведения

Нажав OK, вы сгенерите код:

= Table.Pivot(
    #"Removed Other Columns",
    List.Distinct(#"Removed Other Columns"[Name]),
    "Name",
    "Value"
)

= Table.Pivot(

#"Removed Other Columns",

List.Distinct(#"Removed Other Columns"[Name]),

"Name",

"Value"

)

Включите выражение внутрь Table.ToRecords, не забудьте закрывающую скобку в конце:

= Table.ToRecords(
    Table.Pivot(
        #"Removed Other Columns",
        List.Distinct(#"Removed Other Columns"[Name]),
        "Name",
        "Value"
    )
)

= Table.ToRecords(

Table.Pivot(

#"Removed Other Columns",

List.Distinct(#"Removed Other Columns"[Name]),

"Name",

"Value"

)

Это выражение возвращает список, содержащий одну запись. Чтобы получить доступ к значению записи, кликните на record. Это эквивалентно добавлению к предыдущему коду {0} после закрывающей скобки. В качестве альтернативы выражение Table.ToRecords(…) можно поместить внутрь функции List.First.

К сожалению одно из полей возвращает ошибку:

Рис. 8.36. Отображение ошибки на уровне поля или ячейки

Это связано с тем, что первый столбец таблицы Name содержит повторяющиеся значения (см. рис. 8.33).

Чтобы решить эту проблему, вернитесь на шаг Другие удаленные столбцы (Removed Other Columns). Проверьте данные на наличие дубликатов и решите, что с ними делать. Для третьей и четвертой строк идентификаторы в столбце Name одинаковые – Х3. Вы можете удалить строку с blue, отфильтровав по столбцу Value. Для этого пройдите Главная –> Удалить строки –> Удалить дубликаты или Удалить строки –> Удалить нижние строки –> 1. Когда появится окно о вставке шага, кликните Вставить.

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

В качестве альтернативы после удаления дубликатов можно обойтись и без сводной таблицы. Воспользуемся функцией Record.FromTable, для которой требуется таблица с двумя столбцами, первый из которых содержит уникальный текстовый ключ. Столбцы должны именоваться Name и Value. Если имена столбцов таблицы отличаются или столбец Name не содержит уникальных значений, вернется ошибка. Для реализации этого метода перейдите к шагу Удаленные дубликаты, кликните f_X рядом со строкой формул, чтобы создать новый шаг. В строке формул оберните автоматически появившуюся ссылку на предыдущий шаг функцией:

Record.FromTable(#"Удаленные дубликаты")

1	Record.FromTable(#"Удаленные дубликаты")

Запись из списка

Еще один метод создания записи – функция Record.FromList. Эта функция принимает список значений, список имен полей или тип записи:

let
    Rec1 = Record.FromList(
        {102, "alice", "wonder", 60000},
        { "EmployeeID", "FirstName", "LastName", "Salary" }
    ),
    Rec2 = Record.FromList(
        {102, "alice", "wonder", 60000},
        type [
            EmployeeID=number,
            FirstName=text,
            LastName=text,
            Salary=number
        ]
    )
in
    Rec2

let

Rec1 = Record.FromList(

{102, "alice", "wonder", 60000},

{ "EmployeeID", "FirstName", "LastName", "Salary" }

Rec2 = Record.FromList(

{102, "alice", "wonder", 60000},

type [

EmployeeID=number,

FirstName=text,

LastName=text,

Salary=number

]

)

Rec2

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

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

Условный поиск или замена значения

Возможность использовать запись для поиска очень интересна. Этот метод может заменить сложные объединения Excel ВПР и ПРОСМОТРX, и его можно использовать для получения промежуточных значений в более сложных расчетах или преобразованиях. Например, замена идентификаторов на описательные имена упростит анализ данных в Excel, сделав их более насыщенными и понятными для конечных пользователей:

let
    Attributes = Table.FromRows(
        {
            {"Colour", "X1", "Red"},
            {"Colour", "X2", "White"},
            {"Colour", "X3", "Blue"},
            {"Size", "X1", "S"},
            {"Size", "X2", "M"},
            {"Size", "X3", "L"},
            {"Size", "X4", "XL"},
            {"Size", "X5", "XXL"}
        }, type table[Attribute=text, ID=text, Value=text]
    ),
    toRecord = Table.Group(Attributes, {"Attribute"},
        {{"lookup", each Record.FromList( [Value], [ID] )}}),
    lookup = Record.FromList( toRecord[lookup], toRecord[Attribute] ),
    Source = Table.FromRows(
        {
            {"345-6245", "X5", "X1"},
            {"645-1366", "X3", "X2"},
            {"824-9206", "X4", "X3"},
            {"627-2078", "X3", "X3"},
            {"273-7134", "X4", "X2"}
        }, type table[Item=text, Size ID=text, Colour ID=text]
    ),
    AddDescription = Table.AddColumn( Source, "Description", each
        Record.FieldOrDefault( lookup[Size], [Size ID], "Unknown" )
        & ", " &
        Record.FieldOrDefault( lookup[Colour], [Colour ID], "Unknown" ), type text
    ),
    ReplaceWithDescription = Table.TransformColumns( Source,
        {{"Size ID", each Record.FieldOrDefault( lookup[Size], _, "Unknown" ), type text},
        {"Colour ID", each Record.FieldOrDefault( lookup[Colour], _, "Unknown" ), type text}}
    )
in
    ReplaceWithDescription

let

Attributes = Table.FromRows(

{

{"Colour", "X1", "Red"},

{"Colour", "X2", "White"},

{"Colour", "X3", "Blue"},

{"Size", "X1", "S"},

{"Size", "X2", "M"},

{"Size", "X3", "L"},

{"Size", "X4", "XL"},

{"Size", "X5", "XXL"}

}, type table[Attribute=text, ID=text, Value=text]

toRecord = Table.Group(Attributes, {"Attribute"},

{{"lookup", each Record.FromList( [Value], [ID] )}}),

lookup = Record.FromList( toRecord[lookup], toRecord[Attribute] ),

Source = Table.FromRows(

{

{"345-6245", "X5", "X1"},

{"645-1366", "X3", "X2"},

{"824-9206", "X4", "X3"},

{"627-2078", "X3", "X3"},

{"273-7134", "X4", "X2"}

}, type table[Item=text, Size ID=text, Colour ID=text]

AddDescription = Table.AddColumn( Source, "Description", each

Record.FieldOrDefault( lookup[Size], [Size ID], "Unknown" )

& ", " &

Record.FieldOrDefault( lookup[Colour], [Colour ID], "Unknown" ), type text

ReplaceWithDescription = Table.TransformColumns( Source,

{{"Size ID", each Record.FieldOrDefault( lookup[Size], _, "Unknown" ), type text},

{"Colour ID", each Record.FieldOrDefault( lookup[Colour], _, "Unknown" ), type text}}

)

ReplaceWithDescription

Рассмотрим переменные в этом коде.

Attributes – таблица, содержащая атрибуты элементов: цвет и размер.

Рис. 8.36а. Шаг Attributes

toRecord группирует таблицу Attributes по столбцу Attribute, чтобы обеспечить уникальные идентификаторы для каждой группы. Создает запись, где ID – имя поля, а Values – значение поля. Подробнее операция Группировать по… описана в следующем разделе Работа с таблицами.

Рис. 8.36б. Шаг toRecord

Сгруппированные записи затем используются для создания одной записи на шаге lookup. Она служит словарем. Значения атрибутов являются ключами, а связанные записи (созданные на предыдущем шаге) – значениями.

Рис. 8.36в. Шаг lookup

Source – таблица, содержащая коды элементов с их размерами и идентификаторами цветов.

Рис. 8.36г. Шаг Source

AddDescription добавляет столбец Description в таблицу Source. При этом выполняется поиск размера и цвета в записи подстановки. Если идентификатор не найден, будет возвращено значение по умолчанию Unknown.

Рис. 8.36д. Шаг AddDescription

ReplaceWithDescription заменяет значения идентификатора размера и идентификатора цвета в источнике соответствующими описаниями.

Рис. 8.36е. Шаг ReplaceWithDescription

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

Работа с таблицами

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

Мы будем работать с набором данных, названных SurveysData. В дальнейшем мы будем обращаться к нему по этому имени

let
    Source = Table.FromRows( List.Zip( { List.Transform( {"1".."5"}, each "Wave "& _ ),
        Table.Group( Table.FromRows(
        {
            {456, 30, "Female", "Intermediate", "High", "Good", "Yes", "Well organized content", #date(2023,3,1), #date(2023,3,10), 5, 8},
            {457, 52, "Male", "Expert", "Medium", "Good", "Yes", "Requires frequent updates", #date(2023,3,1), #date(2023,3,10), 3, 6},
            {458, 24, "Female", "Beginner", "High", "Excellent", "Yes", "Very engaging and easy to follow", #date(2023,3,1), #date(2023,3,10), 7, 9},
            {459, 43, "Male", "Intermediate", "Low", "Average", "No", "The interface is not user-friendly", #date(2023,3,1), #date(2023,3,10), 2, 4},
            {460, 28, "Non-binary", "Expert", "High", "Good", "Yes", "Feature-rich and versatile", #date(2023,3,1), #date(2023,3,10), 6, 8},
            {461, 37, "Female", "Beginner", "Medium", "Poor", "No", "Overwhelming for beginners", #date(2023,3,11), #date(2023,3,20), 1, 5},
            {462, 46, "Male", "Intermediate", "High", "Excellent", "Yes", "High performance and reliability", #date(2023,3,11), #date(2023,3,20), 4, 8},
            {463, 21, "Female", "Beginner", "Low", "Poor", "No", "Too complex for novices", #date(2023,3,11), #date(2023,3,20), 1, 3},
            {464, 55, "Male", "Expert", "Medium", "Average", "Yes", "Solid but could be more intuitive", #date(2023,3,11), #date(2023,3,20), 3, 6},
            {465, 33, "Non-binary", "Intermediate", "High", "Good", "Yes", "Adaptable to various tasks", #date(2023,3,11), #date(2023,3,20), 5, 7},
            {466, 40, "Female", "Beginner", "Medium", "Average", "No", "Lacks in-depth tutorials", #date(2023,3,21), #date(2023,3,30), 2, 5},
            {467, 26, "Male", "Expert", "High", "Excellent", "Yes", "Efficient in processing large datasets", #date(2023,3,21), #date(2023,3,30), 6, 9},
            {468, 48, "Female", "Intermediate", "Low", "Poor", "No", "Unreliable with frequent downtime", #date(2023,3,21), #date(2023,3,30), 1, 3},
            {469, 23, "Non-binary", "Beginner", "Medium", "Good", "Yes", "Good for beginners, but lacks advanced features", #date(2023,3,21), #date(2023,3,30), 3, 6},
            {470, 39, "Male", "Expert", "High", "Excellent", "Yes", "Supports a wide range of functions", #date(2023,3,21), #date(2023,3,30), 5, 8},
            {471, 34, "Female", "Intermediate", "High", "Good", "Yes", "User community is very helpful", #date(2023,3,21), #date(2023,3,30), 4, 7},
            {472, 50, "Male", "Beginner", "Low", "Average", "No", "Difficult to get started without assistance", #date(2023,4,1), #date(2023,4,10), 2, 4},
            {473, 22, "Female", "Expert", "Medium", "Good", "Yes", "Impressive analytics capabilities", #date(2023,4,1), #date(2023,4,10), 6, 7},
            {474, 41, "Non-binary", "Intermediate", "High", "Excellent", "Yes", "Meets professional standards", #date(2023,4,1), #date(2023,4,10), 4, 8},
            {475, 29, "Male", "Beginner", "Medium", "Average", "No", "Features are not well explained", #date(2023,4,1), #date(2023,4,10), 2, 5}
        },  {"RespondentID", "Age", "Gender", "ExperienceLevel", "OverallQuality", "EaseOfUse", "WouldRecommend", "SpecificFeedback", "SurveyStartDate", "SurveyEndDate", "UsageFrequency", "SatisfactionScore"}
    ), {"SurveyStartDate"}, {{"Results", each _ , type table }})[Results] & {#table({"RespondentID", "Age", "Gender", "ExperienceLevel", "OverallQuality", "EaseOfUse", "WouldRecommend", "SpecificFeedback", "SurveyStartDate", "SurveyEndDate", "UsageFrequency", "SatisfactionScore"}, {})} }),
    type table [Survey=text, Results= table [RespondentID=number, Age=number, Gender=text, ExperienceLevel=text, OverallQuality=text, EaseOfUse=text, WouldRecommend=text, SpecificFeedback=text, SurveyStartDate=date, SurveyEndDate=date, UsageFrequency=number, SatisfactionScore=number]]
    )
in
    Source

let

Source = Table.FromRows( List.Zip( { List.Transform( {"1".."5"}, each "Wave "& _ ),

Table.Group( Table.FromRows(

{

{456, 30, "Female", "Intermediate", "High", "Good", "Yes", "Well organized content", #date(2023,3,1), #date(2023,3,10), 5, 8},

{457, 52, "Male", "Expert", "Medium", "Good", "Yes", "Requires frequent updates", #date(2023,3,1), #date(2023,3,10), 3, 6},

{458, 24, "Female", "Beginner", "High", "Excellent", "Yes", "Very engaging and easy to follow", #date(2023,3,1), #date(2023,3,10), 7, 9},

{459, 43, "Male", "Intermediate", "Low", "Average", "No", "The interface is not user-friendly", #date(2023,3,1), #date(2023,3,10), 2, 4},

{460, 28, "Non-binary", "Expert", "High", "Good", "Yes", "Feature-rich and versatile", #date(2023,3,1), #date(2023,3,10), 6, 8},

{461, 37, "Female", "Beginner", "Medium", "Poor", "No", "Overwhelming for beginners", #date(2023,3,11), #date(2023,3,20), 1, 5},

{462, 46, "Male", "Intermediate", "High", "Excellent", "Yes", "High performance and reliability", #date(2023,3,11), #date(2023,3,20), 4, 8},

{463, 21, "Female", "Beginner", "Low", "Poor", "No", "Too complex for novices", #date(2023,3,11), #date(2023,3,20), 1, 3},

{464, 55, "Male", "Expert", "Medium", "Average", "Yes", "Solid but could be more intuitive", #date(2023,3,11), #date(2023,3,20), 3, 6},

{465, 33, "Non-binary", "Intermediate", "High", "Good", "Yes", "Adaptable to various tasks", #date(2023,3,11), #date(2023,3,20), 5, 7},

{466, 40, "Female", "Beginner", "Medium", "Average", "No", "Lacks in-depth tutorials", #date(2023,3,21), #date(2023,3,30), 2, 5},

{467, 26, "Male", "Expert", "High", "Excellent", "Yes", "Efficient in processing large datasets", #date(2023,3,21), #date(2023,3,30), 6, 9},

{468, 48, "Female", "Intermediate", "Low", "Poor", "No", "Unreliable with frequent downtime", #date(2023,3,21), #date(2023,3,30), 1, 3},

{469, 23, "Non-binary", "Beginner", "Medium", "Good", "Yes", "Good for beginners, but lacks advanced features", #date(2023,3,21), #date(2023,3,30), 3, 6},

{470, 39, "Male", "Expert", "High", "Excellent", "Yes", "Supports a wide range of functions", #date(2023,3,21), #date(2023,3,30), 5, 8},

{471, 34, "Female", "Intermediate", "High", "Good", "Yes", "User community is very helpful", #date(2023,3,21), #date(2023,3,30), 4, 7},

{472, 50, "Male", "Beginner", "Low", "Average", "No", "Difficult to get started without assistance", #date(2023,4,1), #date(2023,4,10), 2, 4},

{473, 22, "Female", "Expert", "Medium", "Good", "Yes", "Impressive analytics capabilities", #date(2023,4,1), #date(2023,4,10), 6, 7},

{474, 41, "Non-binary", "Intermediate", "High", "Excellent", "Yes", "Meets professional standards", #date(2023,4,1), #date(2023,4,10), 4, 8},

{475, 29, "Male", "Beginner", "Medium", "Average", "No", "Features are not well explained", #date(2023,4,1), #date(2023,4,10), 2, 5}

}, {"RespondentID", "Age", "Gender", "ExperienceLevel", "OverallQuality", "EaseOfUse", "WouldRecommend", "SpecificFeedback", "SurveyStartDate", "SurveyEndDate", "UsageFrequency", "SatisfactionScore"}

), {"SurveyStartDate"}, {{"Results", each _ , type table }})[Results] & {#table({"RespondentID", "Age", "Gender", "ExperienceLevel", "OverallQuality", "EaseOfUse", "WouldRecommend", "SpecificFeedback", "SurveyStartDate", "SurveyEndDate", "UsageFrequency", "SatisfactionScore"}, {})} }),

type table [Survey=text, Results= table [RespondentID=number, Age=number, Gender=text, ExperienceLevel=text, OverallQuality=text, EaseOfUse=text, WouldRecommend=text, SpecificFeedback=text, SurveyStartDate=date, SurveyEndDate=date, UsageFrequency=number, SatisfactionScore=number]]

)

Source

Запрос SurveysData показан на рисунке 8.38 вместе с предварительным просмотром вложенной таблицы Wave 1. Обратите внимание, что показаны лишь некоторые столбцы вложенной таблицы. Для предварительного просмотра вложенных структур щелкните в любом свободном месте ячейки. Вы можете изменить просматриваемую вложенную таблицу с помощью клавиш со стрелками вверх и вниз. Быстро просмотрите вложенные таблицы, чтобы понять, с какими данными вы имеете дело:

Рис. 8.38. SurveysData и несколько столбцов, предпросмотра таблицы Wave 1

Преобразование таблиц

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

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

Извлечение значения ячейки

Детализация одной ячейки таблицы приведет к одному из двух вариантов синтаксиса, в зависимости от того, есть ли в таблице ключи. В любом случае, для получения значения из ячейки всегда сработает следующий метод. Сначала выберите строку (1), применив доступ к элементу, затем – столбец (2), применив выбор поля. Чтобы защитить выражение, вы можете сделать эти выборки необязательными, добавив вопросительный знак (?):

Рис. 8.39. Доступа к элементам и полям таблицы

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

= SurveysData

1	= SurveysData

Кликните символ мини-таблицы в левом верхнем углу таблицы (3), и выберите Добавить пользовательский столбец. Введите имя столбца Survey Start Date и формулу:

[Results]{0}?[SurveyStartDate]?

1	[Results]{0}?[SurveyStartDate]?

Что делает код на рис. 8.39? each [Results] – для каждой строки большой таблицы извлекает вложенную таблицу из столбца Results. [Results]{0} – извлекает первую строку (индекс 0) из вложенной таблицы. Однако, если таблица пуста, как в случае со строкой, соответствующей Wave 5, эта операция приводит к ошибке, так как нет строки для доступа. Добавив вопросительный знак [Results]{0}?, выбор становится необязательным. Если запрошенная строка отсутствует, вместо ошибки возвращается null.

Следующая часть, [SurveyStartDate], извлекает поле с именем SurveyStartDate из строки, полученной [Results]{0}? Однако, если [Results]{0}? вернул null, попытка доступа к SurveyStartDate приведет к ошибке. Это связано с тем, что операция пытается извлечь поле из значения null, а не из записи. Отсюда и включение второго вопросительного знака (?), что делает доступ к полю SurveyStartDate необязательным, тем самым предотвращая ошибки, если начальная часть возвращает значение null или поле отсутствует в записи. Такой методичный подход гарантирует, что запрос не завершится ошибкой даже при работе с отсутствующими, неполными или несоответствующими данными.

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

В М существует функция, которая сразу извлекает значение ячейки – Table.FirstValue. Однако она может получить значение только из первого столбца первой строки или вернуть значение по умолчанию. Конечно, существуют функции, которые могут возвращать одну запись из таблицы, такие как Table.First или Table.Last. В сочетании с доступом к полям они также позволяют получить определенное значение ячейки. Кроме того, важно иметь в виду, что эффективность этих функций возрастает в сочетании с другими функциями, которые изменяют размер или фильтруют таблицы.

Изменение размера таблицы по длине

Функции списка и таблиц имеют много общего. По аналогии с функциями списка существуют функции таблиц, изменяющие размер таблицы без доступа к значениям в ячейках. Такие функции возвращают новые таблицы с меньшим или большим количеством строк на основе смещения или количества. Среди них Table.Skip, Table.FirstN, Table.Range, Table.RemoveLastN и Table.Repeat. В этом разделе мы сосредоточимся на Table.RemoveFirstN и Table.AlternateRows.

Функции Table.RemoveFirstN и Table.Skip взаимозаменяемы. Обе они полезны для удаления нежелательных или «мусорных» строк из верхней части таблицы. Функции гибкие: вы можете указать строки, которые нужно удалить. Это может быть первая строка, заранее определенное количество строк или строки, соответствующие критериям. Например:

Запрос 8.3
let
    Source = SurveysData,
    Step1 = Table.AddColumn(Source, "NoCountOrCondition",
        each Table.RemoveFirstN([Results])),
    Step2 = Table.AddColumn(Step1, "Count",
        each Table.RemoveFirstN([Results], 2)),
    Step3 = Table.AddColumn(Step2, "Condition",
        each Table.RemoveFirstN([Results], each [Age] < 45 ))
in
    Step3

Запрос 8.3

let

Source = SurveysData,

Step1 = Table.AddColumn(Source, "NoCountOrCondition",

each Table.RemoveFirstN([Results])),

Step2 = Table.AddColumn(Step1, "Count",

each Table.RemoveFirstN([Results], 2)),

Step3 = Table.AddColumn(Step2, "Condition",

each Table.RemoveFirstN([Results], each [Age] < 45 ))

Step3

Рис. 8.40. Шаги запроса 8.3 и предпросмотр первых двух столбцов вложенной таблицы Wave 1

На каждом шаге запроса 8.3 добавляется столбец с урезанными таблицами. На рис. 8.40 в нижней части предпросмотра показаны первые два столбца таблицы Wave 1. Step 1 соответствует столбцу NoCountOrCondition. Чтобы удалить только верхнюю строку, опустите второй аргумент функции countOrCondition. Step 2 соответствует столбцу Count. Чтобы удалить несколько верхних строк во втором аргументе укажите счетчик, отсчитываемый от единицы. Step 3 соответствует столбцу Condition. Во втором аргументе функции Table.RemoveFirstN можно указать условие для удаления строк, например [Age] < 45. Условие работает не как фильтр, а как отсечка. Как только встретится первая строка не удовлетворяющая условию, она и все последующие строки будут возвращены.

Table.AlternateRows – еще одна полезная функция, похожая на List.Alternate по функциональности, но отличающаяся типом обрабатываемых значений. Внимание! Порядок параметров в этих двух функциях не совпадает.

Table.AlternateRows фильтрует данные в соответствии с повторяющимся шаблоном строк. Примеры использования:

Выборка или сокращение данных: в больших наборах данных функцию можно использовать для получения репрезентативной выборки, для разработки запроса на небольшом объеме данных, для предварительного статистического анализа.
Удаление избыточных заголовков после объединения данных: ограничено случаями, когда заголовки повторяются через фиксированные интервалы.
Фильтрация шума: при возникновении периодических помех в данных, например, когда каждая n-й строка – это техническое обслуживание или калибровка.

Table.AlternateRows( // все аргументы обязательны
   table as table, // исходная таблица
   offset as number, // смещение, отсчитывается от нуля
   skip as number, // количество пропускаемых строк, отсчитывается от 1
   take as number, // количество извлекаемых строк, отсчитывается от 1
) as table

Table.AlternateRows( // все аргументы обязательны

table as table, // исходная таблица

offset as number, // смещение, отсчитывается от нуля

skip as number, // количество пропускаемых строк, отсчитывается от 1

take as number, // количество извлекаемых строк, отсчитывается от 1

) as table

Запрос 8.4
let
    Source = SurveysData,
    Step1 = Table.AddColumn(Source, "V1",
        each Table.AlternateRows([Results], 0, 1, 1)),
    Step2 = Table.AddColumn(Step1, "V2",
        each Table.AlternateRows([Results], 1, 0, 1)),
    Step3 = Table.AddColumn(Step2, "V3",
        each Table.AlternateRows([Results], 1, 1, 0 )),
    Step4 = Table.AddColumn(Step3, "V4",
        each Table.AlternateRows([Results], 1, 1, 1 ))
in
    Step4

Запрос 8.4

let

Source = SurveysData,

Step1 = Table.AddColumn(Source, "V1",

each Table.AlternateRows([Results], 0, 1, 1)),

Step2 = Table.AddColumn(Step1, "V2",

each Table.AlternateRows([Results], 1, 0, 1)),

Step3 = Table.AddColumn(Step2, "V3",

each Table.AlternateRows([Results], 1, 1, 0 )),

Step4 = Table.AddColumn(Step3, "V4",

each Table.AlternateRows([Results], 1, 1, 1 ))

Step4

Запрос для Wave 1 генерирует следующие выходные данные (на этот раз представление ограничено только столбцом RespondentID):

Рис. 8.41. Результат запроса 8.4 для Wave 1 по шагам

Подробнее о шаге 1. Table.AlternateRows([Results], 0, 1, 1) применяет следующий шаблон для каждой строки входной таблице: начинает с верхней строки (смещение = 0), пропускает 1 строку, оставляет 1 строку, повторяет для всех оставшихся строк в таблице. Изучите результаты иных шагов самостоятельно.

Изменение размера таблицы по ширине

Изменение размера таблицы может включать в себя настройку как строк, так и столбцов. В случаях, когда вам нужно определенное количество столбцов – скажем, первые три – примените проекцию. Она позволяет создать таблицу с меньшим количеством столбцов. Этот метод эффективен и позволяет сделать выбор столбцов или полей необязательным, защищая выражение, как описано в главе 6 Структурированные значения.

Операция Удалить столбцы пользовательского интерфейса активирует функцию Table.RemoveColumns. А операции Выбор столбцов и Удалить другие столбцы – функцию Table.SelectColumns. У всех этих функций есть необязательный третий параметр MissingField.Type. Это перечисление определяет, как будет вести себя функция, когда нет столбца, указанного во втором параметре.

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

Рассмотрим оба метода, а начнем с Table.SelectColumns.

Запрос 8.5
let
    Source = SurveysData,
    Step1 = Table.AddColumn(Source, "V1", each
    Table.SelectColumns(
        [Results],
        {"RespondentID", "Age", "Gender", "Country"} )),
    Step2 = Table.AddColumn(Source, "V2", each
    Table.SelectColumns(
        [Results],
        {"RespondentID", "Age", "Gender", "Country"},
        MissingField.Ignore )),
    Step3 = Table.AddColumn(Source, "V3", each
    Table.SelectColumns(
        [Results],
        {"RespondentID", "Age", "Gender", "Country"},
        MissingField.UseNull ))
in
    Step3

Запрос 8.5

let

Source = SurveysData,

Step1 = Table.AddColumn(Source, "V1", each

Table.SelectColumns(

[Results],

{"RespondentID", "Age", "Gender", "Country"} )),

Step2 = Table.AddColumn(Source, "V2", each

Table.SelectColumns(

[Results],

{"RespondentID", "Age", "Gender", "Country"},

MissingField.Ignore )),

Step3 = Table.AddColumn(Source, "V3", each

Table.SelectColumns(

[Results],

{"RespondentID", "Age", "Gender", "Country"},

MissingField.UseNull ))

Step3

Чтобы ограничить таблицу определенным подмножеством столбцов, можно использовать Table.SelectColumns. В ней явно перечислены столбцы, которые необходимо сохранить. Обратите внимание, что столбцы вновь добавленные в источнике данных не будут включены без изменения кода запроса. По умолчанию функция вернет ошибку, если какое-либо из перечисленных имен столбцов отсутствует в исходной таблице:

Рис. 8.42. При выборе несуществующего столбца возникает ошибка

При использовании Table.SelectColumns с типом MissingField.Ignore (шаг 2 запроса 8.5), функция ведет себя по-другому. Если какие-либо имена столбцов, указанные в списке, не совпадают со столбцами в таблице, они будут автоматически исключены без возникновения ошибки. Однако такой подход может привести к проблемам на последующих шагах запроса, особенно если они зависят от поля, которое неожиданно отсутствует.

Рис. 8.43. MissingField.Ignore предотвращает ошибку и возвращает только допустимые столбцы

Если для типа MissingField задано значение MissingField.UseNull (Step3 запроса 8.5), функция включает в выходные данные все указанные столбцы. В том числе и отсутствующие в исходной таблице. Несуществующие столбцы будут содержать значения null, что не вызовет ошибки. Однако имейте в виду, что это может привести к сложностям на следующих этапах запроса, особенно при вычислениях или преобразованиях, которые зависят от значений этих полей.

Рис. 8.44. MissingField.UseNull предотвращает ошибку и возвращает все выбранные столбцы

C помощью Table.RemoveColumns можно удалить определенное подмножество столбцов. Функция явно перечисляет столбцы для удаления. Все новые столбцы, поступающие из источника данных, автоматически включаются в выходные данные. Излишне говорить, что это может привести к сохранению ненужных столбцов, если код не изменяется вручную. Использование функции с перечислениями MissingField.UseNull и MissingField.Ignore предотвращает ошибки при отсутствии указанных столбцов во втором параметре.

let
    Source = SurveysData,
    Step1 = Table.AddColumn(Source, "V1", each
    Table.RemoveColumns(
        [Results],
        {"Age", "Gender", "Country"} )),
    Step2 = Table.AddColumn(Source, "V2", each
    Table.RemoveColumns(
        [Results],
        {"Age", "Gender", "Country"},
        MissingField.Ignore )),
    Step3 = Table.AddColumn(Source, "V3", each
    Table.RemoveColumns(
        [Results],
        {"Age", "Gender", "Country"},
        MissingField.UseNull ))
in
    Step3

let

Source = SurveysData,

Step1 = Table.AddColumn(Source, "V1", each

Table.RemoveColumns(

[Results],

{"Age", "Gender", "Country"} )),

Step2 = Table.AddColumn(Source, "V2", each

Table.RemoveColumns(

[Results],

{"Age", "Gender", "Country"},

MissingField.Ignore )),

Step3 = Table.AddColumn(Source, "V3", each

Table.RemoveColumns(

[Results],

{"Age", "Gender", "Country"},

MissingField.UseNull ))

Step3

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

Фильтрация таблиц

Функции выбора или фильтрации могут уменьшить количество строк в таблице: Table.SelectRowsWithErrors, Table.FindText и Table.SelectRows. Последняя из них является одной из самых используемых в языке M и часто вызывается через интерфейс. Второй аргумент – условие в виде функции. Но прежде чем погрузиться в детали, рассмотрим основы.

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

Рис. 8.45. Сообщение указывает, что отображаются не все уникальные значения в столбце

Мы специально создали провокационный код:

Table.FromColumns(
    {List.Repeat({"High"}, 999) & {"Medium", "Low"}}
)

Table.FromColumns(

{List.Repeat({"High"}, 999) & {"Medium", "Low"}}

)

При просмотре выпадающего меню заголовка столбца из Column1 отображаются два значения (High и Medium), хотя в столбце представлено три значения. Если снять флажок High и нажать кнопку ОK, отобразятся только значения Medium. Это связано с тем, что M сгенерировал код ([Column1] = "Medium")), а не что-то типа ([Column1] <> "High").

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

Для контроля над данными пользовательский интерфейс предоставляет дополнительные фильтры. Например, для текстовых столбцов:

Рис. 8.45а. Набор фильтров для текстовых столбцов

Для столбцов с типом number и date набор фильтров будет иным.

Продолжим изучение фильтрации. Удалите шаг Строки с примененным фильтром. Применим Текстовый фильтр, выбрав Не равно. Откроется диалоговое окно Фильтрация строк. Введите High и нажмите OK. Теперь результат соответствует нашим ожиданиям. А код M отражает то, что нам было нужно: ([Column1] <> "High").

В диалоговом окне Фильтрация строк есть расширенная настройка – Подробнее, которая позволяет задавать сложные условия комбинированного фильтра. Однако она не обеспечивает контроль над приоритетом операторов. В языке M логическое И вычисляется перед логическим ИЛИ. Для изменения этого приоритета нужно использовать скобки.

Рис. 8.45б. Расширенная условная логика

Давайте рассмотрим второй параметр функции Table.SelectRows. Он ожидает функцию, по сути, логическую проверку. Когда выражение возвращает true, строка удовлетворяет критериям и сохраняется. Если выражение возвращает false, строка исключается из выходных данных.

Взгляните на критерии на рис. 8.46. Мы будем применять прямые сравнения с конкретными значениям столбцов. То есть столбец ExperienceLevel должен включать только строки со значениями Intermediate или Expert. А столбцы OverallQuality, EaseOfUse и WouldRecommended –High, Good и Yes соответственно.

Рис. 8.46. Несколько критериев фильтрации

Для начала создайте ссылку на запрос SurveysData: = SurveysData. Введите код, который преобразует каждую вложенную таблицу. Обратите внимание, как выражение в скобках используется для изменения приоритета операторов по умолчанию:

let
    Source = SurveysData,
    SelectRows = Table.TransformColumns( Source, {{"Results", each
        Table.SelectRows( _, each
            (
                [ExperienceLevel] = "Intermediate"
                or [ExperienceLevel] = "Expert"
            )
            and [OverallQuality] = "High"
            and [EaseOfUse] = "Good"
            and [WouldRecommend] = "Yes"
        ) }} )
in
    SelectRows

let

Source = SurveysData,

SelectRows = Table.TransformColumns( Source, {{"Results", each

Table.SelectRows( _, each

(

[ExperienceLevel] = "Intermediate"

or [ExperienceLevel] = "Expert"

)

and [OverallQuality] = "High"

and [EaseOfUse] = "Good"

and [WouldRecommend] = "Yes"

) }} )

SelectRows

Чтобы обеспечить точность сложных фильтров, можно добавить этап проверки. Хотя добавление столбца может показаться странным, оно дает преимущества в качестве вспомогательного средства разработки, позволяя точно настраивать критерии фильтрации и проверять результаты перед передачей логики фильтра на шаг SelectRows. Кроме того, вы можете исключить выполнение шага ValidateFilter во время выполнения кода, убедившись, что на него не ссылается какой-либо другой шаг и что он не является последним шагом в запросе. При соблюдении этого правила производительность не ухудшается. Между шагами Source и SelectRows вставьте шаг:

    ValidateFilter = Table.TransformColumns( Source, {{"Results", each
        Table.AddColumn( _, "Validation", each
            (
                [ExperienceLevel] = "Intermediate"
                or [ExperienceLevel] = "Expert"
            )
            and [OverallQuality] = "High"
            and [EaseOfUse] = "Good"
            and [WouldRecommend] = "Yes"
        ) }} ),

ValidateFilter = Table.TransformColumns( Source, {{"Results", each

Table.AddColumn( _, "Validation", each

(

[ExperienceLevel] = "Intermediate"

or [ExperienceLevel] = "Expert"

)

and [OverallQuality] = "High"

and [EaseOfUse] = "Good"

and [WouldRecommend] = "Yes"

) }} ),

В расширенном редакторе нажмите Готово. На панели Примененные шаги выберите шаг ValidateFilter и проверьте результат для каждой вложенной таблицы. На рисунке показаны критерии в столбце Validation для Wave 1:

Рис. 8.47. На шаге ValidateFilter отображается столбец Validation

Кроме того, вложенные таблицы не обязательно предоставляются внешними источниками. Они могут возникнуть в результате преобразования. Например, их можно использовать при поиске приблизительного совпадения, что является распространенным требованием при работе со скидками за объем. Что делает работу с вложенными таблицами сложной, так это необходимость доступа к полям как из внешних, так и из внутренних таблиц. А это требует понимание области видимости, которое рассматривалось в главе 7 Концепция M.

Приблизительное совпадение

Допустим у вас есть две таблицы: SalesData, содержащая заказы, и DiscountRates. Скидка зависит от стоимости заказа. Таблицы нельзя объединить, так как у них нет общего ключа.

Рис. 8.48. Таблицы SalesData и DiscountRate

Добавим новый столбец Net Value в таблицу SalesData. Из соображений аудита мы оставим столбец Total Value без изменений.

Рис. 8.48а. Чистая стоимость с учетом скидки

Изучим шаг AddNetValue подробнее:

each [Total Value] ссылается на значение в столбце Total Value таблицы SalesData, применимое к текущей строке.
Table.SelectRows(DiscountRates, (row) => row[Value] <= [Total Value]) фильтрует таблицу DiscountRates, чтобы сохранить строки, в которых значение меньше или равно Total Value для текущей строки в SalesData.
[Discount] извлекает столбец Discount из отфильтрованной таблицы в виде списка.
Last() извлекает последнее значение из этого списка скидок.
(1 — …) ?? 1 вычитает значение скидки из 1; при отсутствии скидки (если значение скидки равно null), возвращает значение по умолчанию = 1 (т.е., отсутствие скидки).

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

Преобразование записи в таблицу

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

Рис. 8.49. Предварительный просмотр выходных данных Record.ToTable при применении к каждой строке таблицы

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

Table.FillDown – заполняет значения вниз по столбцу, заменяя пустые ячейки значениями из вышестоящих непустых ячеек. Table.FillUp – заполняет значения вверх по столбцу, заменяя пустые ячейки значениями из нижестоящих непустых ячеек. Эти функции полезны для заполнения пропущенных значений в таблице, чтобы обеспечить целостность данных и упростить дальнейший анализ.

Нет прямого аналога для распространения значений горизонтально, по строке, слева направо или наоборот. Но, преобразовав каждую строку (запись) в таблицу с помощью Record.ToTable (как показано на рис. 8.49), вы можете обойти это ограничение.

Следующий код…

Запрос 8.6
let
    Source = Table.FromRows({
        {null, 2023, null, null, null, 2024},
        {"Branch", "Q1", "Q2", "Q3", "Q4", "Q1"},
        {"BU1", 463, 582, 325, 487, 582},
        {"BU2", 264, 384, 201, 298, 275}
}),
    FillRight = Table.AddColumn(Source, "Rec to tbl", each
        if [Column1] is null
        then Record.FromTable( Table.FillDown( Record.ToTable(_), {"Value"}))
        else _),
    ToTable = Table.FromRecords( FillRight[Rec to tbl] )
in
    ToTable

Запрос 8.6

let

Source = Table.FromRows({

{null, 2023, null, null, null, 2024},

{"Branch", "Q1", "Q2", "Q3", "Q4", "Q1"},

{"BU1", 463, 582, 325, 487, 582},

{"BU2", 264, 384, 201, 298, 275}

}),

FillRight = Table.AddColumn(Source, "Rec to tbl", each

if [Column1] is null

then Record.FromTable( Table.FillDown( Record.ToTable(_), {"Value"}))

else _),

ToTable = Table.FromRecords( FillRight[Rec to tbl] )

ToTable

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

Запрос 8.6 начинается с создания таблицы Source со значения null, которые нужно заполнить.

Рис. 8.49а. Исходная таблица со значениями null в строке

Ключевой частью запроса является шаг FillRight, где мы добавляем новый столбец, Rec to tbl. В этом столбце для каждой строки мы проверяем, является ли значение в столбце Column1 нулевым. Только для этой строки нам нужно использовать операцию FillRight. Если он равен null, мы преобразуем строку в таблицу с помощью Record.ToTable, а затем применяем функцию Table.FillDown к столбцу Value.

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

Создание таблиц из столбцов, строк или записей

Другая полезная категория функций – это функции, которые преобразуют таблицу в столбцы, строки, записи и обратно. Типичный шаблон, с которым вы столкнетесь, включает: (1) преобразование таблицы в список столбцов, строк или записей, (2) преобразование данных, (3) сборку элементов обратно в формат таблицы.

В этой книге есть множество примеров, которые следуют этой схеме. Чтобы избежать повторений, подробные демонстрации не включены в этот раздел. Но далее в разделе, озаглавленном Работа со смешанными структурами, будут рассмотрены примеры для каждой из функций Table.From:

FromColumns: создает таблицу из списка списков значений столбцов. Можно включить необязательный параметр columns. Порядок элементов списка определяет порядок столбцов таблицы. Если списки значений столбцов имеют разную длину, для преодоления разрыва используются значения null. Обратная функция – Table.ToColumns.
FromRows: использует список списков с однострочными значениями и необязательный параметр columns. Порядок значений в каждом вложенном списке определяет порядок значений по столбцам таблицы. Все списки значений строк должны содержать одинаковое количество элементов, если списки имеют разную длину возникает ошибка. Обратная функция – Table.ToRows.
FromRecords: принимает список записей и может включать необязательные параметры columns и missingField. Порядок записей в списке определяет порядок строк в таблице. Обратная функция – Table.ToRecords.

Информация о таблице

Язык M включает в себя набор функций, специально разработанных для предоставления информации о структуре и содержимом таблицы. К этим функциям относятся Table.ColumnCount, Table.RowCount, Table.IsEmpty, Table.Keys, Table.Profile и Table.Schema. Они полезны для задач по оценке и обработке данных.

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

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

Рис. 8.50. Часть таблицы, возвращаемой функцией Table.Schema

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

Рис. 8.51. Часть значений Table.Profile, которые помогают оценить данные столбцов

Более того, в Table.Profile можно добавить агрегаты с помощью необязательного параметра.

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

Имя нового столбца: это имя дополнительного столбца, который будет добавлен в выходные данные. Рекомендуется указать описательное имя.
Функция проверки типа: простое выражение, такое как each true, гарантирует, что функция агрегирования будет применена ко всем столбцам, независимо от типа. Однако, если вы хотите ограничить функцию агрегирования столбцами определенного типа, необходимо предоставить функцию, которая проверяет каждое значение. Например, Is(_, type number) определит столбцы типа number.
Функция агрегирования: если столбец не проходит проверку типа (2), функция возвращает значение null, и вычисление функции агрегирования пропускается. Для столбцов, прошедших проверку типа, вызывается эта статистическая функция.

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

let
    Source = Table.FromColumns(
        {List.Numbers(1, 200, 1) & {null, "text", null, null}},
        type table [myNumber = number]
    ),
    getProfile = Table.Profile( Source,
        {
            {"count of Elements", each Type.Is(_, type number), each List.Count(_)},
            {"count No Nulls", each Type.Is(_, type number), each List.NonNullCount(_)},
            {"raise Type Error", each Type.Is(_, type number), each List.NonNullCount(List.Transform(_, Number.From))},
            {"count of Numbers", each Type.Is(_, type number), each List.Count( List.Select( _, (x)=> Type.Is(Value.Type(x), type number)))}
        }
    ),
    demoteHeaders = Table.DemoteHeaders(getProfile),
    transposeTable = Table.Transpose(demoteHeaders)
in
    transposeTable

let

Source = Table.FromColumns(

{List.Numbers(1, 200, 1) & {null, "text", null, null}},

type table [myNumber = number]

getProfile = Table.Profile( Source,

{

{"count of Elements", each Type.Is(_, type number), each List.Count(_)},

{"count No Nulls", each Type.Is(_, type number), each List.NonNullCount(_)},

{"raise Type Error", each Type.Is(_, type number), each List.NonNullCount(List.Transform(_, Number.From))},

{"count of Numbers", each Type.Is(_, type number), each List.Count( List.Select( _, (x)=> Type.Is(Value.Type(x), type number)))}

}

demoteHeaders = Table.DemoteHeaders(getProfile),

transposeTable = Table.Transpose(demoteHeaders)

transposeTable

На шаге Source создается таблицу из одного столбца. Далее мы получаем статистику (шаг getProfile), в том числе 4 пользовательских агрегата. Затем понижаем заголовки (demoteHeaders) и транспонируем таблицу (transposeTable). Дополнительные агрегаты содержат условие, которое проверяет, имеет ли столбец тип number. Так как столбцу исходной таблицы myNumber присвоен числовой тип, он проходит проверку типа и выполняется функция агрегирования.

Рис. 8.52. Дополнительные агрегаты для Table.Profile

Понимание значения, возвращаемого статистической обработкой, важно, так как оно влияет на то, как функции обрабатывают данные. Чтобы упростить проверку результатов, мы будем подсчитывать только элементы. Возьмем, к примеру, List.Count. Этой функции не нужно обращаться ни к одному из элементов списка, чтобы определить количество элементов в списке. Функция возвращает то же число, что и количество строк таблицы, 204, как показано в строке 9.

С другой стороны, List.NonNullCount обращается ко всем элементам списка, так как ей необходимо определить, является ли элемент списка чем-то, отличным от null. Возвращает число 201, как показано в строке 10 на рис. 8.52. Но была ли эта цифра ожидаемой?

Это зависит от того, следует или не следует учитывать тип значения в списке. В конце концов, список содержит текстовое значение, даже если столбцу присвоен числовой тип. Зная, что базовый запрос (на шаге Source ) вызовет ошибку преобразования типа во время выполнения запроса, если он загружен отдельно в модель данных, вы можете также вызвать ошибку в профиле, как показано в строке 11, или подсчитать только элементы списка, которые преобразуются в число, как показано в строке 12 на рис. 8.52.

Эти важные соображения и различия нужно учитывать при добавлении агрегатов к Table.Profile.

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

Работа со смешанными структурами

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

Списки таблиц, списков или записей

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

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

Табличные значения
Список списков, где каждый внутренний список представляет значения столбцов
Список списков, где каждый внутренний список представляет значения строк
Список записей, где каждая запись представляет строку таблицы

Рассмотрим запрос, который сгенерирует такую таблицу:

Запрос 8.7
let
  Source = #table(
    {"Tables", "ColumnLists", "RowLists", "Records"},
    {
      {
        #table(2, {{1, 2}, {3, 4}}),
        {{1, 3}, {2, 4}},
        {{1, 2}, {3, 4}},
        {[C1 = 1, C2 = 2], [C1 = 3, C2 = 4]}
      },
      {
        #table(3, {{5, 6, 7}, {8, 9, 0}}),
        {{5, 8}, {6, 9}, {7, 0}},
        {{5, 6, 7}, {8, 9, 0}},
        {[C1 = 5, C2 = 6, C3 = 7], [C1 = 8, C2 = 9, C3 = 0]}
      }
    }
  ),
  listOfTables = Table.Combine(Source[Tables]),
  listOfColumnLists = Table.Combine(
    List.Transform(Source[ColumnLists], each Table.FromColumns(_))),
  listOfRowLists = Table.Combine(
    List.Transform(Source[RowLists], each Table.FromRows(_))),
  listsOfRecords = Table.Combine(
    List.Transform(Source[Records], each Table.FromRecords(_)))
in
  listsOfRecords

Запрос 8.7

let

Source = #table(

{"Tables", "ColumnLists", "RowLists", "Records"},

{

#table(2, {{1, 2}, {3, 4}}),

{{1, 3}, {2, 4}},

{{1, 2}, {3, 4}},

{[C1 = 1, C2 = 2], [C1 = 3, C2 = 4]}

{

#table(3, {{5, 6, 7}, {8, 9, 0}}),

{{5, 8}, {6, 9}, {7, 0}},

{{5, 6, 7}, {8, 9, 0}},

{[C1 = 5, C2 = 6, C3 = 7], [C1 = 8, C2 = 9, C3 = 0]}

}

listOfTables = Table.Combine(Source[Tables]),

listOfColumnLists = Table.Combine(

List.Transform(Source[ColumnLists], each Table.FromColumns(_))),

listOfRowLists = Table.Combine(

List.Transform(Source[RowLists], each Table.FromRows(_))),

listsOfRecords = Table.Combine(

List.Transform(Source[Records], each Table.FromRecords(_)))

listsOfRecords

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

Выполните детализацию (Drill down) столбца, содержащего структурированное значение, чтобы извлечь его в виде списка.
Преобразуйте каждый элемент списка в таблицу с помощью соответствующей функции From.
Используйте Combine, чтобы объединить все таблицы в списке в одну таблицу.

Исключением из этого шаблона является ситуация, когда вы имеете дело со столбцом таблиц. В таком случае достаточно операции объединения, минуя этап преобразования.

Давайте применим теорию на практике для данных из запроса 8.7:

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

= Source[Tables]

1	= Source[Tables]

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

= Table.Combine(Source[Tables])

1	= Table.Combine(Source[Tables])

Это преобразование соответствует шагу listOfTables запроса ListsOf файла Chapter 8 — Working with Structured Values.pbix, который можно скачать с репозитория GitHub.

Чтобы продолжить пример, вы можете либо удалить этот шаг, чтобы вернуться к исходному коду, либо вставить шаг вручную, щелкнув f_Х рядом со строкой формул и заменив автоматически подставленную переменную, на =Source.

При детализации других столбцов будут получены списки списков. Их можно преобразовать в таблицы. На шаге listOfColumnLists это сделано для столбца ColumnLists. Каждый элемент списка преобразован с помощью функции Table.FromColumns, чтобы поместить списки столбцов рядом, образуя таблицу…

= List.Transform(Source[ColumnLists], each Table.FromColumns(_))

1	= List.Transform(Source[ColumnLists], each Table.FromColumns(_))

… а затем объединить таблицы, обернув функцией Table.Combine:

= Table.Combine(List.Transform(Source[ColumnLists], each Table.FromColumns(_)))

1	= Table.Combine(List.Transform(Source[ColumnLists], each Table.FromColumns(_)))

Аналогично переменные listOfRowLists и listsOfRecords преобразуют элементы списка, накладывая их друг на друга для формирования строк в таблице, прежде чем объединить таблицы:

= Table.Combine(
    List.Transform(Source[RowLists], each Table.FromRows(_)))
= Table.Combine(
    List.Transform(Source[Records], each Table.FromRecords(_)))

= Table.Combine(

List.Transform(Source[RowLists], each Table.FromRows(_)))

= Table.Combine(

List.Transform(Source[Records], each Table.FromRecords(_)))

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

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

Выбор метода для преобразования значений в таблице. Мы продемонстрируем использование функции TransformColumns для преобразования столбца ColumnLists.
Передача функции в таблицу Source в качестве первого аргумента и transformOperations в виде списка. {{«ColumnLists», each Table.FromColumns(_) }} указывает имя столбца, а также преобразование, которое нужно применить к каждой строке в таблице.
Использование опции расширения столбца, изображенной боковыми стрелками, в заголовке столбца ColumnLists и выбор всех столбцов; при необходимости отметьте или снимите флажок Использовать исходное имя столбца как префикс.

Следующий запрос (в файле он назван ListsOf_2) отражает шаги, описанные выше:

let
    Source = Table.FromColumns(
        {
            {"A", "B"},
            {"a", "b"},
            {{{1, 3}, {2, 4}}, {{5, 8}, {6, 9}, {7, 0}}}
        }, {"Field1",  "Field2", "ColumnLists"}
    ),
    Transform = Table.TransformColumns( Source,
        {{"ColumnLists", each Table.FromColumns(_) }}
    ),
    ExpandColumnLists = Table.ExpandTableColumn( Transform,
        "ColumnLists",
        {"Column1", "Column2", "Column3"},
        {"Column1", "Column2", "Column3"}
    )
in
    ExpandColumnLists

let

Source = Table.FromColumns(

{

{"A", "B"},

{"a", "b"},

{{{1, 3}, {2, 4}}, {{5, 8}, {6, 9}, {7, 0}}}

}, {"Field1", "Field2", "ColumnLists"}

Transform = Table.TransformColumns( Source,

ExpandColumnLists = Table.ExpandTableColumn( Transform,

"ColumnLists",

{"Column1", "Column2", "Column3"},

{"Column1", "Column2", "Column3"}

)

ExpandColumnLists

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

Таблицы со списками, записями или таблицами

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

Рис. 8.53. Отображение опции Развернуть столбец для столбцов, содержащих списки

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

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

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

let
    allTables = {
        #table({"A", "B"}, {{1, 2}}),
        #table({"a", "b"}, {{3, 4}, {5, 6}}),
        #table({"x", "y"}, {{null, null}, {9, 0}})
    },
    allCols = List.Combine(
        List.Transform( allTables, Table.ToColumns )),
    allNames = List.Combine(
        List.Transform( allTables, Table.ColumnNames )),
    result = Table.FromColumns( allCols, allNames )
in
    result

let

allTables = {

#table({"A", "B"}, {{1, 2}}),

#table({"a", "b"}, {{3, 4}, {5, 6}}),

#table({"x", "y"}, {{null, null}, {9, 0}})

allCols = List.Combine(

List.Transform( allTables, Table.ToColumns )),

allNames = List.Combine(

List.Transform( allTables, Table.ColumnNames )),

result = Table.FromColumns( allCols, allNames )

result

Здесь создается единый список, allCols, содержащий все списки столбцов, и другой список, allNames, со всеми именами столбцов. Эти списки передаются в функцию Table.FromColumns для создания таблицы. Обратите внимание, что имена столбцов должны быть уникальными, иначе возникнет ошибка.

Смешанные структуры

Далее представлены шаблоны, предназначенные для смешанных структурированных типов данных.

Выровнять все

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

Рис. 8.55. Таблица со смешанными типами

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

let
    Source = #table(4, {
        {{"Let's", "go"}, "", {1, 2}, {"3".."9"}},
        {{null}, {true, true, false}, [a=1, b=2], [a=1, b=2]},
        {#date(2024, 1, 21), "", {1, 2}, {"3".."9"}},
        {#date(2024, 3, 7), "", {1, 2}, {"3".."9"}},
        {{null}, {true, true, false}, [a=1, b=2], [a=1, b=2]}
    }),
    getValues = Table.TransformColumns( Source, {}, each
        if Value.Is(_, type list)
        then Text.Combine( List.Transform(_, Text.From), ", " )
        else if Value.Is(_, type record)
        then Text.Combine( List.Transform(
            List.Zip({ Record.FieldNames(_), Record.ToList(_) }),
                (x)=> Text.Combine({ x{0}, " = ", Text.From(x{1}) })
            ), ", " )
        else _ )
in
    getValues

let

Source = #table(4, {

{{"Let's", "go"}, "", {1, 2}, {"3".."9"}},

{{null}, {true, true, false}, [a=1, b=2], [a=1, b=2]},

{#date(2024, 1, 21), "", {1, 2}, {"3".."9"}},

{#date(2024, 3, 7), "", {1, 2}, {"3".."9"}},

{{null}, {true, true, false}, [a=1, b=2], [a=1, b=2]}

}),

getValues = Table.TransformColumns( Source, {}, each

if Value.Is(_, type list)

then Text.Combine( List.Transform(_, Text.From), ", " )

else if Value.Is(_, type record)

then Text.Combine( List.Transform(

List.Zip({ Record.FieldNames(_), Record.ToList(_) }),

(x)=> Text.Combine({ x{0}, " = ", Text.From(x{1}) })

), ", " )

else _ )

getValues

В коде показаны три метода обработки:

Если значение является списком, код преобразует каждый элемент в текст и объединяет элементы в одну текстовую строку, разделенную запятыми.
Если значение является записью, код преобразует каждое поле в текстовую строку в формате «имя поля = значение поля» и объединяет строки, разделяя запятыми.
Если значение не является ни списком, ни записью, оно остается без изменений.

Рис. 8.56. Результат обработки для шага getValues

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

Распаковка всех полей записей из списков

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

let
  Source = Table.FromColumns(
    {
      {1, 2},
      {
        {
          [Tag = 2, Other = "text1"],
          [Tag = 3, Other = "text2"],
          [Tag = 4, Other = "text3"],
          [Tag = 5, Other = "text4"]
        },
        {
          [Tag = 9, Other = "textA"],
          [Tag = 8, Other = "textB"],
          [Tag = 7, Other = "textC"],
          [Tag = 6, Other = "textD"]
        }
      }
    },
    type table [Key = number, Changes = list]
  ),
  PrefixNewColumnNamesWith = "Change ",
  ExtractRecords = List.Accumulate(
    Record.FieldNames(Source[Changes]{0}{0}),
    Source,
    (s, a) =>
      Table.AddColumn(
        s,
        PrefixNewColumnNamesWith & a,
        (x) => Text.Combine(List.Transform(x[Changes],
            (y) => Text.From(Record.Field( y, a))), ", "),
        type text
      )
  )
in
  ExtractRecords

let

Source = Table.FromColumns(

{

{1, 2},

{

[Tag = 2, Other = "text1"],

[Tag = 3, Other = "text2"],

[Tag = 4, Other = "text3"],

[Tag = 5, Other = "text4"]

{

[Tag = 9, Other = "textA"],

[Tag = 8, Other = "textB"],

[Tag = 7, Other = "textC"],

[Tag = 6, Other = "textD"]

}

type table [Key = number, Changes = list]

PrefixNewColumnNamesWith = "Change ",

ExtractRecords = List.Accumulate(

Record.FieldNames(Source[Changes]{0}{0}),

Source,

(s, a) =>

Table.AddColumn(

PrefixNewColumnNamesWith & a,

(x) => Text.Combine(List.Transform(x[Changes],

(y) => Text.From(Record.Field( y, a))), ", "),

type text

)

ExtractRecords

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

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

На шаге ExtractRecords используется функция List.Accumulate, которая обрабатывает каждое имя поля в записях. Вы узнаете больше об этой функции в главе 13 Итерация и рекурсия, но здесь мы дадим краткий обзор.

Функция List.Accumulate выполняет итерацию по именам полей записей, полученных с помощью Record.FieldNames(Source[Changes]{0}{0}). Имена полей относятся к первой строке столбца Changes в таблице Source. Функция получает первый элемент списка, который является записью, чтобы извлечь все имена полей:

Для каждой итерации она применяет функцию для добавления нового столбца в таблицу (s), который ссылается на второй аргумент в Accumulate, таблицу Source.
Новое имя столбца создается путем объединения PrefixFieldNamesWith с текущим обрабатываемым именем поля (a), которое ссылается на первый аргумент в Accumulate.
Затем функция применяется к каждому списку в столбце Changes, чтобы преобразовать каждую запись в списке, извлекая значение текущего поля (a) и преобразовывая его в текст. Наконец, эти текстовые значения объединяются в одну строку, разделенную запятыми.

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

Извлечение данных с помощью поиска

В последних примерах основное внимание уделялось сведению данных, но существует множество сценариев, требующих более целенаправленного подхода к извлечению значения. Поскольку таблицы являются доминирующей структурой, мы продемонстрируем, как искать строки, соответствующие критериям во вложенной таблице, что встречается довольно часто. В примере внешняя таблица содержит одну строку для каждого проекта, а столбец Details содержит запись с двумя полями: Version, список с данными журнала изменений, где окончательный элемент списка представляет активную версию (для Project 98731 это 1,01); и Details, вложенная таблица, содержащая столбец с номером версии и столбец с примечаниями для каждой версии.

Рис. 8.57. Проектные данные и представление содержимого вложенной записи

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

let
  Source = Table.FromRows(
    {
      { 98731, "In development",
        [
          Version = {1.00, 1.01, 1.10, 1.01},
          Details = #table(
            {"Version", "Notes"},
            {
              {1.00, "Draft version"},
              {1.01, "Minor updates"},
              {1.10, "Updated schema"}
            }
          )
        ]
      },
      { 98732, "Internal test",
        [
          Version = {1.00, 2.00, 2.50},
          Details = #table(
            {"Version", "Notes"},
            {
              {1.00, "Beta version"},
              {2.00, "Major overhaul"},
              {2.50, "Performance improvements"}
            }
          )
        ]
      },
      { 98733, "Beta test",
        [
          Version = {2.00, 2.01},
          Details = #table(
            {"Version", "Notes"},
            {
              {2.00, "New release"},
              {2.01, "Minor Bug fixes"}
            }
          )
        ]
      },
      { 98734, "In development",
        [
          Version = {1.00, 1.20},
          Details = #table(
            {"Version", "Notes"},
            {
              {1.00, "Initiated from template"},
              {1.20, "Feature additions"}
            }
          )
        ]
      },
      { 98735, "Rework",
        [
          Version = {1.00, 2.00, 3.00, 3.50, 4.00, 3.50},
          Details = #table(
            {"Version", "Notes"},
            {
              {1.00, "Draft version"},
              {2.00, "New release"},
              {3.00, "Major overhaul"},
              {3.50, "Beta version"},
              {4.00, "To Production environment"}
            }
          )
        ]
      }
    }, {"Project", "Status", "Details"}
  ),
    ReplaceValue = Table.ReplaceValue( Source,
      each [Details],
      each [
        Version=List.Last([Details][Version]),
        Notes=[Details][Details]{[Version=Version]}[Notes] ],
        Replacer.ReplaceValue,
        {"Details"}
    ),
    ExpandDetails = Table.ExpandRecordColumn( ReplaceValue,
      "Details",
      {"Version", "Notes"},
      {"Version", "Notes"}
    )
in
    ExpandDetails

let

Source = Table.FromRows(

{

{ 98731, "In development",

[

Version = {1.00, 1.01, 1.10, 1.01},

Details = #table(

{"Version", "Notes"},

{

{1.00, "Draft version"},

{1.01, "Minor updates"},

{1.10, "Updated schema"}

}

)

]

{ 98732, "Internal test",

[

Version = {1.00, 2.00, 2.50},

Details = #table(

{"Version", "Notes"},

{

{1.00, "Beta version"},

{2.00, "Major overhaul"},

{2.50, "Performance improvements"}

}

)

]

{ 98733, "Beta test",

[

Version = {2.00, 2.01},

Details = #table(

{"Version", "Notes"},

{

{2.00, "New release"},

{2.01, "Minor Bug fixes"}

}

)

]

{ 98734, "In development",

[

Version = {1.00, 1.20},

Details = #table(

{"Version", "Notes"},

{

{1.00, "Initiated from template"},

{1.20, "Feature additions"}

}

)

]

{ 98735, "Rework",

[

Version = {1.00, 2.00, 3.00, 3.50, 4.00, 3.50},

Details = #table(

{"Version", "Notes"},

{

{1.00, "Draft version"},

{2.00, "New release"},

{3.00, "Major overhaul"},

{3.50, "Beta version"},

{4.00, "To Production environment"}

}

)

]

}

}, {"Project", "Status", "Details"}

ReplaceValue = Table.ReplaceValue( Source,

each [Details],

each [

Version=List.Last([Details][Version]),

Notes=[Details][Details]{[Version=Version]}[Notes] ],

Replacer.ReplaceValue,

{"Details"}

ExpandDetails = Table.ExpandRecordColumn( ReplaceValue,

"Details",

{"Version", "Notes"},

{"Version", "Notes"}

)

ExpandDetails

Шаг ReplaceValue выполняет преобразование в столбце Details таблицы Source. Table.ReplaceValue заменяет определенные значения в столбце таблицы. Функция принимает пять аргументов: таблицу для преобразования, старое значение, новое значение, заменитель и столбцы, в которых должны происходить замены. Вот как это работает:

OldValue: each [Details] указывает, что старое значение, которое нужно заменить, является текущим значением в столбце Details.

NewValue: инициализирует запись с двумя полями: Version и Notes. Поля задаются выражениями:

Version = List.Last([Details][Version]): извлекает последний элемент из Version в текущей записи.
Notes=[Details][Details]{[Version=Version]}[Notes]: выполняет поиск во вложенной таблице текущей записи Details и использует {[Version=Version]} для поиска строки, в которой столбец Version совпадает с последним номером версии, извлеченным ранее (i), извлекая поле [Notes] из соответствующей строки.

По сути, для каждой строки в таблице Source запись Details заменяется новой записью, содержащей последний элемент списка из списка Version и примечанием из вложенной таблицы.

Replacer.ReplaceValue является одним из встроенных заменителей, эта функция заменит все содержимое ячейки, если текущее значение точно совпадает с oldValue.

Column to search: {«Details»} задает список столбцов для поиска и замены старого значения; любые замены будут происходить только в этих столбцах.

Как упоминалось ранее, во всех случаях, когда ожидается более одной строки – или, скорее, когда нельзя гарантировать одну строку – вместо поиска следует использовать Table.SelectRows. Это означает, что значение третьего аргумента, указанного в Ta