Megtudhatja, hogyan kombinálhat több adatforrást (Power Query)

Ebben a feladatban termékeket importál a Products and Orders.xlsx (fent letöltött és átnevezett) fájlból egy Excel-munkafüzetbe, előlépteti a sorokat oszlopfejlécekké, eltávolít néhány oszlopot, és betölti a lekérdezést egy munkalapra.

1. lépés: Csatlakozás egy Excel-munkafüzethez

1. Hozzon létre egy Excel-munkafüzetet.

2. Válassza az Adatok > Adatok lekérése > fájlból > munkafüzetből lehetőséget.

3. Az Adatok importálása párbeszédpanelen keresse meg és keresse meg a letöltött Products.xlsx fájlt, majd válassza a Megnyitás lehetőséget.

4. A Kezelő panelen kattintson duplán a Termékek táblára. Megjelenik a Power Lekérdezésszerkesztő.

2. lépés: A lekérdezési lépések vizsgálata

Alapértelmezés szerint a Power Query automatikusan több lépést is hozzáad az Ön kényelme érdekében. További információért tekintse meg az egyes lépéseket a Lekérdezés beállításai panel Alkalmazott lépések területén.

1. Kattintson a jobb gombbal a Forrás lépésre, és válassza a Beállítások szerkesztése lehetőséget. Ez a lépés a munkafüzet importálásakor jött létre.

2. Kattintson a jobb gombbal a Navigációs lépésre, és válassza a Beállítások szerkesztése lehetőséget. Ez a lépés akkor jött létre, amikor kiválasztotta a táblát a Navigációs párbeszédpanelen.

3. Kattintson a jobb gombbal a Típus módosítása lépésre, és válassza a Beállítások szerkesztése lehetőséget. Ezt a lépést Power Query hozta létre, amely az egyes oszlopok adattípusait következtetett ki. A teljes képlet megtekintéséhez válassza a szerkesztőléc jobb oldalán található lefelé mutató nyilat.

3. lépés: A többi oszlop eltávolítása, hogy csak a kívánt oszlopok maradjanak meg

Ebben a lépésben eltávolítja az összes oszlopot a következők kivételével: ProductID (Termékazonosító), ProductName (Terméknév), CategoryID (Kategóriaazonosító) és QuantityPerUnit (EgységenkéntiMennyiség).

1. Az Adatelőnézet területen válassza ki a ProductID, a ProductName, a CategoryID és a QuantityPerUnit oszlopot (használja a Ctrl+Kattintás vagy a Shift+Kattintás billentyűkombinációt).

2. Válassza az Oszlopok eltávolítása > Az egyéb oszlopok eltávolítása lehetőséget.

   

4. lépés: A termékek lekérdezésének betöltése

Ebben a lépésben betölti a Termékek lekérdezést egy Excel-munkalapra.

• Válassza a Kezdőlap > Bezárás & Betöltés lehetőséget. A lekérdezés egy új Excel-munkalapon jelenik meg.

Összefoglalás: Power Query 1. feladatban létrehozott lépések

Amikor lekérdezési tevékenységeket végez Power Query, a lekérdezési lépések a Lekérdezés beállításai panel Alkalmazott lépések listájában jönnek létre és jelennek meg. Mindegyik lekérdezéslépéshez tartozik egy Power Query-képlet vagy más néven „M” nyelv. A Power Query képletekkel kapcsolatos további információkért lásd: Power Query képletek létrehozása az Excelben.

Művelet	Lekérdezési lépés	Képlet
Excel-munkafüzet importálása	Forrás	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Válassza a Products (Termékek) táblát	Navigálás:	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query automatikusan észleli az oszlop adattípusait	Módosított típus	= 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}, {"Discontinued", type logical}})
A többi oszlop eltávolítása, hogy csak a szükségesek maradjanak meg	Egyéb oszlopok eltávolítva	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Ebben a feladatban adatokat importál az Excel-munkafüzetbe a http://services.odata.org/Northwind/Northwind.svc northwind OData mintaadatcsatornájából ,kibontja a Order_Details táblát, oszlopokat távolít el, kiszámítja a sorösszeget, átalakít egy OrderDate értéket, sorokat csoportosít termékazonosító és év szerint, átnevezi a lekérdezést, és letiltja a lekérdezés letöltését az Excel-munkafüzetbe.

1. lépés: Csatlakozás OData-adatcsatornához

1. Válassza az Adatok > Adatok lekérése > más forrásokból > OData-adatcsatornából lehetőséget.

2. Az OData-adatcsatorna párbeszédpanelen írja be a Northwind OData-adatcsatorna URL-címét.

