Об’єднання даних із кількох джерел (Power Query)

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Примітка :  Надбудова Power Query відома як набір функцій Завантажити та перетворити в програмі Excel 2016. Викладена тут інформація стосується обох компонентів. Докладні відомості див. в статті Функція "Завантажити та перетворити" в Excel 2016.

У цьому посібнику ви будете використовувати редактор запитів Power Query для імпорту даних із локального файлу Excel, що містить відомості про продукт і з OData каналу, який містить відомості про продукт порядку. Виконайте кроки перетворення та збирання та об'єднувати дані з обох джерел для створення звіту Загальний обсяг продажів за рік і продуктів .

Щоб виконати завдання цього навчального посібника, створіть книгу Продукти та замовлення. У діалоговому вікні Збереження документа введіть ім’я файлу Продукти та замовлення.xlsx.

Зміст навчальної вправи

Завдання 1. Імпорт даних про продукти до книги Excel

Крок 1. Підключення до книги Excel

Крок 2. Перетворення першого рядка на рядок заголовків стовпців таблиці

Крок 3. Видалення інших стовпців для відображення тільки потрібних

Створені кроки в запиті Power Query

Крок 4. Імпорт запиту даних про продукти

Завдання 2. Імпорт даних про замовлення з каналу OData

Крок 1. Підключення до каналу OData

Крок 2. Розгортання таблиці Order_Details

Розгортання посилання на таблицю Order_Details

Крок 3. Видалення інших стовпців для відображення тільки потрібних

Видалення вибраних стовпців

Крок 4. Обчислення підсумку для кожного рядка таблиці Order_Details

Обчислення підсумку для кожного рядка таблиці Order_Details

Крок 5. Перетворення стовпця "Дата_замовлення" на стовпець року

Крок 6. Групування рядків за стовпцями ProductID та "Рік"

Крок 7. Перейменування запиту

Остаточні результати запиту

Створені кроки в запиті Power Query

Крок 8. Вимкнення завантаження запиту до книги Excel

Вимкнення завантаження запиту

Завдання 3. Об’єднання запитів Products і "Загальний обсяг продажів"

Крок 1. Об’єднання стовпця ProductID із запитом "Загальний обсяг продажів"

Крок 2. Розгортання об’єднаного стовпця

Розгортання посилання на таблицю NewColumn

Створені кроки в запиті Power Query

Крок 3. Завантаження запиту "Загальний обсяг продажів за продуктами" до моделі даних Excel

Завантаження запиту "Загальний обсяг продажів за продуктами" до моделі даних Excel

Остаточний запит "Загальний обсяг продажів за продуктами"

Завдання 1. Імпорт даних про продукти до книги Excel

Щоб виконати це завдання, потрібно імпортувати продукти з файлу Продукти та замовлення.xlsx до книги Excel.

Крок 1. Підключення до книги Excel

  1. Створіть книгу Excel.

  2. На вкладці POWER QUERY стрічки послідовно виберіть елементи З файлу > З Excel.

  3. У діалоговому вікні огляду Excel знайдіть файл "Продукти та замовлення.xlsx" або введіть шлях до нього, щоб імпортувати файл або створити посилання на нього.

  4. В області Навігатор двічі клацніть аркуш Products або послідовно виберіть елементи Products і Редагувати. Якщо вибрати редагування запиту або підключення до нового джерела даних, з’явиться вікно Редактор запитів.

    Примітка : Коротке відео про те, як відобразити редактор запитів, див. в кінці цієї статті.

Крок 2. Перетворення першого рядка на рядок заголовків стовпців таблиці

У сітці Попередній перегляд запиту перший рядок таблиці не містить назв стовпців таблиці. Як зробити перший рядок заголовком стовпців таблиці:

  1. Клацніть піктограму таблиці ( Піктограма таблиці ) у верхньому лівому куті області попереднього перегляду даних.

  2. Виберіть елемент Використати перший рядок як заголовки.

Перетворення першого рядка на рядок заголовків стовпців таблиці

Крок 3. Видалення інших стовпців для відображення тільки потрібних

