Научете как да комбинирате няколко източника на данни (Power Query)

В тази задача импортирате продукти от файла Products и Orders.xlsx (изтеглен и преименуван по-горе) в работна книга на Excel, повишавате редове до заглавки на колони, премахвате някои колони и зареждате заявката в работен лист.

Стъпка 1: Свързване към работна книга на Excel

1. Създайте работна книга на Excel.

2. Изберете > получаване на данни > от > на файлот работна книга.

3. В диалоговия прозорец Импортиране на данни потърсете и намерете Products.xlsx файл, който сте изтеглили, след което изберете Отвори.

4. В екрана Навигатор щракнете двукратно върху таблицата Продукти . Появява се Редактор на Power Query на захранването.

Стъпка 2: Прегледайте стъпките на заявката

По подразбиране Power Query автоматично добавя няколко стъпки, за да ви е удобно. Прегледайте всяка стъпка под Приложени стъпки в екрана Настройки на заявка, за да научите повече.

1. Щракнете с десния бутон върху стъпката Източник и изберете Редактиране на настройките. Тази стъпка е създадена, когато импортирахте работната книга.

2. Щракнете с десния бутон върху стъпката Навигация и изберете Редактиране на настройките. Тази стъпка е създадена, когато сте избрали таблицата от диалоговия прозорец Навигация .

3. Щракнете с десния бутон върху стъпката Променен тип и изберете Редактиране на настройките. Тази стъпка е създадена от Power Query което подразбира типовете данни на всяка колона. Изберете стрелката надолу отдясно на лентата за формули, за да видите пълната формула.

Стъпка 3: Премахване на другите колони, за да се показват само важните колони

В тази стъпка премахвате всички колони без ProductID, ProductName, CategoryID и QuantityPerUnit.

1. Във Визуализация на данни изберете колоните ProductID, ProductName, CategoryID и QuantityPerUnit (използвайте Ctrl+щракване или Shift+щракване).

2. Изберете Премахване на колони > Премахване на други колони.

   

Стъпка 4: Зареждане на заявката за продукти

В тази стъпка зареждате заявката Products в работен лист на 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)
Изберете таблицата "Продукти"	Навигиране	= Източник{[Елемент="Продукти",Вид="Таблица"]}[Данни]
Power Query автоматично открива типове данни за колони	Променен тип	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", въведете text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", въведете text}, {"Единична цена", тип номер}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Прекъснато", въведете logical}})
Премахване на другите колони, за да се показват само важните колони	Премахнати са други колони	= Table.SelectColumns(FirstRowAsHeader,{"ProductID";"ProductName"; "CategoryID"; "QuantityPerUnit"})

В тази задача импортирате данни в работна книга на Excel от примерния канал на OData на Northwind в http://services.odata.org/Northwind/Northwind.svc,разгъвате таблицата Order_Details, премахвате колони, изчислявате сумата на редовете, трансформирате OrderDate, групирате редове по ProductID и Year, преименувате заявката и забранявате изтеглянето на заявка в работната книга на Excel.

Стъпка 1: Свързване към канал на OData

1. Изберете >получаване на данни> от други източници > от канал на OData.

2. В диалоговия прозорец Емисия на OData въведете URL адрес за канала на OData на Northwind.

3. Изберете OK.

4. В екрана Навигатор щракнете двукратно върху таблицата Поръчки .

Стъпка 2: Разгъване на таблица Order_Details

В тази стъпка разширявате таблицата Order_Details, която е свързана с таблицата Orders, за да комбинирате колоните ProductID, UnitPrice и Quantity от Order_Details в таблицата Orders. Операцията Разгъване комбинира колони от свързана таблица в подчинена таблица. Когато заявката се изпълни, редовете от свързаната таблица (Order_Details) се комбинират в редове с основната таблица (Orders).

В Power Query в клетката има стойност Запис или Таблица, съдържаща свързана таблица. Те се наричат структурирани колони. Запис показва един свързан запис и представлява релация "един към един" с текущите данни или първичната таблица. Таблица показва свързана таблица и представлява релация "един към много" с текущата или първичната таблица. Структурирана колона представя релация в източник на данни, който има релационни модел. Например структурирана колона показва обект с асоциация с външен ключ в канал на OData или релация на външен ключ в база данни на SQL Server.

След като разгънете таблицата Order_Details, три нови колони и допълнителни редове се добавят към таблицата Orders – една за всеки ред във вложената или свързана таблица.

1. Във Визуализация на данни превъртете хоризонтално до Order_Details колона.

2. В колоната Order_Details изберете иконата за разгъване ().

3. В падащото меню Разширяване:

   1. Изберете (Избери всички колони), за да изчистите всички колони.

   2. Изберете ProductID, UnitPrice и Quantity.

   3. Изберете OK.

      

      Забележка: В Power Query можете да разгънете таблиците, свързани от колона, и да агрегирате колоните на свързаната таблица, преди да разширите данните в таблицата за тема. За повече информация как да извършвате агрегатни операции, вижте Агрегиране на данни от колона.

Стъпка 3: Премахване на другите колони, за да се показват само важните колони

В тази стъпка ще премахнете всички колони освен колоните OrderDate, ProductID, UnitPrice и Quantity. 

1. Във Визуализацияна данни изберете следните колони: 

   1. Изберете първата колона – OrderID.

   2. Shift+Щракнете върху последната колона, Доставчик.

   3. Използвайте Ctrl+щракване върху колоните OrderDate, Order_Details.ProductID, Order_Details.UnitPrice и Order_Details.Quantity.

2. Щракнете с десния бутон върху заглавката на избраната колона и изберете Премахване на другите колони.

