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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Нажмите ОК.

Результат

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

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

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

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

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

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

5. Нажмите ОК.

Результат

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

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

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

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

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

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

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

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

5. Нажмите ОК.

Результат для функции "Развернуть до новых строк"

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

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

1. На домашней > преобразовать, а затем в группе Структурированный столбец выберите одну из следующих команд:

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

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

С помощью команды "Развернуть"можно отобразить контекстную вкладку Преобразование в списке Работа со списком на ленте редактора Power Query и отобразить дополнительные команды для работы со структурированными столбцами. Не путайте эту вкладку Transform (Преобразование) с другой вкладкой Transform (Преобразование), которая регулярно доступна с лентой редактора Power Query.

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

2. Чтобы преобразовать список или запись в таблицу, в области Инструментысо списком выберите преобразовать > в таблицу.

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

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

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

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

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

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