На цьому кроці ми видаляємо всі стовпці, окрім Ідентифікатор_продукту, Назва_продукту, Ідентифікатор_категорії та Кількість_на_одиницю.

  1. У сітці Попередній перегляд запиту виберіть стовпці Ідентифікатор_продукту, Назва_продукту, Ідентифікатор_категорії та Кількість_на_одиницю (використовуйте Ctrl+клацання або Shift+клацання).

  2. На стрічці Редактор запитів послідовно виберіть елементи Видалити стовпці > Видалити інші стовпці або клацніть правою кнопкою миші заголовок стовпця та виберіть команду Видалити інші стовпці.

    Приховування інших стовпців

Кроки зі створення запиту Power Query

Під час виконання дій із запитами на вкладці Power Query кроки запиту створюються та відображаються у списку ЗАСТОСОВАНІ КРОКИ області Параметри запиту. Кожному кроку відповідає формула Power Query, створена за допомогою мови "M". Докладні відомості про мову формул Power Query див. у статті Відомості про формули Power Query.

Завдання

Крок запиту

Формула

Підключитися до книги Excel

Source

Source{[Name="Продукти"]}[Data]

Перетворення першого рядка на рядок заголовків стовпців таблиці

FirstRowAsHeader

Table.PromoteHeaders

(Продукти)

Видалити інші стовпці, щоб відображалися тільки потрібні

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader;{"Ідентифікатор_продукту"; "Назва_продукту"; "Ідентифікатор_категорії"; "Кількість_на_одиницю"})

Крок 4. Імпорт запиту даних про продукти

На цьому кроці ви імпортуєте запит Продукти у свою книгу Excel.

  1. На стрічці Редактор запитів виберіть елемент Застосувати й закрити. Результати відображатимуться в новому аркуші Excel.

На початок сторінки

Завдання 2. Імпорт даних про замовлення з каналу OData

Щоб виконати це завдання, потрібно імпортувати дані до книги Excel зі зразка каналу OData "Northwind" за адресоюhttp://services.odata.org/Northwind/Northwind.svc.

Крок 1. Підключення до каналу OData

  1. На вкладці POWER QUERY стрічки послідовно виберіть елементи З інших джерел > З каналу OData.

  2. У діалоговому вікні Канал OData введіть URL-адресу для каналу OData компанії Northwind.

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

  4. В області Навігатор двічі клацніть таблицю Замовлення або клацніть елемент Замовлення та виберіть пункт Редагувати.

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

Наведення вказівника миші на джерело даних

Крок 2. Розгортання таблиці Order_Details

На цьому кроці виконується розгортання таблиці Дані_замовлення, зв’язаної з таблицею Замовлення, щоб об’єднати стовпці Ідентифікатор_продукту, Ціна_за_одиницю та Кількість із таблиці Дані_замовлення в таблицю Замовлення. Дія Розгорнути об’єднує стовпці зі зв’язаної таблиці в тематичну таблицю. Під час виконання запиту рядки зі зв’язаної таблиці (Дані_замовлення) об’єднуються в рядки тематичної таблиці (Замовлення).

У надбудові Power Query стовпець із посиланням на зв’язану таблицю містить посилання типу Запис або Таблиця. Посилання типу Запис спрямовує до окремого зв’язаного запису та відтворює зв’язок "один-до-одного" з тематичною таблицею. Посилання типу Таблиця спрямовує до зв’язаної таблиці та відтворює зв’язок "один-до-багатьох" із тематичною таблицею. Посилання відповідає властивостям навігації в джерелі даних у межах реляційної моделі. Для каналу OData властивості навігації відповідають об’єкту з асоціацією зовнішніх ключів. У базі даних, зокрема SQL Server, властивості навігації відтворюють у базі даних зв’язки із зовнішнім ключем.

Розгортання посилання на таблицю "Дані_замовлення"

Після розгортання таблиці Дані_замовлення до таблиці Замовлення додаються три нових стовпці та додаткові рядки – по одному для кожного рядка вкладеної чи зв’язаної таблиці.

  1. В області Попередній перегляд запиту прокрутіть до стовпця Дані_замовлення.

  2. У стовпці Order_Details клацніть піктограму розгортання ( Розгорнути ).

  3. У розкривному списку Розгорнути зробіть ось що.

    1. Виберіть пункт (Виділити всі стовпці), щоб скасувати вибір стовпців.

    2. Клацніть стовпці ProductID, UnitPrice і Quantity.

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

      Розгортання посилання на таблицю "Дані_замовлення"

      Примітка :  У надбудові Power Query можна розгорнути таблиці у стовпці, на які є посилання, а також виконати дії агрегації в стовпцях зв’язаної таблиці перед розгортанням даних до тематичної таблиці. Докладні відомості про виконання дій з агрегації див. у статті Агрегація даних зі стовпця.

