Робота зі структурованим стовпцем "Список", "Запис" або "Таблиця" (Power Query)

Є три типи структурованих стовпців:

• Список    Зберігає список значень, наприклад{ 1, 2, 5, 10 } або { "привіт", "привіт", "бувай" }. Значення List ( Список ) у клітинці – це найпростіший структурований стовпець, і зв'язок між таблицями відсутній.

• Запис    Зберігає набір іменованих полів, згрупованих у одиницю. Наприклад, [ Ім'я = "Джо", Прізвище = "Коваль", Birthdate = #date(2010; 1; 2) ]. Значення параметра Record (Запис) у клітинці вказує зв'язок "один-до-одного" з поточною або головною таблицею.

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

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

Щоб переглянути стовпці з пов'язаної таблиці, можна скористатися піктограмою Розгорнути . Наприклад, у таблиці "Замовлення " операція розгортання об'єднує Order_Details записи, пов'язані з таблицею "Замовлення ", щоб поєднати елементи рядка замовлення з кожним замовленням. Операція розгортання розширює основну таблицю, долучити стовпці з додаткової та пов'язаної таблиці. Щоб проілюструвати:

Основна таблиця містить стовпці A та B.

Пов'язана таблиця містить стовпець C.

Операція розгортання розширює головну таблицю на стовпець C і розширює головну таблицю з пов'язаними значеннями з додаткової та пов'язаної таблиці, яка містить стовпець C.

Щоб підсумувати, коли ви розгортаєте структурований стовпець "Таблиця ", значення відображаються поряд з іншими стовпцями в режимі попереднього перегляду даних. Докладні відомості про поєднання основних і пов'язаних таблиць див. в статті Поєднання кількох джерел даних. 

У цьому прикладі операція розгортання розширює таблицю "Замовлення ", яка включає стовпці Order_Details.ProductID, Order_Details.Ціна за одиницю та Order_Details.Кількість , щоб об'єднати рядки основної таблиці "Замовлення" та пов'язану таблицю Order_Details рядків.

1. Щоб відкрити запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних, а потім натисніть кнопку Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.
   
   Примітка    У запиті має бути принаймні дві таблиці, об'єднані у зв'язок. Докладні відомості див. в статті Об'єднання запитів. 

1. Клацніть піктограму розгортання () у заголовку структурованого стовпця.

2. У розкривному списку Імена стовпців виберіть потрібні стовпці та зніміть непотрібні стовпці. 
   
   

3. Натисніть кнопку OK.

Результат

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

Якщо розгорнути структурований стовпець Record (Запис), значення відображатимуться як нові стовпці в поданні попереднього перегляду даних. Якщо не розгорнути його, а потім завантажити запит на аркуш, для кожної клітинки в стовпці відобразиться значення покажчика місця заповнення [Запис].

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

2. Щоб переглянути певне значення, не розгортаючи стовпець, виберіть значення в структурованому стовпці зі значенням Record (Запис ). Наприклад:
   
   

3. Клацніть піктограму розгортання () у заголовку структурованого стовпця.

4. У розкривному списку Імена стовпців виберіть потрібні стовпці та зніміть непотрібні стовпці.
   
   

5. Натисніть кнопку OK.

Результат

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

Якщо розгорнути структурований стовпець списку, значення відображатимуться як нові стовпці в поданні попереднього перегляду даних. Якщо не розгорнути його, а потім завантажити запит на аркуш, для кожної клітинки стовпця відобразиться покажчик місця заповнення [Список].

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

2. Щоб переглянути певне значення, не розгортаючи стовпець, виберіть значення в структурованому стовпці зі значенням "Список". Наприклад:
   
   

3. Клацніть піктограму розгортання () у заголовку структурованого стовпця.

4. Виконайте одну з таких дій:

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

   • Щоб створити список текстових значень, розділених роздільником, натисніть кнопку Видобути значення.

5. Натисніть кнопку OK.

Результат розгортання до нових рядків

Результат видобування значень із крапкою з комою (;) як роздільник

Щоб працювати зі структурованими стовпцями, на стрічці Редактор Power Query можна скористатися такими додатковими командами:

1. Виберіть Основне > Перетворення , а потім виберіть одну з наведених нижче команд у групі Структурований стовпець .

   Розширити    Підвищення рівня структурованого стовпця ("Список", "Записи" або "Таблиця") до нових стовпців і рядків у поточній таблиці запиту. Ця команда збігається з піктограмою "Розгорнути ".
   
   Сукупності    Підсумовуйте значення в структурованому стовпці таблиці за допомогою агрегатних функцій, зокрема Sum і Count. Ця команда збігається з вибором піктограми Розгорнути , а потім у діалоговому вікні Імена стовпців виберіть агрегат. Він доступний лише для структурованого стовпця "Таблиця ".
   
   Видобути значення Видобуває значення стовпця структурованого списку , об'єднуючи їх в одне текстове значення за допомогою вказаного роздільника.

Примітка    Також можна скористатися командою Статистика в групі Числовий стовпець (Вибрати перетворення > статистики) зі структурованим стовпцем Таблиця . Він має такий самий список агрегатних функцій, що й команда Статистика в групі Числовий список контекстної вкладки Перетворення в розділі Знаряддя списку на стрічці Редактор Power Query.

За допомогою команди Деталізувати можна відобразити контекстну вкладку Перетворення в розділі Знаряддя списку на стрічці Редактор Power Query та відобразити додаткові команди для роботи зі структурованими стовпцями. Не плутайте цю вкладку Перетворення з іншою вкладкою Перетворення, яка регулярно доступна на стрічці Редактор Power Query.

1. Клацніть заголовок стовпця правою кнопкою миші та виберіть деталізувати.

2. Щоб перетворити список або запис на таблицю, у розділі Знаряддя списку виберіть пункт Перетворити > на таблицю.

3. Щоб зберегти верхній, нижній або нижній діапазон елементів, у розділі Знаряддя списку виберіть пункт Перетворити > Зберегти елементи.

4. Щоб видалити елементи згори, знизу або альтернативних елементів, у розділі Знаряддя списку виберіть пункт Перетворити > Видалити елементи.

5. Щоб видалити повторювані значення, у розділі Знаряддя списку виберіть пункт Перетворити > Видалити дублікати. Докладні відомості див. в статті Збереження або видалення повторюваних рядків.

6. Щоб скасувати елементи, у розділі Знаряддя для списків виберіть Пункт Перетворити > Зворотні елементи.

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

8. Щоб відобразити різні статистичні дані, у розділі Знаряддя списку виберіть пункт Перетворити > статистику. Доступні такі статистичні дані: Сума, Мінімум, Максимум, Медіана, Середнє, Стандартне, Відхилення, Кількість значень і Кількість унікальних значень. Докладні відомості див. в статті Агрегація даних зі стовпця.