Преминаване към основното съдържание
Office

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

Забележка: Power Query е известен като Получаване и трансформиране в Excel 2016. Информацията, предоставена тук, се отнася и за двата варианта. За да научите повече, вижте Получаване и трансформиране в Excel 2016.

В този урок ще използвате редактора на заявки на Power Query, за да импортирате данни от локален файл на Excel, който съдържа информация за продукта, и от канал на OData, който съдържа информация за поръчката за продукти. Извършвате стъпки на трансформация и агрегиране, а също комбинирате данни от двата източника, за да създадете отчет Total Sales per Product and Year.

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

В този урок

Задача 1: Импортиране на продукти в работна книга на Excel

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

Стъпка 2: Повишаване на първия ред до заглавки на колони на таблица

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

Създадени стъпки на Power Query

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

Задача 2: Импортиране на данни за поръчки от канал на OData

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

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

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

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

Премахване на избраните колони

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

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

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

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

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

Резултати от заключителна заявка

Създадени стъпки на Power Query

Стъпка 8: Забраняване на изтегляне на заявка в работна книга на Excel

Забраняване на изтегляне на заявка

Задача 3: Комбиниране на заявките Products и Total Sales

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

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

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

Създадени стъпки на Power Query

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

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

Финална заявка Total Sales per Product

Задача 1: Импортиране на продукти в работна книга на Excel

В тази задача импортирате продукти от файла Products and orders. xlsx в работна книга на Excel.

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

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

  2. В раздела на лентата POWER QUERY щракнете върху От файл > От Excel.

  3. В диалоговия прозорец за преглед на Excel потърсете или въведете пътя до Products and Orders.xlsx, за да импортирате или да се свържете към файл.

  4. В прозореца Навигатор щракнете двукратно върху работния лист Products или щракнете върху Products и върху Редактиране на заявка. Когато редактирате заявка или се свързвате към нов източник на данни, се появява прозорец Редактор на заявки.

    Забележка: За много кратко видео за начина на показване на Редактор на заявки вижте края на тази статия.

Стъпка 2: Повишаване на първия ред до заглавки на колони на таблица

В мрежата Преглед на заявка първият ред на таблицата не съдържа имената на колоните на таблицата. За да повишите първия ред до заглавки на колони на таблица:

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

  2. Щракнете върху Използване на първия ред като заглавки.

Повишаване на първия ред до заглавки на колони на таблица

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

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

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

  2. В лентата Редактор на заявки щракнете върху Премахване на колони > Премахване на другите колони или щракнете с десния бутон върху заглавка на колона, след което щракнете върху Премахване на другите колони.

    Скриване на други колони

Създадени стъпки на Power Query

Докато изпълнявате дейности по заявката в Power Query, стъпки на заявката се създават и изреждат в екрана Настройки за заявка, в списъка ПРИЛОЖЕНИ СТЪПКИ. Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като език "M". За повече информация за езика за формули на Power Query вижте Научете повече за формулите на Power Query.

Задача

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

Формула

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

Source

Source{[Name="Products"]}[Data]

Повишаване на първия ред до заглавки на колони на таблица

FirstRowAsHeader

Table. PromoteHeaders

(Products)

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

RemovedOtherColumns

Table. SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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

В тази стъпка вие импортирате заявката Products във вашата работна книга на Excel.

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

Най-горе на страницата

Задача 2: Импортиране на данни за поръчки от канал на OData

В тази задача импортирате данни в работна книга на Excel от примерния канал на OData на Northwind на адрес http://services.odata.org/Northwind/Northwind.svc.

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

  1. В раздела на лентата POWER QUERY щракнете върху От други източници > От емисия на OData.

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

  3. Щракнете върху OK.

  4. В прозореца Навигатор щракнете двукратно върху таблицата 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 – една за всеки ред във вложената или свързана таблица.

  1. В екрана Преглед на заявка превъртете до колоната Order_Details.

  2. В колоната Order_Details щракнете върху иконата за разгъване ( Разгъване ).

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

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

    2. Щракнете върху ProductID, UnitPrice и Quantity.

    3. Щракнете върху OK.

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

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

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

В тази стъпка ще премахнете всички колони освен колоните OrderDate, ProductID, UnitPrice и Quantity. В предишната задача използвахте Премахване на другите колони. За тази задача ще премахнете избраните колони.

Премахване на избраните колони

  1. В екрана Преглед на заявка изберете всички колони:

    1. Щракнете върху първата колона (OrderID).

    2. Използвайте Shift+щракване върху последната колона (Shipper).

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

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

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

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

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

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

  2. Щракнете върху Вмъкване на колона > По избор.

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

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

  5. Щракнете върху OK.

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

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

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

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

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

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

    2. Щракнете с десния бутон върху колоната OrderDate, щракнете върху Преименуване и въведете Year.

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

  1. В мрежата Преглед на заявка изберете Year и Order_Details.ProductID.

  2. Щракнете с десния бутон върху една от заглавките, след което щракнете върху Групиране по.

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

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

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

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

  4. Щракнете върху OK.

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

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

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

  1. В екрана Настройки за заявка, в текстовото поле Име въведете Total Sales.

