Об’єднання даних із кількох джерел (Power Query)
Для виконання в цьому підручнику, вам потрібно книги продукти та замовлення . У діалоговому вікніЗберегти якім'я файлуProducts and Orders.xlsx.
У цьому підручнику
Завдання 1. Імпорт товарів у книгу Excel
Для цього потрібно імпортувати товари з файлу Products and Orders.xlsx до книги Excel.
Крок 1. Підключення до книги Excel
Створіть книгу Excel.
На вкладці стрічкиPOWER QUERYклацнітьЗ файлу>З Excel.
У діалоговому вікніExcelзнайдіть або введіть шлях до файлу Products and Orders.xlsx, щоб імпортувати або створити посилання.
В областіНавігатордвічі клацніть аркушProductsабо клацнітьProductsі виберітьЗмінити запит. Коли ви змінюєте запит або підключаєтеся до нового джерела даних, відкриється вікноРедактор запитів.
Крок 2. Перетворення першого рядка на заголовки стовпців таблиці
НатиснітьВикористовувати перший рядок як заголовки.
Крок 3. Видалення непотрібних стовпців
У цьому кроці ви видаляєте всі стовпці, крімProductID,ProductName,CategoryIDтаQuantityPerUnit.
Створені кроки Power Query
У міру виконання дій із запитом у Power Query створюються кроки запиту, які вказуються на панеліПараметри запитуу спискуЗАСТОСУВАНІ КРОКИ. Кожному кроці запиту відповідає формула Power Query, що називають мовою "M". Щоб отримати додаткові відомості про мову формул Power Query, див. Формули Power Query.
Підключення до книги Excel
Перетворення першого рядка на заголовки стовпців таблиці
Видалення непотрібних стовпців
Крок 4. Імпорт запиту товарів
У цьому кроці виімпортуєте запитProductsдо книги Excel.
Завдання 2. Імпорт даних про замовлення із веб-каналу OData
Крок 1. Підключення до веб-канала OData
На вкладці стрічкиPOWER QUERYклацнітьЗ інших джерел>З каналу OData.
Натисніть кнопкуОК.
В областіНавігатордвічі клацнітьOrdersабо клацнітьOrdersі виберітьЗмінити.
Крок 2. Розгортання таблиці Order_Details
У цьому кроці ви розгортаєте таблицюOrder_Details, яка відноситься до таблиціOrders, щоб об'єднати стовпціProductID,UnitPriceтаQuantityз таблиціOrder_Detailsз таблицеюOrders. ОпераціяРозширитипоєднує стовпці зі зв'язаної таблиці з кінцевою таблицею. При виконанні запиту рядки з пов'язаної таблиці (Order_Details) поєднуються з рядками кінцевої таблиці (Orders).
Power Query стовпець, що містить посилання на пов'язану таблицю, включає посилання назаписаботаблицю. Посилання назаписдозволяє перейти до одного пов'язаного запису і представляє відношення "один до одного" з кінцевою таблицею.Посилання натаблицюдозволяє перейти до пов'язаної таблиці і представляє відношення "один до багатьох" із кінцевою таблицею. Посилання представляє властивості навігації у джерелі даних у рамках реляційної моделі. Для каналу OData властивості навігації є об'єктом з асоціацією на основі зовнішнього ключа. У базі даних, наприклад SQL Server, властивості навігації є зв'язки із зовнішнього ключа в базі даних.
Розгортання таблиці Order_Details
Після розгортання таблиціOrder_Detailsдо таблиціOrdersдодаються три нові стовпці та додаткові рядки (поодному стовпцю для кожного рядка у вкладеній чи зв'язаній таблиці).
У стовпціOrder_Detailsклацніть піктограму розгортання ( ).
У спискуРозширити:
Щоб очистити всі стовпці, натисніть(Виділити всі стовпці).
НатиснітьProductID,UnitPriceтаQuantity.
Натисніть кнопкуОК.
Примітка:У Power Query ви можете розгортати зв'язані таблиці в стовпець, а також виконувати операції агрегування над стовпцями зі зв'язаної таблиці перед розгортанням даних у кінцевій таблиці. Додаткові відомості про те, як виконувати операції агрегування, див. у статті Агрегування даних зі стовпця.
Крок 3. Видалення непотрібних стовпців
У цьому кроці ви видаляєте всі стовпці, крімOrderDate,ProductID,UnitPriceтаQuantity. У попередній задачі ви використовували командуВидалити інші стовпці. Для виконання цього завдання видаліть виділені стовпці.
Видалення виділених стовпців
Клацніть перший стовпець (OrderID).
Клацніть останній стовпець (Shipper), утримуючи клавішу SHIFT.
НатиснітьOrderDate,Order_Details.ProductID,Order_Details.UnitPriceтаOrder_Details.Quantity, утримуючи клавішу CTRL.
Клацніть правою кнопкою миші заголовок виділеного стовпця та виберітьВидалити стовпці.
Крок 4. Обчислення загальної суми для кожного рядка Order_Details
У цьому кроці створюєтьсяСтовпець користувачадля обчислення загальної суми для кожного рядкаOrder_Details.
Обчислення загальної суми для кожного рядка Order_Details
КлацнітьВставити стовпець>Користувачський.
У діалоговому вікніВставити стовпець користувачау полеКористувацька формула стовпцявведіть[Order_Details.UnitPrice] * [Order_Details.Quantity].
У текстовому поліІм'я нового стовпцявведітьLine Total.
Натисніть кнопкуОК.
Крок 5. Перетворення стовпця OrderDate на стовпець року
У цьому кроці ви перетворите стовпецьOrderDateдля відображення року замовлення.
Перейменуйте стовпецьOrderDateнаYear:
Двічі клацнітьOrderDateі введітьYearабо
клацніть правою кнопкою миші стовпецьOrderDate, виберіть командуПерейменуватиі введітьYear.
Крок 6. Угруповання рядків за значеннями ProductID та Year
Клацніть правою кнопкою миші один із заголовків і виберіть командуГрупувати по.
У діалоговому вікніГрупувати по:
У текстовому поліІм'я нового стовпцявведітьTotal Sales.
У розкривному спискуОпераціявиберітьСума.
У спискуСтовпецьвиберітьLine Total.
Натисніть кнопкуОК.
Крок 7. Перейменування запиту
Перед імпортом даних продажів у Excel надайте запиту ім'яTotal Sales:
На панеліУстановки запитуу текстовому поліІм'явведітьTotal Sales.
Підсумковий запит
Після виконання всіх кроків у вас буде запит Total Sales для каналу OData Northwind.
Створені кроки Power Query
У міру виконання дій із запитом у Power Query створюються кроки запиту, які вказуються на панеліПараметри запитуу спискуЗАСТОСУВАНІ КРОКИ. Кожному кроці запиту відповідає формула Power Query, що також називають мовою"M". Щоб отримати додаткові відомості про мову формул Power Query, див. Формули Power Query.