В този урок можете да използвате Редактор на заявки на Power Query на Power Query, за да импортирате данни от локален файл на Excel, който съдържа информация за продукта, както и от канал на OData, съдържащ информация за поръчки на продукта. Извършвате стъпки на трансформация и агрегиране, а също комбинирате данни от двата източника, за да създадете отчет "Общи продажби по продукт и година".
За да изпълните този урок, ви трябва работната книга "Продукти". В диалоговия прозорец Запиши като дайте име на файла Products and Orders.xlsx.
Задача 1: Импортиране на продукти в работна книга на Excel
В тази задача импортирате продукти от файла "Продукти и Orders.xlsx" (изтеглен и преименуван по-горе) в работна книга на Excel, повишавате редове до заглавки на колони, премахвате някои колони и зареждате заявката в работен лист.
Стъпка 1: Свързване към работна книга на Excel
- Създайте работна книга на Excel.
- Select data>get>data from file>from workbook.
- В диалоговия прозорец за импортиране на данни намерете и намерете файла на Products.xlsx, който изтеглихте, и след това изберете "Отвори".
- В навигационния екран щракнете двукратно върху таблицата "Продукти". Появява се Редактор на Power Query.
Стъпка 2: Разглеждане на стъпките на заявката
По подразбиране Power Query автоматично добавя няколко стъпки за ваше удобство. Разгледайте всяка стъпка под "Приложени стъпки " в екрана " Настройки за заявка ", за да научите повече.
- Щракнете с десния бутон върху стъпката " Източник " и изберете "Редактиране на настройките". Тази стъпка е създадена, когато сте импортирали работната книга.
- Щракнете с десния бутон върху стъпката за навигация и изберете "Редактиране на настройките". Тази стъпка е създадена, когато сте избрали таблицата от диалоговия прозорец за навигация .
- Щракнете с десния бутон върху стъпката "Променен тип " и изберете "Редактиране на настройките". Тази стъпка е създадена от Power Query, който извежда типовете данни на всяка колона. Изберете стрелката надолу вдясно от лентата за формули, за да видите цялата формула.
Стъпка 3: Премахване на другите колони, за да се показват само важните колони
В тази стъпка премахвате всички колони без ProductID, ProductName, CategoryID и QuantityPerUnit.
- Във "Визуализация на данни" изберете колоните "ИД на продукт", "Име на продукт", "ИД на категория" и "Количество" ( използвайте Ctrl+щракване или Shift+щракване).
- Изберете "Премахване на колони" Премахване>на другите колони.
Стъпка 4: Зареждане на заявката за продукти
В тази стъпка зареждате заявката за продукти в работен лист на Excel.
- Изберете "Начало>", "Затвори" & "Зареди". Заявката се появява в нов работен лист на Excel.
Резюме: Стъпки на Power Query, създадени в задача 1
Докато изпълнявате дейности по заявката в Power Query, стъпки на заявката се създават и изреждат в екрана "Настройки за заявка", в списъка "Приложени стъпки". Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като езика "M". За повече информация относно формулите на Power Query вижте "Създаване на формули на Power Query в Excel".
| Задача | Стъпка на заявка | Формула |
|---|---|---|
| Импортиране на работна книга на Excel | Източник | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null; true) |
| Изберете таблицата "Продукти" | Навигиране | = Source{[Item="Продукти",Kind="Таблица"]}[Данни] |
| Power Query автоматично разпознава типовете данни в колоната | Променен тип | = Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", въведете text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", въведете text}, {"UnitPrice", въведете число}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued ", type logical}}) |
| Премахване на другите колони, за да се показват само важните колони | Премахнати са други колони | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Задача 2: Импортиране на данни за поръчки от канал на OData
В тази задача импортирате данни в работна книга на Excel от примерния канал на OData на Northwind на http://services.odata.org/Northwind/Northwind.svc, разширявате таблицата Order_Details, премахвате колони, изчислявате сумарен ред на сума, трансформирате OrderDate, групирате редове по ProductID и Year, преименувате заявката и забранявате изтеглянето на заявки в работната книга на Excel.
Стъпка 1: Свързване към канал на OData
- Избор на данни>Получаванена данни> от други източници >от канал на OData.
- В диалоговия прозорец Емисия на OData въведете URL адрес за канала на OData на Northwind.
- Изберете OK.
- В навигационния екран щракнете двукратно върху таблицата "Поръчки ".
Стъпка 2: Разгъване на таблица на Order_Details
В тази стъпка разширявате таблицата Order_Details, която е свързана с таблицата Orders, за да комбинирате колоните ProductID, UnitPrice и Quantity от Order_Details в таблицата Orders. Операцията Разгъване комбинира колони от свързана таблица в подчинена таблица. Когато заявката се изпълнява, редове от свързаната таблица (Order_Details) се комбинират в редове с основната таблица ("Поръчки").
В Power Query колона, съдържаща свързана таблица, има стойността "Запис" или "Таблица" в клетката. Те се наричат структурирани колони. Record показва единичен свързан запис и представлява релация "един към един" с текущите данни или основната таблица. Таблицата показва свързана таблица и представлява релация "един към много" с текущата или основната таблица. Структурираната колона представя релация в източник на данни, който има релационен модел. Например структурирана колона показва обект с асоциация с външен ключ в канал на OData или релация с външен ключ в база данни на SQL Server.
След като разгънете Order_Details таблица, три нови колони и допълнителни редове се добавят към таблицата "Поръчки " – една за всеки ред във вложената или свързана таблица.
Във "Визуализация на данни" превъртете хоризонтално до Order_Details колона.
В колоната Order_Details изберете иконата за разгъване (
).В падащото меню Разширяване:
Изберете (Избор на всички колони), за да изчистите всички колони.
Изберете "ИД на продукт", "Единична цена" и "Количество".
Изберете OK.
Забележка
В Power Query можете да разгънете таблиците, свързани от колона, и да агрегирате колоните на свързаната таблица, преди да разширите данните в подчинената таблица. За повече информация как да извършвате агрегатни операции, вижте "Агрегиране на данни от колона".
Стъпка 3: Премахване на другите колони, за да се показват само важните колони
В тази стъпка ще премахнете всички колони освен колоните OrderDate, ProductID, UnitPrice и Quantity.
Във "Визуализация на данни" изберете следните колони:
- Изберете първата колона, OrderID.
- Shift+щракване върху последната колона – "Доставчик".
- Използвайте Ctrl+щракване върху колоните OrderDate, Order_Details.ProductID, Order_Details.UnitPrice и Order_Details.Quantity.
Щракнете с десния бутон върху заглавка на избрана колона и изберете "Премахване на другите колони".
Стъпка 4: Изчисляване на сумата по реда за всеки Order_Details ред
В тази стъпка създавате Колона по избор, за да изчислите сумата по реда за всеки ред на Order_Details.
- Във "Визуализация на данни" изберете иконата на таблица (
) в горния ляв ъгъл на визуализацията. - Щракнете върху "Добавяне на колона по избор".
- В диалоговия прозорец " Колона по избор ", в полето за формула за колона по избор въведете [Order_Details.ЕдиничнаЦена] * [Order_Details.Количество].
- В полето Име на нова колона въведете "Обща сума за реда".
- Изберете OK.
Стъпка 5: Трансформиране на колона за година OrderDate
В тази стъпка можете да трансформирате колоната OrderDate, за да предадете годината на датата на поръчката.
Във "Визуализация на данни" щракнете с десния бутон върху колоната "Дата на поръчка " и изберете "Преобразуване>на година".
Преименувайте колоната OrderDate на Year:
- Щракнете двукратно върху колоната OrderDate и въведете Year или
- Right-Click колоната "Дата на поръчка" изберете "Преименуване" и въведете "Година".
Стъпка 6: Групиране на редове по ProductID и Year
Във "Визуализация на данни" изберете Year и Order_Details.ProductID.
Right-Click едно от заглавките и изберете "Групиране по".
В диалоговия прозорец Групиране по:
- В текстовото поле Име на нова колона въведете Total Sales.
- В падащото меню Операция изберете Сума.
- В падащото меню Колона изберете Line Total.
Изберете OK.
Стъпка 7: Преименуване на заявка
Преди да импортирате данни за продажби в Excel, преименувайте заявката:
- В екрана за настройки за заявка , в полето "Име " въведете Total Sales.
Резултати: Заключителна заявка за задача 2
След като изпълните всяка стъпка, ще имате заявка Total Sales над канала на 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"]) |
| Select a table | Навигация | = Source{[Name="Orders"]}[Data] |
| Разгъване на таблицата Order_Details | Разгъване на Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Премахване на другите колони, за да се показват само важните колони | RemovedColumns | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Изчисляване на сумата по реда за всеки ред от 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"}}) |
| Трансформиране на колоната OrderDate да представя годината | Извлечена година | = Table.TransformColumns(#"Групирани редове",{{"Year", Date.Year, Int64.Type}}) |
| Променете на по-смислени имена, OrderDate и Year |
Преименувани колони 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Групиране на редове по ProductID и Year | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), въведете число}}) |
Задача 3: Комбиниране на заявките Products и Total Sales
Power Query ви позволява да комбинирате няколко заявки, като ги обединявате или добавяте. Операцията "Обединяване" се изпълнява върху всяка заявка на Power Query с таблична форма, независимо от източника на данни, откъдето идват данните. За повече информация относно комбинирането на източници на данни вж. "Комбиниране на няколко заявки".
В тази задача комбинирате заявките Products и Total Sales с помощта на заявка за обединяване и операция разгъване , след което зареждате заявката Total Sales per Product в модела на данни на Excel.
Стъпка 1: Обединяване на ProductID в заявка Total Sales
В работната книга на Excel отидете до заявката "Продукти" в раздела на работния лист "Продукти".
Изберете клетка в заявката и след това изберете Обединяване на заявка>.
В диалоговия прозорец " Обединяване " изберете "Продукти " като основна таблица и изберете "Общо продажби " като вторична или свързана заявка за обединяване. "Общи продажби " ще стане нова структурирана колона с икона за разширяване.
За да направите Total Sales така, че да съответства на Products по ProductID, изберете колоната ProductID от таблицата Products и колоната Order_Details.ProductID от таблицата Total Sales.
В диалоговия прозорец Нива на поверителност:
- Изберете Организационно за нивото на изолиране на поверителността за двата източника на данни.
- Изберете Запиши.
Изберете OK.
Забележка
Нива на поверителност пречи на даден потребител неволно да комбинира данни от няколко източника на данни, които може да са с ниво лични или организационни. В зависимост от заявката, потребителят може по невнимание да изпрати данни от източника на лични данни към друг източник на данни, който може да бъде опасен. Power Query анализира всеки източник на данни и го класифицира в определеното ниво на поверителност: публично, организационно и лично. За повече информация относно нивата на поверителност вж. "Задаване на нива на поверителност".
Резултат
Операцията "Обединяване " създава заявка. Резултатът от заявката съдържа всички колони от основната таблица ("Продукти") и една колона със структурирана структура в таблицата към свързаната таблица ("Общ брой продажби"). Изберете иконата за разгъване , за да добавите нови колони към основната таблица от вторичната или свързаната таблица.
Стъпка 2: Разгъване на обединена колона
В тази стъпка разширявате обединената колона с името NewColumn , за да създадете две нови колони в заявката за продукти : Year и Total Sales.
Във "Визуализация на данни" изберете иконата за разгъване (
) до NewColumn.В падащия списък "Разгъване ":
- Изберете (Избор на всички колони), за да изчистите всички колони.
- Изберете година и обща сума на продажбите.
- Изберете OK.
Преименувайте тези две колони на Year и Total Sales.
За да разберете кои продукти и през кои години продуктите са имали най-голям обем на продажби, изберете Низходящо сортиране по общ обем на продажбите.
Преименувайте заявката на Total Sales per Product.
Резултат
Стъпка 3: Зареждане на заявка Total Sales per Product в модел на данни на Excel
В тази стъпка зареждате заявка в модел на данни на Excel, за да съставите отчет, свързан с резултата от заявката. След като заредите данни в модела на данни на Excel, можете да използвате Power Pivot за допълнителни анализи на данните.
- Изберете "Начало>", "Затвори" & "Зареди".
- В диалоговия прозорец за импортиране на данни изберете "Добавяне на тези данни към модела на данни". За повече информация относно използването на този диалогов прозорец изберете въпросителен знак (?).
Резултат
Имате заявка Total Sales per Product , която комбинира данни от файла на Products.xlsx и от канала на OData на Northwind. Тази заявка се прилага към модел на Power Pivot. В допълнение към това промените в заявката променят и обновяват получената таблица в модела на данни.
Резюме: Стъпки на Power Query, създадени в задача 3
Докато изпълнявате дейности по обединяване на заявки в Power Query, стъпки на заявката се създават и изреждат в екрана "Настройки за заявка", в списъка "Приложени стъпки". Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като езика "M". За повече информация за формулите на Power Query вижте "Научете повече за формулите на Power Query".
| Задача | Стъпка на заявка | Формула |
|---|---|---|
| Обединяване на ProductID в заявката Total Sales | Източник (източник на данни за операцията Обединяване) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Разгъване на колона за обединяване | Expanded Total Sales | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Преименуване на две колони | Преименувани колони | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Сортиране на общата сума Продажби във възходящ ред | Сортирани редове | = Table.Sort(#"Renamed columns",{{"Total Sales", Order.Ascending}}) |