3. Kattintson az OK gombra.

4. A Kezelő panelen kattintson duplán a Rendelések táblára.

2. lépés: Egy Order_Details (Rendelés_részletei) tábla bővítése

Ebben a lépésben bővíteni fogja az Order_Details (Rendelés_részletei) táblát, amely az Orders (Rendelések) táblához kapcsolódik, hogy kombinálja a ProductID (Termékazonosító), a UnitPrice (Egységár) és a Quantity (Mennyiség) oszlopot az Order_Details táblából az Orders táblába. A Kibontás művelet kombinálja az oszlopokat egy kapcsolódó táblából egy főtáblába. A lekérdezés futtatásakor a kapcsolódó tábla (Order_Details) sorait a rendszer az elsődleges táblával (Orders) kombinálja.

A Power Query egy kapcsolódó táblát tartalmazó oszlopban a Cellában a Record (Rekord) vagy a Table (Tábla) érték szerepel. Ezeket strukturált oszlopoknak nevezzük. A Rekord egyetlen kapcsolódó rekordot jelöl, és egy-az-egyhezkapcsolatot jelöl az aktuális adatokkal vagy az elsődleges táblával. A Tábla egy kapcsolódó táblát jelöl, és egy-a-többhöz kapcsolatot jelöl az aktuális vagy az elsődleges táblával. A strukturált oszlop egy relációs modellel rendelkező adatforrásban lévő kapcsolatot jelöl. A strukturált oszlop például egy olyan entitást jelöl, amely egy OData-adatcsatornában idegenkulcs-társítással vagy egy SQL Server adatbázis idegenkulcs-kapcsolatával rendelkezik.

Miután kibővítette az Order_Details (Rendelés_részletei) táblát, három új oszlop és további sorok kerülnek be az Orders (Rendelések) táblába – a beágyazott vagy kapcsolódó tábla minden sorához egy.

1. Az Adatelőnézetben görgessen vízszintesen a Order_Details oszlophoz.

2. A Order_Details oszlopban válassza a kibontás ikont ().

3. A Kibontás legördülő listában tegye a következőket:

   1. Az összes oszlop törléséhez válassza az (Összes oszlop kijelölése) lehetőséget.

   2. Válassza a Termékazonosító, az Egységár és a Mennyiség lehetőséget.

   3. Kattintson az OK gombra.

      

      Megjegyzés: A Power Query kibonthatja az oszlopból csatolt táblákat, és összesítheti a csatolt tábla oszlopait, mielőtt kibontja a tárgytábla adatait. Az összesítő műveletek elvégzésének módjáról az Egy oszlop adatainak összesítése című témakörből tájékozódhat.

3. lépés: A többi oszlop eltávolítása, hogy csak a kívánt oszlopok maradjanak meg

Ebben a lépésben eltávolítja az összes oszlopot, kivéve a következőket: OrderDate (RendelésDátuma), ProductID (Termékazonosító), UnitPrice (Egységár) és Quantity (Mennyiség). 

1. Az Adatelőnézet területen válassza ki a következő oszlopokat: 

   1. Válassza ki az első orderID oszlopot.

   2. Shift+Kattintson az utolsó, Szállító oszlopra.

   3. A Ctrl billentyűt nyomva tartva kattintson az OrderDate (RendelésDátuma), Order_Details.ProductID (Rendelés_részletei.Termékazonosító), Order_Details.UnitPrice (Rendelés_részletei.Egységár) és Order_Details.Quantity (Rendelés_részletei.Mennyiség) oszlopra.

2. Kattintson a jobb gombbal a kijelölt oszlopfejlécre, és válassza az Egyéb oszlopok eltávolítása parancsot.

4. lépés: Sor végösszegének kiszámítása minden Order_Details (Rendelés_részletei) sorhoz

Ebben a lépésben létrehoz egy egyéni oszlopot, hogy kiszámítsa a sor végösszegét minden Order_Details sorhoz.

1. Az Adatelőnézet területen válassza a tábla ikont () az előnézet bal felső sarkában.

2. Kattintson az Egyéni oszlop hozzáadása elemre.

3. Az Egyéni oszlop párbeszédpanel Egyéni oszlop képletmezőjében írja be a következőt: [Order_Details.UnitPrice] * [Order_Details.Quantity].

4. Az Új oszlop neve mezőbe írja be a Sor összege kifejezést.

5. Kattintson az OK gombra.

5. lépés: Egy OrderDate (RendelésDátuma) évoszlop átalakítása

Ebben a lépésben az OrderDate (RendelésDátuma) oszlopot fogja átalakítani a rendelés évének megjelenítésére.

