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

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

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

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

За да изпълните този урок, трябва продукти и поръчки работната книга. В диалоговия прозорец Запиши като Дайте име на файла, продукти и 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

В тази задача импортирате продукти от продукти и 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 щракнете върху иконата за разгъване ( Expand ).

  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.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), въведете число}})

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

Тъй като заявката Total Sales не представлява финалния отчет Total Sales per Product and Year, вие забранявате изтеглянето на заявката в работната книга на Excel. Когато опцията Зареждане в работен лист е Изкл. в екрана Настройки за заявка, резултатът с данни на тази заявка не се изтегля, но заявката все още може да се комбинира с други заявки, за да се състави желаният резултат. Научете как да комбинирате тази заявка със заявката "Products" в следващата задача.

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

  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: Разгъване на колона за обединяване

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

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

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

  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

Table.Sort

(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.

×