Научете се да комбинирате множество източници на данни (Power Query)

В този урок можете да използвате редактора на заявки на Power Query, за да импортирате данни от локален файл Excel, който съдържа информация за продукта, и от канал на OData, който съдържа информация за поръчка на продукт. Изпълнявате стъпки за трансформация и агрегиране и комбинирате данни от двата източника, за да генерирате отчет "Общи продажби за продукт и година".   

За да изпълните този урок, имате нужда от работната книга "Продукти". В диалоговия прозорец Запиши като дайте име на файла Products and Orders.xlsx.

В тази задача импортирате продукти от файла Продукти и 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: Зареждане на заявката за продукти

В тази стъпка зареждате заявката "Продукти" в Excel работен лист.

  • Изберете Начало >Затвори & Зареждане. Заявката се показва в нов Excel работен лист.

Резюме: Стъпки на Power Query, създадени в задача 1

Докато извършвате дейности по заявки в Power Query, стъпките на заявките се създават и изброяват в екрана заявка Настройки заявка, в списъка Приложени стъпки. Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като езика "M". За повече информация относно формулите на Power Query вижте Създаване на формули на Power Query в Excel.

Задача

Стъпка на заявка

Формула

Импортиране на Excel работна книга

Източник

= Excel. Работна книга(File.Contents("C:\Products and Orders.xlsx"), null, true)

Изберете таблицата "Продукти"

Навигиране

= Източник{[Елемент="Продукти";Kind="Таблица"]}[Данни]

Power Query автоматично открива типовете данни на колоните

Променен тип

= Table.TransformColumnTypes(Products_Table;{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type 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, премахнете колони, изчислите обща сума на линията, трансформирайте 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) се комбинират в редове с основната таблица (Поръчки).

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

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

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

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

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

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

    2. Изберете ProductID,UnitPriceи Количество.

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

      Връзка за разгъване на таблицата Order_Details

      Забележка: В 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.UnitPrice] * [Order_Details.Quantity].

  4. В полето Ново име на колона въведете Обща сума на реда.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Диалогов прозорец "Групиране по" за агрегатни операции

Стъпка 7: Преименуване на заявка

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

  • В екрана Заявка Настройки, в полето Име въведете Общи продажби.

Резултати: Окончателна заявка за задача 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

Навигация

= Източник{[Име="Поръчки"]}[Данни]

Разгъване на таблицата Order_Details

Разгъване на Order_Details

= Table.ExpandTableColumn(Поръчки, "Order_Details", {"ProductID", "Единична цена", "Количество"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Количество"})

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

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", всеки [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Разгънато Order_Details", "Обща сума на реда", всеки [Order_Details.Единичнацена] * [Order_Details.Количество])

Промяна на по-смислено име, Lne Total

Преименувани колони

= Table.RenameColumns(InsertedCustom;{"Custom", "Line Total"}})

Трансформиране на колоната OrderDate да представя годината

Извлечена година

= Table.TransformColumns(#"Групирани редове";{{"Година", Date.Year, Int64.Type}})

Промяна на 

по-смислени имена, OrderDate и Year

Преименувани колони 1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Групиране на редове по ProductID и Year

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", всеки списък.Sum([Обща сума на реда]), въведете число}})

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

В тази задача комбинирате заявките "Продукти" и "Общи продажби", като използвате заявка за обединяване и операция "Разгъване", и след това заредите заявката "Общи продажби на продукт" в Excel на данни.

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

  1. В работната Excel отидете на заявката Продукти в раздела Работен лист "Продукти".

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

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

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

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

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

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

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

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

    Диалогов прозорец "Обединяване"

Резултат

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

Финално обединяване

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

В тази стъпка разширявате обединената колона с името NewColumn, за да създадете две нови колони в заявката "Продукти":"Година" и "Общи продажби".

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

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

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

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

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

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

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

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

Резултат

Връзка за разгъване на таблица

Стъпка 3: Зареждане на заявка Total Sales per Product в модел на данни на 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.

Задача

Стъпка на заявка

Формула

Обединяване на ProductID в заявката Total Sales

Източник (източник на данни за операцията Обединяване)

= Table.NestedJoin(Продукти, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)

Разгъване на колона за обединяване

Разширена обща сума на продажбите

= Table.ExpandTableColumn(Източник, "Общи продажби", {"Година", "Общо продажби"}, {"Общо продажби.Година", "Общо продажби.Общо продажби"})

Преименуване на две колони

Преименувани колони

= Table.RenameColumns(#"Разширена обща сума на продажбите";{"Общо продажби.Година", "Година"}, {"Общо продажби.Общо продажби", "Общо продажби"}})

Сортиране на общата сума на продажбите във възходящ ред

Сортирани редове

= Таблица.Сортиране(#"Преименувани колони";{{"Общи продажби", Order.Ascending}})

Вижте също

Помощ за Power Query Excel Power Query

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×