1. Az Adatelőnézet területen kattintson a jobb gombbal az OrderDate oszlopra , és válassza az Átalakítás > év lehetőséget.

2. Nevezze át az OrderDate oszlopot Year (Év) oszlopra:

   1. Kattintson duplán az OrderDate oszlopra, és írja be a Year nevet, vagy

   2. Right-Click Az OrderDate oszlopban válassza az Átnevezés lehetőséget, és írja be az Év kifejezést.

6. lépés: Sorok csoportosítása ProductID (Termékazonosító) és Year (Év) szerint

1. Az Adatelőnézet területen válassza az Év és Order_Details.ProductID lehetőséget.

2. Right-Click az egyik fejlécet, és válassza a Csoportosítás szempontja lehetőséget.

3. A Csoportosítási szempont párbeszédpanelen tegye a következőket:

   1. Az Új oszlop neve mezőbe írja be a Total Sales (Összes eladás) címet.

   2. A Művelet legördülő listában válassza ki az Összeg elemet.

   3. Az Oszlop legördülő listában válassza a Line Total (Sor végösszege) elemet.

4. Kattintson az OK gombra.

   

7. lépés: Lekérdezés átnevezése

Mielőtt importálja az értékesítési adatokat az Excelbe, nevezze át a lekérdezést:

• A Lekérdezés beállításai panel Név mezőjébe írja be a Total Sales (Összes értékesítés) kifejezést.

Eredmények: A 2. feladat végleges lekérdezése

Minden egyes lépés végrehajtása után lesz egy Total Sales (Összes eladás) lekérdezése a Northwind OData-adatcsatornán.

Összefoglalás: Power Query 2. feladatban létrehozott lépések 

Amikor lekérdezési tevékenységeket végez Power Query, a lekérdezési lépések a Lekérdezés beállításai panel Alkalmazott lépések listájában jönnek létre és jelennek meg. Mindegyik lekérdezéslépéshez tartozik egy Power Query-képlet vagy más néven „M” nyelv. A Power Query képletekről további információt a További tudnivalók Power Query képletekről című témakörben talál.

Művelet	Lekérdezési lépés	Képlet
Csatlakozás OData-adatcsatornához	Forrás	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc"; null; [Implement="2.0"])
Tábla választása	Navigáció	= Source{[Name="Orders"]}[Data]
Az Order_Details (Rendelés_részletei) tábla bővítése	Expand Order_Details (Order_Details bővítése)	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
A többi oszlop eltávolítása, hogy csak a szükségesek maradjanak meg	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Sor végösszegének kiszámítása minden Order_Details (Rendelés_részletei) sorhoz	Egyéni hozzáadva	= 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])
Váltson át egy kifejezőbb névre( Lne Total)	Oszlopok átnevezve	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Az OrderDate (RendelésDátuma) oszlop átalakítása az év megjelenítéséhez	Kinyert év	= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
Váltás erre:  kifejezőbb nevek, OrderDate és Year	Átnevezett oszlopok 1	Table.RenameColumns (TransformedColumn;{{"OrderDate"; "Year"}})
Sorok csoportosítása termékazonosító és év szerint	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

A Power Query lehetővé teszi több lekérdezés kombinálását azok egyesítésével vagy összefűzésével. Az Egyesítés művelet bármely táblázatos formátumú Power Query-lekérdezésen elvégezhető, függetlenül attól, hogy az adatok milyen adatforrásból származnak. Az adatforrások kombinálásáról a Több lekérdezés kombinálása című témakörből tudhat meg többet.

Ebben a feladatban kombinálja a Termékek és az Összes értékesítés lekérdezést egy Egyesítés és a Kibontás művelettel, majd betölti a Total Sales per Product (Összes értékesítés termékenként) lekérdezést az Excel-adatmodellbe.

1. lépés: A ProductID (Termékazonosító) oszlop egyesítése egy Total Sales (Összes eladás) lekérdezésbe

1. Az Excel-munkafüzetben lépjen a Termékek lekérdezésre a Termékek munkalapon.

2. Jelöljön ki egy cellát a lekérdezésben, majd válassza a Lekérdezés > Egyesítés lehetőséget.

3. Az Egyesítés párbeszédpanelen válassza a Termékek elemet elsődleges táblaként, majd válassza a Total Sales (Összes értékesítés ) lehetőséget másodlagos vagy kapcsolódó egyesítési lekérdezésként. A Total Sales egy új strukturált oszlop lesz, kibontás ikonnal.

4. A Total Sales (Összes eladás) és a Products (Termékek) tábla ProductID (Termékazonosító) szerinti összekapcsolásához jelölje ki a ProductID oszlopot a Products táblában, és az Order_Details.ProductID (Rendelés_részletei.Termékazonosító) oszlopot a Total Sales táblában.

