У цій навчальній вправі за допомогою Редактор Power Query Power Query імпортуйте дані з локального файлу Excel, який містить відомості про продукти, і з каналу OData, який містить відомості про замовлення продуктів. Виконайте кроки з перетворення й агрегації та об'єднання даних з обох джерел, щоб створити звіт " Загальний обсяг продажів за продуктами та роками ".
Щоб завершити цей посібник, знадобиться книга "Продукти ". У діалоговому вікні Збереження документа введіть ім’я файлу Продукти та замовлення.xlsx.
Завдання 1. Імпорт даних про продукти до книги Excel
Щоб виконати це завдання, потрібно імпортувати продукти з файлу Products and Orders.xlsx (завантаженого й перейменованого в попередньому розділі) до книги Excel. Потім ви підвищуєте рівень рядків до заголовків стовпців, видаляєте деякі стовпці та завантажуєте запит до аркуша.
Крок 1. Підключення до книги Excel
- Створіть книгу Excel.
- Виберіть пункт "Дані>", "Отримати дані>з файлу>книги".
- У діалоговому вікні " Імпорт даних " знайдіть завантажений файл Products.xlsx , а потім натисніть кнопку "Відкрити".
- В області "Навігатор" двічі клацніть таблицю "Товари ". З'явиться Редактор Power Query.
Крок 2. Перевірка кроків запиту
За замовчуванням Power Query автоматично додає кілька кроків для зручності. Докладніше див. в розділі "Застосовані кроки " області "Параметри запиту ".
- Клацніть правою кнопкою миші крок "Джерело " та виберіть "Змінити параметри". Цей крок створювався під час імпорту книги.
- Клацніть правою кнопкою миші етап навігації та виберіть "Змінити параметри". Цей крок створено, коли ви вибрали таблицю в діалоговому вікні " Навігація ".
- Клацніть правою кнопкою миші крок "Змінений тип " і виберіть "Змінити параметри". Цей крок створив Power Query, який визначив типи даних кожного стовпця. Клацніть стрілку вниз праворуч від рядка формул, щоб переглянути всю формулу.
Крок 3. Видалення інших стовпців для відображення тільки потрібних
На цьому кроці ми видаляємо всі стовпці, окрім "Ідентифікатор_продукту", " Назва_продукту", " Ідентифікатор_категорії" та "Кількість_на_одиницю".
- У режимі попереднього перегляду даних виділіть стовпці "Ідентифікатор_продукту", " Назва_продукту", "Ідентифікатор_ категорії" та " Кількість_на_одиницю " (використовуйте сполучення клавіш Ctrl+Click або Shift+Click).
- Виберіть "Видалити стовпці", ">Видалити інші стовпці".
Крок 4. Завантаження запиту продуктів
На цьому кроці ви завантажуєте запит " Продукти " до аркуша Excel.
- Виберіть Головна>сторінка Закрити & Завантажити. Запит з'явиться на новому аркуші Excel.
Зведення: Power QueryPower Query кроки, створені в Завданні 1
Під час виконання дій із запитами в Power Query створюється й перелічується список етапів запиту в області "Параметри запиту" у списку застосованих кроків. Кожному кроку запиту відповідає Power Query формула, також відома як мова "M". Докладні відомості про формули Power Query див. в документації Power Query.
| Завдання | Крок запиту | Формула |
|---|---|---|
| Імпорт книги Excel | Source (Джерело) | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Виберіть таблицю "Товари" | Перехід. | = Source{[Item="Продукти",Kind="Таблиця"]}[Дані] |
| Power QueryНадбудова Power Query автоматично визначає типи даних стовпця | Змінений тип | = Table.TransformColumnTypes( Products_Table,{{"Ідентифікатор_продукту", Int64.Type}, {"Назва_продукту", type text}, {"Ідентифікатор постачальника", Int64.Type}, {"Ідентифікатор_категорії", Int64.Type}, {"Кількість_на_одиницю", type text}, {"Ціна_за_одиницю", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Скасовано", type logical}}) |
| Видалити інші стовпці, щоб відображалися тільки потрібні | Видалено інші стовпці | = Table.SelectColumns(FirstRowAsHeader;{"Ідентифікатор_продукту"; "Назва_продукту"; "Ідентифікатор_категорії"; "Кількість_на_одиницю"}) |
Завдання 2. Імпорт даних про замовлення з каналу OData
Щоб виконати це завдання, потрібно імпортувати дані до книги Excel зі зразка каналу OData "Northwind за адресою http://services.odata.org/Northwind/Northwind.svc", розгорнути таблицю Order_Details, видалити стовпці, обчислити підсумок, перетворити дату замовлення, згрупувати рядки за стовпцями "Ідентифікатор_продукту" та "Рік", перейменувати запит і вимкнути завантаження запиту до книги Excel.
Крок 1. Підключення до каналу OData
- Виберіть дані>. Отримуйте дані>з інших джерел>із каналу OData.
- У діалоговому вікні Канал OData введіть URL-адресу для каналу OData компанії Northwind.
- Натисніть кнопку OK.
- В області "Навігатор" двічі клацніть таблицю "Замовлення ".
Крок 2. Розгортання таблиці Order_Details
На цьому кроці виконується розгортання таблиці Дані_замовлення, зв’язаної з таблицею Замовлення, щоб об’єднати стовпці Ідентифікатор_продукту, Ціна_за_одиницю та Кількість із таблиці Дані_замовлення в таблицю Замовлення. Дія Розгорнути об’єднує стовпці зі зв’язаної таблиці в тематичну таблицю. Під час виконання запиту рядки зі зв'язаної таблиці (Order_Details) об'єднуються в рядки з головною таблицею (Замовлення).
У Power Query стовпець, який містить зв'язану таблицю, містить у клітинці значення «Запис» або «Таблиця». Вони називаються структурованими стовпцями. Запис указує на один пов'язаний запис і представляє зв'язок "один-до-одного" з поточними даними або головною таблицею. Стовпець «Таблиця» вказує на зв'язану таблицю та представляє зв'язок «один-до-багатьох» із поточною або головною таблицею. Структурований стовпець представляє зв'язок у джерелі даних, який має реляційну модель. Наприклад, структурований стовпець позначає сутність із зв'язком зовнішнього ключа в каналі OData або зв'язку зовнішнього ключа в базі даних SQL Server.
Після розгортання таблиці Order_Details до таблиці "Замовлення " додаються три нових стовпці та додаткові рядки – по одному для кожного рядка вкладеної чи зв'язаної таблиці.
У режимі попереднього перегляду даних прокрутіть його по горизонталі до стовпця Order_Details .
У стовпці Order_Details виберіть піктограму розгортання (
).У розкривному списку Розгорнути зробіть ось що.
Виберіть пункт (Виділити всі стовпці), щоб скасувати вибір усіх стовпців.
Виберіть стовпці "Ідентифікатор_продукту", " Вартість одиниці товару" та " Кількість".
Натисніть кнопку OK.
Примітка.
У Power Query можна розгорнути таблиці, зв'язані зі стовпцем, і агрегувати стовпці зв'язаної таблиці, перш ніж розгортати дані в тематичній таблиці. Докладні відомості про виконання агрегатних операцій див. в статті "Агрегатні дані зі стовпця (Power Query)".
Крок 3. Видалення інших стовпців для відображення тільки потрібних
На цьому кроці ми видаляємо всі стовпці, окрім " Дата_замовлення", " Ідентифікатор_продукту", " Вартість_одиниці_товару" та "Кількість ".
У режимі попереднього перегляду даних виберіть такі стовпці:
- Виділіть перший стовпець (Ідентифікатор замовлення).
- Shift+останній стовпець, "Перевізник".
- Утримуючи натиснутою клавішу Ctrl, клацніть стовпці Дата_замовлення, Дані_замовлення.Ідентифікатор_продукту, Дані_замовлення.Ціна_за_одиницю та Дані_замовлення.Кількість.
Клацніть правою кнопкою миші заголовок вибраного стовпця та виберіть команду "Видалити інші стовпці".
Крок 4. Обчислення підсумку для кожного рядка Order_Details
На цьому кроці буде створено Настроюваний стовпець для обчислення підсумка для кожного рядка таблиці Дані_замовлення.
- У режимі попереднього перегляду даних виберіть піктограму таблиці (
) у верхньому лівому куті вікна попереднього перегляду. - Виберіть "Додати спеціальний стовпець".
- У діалоговому вікні "Настроюваний стовпець" у полі формули настроюваного стовпця введіть [Order_Details.Ціна_за_одиницю] * [Order_Details.Кількість].
- У полі " Новий стовпець " введіть "Підсумок".
- Натисніть кнопку OK.
Крок 5. Перетворення стовпця "Дата_замовлення" на стовпець року
У цьому кроці відбувається перетворення стовпця Дата_замовлення на стовпець року.
У режимі попереднього перегляду даних клацніть правою кнопкою миші стовпець "Дата_замовлення " та виберіть команду "Перетворити>рік".
Перейменуйте стовпець Дата_замовлення на Рік.
- Двічі клацніть стовпець "Дата_замовлення " та введіть "Рік" або
- Клацніть правою кнопкою миші стовпець "Дата_замовлення ", виберіть команду "Перейменувати" та введіть поле "Рік".
Крок 6. Групування рядків за стовпцями ProductID і "Рік"
У попередньому перегляді даних виберіть стовпці "Рік" і Order_Details.ProductID.
Клацніть правою кнопкою миші один із колонтитулів і виберіть команду "Групувати за".
У діалоговому вікні Групувати за виконайте наведені нижче дії.
- У текстовому полі Нове ім’я стовпця введіть Загальний обсяг продажів.
- У розкривному списку Операції виберіть елемент Сума.
- У розкривному списку Стовпець виберіть елемент Разом у рядку.
Натисніть кнопку OK.
Перш ніж імпортувати дані про продажі до програми Excel, перейменуйте запит.
- В області "Параметри запиту " в полі "Ім'я " введіть "Загальний обсяг продажів".
Результати: остаточний запит для Завдання 2
Після виконання кожного кроку надходитиме запит "Загальний обсяг продажів" через канал OData компанії Northwind.
Зведення: Power QueryPower 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"]) |
| Вибір таблиці | Навігація | = source{[name="Orders"]}[Data] |
| Розгорнути таблицю Дані_замовлення | Expand Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"Ідентифікатор_продукту"; "Ціна_за_одиницю"; "Кількість"}; {"Order_Details.Ідентифікатор_продукту"; "Order_Details.Ціна_за_одиницю"; "Order_Details.Кількість"}) |
| Видалити інші стовпці, щоб відображалися тільки потрібні | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details";{"Ідентифікатор_замовлення"; "Ідентифікатор_клієнта"; "Ідентифікатор_працівника"; "Потрібна_дата"; "Дата_перевезення"; "Спосіб_перевезення"; "Вартість_доставки"; "Назва"; "Адреса_перевезення"; "Місто"; "Регіон"; "Поштовий_індекс"; "Країна"; "Клієнт"; "Працівник"; "Транспортна_компанія"}) |
| Обчислення підсумка для кожного рядка таблиці "Дані_замовлення" | Додано настроювані |
= Table.AddColumn(RemovedColumns; "Настроюваний"; each [Order_Details.Ціна_за_одиницю] * [Order_Details.Кількість]) = Table.AddColumn(#"Розгорнутий Order_Details", "Підсумок рядка", each [Order_Details.Ціна_за_одиницю] * [Order_Details.Кількість]) |
| Змініть ім'я на зрозуміліше, Lne Total. | Перейменовані стовпці | = Table.RenameColumns(InsertedCustom;{{"Настроюваний", "Підсумок"}}) |
| Перетворити стовпець "Дата_замовлення" на стовпець року | Видобутий рік | = Table.TransformColumns(#"Згруповані рядки",{{"Рік", Дата.Рік, Int64.Type}}) |
| Змінити на зрозумілі імена, "Дата_замовлення" та "Рік" |
Перейменовані стовпці 1 |
Table.RenameColumns (TransformedColumn;{{"Дата_замовлення"; "Рік"}}) |
| Згрупувати рядки за стовпцями "Ідентифікатор_продукту" та "Рік" | GroupedRows | = Table.Group(RenamedColumns1, {"Рік", "Order_Details.Ідентифікатор_продукту"}, {{"Загальний обсяг продажів", each List.Sum([Line Total]), type number}}) |
Завдання 3. Об’єднання запитів Products і "Загальний обсяг продажів"
Power QueryНадбудова Power Queryдає змогу групувати кілька запитів шляхом їх об'єднання або додавання. Операцію "Об'єднати" можна виконати в будь-якому запиті Power Query з табличною формою, незалежно від джерела даних. Докладні відомості про об'єднання джерел даних див. в статті "Об'єднання кількох запитів (Power QueryPower Query)".
У цьому завданні потрібно поєднати запити "Продукти" та "Загальний обсяг продажів " за допомогою дії " Об'єднати та розгорнути ", а потім завантажити запит "Загальний обсяг продажів за продуктами" в модель даних Excel.
Крок 1. Об'єднання стовпця ProductID із запитом "Загальний обсяг продажів"
У книзі Excel перейдіть до запиту " Продукти " на вкладці аркуша "Продукти ".
Виберіть клітинку в запиті, а потім виберіть "Злиттязапиту>".
У діалоговому вікні " Об'єднання " виберіть елемент "Продукти " як головну таблицю, а потім – "Загальний обсяг продажів " як додатковий або зв'язаний запит для об'єднання. Загальний обсяг продажів перетвориться на новий структурований стовпець із піктограмою розширення.
Щоб зіставити таблицю Загальний обсяг продажів із таблицею Продукти за стовпцем Ідентифікатор_продукту, виділіть стовпець Ідентифікатор_продукту в таблиці Продукти та стовпець Дані_замовлення.Ідентифікатор_продукту в таблиці Загальний обсяг продажів.
У діалоговому вікні Рівні конфіденційності виконайте наведені нижче дії.
- Виберіть значення Організаційні для рівня ізоляції конфіденційності для обох джерел даних.
- Виберіть команду Зберегти.
Натисніть кнопку OK.
Примітка.
Рівні конфіденційності не дають користувачу випадково об'єднати дані з кількох джерел, які можуть бути приватними або організаційними. Залежно від запиту користувач може ненавмисно надіслати дані з приватного джерела даних до іншого джерела даних, яке може бути зловмисним. Power QueryНадбудова Power Query аналізує кожне джерело даних і класифікує його за визначеним рівнем конфіденційності: загальнодоступний, організаційний і приватний. Докладні відомості про рівні конфіденційності див. в статті "Установлення рівнів конфіденційності" (Power QueryPower Query).
Результат
Дія "Об'єднати" створює запит. Результат запиту містить всі стовпці з головної таблиці (Товари) і один структурований стовпець таблиці зі зв'язаної таблиці (Загальний обсяг продажів). Клацніть піктограму "Розгорнути ", щоб додати до головної таблиці нові стовпці з допоміжної або пов'язаної таблиці.
Крок 2. Розгортання об'єднаного стовпця
На цьому кроці розгорніть об'єднаний стовпець з іменем NewColumn , щоб створити два нові стовпці в запиті " Продукти ": " Рік " і " Загальний обсяг продажів".
У режимі попереднього перегляду даних виберіть піктограму " Розгорнути " (
) поруч із пунктом "Новий_стовпець".У розкривному списку "Розгорнути " виконайте такі дії:
- Виберіть пункт (Виділити всі стовпці), щоб скасувати вибір усіх стовпців.
- Виберіть стовпці "Рік" і "Загальний обсяг продажів".
- Натисніть кнопку OK.
Перейменуйте ці два стовпці на Рік і Загальний обсяг продажів.
Щоб дізнатися, які товари та в які роки вони отримали найбільший обсяг продажів, виберіть елемент "Сортувати за спаданням за загальним обсягом продажів".
Перейменуйте запит на Загальний обсяг продажів за продуктами.
Результат
Крок 3. Завантаження запиту "Загальний обсяг продажів за продуктами" до моделі даних Excel
На цьому кроці ви завантажуєте запит у модель даних Excel, щоб можна було створити звіт, зв'язаний із результатами запиту. Після завантаження даних у модель даних Excel можна скористатися надбудовою Power Pivot, щоб продовжити аналіз даних.
- Виберіть Головна>сторінка Закрити & Завантажити.
- У діалоговому вікні "Імпорт даних " переконайтеся, що вибрано параметр "Додати дані до моделі даних". Щоб отримати докладніші відомості про використання цього діалогового вікна, клацніть знак питання (?).
Результат
У вас є запит " Загальний обсяг продажів за продуктами ", який поєднує в собі дані з файлу Products.xlsx і каналу OData компанії Northwind. Цей запит застосовується до моделі Power Pivot. Крім того, зміни до запиту призводять до змін у остаточній таблиці в моделі даних і її оновлення.
Зведення: Power QueryPower Query кроки, створені в Завданні 3
Під час виконання дій із об'єднанням запитів у Power Query кроки запиту створюються та відображаються в області "Параметри запиту" у списку застосованих кроків. Кожному кроку запиту відповідає Power Query формула, також відома як мова "M". Докладні відомості про формули Power Query див. в документації Power Query.
| Завдання | Крок запиту | Формула |
|---|---|---|
| Об’єднання стовпця "Ідентифікатор_продукту" із запитом "Загальний обсяг продажів" | Source (джерело даних для дії Об’єднати) | = Table.NestedJoin(Продукти; {"Ідентифікатор_продукту"}; #"Загальний обсяг продажів"; {"Order_Details.Ідентифікатор_продукту"}; "Загальний обсяг продажів"; JoinKind.LeftOuter) |
| Розгортання об’єднаного стовпця | Збільшений загальний обсяг продажів | = Table.ExpandTableColumn(Джерело; "Загальний обсяг продажів"; {"Рік"; "Загальний обсяг продажів"}; {"Загальний обсяг продажів.Рік"; "Загальний обсяг продажів.Загальний обсяг продажів"}) |
| Перейменування двох стовпців | Перейменовані стовпці | = Table.RenameColumns(#"Розгорнутий загальний обсяг продажів";{{"Загальний обсяг продажів.Рік"; "Рік"}; {"Загальний обсяг продажів.Загальний_обсяг_продажів"; "Загальний обсяг продажів"}}) |
| Сортування загального обсягу збуту за зростанням | Відсортовані рядки | = Table.Sort(#"Перейменовані стовпці";{{"Загальний обсяг продажів"; Order.Ascending}}) |
Додаткові відомості
Power QueryДовідка з надбудови Power Query для програми Excel