Дізнайтеся, як об'єднати кілька джерел даних (Power Query)

У цьому завданні ви імпортуєте продукти з файлу Продукти та Orders.xlsx (завантажені та перейменовані вище) до книги Excel, підвищуєте рівень рядків до заголовків стовпців, видаляєте деякі стовпці та завантажуєте запит на аркуш.

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

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

2. Виберіть елемент Дані > Отримати > данихіз файлу > з книги.

3. У діалоговому вікні Імпорт даних знайдіть завантажений Products.xlsx файл і натисніть кнопку Відкрити.

4. В області Навігатор двічі клацніть таблицю Товари . З'явиться Редактор Power Queryживлення.

Крок 2. Перевірка кроків запиту

За замовчуванням Power Query автоматично додає кілька кроків для зручності. Щоб дізнатися більше, перегляньте кожен крок у розділі Застосовані кроки в області Параметри запиту .

1. Клацніть правою кнопкою миші крок Джерело та виберіть пункт Редагувати настройки. Цей крок створено під час імпортування книги.

2. Клацніть правою кнопкою миші етап переходів і виберіть пункт Редагувати настройки. Цей крок створено, коли ви вибрали таблицю в діалоговому вікні Навігація .

3. Клацніть правою кнопкою миші крок Змінений тип і виберіть пункт Редагувати настройки. Цей крок створено за допомогою Power Query, які висували типи даних кожного стовпця. Клацніть стрілку вниз праворуч від рядка формул, щоб переглянути повну формулу.

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

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

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

2. Виберіть видалити стовпці , > видалити інші стовпці.

   

Крок 4. Завантаження запиту продуктів

На цьому кроці ви завантажуєте запит "Продукти" до аркуша Excel.

• Натисніть кнопку Основне > Закрити & Завантажити. Запит з'явиться на новому аркуші Excel.

Зведення: Power Query кроки, створені в завданні 1

Під час виконання дій із запитами в Power Query кроки запиту створюються та відображаються в області Параметри запиту в списку Застосовані кроки. Кожен крок запиту має відповідну Power Query формулу, також відому як мова "M". Докладні відомості про формули Power Query див. в статті Створення формул Power Query в Excel.