Крок 3. Видалення інших стовпців для відображення тільки потрібних

На цьому кроці ми видаляємо всі стовпці, окрім Дата_замовлення, Ідентифікатор_продукту, Ціна_за_одиницю та Кількість. У попередньому завданні використовувалася команда Видалити інші стовпці. У цьому завданні виконуватиметься видалення вибраних стовпців.

Видалення вибраних стовпців

  1. В області Попередній перегляд запиту виділіть усі стовпці.

    1. Клацніть перший стовпець (Ідентифікатор_замовлення).

    2. Утримуючи натиснутою клавішу Shift, клацніть останній стовпець (Служба_доставки).

    3. Утримуючи натиснутою клавішу Ctrl, клацніть стовпці Дата_замовлення, Дані_замовлення.Ідентифікатор_продукту, Дані_замовлення.Ціна_за_одиницю та Дані_замовлення.Кількість.

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

Крок 4. Обчислення підсумку для кожного рядка таблиці Order_Details

На цьому кроці буде створено Настроюваний стовпець для обчислення підсумка для кожного рядка таблиці Дані_замовлення.

Обчислення підсумка для кожного рядка таблиці "Дані_замовлення"

  1. У верхньому лівому куті області Попередній перегляд запиту клацніть піктограму таблиці ( Піктограма таблиці ) попереднього перегляду.

  2. Послідовно виберіть елементи Вставити стовпець > Настроюваний.

  3. У діалоговому вікні Вставити настроюваний стовпець у текстовому полі Формула настроюваного стовпця введіть [Дані_замовлення.Ціна_за_одиницю] * [Дані_замовлення.Кількість].

  4. У текстовому полі Нове ім’я стовпця введіть Підсумок.

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

Обчислення підсумка для кожного рядка таблиці "Дані_замовлення"

Крок 5. Перетворення стовпця "Дата_замовлення" на стовпець року

У цьому кроці відбувається перетворення стовпця Дата_замовлення на стовпець року.

  1. У сітці Попередній перегляд клацніть правою кнопкою миші стовпець Дата_замовлення та послідовно виберіть елементи Перетворити > Рік.

  2. Перейменуйте стовпець Дата_замовлення на Рік.

    1. Для цього двічі клацніть стовпець Дата_замовлення та введіть Рік або

    2. клацніть правою кнопкою миші стовпець Дата_замовлення, виберіть команду Перейменувати та введіть Рік.

Крок 6. Групування рядків за стовпцями ProductID і "Рік"

  1. У сітці Попередній перегляд запиту виділіть стовпці Рік і Дані_замовлення.Ідентифікатор_продукту.

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

  3. У діалоговому вікні Групувати за виконайте наведені нижче дії.

    1. У текстовому полі Нове ім’я стовпця введіть Загальний обсяг продажів.

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

    3. У розкривному списку Стовпець виберіть елемент Разом у рядку.

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

    Діалогове вікно "Групувати за" для дій з агрегації

Крок 7. Перейменування запиту

Перед імпортом даних про продажі до програми Excel назвіть запит Загальний обсяг продажів.

  1. У текстовому полі Ім’я області Параметри запиту введіть Загальний обсяг продажів.

Остаточні результати запиту

Після виконання кожного кроку запит "Загальний обсяг продажів" надходитиме через канал OData компанії Northwind.

Загальний обсяг продажів

Кроки зі створення запиту Power Query

Під час виконання дій із запитами на вкладці Power Query кроки запиту створюються та відображаються у списку ЗАСТОСОВАНІ КРОКИ області Параметри запиту. Кожному кроку відповідає формула Power Query, створена за допомогою мови "M". Докладні відомості про мову формул Power Query див. у статті Відомості про формули Power Query.

Завдання

Крок запиту

Формула

Підключитися до каналу OData

Source

Source{[Name="Замовлення"]}[Data]