Стъпка 4: Изчисляване на сумата по реда за всеки ред от Order_Details

В тази стъпка създавате Колона по избор, за да изчислите сумата по реда за всеки ред на Order_Details.

1. Във Визуализация на данни изберете иконата на таблица () в горния ляв ъгъл на визуализацията.

2. Щракнете върху Добавяне на колона по избор.

3. В диалоговия прозорец Колона по избор , в полето Формула за колона по избор въведете [Order_Details.Единична цена] * [Order_Details.Количество].

4. В полето Име на нова колона въведете Сума за реда.

5. Изберете OK.

Стъпка 5: Трансформиране на колона за година OrderDate

В тази стъпка можете да трансформирате колоната OrderDate, за да предадете годината на датата на поръчката.

1. Във Визуализация на данни щракнете с десния бутон върху колоната OrderDate и изберете Трансформация > година.

2. Преименувайте колоната OrderDate на Year:

   1. Щракнете двукратно върху колоната OrderDate и въведете Year или

   2. Right-Click в колоната OrderDate изберете Преименуване и въведете Year.

Стъпка 6: Групиране на редове по ProductID и Year

1. Във Визуализация на данни изберете Year и Order_Details.ProductID.

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

3. В диалоговия прозорец Групиране по:

   1. В текстовото поле Име на нова колона въведете Total Sales.

   2. В падащото меню Операция изберете Сума.

   3. В падащото меню Колона изберете Line Total.

4. Изберете 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; [Реализация="2,0"])
Select a table	Навигация	= Източник{[Име="Поръчки"]}[Данни]
Разгъване на таблицата 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(#"Групирани редове",{{"Година", Дата.Година, 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]), въведете number}})

Power Query ви позволява да комбинирате няколко заявки, като ги обединявате или добавяте. Операцията Обединяване се извършва върху всяка заявка на Power Query с таблична форма, независимо от източника на данни, от който идват данните. За повече информация за комбинирането на източници на данни вижте Комбиниране на няколко заявки.

В тази задача комбинирате заявките Products и Total Sales с помощта на заявка за обединяване и операцията Expand , а след това зареждате заявката Total Sales per Product в модела на данни на Excel.

Стъпка 1: Обединяване на ProductID в заявка Total Sales

1. В работната книга на Excel се придвижете до заявката Products в раздела на работния лист Products .

2. Изберете клетка в заявката и след това изберете Заявка > Обединяване.

3. В диалоговия прозорец Обединяване изберете Products като основна таблица и изберете Total Sales като допълнителна или свързана заявка за обединяване. Total Sales ще стане нова структурирана колона с икона за разгъване.

4. За да направите Total Sales така, че да съответства на Products по ProductID, изберете колоната ProductID от таблицата Products и колоната Order_Details.ProductID от таблицата Total Sales.

5. В диалоговия прозорец Нива на поверителност:

   1. Изберете Организационно за нивото на изолиране на поверителността за двата източника на данни.

   2. Изберете Запиши.

6. Изберете OK.

   Забележка относно защитата:  Нива на поверителност пречи на даден потребител неволно да комбинира данни от няколко източника на данни, които може да са с ниво лични или организационни. В зависимост от заявката, потребителят може по невнимание да изпрати данни от източника на лични данни към друг източник на данни, който може да бъде опасен. Power Query анализира всеки източник на данни и го класифицира в определеното ниво на поверителност: публично, организационно и лично. За повече информация относно нивата на поверителност вижте Задаване на нива на поверителност.

   

Result

Операцията Обединяване създава заявка. Резултатът от заявката съдържа всички колони от основната таблица (Products) и една структурирана колона на Таблица към свързаната таблица (Total Sales). Изберете иконата Разгъване , за да добавите нови колони към основната таблица от вторичната или свързаната таблица.

Стъпка 2: Разгъване на обединена колона

В тази стъпка разгъвате обединената колона с името NewColumn , за да създадете две нови колони в заявката Products : Year и Total Sales.

1. Във Визуализация на данни изберете Икона за разгъване () до NewColumn.

2. В падащия списък Разгъване :

   1. Изберете (Избери всички колони), за да изчистите всички колони.

   2. Изберете Година и Общо продажби.

   3. Изберете OK.

3. Преименувайте тези две колони на Year и Total Sales.

4. За да разберете кои продукти и в кои години продуктите са получили най-голям обем продажби, изберете Сортирай низходящо по Общо продажби.

5. Преименувайте заявката на Total Sales per Product.

Result

Стъпка 3: Зареждане на заявка Total Sales per Product в модел на данни на Excel

В тази стъпка зареждате заявка в модел на данни на Excel, за да създадете отчет, свързан с резултата от заявката. След като заредите данни в модела на данни на Excel, можете да използвате Power Pivot за по-нататъшен анализ на данни.

1. Изберете Начало > Затваряне & Зареждане.

2. В диалоговия прозорец Импортиране на данни се уверете, че сте избрали Добавяне на тези данни към модела на данни. За повече информация относно използването на този диалогов прозорец изберете въпросителния знак (?).

Result

Имате заявка 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(Продукти, {"ProductID"}, #"Общо продажби", {"Order_Details.ProductID"}, "Общо продажби", JoinKind.LeftOuter)
Разгъване на колона за обединяване	Разгънати общи продажби	= Table.ExpandTableColumn(източник; "Общо продажби"; {"Година"; "Общо продажби"}, {"Общо продажби.Година", "Общо продажби.Общо продажби"})
Преименуване на две колони	Преименувани колони	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Сортиране на общите продажби във възходящ ред	Сортирани редове	= Table.Sort(#"Преименувани колони",{{"Total Sales", Order.Ascending}})