Завдання	Крок запиту	Формула
Імпорт книги Excel	Source (Джерело)	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Виберіть таблицю "Товари"	Перехід.	= Джерело{[Елемент="Продукти",Kind="Таблиця"]}[Дані]
Power Query автоматично виявляє типи даних стовпців	Змінений тип	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", введіть текст}, {"Ідентифікатор постачальника", Int64.Type}, {"Ідентифікатор категорії", Int64.Type}, {"QuantityPerUnit", введіть текст}, {"Вартість одиниці товару", введіть number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Припинення", введіть лог}})
Видалити інші стовпці, щоб відображалися тільки потрібні	Видалено інші стовпці	= Table.SelectColumns(FirstRowAsHeader;{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

У цьому завданні ви імпортуєте дані до книги Excel зі зразка каналу OData компанії Northwind на http://services.odata.org/Northwind/Northwind.svc,розгортаєте Order_Details таблицю, видаляєте стовпці, обчислюєте підсумок рядків, перетворюєте дані "Дата_замовлення", групуєте рядки за ідентифікатором товару та роком, перейменовуєте запит і вимикаєте завантаження запиту до книги Excel.

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

1. Виберіть Дані > Отримати> даних з інших джерел > з каналу OData.

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

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

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

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

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

У Power Query стовпець, який містить пов'язану таблицю, має значення Record (Запис) або Table (Таблиця) у клітинці. Вони називаються структурованими стовпцями. Запис позначає один пов'язаний запис і представляєзв'язок "один-до-одного" з поточними даними або головною таблицею. Таблиця позначає зв'язану таблицю та представляє зв'язок "один-до-багатьох" з поточною або головною таблицею. Структурований стовпець представляє зв'язок у джерелі даних із реляційною моделлю. Наприклад, структурований стовпець позначає сутність зі зв'язком зовнішнього ключа в каналі OData або зв'язку зовнішнього ключа в SQL Server базі даних.

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

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

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

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

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

   2. Виберіть Пункт Ідентифікатор товару, Ціна за одиницю та Кількість.

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

      

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

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

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

1. У поданні попередній переглядданих виберіть такі стовпці: 

   1. Виберіть перший стовпець "Ідентифікатор замовлення".

   2. Shift+ клацніть останній стовпець "Перевізник".

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

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

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

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

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

2. Натисніть кнопку Додати настроюваний стовпець.

3. У діалоговому вікні Настроюваний стовпець у полі Спеціальна формула стовпця введіть [Order_Details.Ціна за одиницю] * [Order_Details.Кількість].

4. У полі Ім'я нового стовпця введіть Підсумок рядка.

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

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

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

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

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

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

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

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

1. У вікні Попередній перегляд даних виберіть Рік і Order_Details.Ідентифікатор товару.

2. Right-Click один із заголовків і натисніть кнопку Групувати за.

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

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

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

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

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

   

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

Перш ніж імпортувати дані про збут до Excel, перейменуйте запит:

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

Результати: остаточний запит для завдання 2

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

Зведення: Power Query кроки, створені в завданні 2 

Під час виконання дій із запитами в Power Query кроки запиту створюються та відображаються в області Параметри запиту в списку Застосовані кроки. Кожен крок запиту має відповідну Power Query формулу, також відому як мова "M". Докладні відомості про формули Power Query див. в статті Докладніше про формули Power Query.

Завдання	Крок запиту	Формула
Підключитися до каналу OData	Source	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null; [Implementation="2.0"])
Вибір таблиці	Навігація	= Джерело{[Ім'я="Замовлення"]}[Дані]
Розгорнути таблицю Дані_замовлення	Expand Order_Details	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "Вартість одиниці товару", "Кількість"}, {"Order_Details.ProductID", "Order_Details.Ціна за одиницю", "Order_Details.Кількість"})
Видалити інші стовпці, щоб відображалися тільки потрібні	RemovedColumns	= Table.RemoveColumns(#"Розгорнути Order_Details",{"Ідентифікатор замовлення", "Ідентифікатор клієнта", "Ідентифікатор працівника", "Потрібна дата", "Дата доставки", "Вартість доставки", "Вартість доставки", "Назва_доставки", "Адреса_доставки", "Дата_доставки", "Регіон доставки", "Поштовий індекс доставки", "Країна_доставки", "Клієнт", "Працівник", "Перевізник"})
Обчислення підсумка для кожного рядка таблиці "Дані_замовлення"	Додано настроюване	= Table.AddColumn(RemovedColumns; "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Змінити на змістовніше ім'я, Lne Total	Перейменовані стовпці	= Table.RenameColumns(InsertedCustom;{{"Custom", "Line Total"}})
Перетворити стовпець "Дата_замовлення" на стовпець року	Видобутий рік	= Table.TransformColumns(#"Згруповані рядки",{{"Рік", Date.Year, Int64.Type}})
Змінити на  змістовніші назви, Дата_замовлення та Рік	Перейменовані стовпці 1	Table.RenameColumns (TransformedColumn;{{"Дата_замовлення"; "Рік"}})
Згрупувати рядки за стовпцями "Ідентифікатор_продукту" та "Рік"	GroupedRows	= Table.Group(RenameedColumns1; {"Year", "Order_Details.ProductID"}; {{"Загальний обсяг продажів", кожен список.Sum([Підсумок рядка]), введіть число}})

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

У цьому завданні ви об'єднуєте запити "Товари" та "Загальний обсяг продажів" за допомогою запиту "Об'єднати" та "Розгорнути", а потім завантажуєте запит "Загальний обсяг продажів за продуктом" у модель даних Excel.

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

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

2. Виберіть клітинку в запиті, а потім натисніть кнопку Запит > Об'єднати.

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

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

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

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

   2. Виберіть команду Зберегти.

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

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

   

Результат

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

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

На цьому кроці потрібно розгорнути об'єднаний стовпець з іменем NewColumn , щоб створити два нові стовпці в запиті "Товари ": Рік і Загальний обсяг продажів.

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

2. У розкривному списку Розгорнути :

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

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

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

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

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

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

Результат

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

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

1. Натисніть кнопку Основне > Закрити & Завантажити.

2. У діалоговому вікні Імпорт даних переконайтеся, що вибрано пункт Додати ці дані до моделі даних. Щоб отримати додаткові відомості про використання цього діалогового вікна, виберіть знак питання (?).

Результат

У вас є запит Загальний обсяг продажів за продуктами , який об'єднує дані з файлу Products.xlsx та каналу OData компанії Northwind. Цей запит застосовується до моделі Power Pivot. Крім того, зміни в запиті змінюються та оновлюються отримані таблиці в моделі даних.

Зведення: Power Query кроки, створені в завданні 3

Коли ви виконуєте дії з об'єднання запитів у Power Query, кроки запиту створюються та відображаються в області Параметри запиту в списку Застосовані кроки. Кожен крок запиту має відповідну Power Query формулу, також відому як мова "M". Докладні відомості про формули Power Query див. в статті Докладніше про формули Power Query.

Завдання	Крок запиту	Формула
Об’єднання стовпця "Ідентифікатор_продукту" із запитом "Загальний обсяг продажів"	Source (джерело даних для дії Об’єднати)	= Table.NestedJoin(Продукти; {"Ідентифікатор товару"}; #"Загальний обсяг продажів", {"Order_Details.ProductID"}; "Загальний обсяг продажів", JoinKind.LeftOuter)
Розгортання об’єднаного стовпця	Розгорнутий загальний обсяг продажів	= Table.ExpandTableColumn(Джерело; "Загальний обсяг продажів", {"Рік", "Загальний обсяг продажів"}; {"Загальний обсяг продажів.Рік","Загальний обсяг продажів.Загальний обсяг продажів"})
Перейменування двох стовпців	Перейменовані стовпці	= Table.RenameColumns(#"Розгорнутий загальний обсяг продажів";{{"Загальний обсяг продажів.Рік","Рік"}; {"Загальний обсяг продажів.Загальний обсяг продажів","Загальний обсяг продажів"}})
Сортування загального обсягу продажів за зростанням	Відсортовані рядки	= Table.Sort(#"Перейменовані стовпці",{{"Загальний обсяг продажів",Order.Ascending}})