5. Az Adatvédelmi szintek párbeszédpanelen tegye a következőket:

   1. Válassza a Szervezeti értéket adatvédelmi szintként mindkét adatforráshoz.

   2. Válassza a Save (Mentés) lehetőséget.

6. Kattintson az OK gombra.

   Biztonsági megjegyzés: Az Adatvédelmi szintek beállítás megakadályozza, hogy a felhasználók véletlenül olyan adatforrásokból kombináljanak adatokat, amelyek személyes vagy szervezeti források lehetnek. A lekérdezéstől függően a felhasználó véletlenül adatokat küldhet a privát adatforrásból egy másik adatforrásba, amely esetleg rossz szándékkal készült. A Power Query elemzi az összes adatforrást, és besorolja azokat a definiált adatvédelmi szintekre: Nyilvános, Szervezeti és Titkos. További információ az adatvédelmi szintekről: Adatvédelmi szintek beállítása.

   

Eredmény

Az Egyesítés művelet létrehoz egy lekérdezést. A lekérdezés eredménye tartalmazza az elsődleges tábla (Products) összes oszlopát, valamint egy tábla strukturált oszlopát a kapcsolódó táblához (Total Sales). A Kibontás ikonra kattintva új oszlopokat adhat hozzá az elsődleges táblához a másodlagos vagy kapcsolódó táblából.

2. lépés: Egyesített oszlop kibontása

Ebben a lépésben kibontja az egyesített oszlopot NewColumn néven, hogy két új oszlopot hozzon létre a Products (Termékek) lekérdezésben: Year (Év ) és Total Sales (Összes értékesítés).

1. Az Adatelőnézet területen válassza a Kibontás ikon () elemet a NewColumn elem mellett.

2. A Kibontás legördülő listában:

   1. Az összes oszlop törléséhez válassza az (Összes oszlop kijelölése) lehetőséget.

   2. Válassza az Év és a Teljes értékesítés lehetőséget.

   3. Kattintson az OK gombra.

3. Nevezze át a két oszlopot, adja nekik a Year és a Total Sales nevet.

4. Ha meg szeretné tudni, hogy mely termékek és mely években jutottak a legtöbb értékesítéshez a termékek, válassza a Rendezés csökkenő sorrendben az összes értékesítés alapján lehetőséget.

5. Az Átnevezés paranccsal nevezze át a lekérdezést Total Sales per Product (Összes eladás termékenként) névre.

Eredmény

3. lépés: Total Sales per Product (Összes eladás termékenként) lekérdezésének betöltése egy Excel-adatmodellbe

Ebben a lépésben egy lekérdezést tölt be egy Excel-adatmodellbe, hogy létrehozhasson egy jelentést, amely a lekérdezés eredményéhez kapcsolódik. Miután betöltötte az adatokat az Excel-adatmodellbe, a Power Pivot használatával tovább elemezheti az adatokat.

1. Válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.

2. Az Adatok importálása párbeszédpanelen válassza az Adatok hozzáadása az adatmodellhez lehetőséget. A párbeszédpanel használatával kapcsolatos további információkért válassza a kérdőjelet (?).

Eredmény

Rendelkezik egy Total Sales per Product (Összes értékesítés termékenként ) lekérdezéssel, amely a Products.xlsx fájlból és a Northwind OData-adatcsatornából származó adatokat egyesíti. Ez a lekérdezés egy Power Pivot-modellre lesz alkalmazva. Emellett a lekérdezés módosításai módosítják és frissítik az eredményül kapott táblát az adatmodellben.

Összefoglalás: Power Query 3. feladatban létrehozott lépések

Amikor egyesítési lekérdezési tevékenységeket végez Power Query, a lekérdezési lépések a Lekérdezés beállításai panel Alkalmazott lépések listájában jönnek létre és jelennek meg. Mindegyik lekérdezéslépéshez tartozik egy Power Query-képlet vagy más néven „M” nyelv. A Power Query képletekről további információt a További tudnivalók Power Query képletekről című témakörben talál.

Művelet	Lekérdezési lépés	Képlet
A ProductID (Termékazonosító) oszlop egyesítése egy Total Sales (Összes eladás) lekérdezésbe	Forrás (az Egyesítés művelet adatforrása)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Egyesített oszlop bővítése	Kibontott összes értékesítés	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Két oszlop átnevezése	Oszlopok átnevezve	= Table.RenameColumns(#"Expand Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Az összes értékesítés rendezése növekvő sorrendben	Rendezett sorok	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})