Работа со структурированным столбцом списка, записи или таблицы (Power Query)

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

• Список    Хранит список значений, например { 1, 2, 5, 10 } или { "hello", "hi", "до свидания" }. Значение List в ячейке — это самый простой структурированный столбец, и связь между таблицами отсутствует.

• Запись    Хранит набор именованных полей, сгруппированных в единицу. Например, [ FirstName = "Joe", LastName = "Smith", Birthdate = #date(2010, 1, 2) ]. Значение Record в ячейке указывает на связь "одна к одной" с текущей или основной таблицей.

• Стол    Хранит таблицу, которая имеет вторичную связь с текущими данными, которая является основной таблицей. Значение Table в ячейке указывает на связь "один ко многим" с текущей или основной таблицей.

Структурированный столбец Record and Table представляет связь в источнике данных, которая имеет реляционную модель, например сущность с ассоциацией внешнего ключа в канале OData или связь внешнего ключа в базе данных SQL Server.

Для просмотра столбцов из связанной таблицы можно использовать значок Развернуть . Например, в таблице Orders операция развертывания объединяет Order_Details записей, связанных с таблицей Order , для объединения элементов строки заказа с каждым заказом. Операция развертывания расширяет первичную таблицу, включив в нее столбцы из дополнительной и связанной таблицы. Чтобы проиллюстрировать, выполните приведенные ниже

Основная таблица содержит столбцы A и B.

Связанная таблица содержит столбец C.

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

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

В этом примере операция развертывания расширяет таблицу Order , включив столбцы Order_Details.ProductID, Order_Details.UnitPrice и Order_Details.Quantity , чтобы объединить строки первичной таблицы Order и связанные таблицы Order_Details строк.

1. Чтобы открыть запрос, найдите ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
   
   Заметка    Запрос должен содержать по крайней мере две таблицы, соединенные в связи. Дополнительные сведения см. в разделе Запросы слияния. 

1. Щелкните значок развертывания () в заголовке столбца структурированного столбца.

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

3. Нажмите кнопку ОК.

Result (Результат)

Таблица теперь содержит новый столбец для каждого столбца, выбранного на шаге 3. 

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

1. Чтобы открыть запрос, найдите ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Чтобы увидеть определенное значение без расширения столбца, выберите значение в структурированном столбце со значением Record . Например,
   
   

3. Щелкните значок развертывания () в заголовке столбца структурированного столбца.

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

5. Нажмите кнопку ОК.

Result (Результат)

Теперь таблица содержит новый столбец для каждого столбца, выбранного на шаге 4. 

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

1. Чтобы открыть запрос, найдите ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.

2. Чтобы увидеть определенное значение без расширения столбца, выберите значение в структурированном столбце со значением List. Например,
   
   

3. Щелкните значок развертывания () в заголовке столбца структурированного столбца.

4. Выполните одно из следующих действий:

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

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

5. Нажмите кнопку ОК.

Результат при развертывании до новых строк

Результат извлечения значений с точкой с запятой (;) в качестве разделителя

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

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

   Расширять    Повышает уровень структурированного столбца (Список, Записи или Таблица), чтобы стать новыми столбцами и строками в текущей таблице запросов. Эта команда аналогична значку Развернуть .
   
   Совокупность    Суммируйте значения в структурированном столбце таблицы с помощью агрегатных функций, включая Sum и Count. Эта команда аналогична выбору значка Развернуть , а затем выбрать Агрегат в раскрывающемся диалоговом окне Имена столбцов . Он доступен только для структурированного столбца Таблицы .
   
   Извлечение значений Извлекает значения структурированного столбца List путем их объединения в одно текстовое значение с помощью указанного разделителя.

Примечание    Вы также можете использовать команду Статистика в группе Числовой столбец (Выберите преобразование > Статистика) со структурированным столбцом таблицы . Он содержит тот же список агрегатных функций, что и команда Статистика в группе Числовый список на вкладке Контекст преобразования в разделе Средства создания списка на ленте редактора Power Query.

Команду Детализация можно использовать для отображения вкладки Контекст преобразования в разделе Средства создания списка на ленте редактора Power Query и отображения дополнительных команд для работы со структурированными столбцами. Не путайте эту вкладку Преобразование с другой вкладкой Преобразование , которая регулярно доступна на ленте редактора Power Query.

1. Щелкните правой кнопкой мыши заголовок столбца и выберите пункт Детализация.

2. Чтобы преобразовать список или запись в таблицу, в разделе Средства создания списка выберите Преобразовать > в таблицу.

3. Чтобы сохранить верхний, нижний или диапазон элементов, в разделе Средства создания списка выберите Преобразовать > Сохранить элементы.

4. Чтобы удалить верхние, нижние или альтернативные элементы, в разделе Средства создания списка выберите Преобразовать > Удалить элементы.

5. Чтобы удалить повторяющиеся значения, в разделе Средства создания списка выберите Преобразовать > Удалить дубликаты. Дополнительные сведения см. в разделе Сохранение или удаление повторяющихся строк.

6. Чтобы изменить элементы, в разделе Средства создания списка выберите Преобразовать > обратные элементы.

7. Чтобы отсортировать данные, в разделе Средства создания списка выберите Сортировка по возрастанию или Сортировка по убыванию . Дополнительные сведения см. в разделе Сортировка данных.

8. Чтобы отобразить различные статистические данные, в разделе Средства создания списка выберите Преобразовать > статистику. Доступны следующие статистические данные: Sum, Minimum, Maximum, Median, Average, Standard, Deviation, Count Values и Count Distinct Values. Дополнительные сведения см. в разделе Статистическая обработка данных из столбца.