Розгорнути таблицю Дані_замовлення

Expand Order_Details

Table.ExpandTableColumn

(Замовлення; "Дані_замовлення"; {"Ідентифікатор_продукту"; "Ціна_за_одиницю"; "Кількість"}; {"Дані_замовлення.Ідентифікатор_продукту"; "Дані_замовлення.Ціна_за_одиницю"; "Дані_замовлення.Кількість"})

Видалити інші стовпці, щоб відображалися тільки потрібні

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details";{"Ідентифікатор_замовлення"; "Ідентифікатор_клієнта"; "Ідентифікатор_працівника"; "Потрібна_дата"; "Дата_перевезення"; "Спосіб_перевезення"; "Вартість_доставки"; "Назва"; "Адреса_перевезення"; "Місто"; "Регіон"; "Поштовий_індекс"; "Країна"; "Клієнт"; "Працівник"; "Транспортна_компанія"})

Обчислення підсумка для кожного рядка таблиці Дані_замовлення

InsertedColumns

Table.AddColumn

(RemovedColumns; "Настроюваний"; each [Дані_замовлення.Ціна_за_одиницю] * [Дані_замовлення.Кількість])

Перетворити стовпець "Дата_замовлення" на стовпець року

RenamedColumns

Table.RenameColumns

(InsertedCustom;{{"Настроюваний", "Підсумок"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns;{{"Дата_замовлення"; Дата.Рік}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn;{{"Дата_замовлення"; "Рік"}})

Згрупувати рядки за стовпцями "Ідентифікатор_продукту" та "Рік"

GroupedRows

Table.Group
(RenamedColumns1, {"Рік", "Order_Details.ProductID"}, {{"Загальний обсяг продажів", each List.Sum([Line Total]), type number}})

Крок 8. Вимкнення завантаження запиту до книги Excel

Оскільки запит Загальний обсяг продажів не відображає остаточний звіт Загальний обсяг продажів за роками та продуктами, завантаження запиту до книги Excel вимикається. Якщо перемикач Завантажити до аркуша перебуває в положенні Вимк. в області Параметри запиту, результат даних цього запиту не завантажується, але запит можна об’єднати з іншими запитами, щоб сформувати потрібний результат. Дізнайтеся про способи об’єднання цього запиту із запитом "Продукти" в наступному завданні.

Вимкнення завантаження запиту

  1. В області Параметри запиту зніміть прапорець Завантажити до аркуша.

  2. На стрічці Редактор запитів виберіть елемент Застосувати й закрити. В області Запити книги запит Загальний обсяг продажів відображає Завантаження вимкнуто.

    Вимкнення завантаження запиту

На початок сторінки

Завдання 3. Об’єднання запитів Products і "Загальний обсяг продажів"

Надбудова Power Query дає можливість групувати кілька запитів шляхом їх об’єднання або додавання. Дія Об’єднати виконується в будь-якому запиті Power Query, що має форму таблиці, незалежно від джерела, з якого надходять дані. Докладні відомості про об’єднання джерел даних див. в статті Об’єднання кількох запитів.

У цьому завданні виконується об’єднання запитів Продукти та Загальний обсяг продажів за допомогою кроків запиту Об’єднати та Розгорнути.

Крок 1. Об’єднання стовпця ProductID із запитом "Загальний обсяг продажів"

  1. У книзі Excel перейдіть до запиту Продукти на аркуші Аркуш2.

  2. На вкладці ЗАПИТ стрічки клацніть елемент Об’єднати.

  3. У діалоговому вікні Об’єднати виберіть елемент Продукти як головну таблицю, а потім – елемент Загальний обсяг продажів як другий або пов’язаний запит для об’єднання. Загальний обсяг продажів стане новим стовпцем із можливістю розгортання.

  4. Щоб зіставити таблицю Загальний обсяг продажів із таблицею Продукти за стовпцем Ідентифікатор_продукту, виділіть стовпець Ідентифікатор_продукту в таблиці Продукти та стовпець Дані_замовлення.Ідентифікатор_продукту в таблиці Загальний обсяг продажів.

  5. У діалоговому вікні Рівні конфіденційності виконайте наведені нижче дії.

    1. Виберіть значення Організаційні для рівня ізоляції конфіденційності для обох джерел даних.

    2. Натисніть кнопку Зберегти.

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

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

    Діалогове вікно "Об’єднати"

Після натискання кнопки OK дія Об’єднати створює запит. Результат запиту містить всі стовпці з головної таблиці (Продукти) і один стовпець, який містить посилання навігації до зв’язаної таблиці (Загальний обсяг продажів). Дія Розгорнути додає до головної чи тематичної таблиці нові стовпці зі зв’язаної таблиці.

Завершення об’єднання

Крок 2. Розгортання об’єднаного стовпця

На цьому кроці виконується розгортання об’єднаного стовпця з назвою Новий_стовпець на два нових стовпці в запиті Продукти. Рік і Загальний обсяг продажів.

Розгортання посилання на таблицю "Новий_стовпець"

  1. У сітці Попередній перегляд запиту клацніть піктограму розгортання NewColumn ( Розгорнути ).

  2. У розкривному списку Розгорнути зробіть ось що.

    1. Виберіть пункт (Виділити всі стовпці), щоб скасувати вибір стовпців.

    2. Клацніть стовпці Рік і Загальний обсяг продажів.

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

  3. Перейменуйте ці два стовпці на Рік і Загальний обсяг продажів.

  4. Виконайте дію Сортувати за спаданням за параметром Загальний обсяг продажів, щоб дізнатися, які продукти та в які роки мали найвищі обсяги продажів.

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

Розгортання посилання на таблицю

Кроки зі створення запиту Power Query

Під час виконання дій із запитом Об’єднати на вкладці Power Query вони відображаються в списку ЗАСТОСОВАНІ КРОКИ області Параметри запиту. Кожному кроку відповідає формула Power Query, створена за допомогою мови "M". Докладні відомості про мову формул Power Query див. у статті Відомості про формули Power Query.

Завдання

Крок запиту

Формула

Об’єднання стовпця "Ідентифікатор_продукту" із запитом "Загальний обсяг продажів"

Source (джерело даних для дії Об’єднати)

Table.NestedJoin

(Продукти;{"Ідентифікатор_продукту"};#"Загальний обсяг продажів";{"Дані_замовлення.Ідентифікатор_продукту"};"Новий_стовпець")

Розгортання об’єднаного стовпця

ExpandNewColumn

Table.ExpandTableColumn

(Джерело; "Новий_стовпець"; {"Рік"; "Загальний обсяг продажів"}; {"Новий_стовпець.Рік"; "Новий_стовпець.Загальний обсяг продажів"})

RenamedColumns

Table.RenameColumns

(#"ExpandNewColumn";{{"Новий_стовпець.Рік"; "Рік"}; {"Новий_стовпець.Загальний_обсяг_продажів"; "Загальний обсяг продажів"}})

SortedRows

Table.Sort

(RenamedColumns;{{"Загальний обсяг продажів"; Order.Descending}})

Крок 3. Завантаження запиту "Загальний обсяг продажів за продуктами" до моделі даних Excel

На цьому кроці вимкніть параметр Завантажити до аркуша та завантажте запит до моделі даних Excel, щоб створити звіт, зв’язаний із результатами пошуку. Окрім завантаження результатів запиту до аркуша Excel надбудова Power Query дає змогу завантажити результати запиту в модель даних Excel. Після завантаження даних в модель даних Excel можна використовувати Power Pivot і Power View для подальшого аналізу даних.

Завантаження запиту "Загальний обсяг продажів за продуктами" в модель даних Excel

  1. В області Параметри запиту зніміть прапорець Завантажити в аркуш і встановіть Завантаження до моделі даних.

  2. Щоб завантажити запит в модель даних Excel, виберіть елемент Застосувати й закрити.

Завантаження до моделі даних Excel

Остаточний запит "Загальний обсяг продажів за продуктами"

Після виконання кожного кроку ви матимете запит Загальний обсяг продажів за продуктами, який об’єднує в собі дані з файлу "Продукти та замовлення.xlsx" і з каналу OData компанії Northwind. Цей запит можна застосовувати до моделі Power Pivot. Окрім того, зміни до запиту в надбудові Power Query призводять до змін у остаточній таблиці та її оновлення в моделі Power Pivot.

На початок сторінки

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

Перегляд редактора запитів у програмі Excel

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×