Резултати от заключителна заявка

След като изпълните всяка стъпка, ще имате заявка Total Sales над канала на OData на Northwind.

Общо продажби

Създадени стъпки на Power Query

Докато изпълнявате дейности по заявката в Power Query, стъпки на заявката се създават и изреждат в екрана Настройки за заявка, в списъка ПРИЛОЖЕНИ СТЪПКИ. Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като език "M". За повече информация за езика за формули на Power Query вижте Научете повече за формулите на Power Query.

Задача

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

Формула

Свързване към канал на OData

Source

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

InsertedColumns

Table. AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

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

RenamedColumns

Table. RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table. TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table. RenameColumns

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

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

GroupedRows

Table. Group
(RenamedColumns1, {"year"; "Order_Details. за производство"}, {{"Total Sales", всеки списък. SUM ([общо за реда]), въведете число}})

Стъпка 8: Забраняване на изтегляне на заявка в работна книга на Excel

Тъй като заявката Total Sales не представлява окончателната Обща сума на продажбите за продукт и отчет за годината, можете да забраните изтеглянето на заявка в работната книга на Excel. Когато опцията " зареждане в работен лист " е изключена в екрана Настройки на заявки , резултатът от тази заявка не се изтегля, но заявката все още може да се комбинира с други заявки, за да се създаде желаният резултат. Ще научите как да комбинирате тази заявка със заявката за продукти в следващата задача.

Забраняване на изтегляне на заявка

  1. В екрана Настройки за заявка изчистете отметката Зареждане в работен лист.

  2. В лентата на Редактор на заявки щракнете върху Приложи и затвори. В екрана Заявки от работни книги заявката Total Sales показва Зареждането е забранено.

    Забраняване на изтегляне на заявка

Най-горе на страницата

Задача 3: Комбиниране на заявките Products и Total Sales

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

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

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

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

  2. В раздела на лентата QUERY щракнете върху Обединяване.

  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 анализира всеки източник на данни и го класифицира в определеното ниво на поверителност: публично, организационно и лично. За повече информация относно нивата на поверителност вижте Нива на поверителност.

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

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

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

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

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

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

  1. В мрежата за Визуализация на заявката щракнете върху иконата за разгъване на новаколона ( Разгъване ).

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

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

    2. Щракнете върху Year и Total Sales.

    3. Щракнете върху OK.

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

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

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

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

Създадени стъпки на Power Query

Докато изпълнявате дейности по Обединяване на заявката в Power Query, стъпки на заявката се създават и изреждат в екрана Настройки за заявка, в списъка ПРИЛОЖЕНИ СТЪПКИ. Всяка стъпка на заявката има съответстваща формула на Power Query, известна още като езика "M". За повече информация за езика за формули на Power Query вижте Научете повече за формулите на Power Query.

Задача

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

Формула

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

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

Table. NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

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

ExpandNewColumn

Table. ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table. RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Таблица. сортирай

(RenamedColumns,{{"Total Sales", Order.Descending}})

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

В тази стъпка забранявате опцията Зареждане в работен лист и зареждате заявка в Модел на данните на Excel, за да съставите отчет, свързан с резултата от заявката. В допълнение към зареждането на резултати от заявката в работен лист на Excel, Power Query ви позволява да заредите резултата от заявката в Модел на данните на Excel. След като заредите данни в Модел на данните на Excel, можете да използвате Power Pivot и Power View за допълнителни анализи на данните.

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

  1. В екрана Настройки за заявка изчистете отметката от Зареждане в работен лист и отметнете Зареждане в модел данни.

  2. За да заредите заявката в Модел на данните на Excel, щракнете върху Приложи и затвори.

Зареждане на модел на данни на Excel

Финална заявка Total Sales per Product

След като изпълните всяка стъпка, ще имате заявка Total Sales per Product, която комбинира данните от Products и файла Orders.xlsx и от канала на OData на Northwind. Тази заявка може да се приложи към модел на Power Pivot. В допълнение към това промените към заявката в Power Query променят и обновяват получената таблица в модела на Power Pivot.

Забележка: Редактор на заявки се показва само когато зареждате, редактирате или създавате нова заявка чрез Power Query. Следващото видео показва прозореца Редактор на заявки, който се показва след редактиране на заявка от работна книга на Excel. За да видите редактора на заявки , без да зареждате или редактирате съществуваща заявка за работна книга, от секцията получаване на външни данни в раздела на лентата на Power Query изберете от други източници > празна заявка. Следващото видео показва един начин да покажете Редактор на заявки.

Как да видите редактора на заявки в Excel

Най-горе на страницата

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

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

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

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

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

×