Ebben az oktatóanyagban Power Query Lekérdezésszerkesztő segítségével importálhat adatokat egy termékinformációkat tartalmazó helyi Excel-fájlból és egy termékrendelési adatokat tartalmazó OData-csatornából. Átalakítási és összesítési lépéseket hajt végre, és mindkét forrásból származó adatokat kombinálva létrehoz egy "Total Sales per Product and Year" (Összes értékesítés termékenként és évente) jelentést.
Az oktatóanyag elvégzéséhez szüksége lesz a Termékek munkafüzetre. A Mentés másként párbeszédpanelen adja a fájlnak a Products and Orders.xlsx nevet.
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
-
Hozzon létre egy Excel-munkafüzetet.
-
Válassza az Adatok > Adatok lekérése > fájlból > munkafüzetből lehetőséget.
-
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.
-
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.
-
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.
-
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.
-
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).
-
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).
-
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
-
Válassza az Adatok > Adatok lekérése > más forrásokból > OData-adatcsatornából lehetőséget.
-
Az OData-adatcsatorna párbeszédpanelen írja be a Northwind OData-adatcsatorna URL-címét.
-
Kattintson az OK gombra.
-
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.
-
Az Adatelőnézetben görgessen vízszintesen a Order_Details oszlophoz.
-
A Order_Details oszlopban válassza a kibontás ikont ().
-
A Kibontás legördülő listában tegye a következőket:
-
Az összes oszlop törléséhez válassza az (Összes oszlop kijelölése) lehetőséget.
-
Válassza a Termékazonosító, az Egységár és a Mennyiség lehetőséget.
-
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).
-
Az Adatelőnézet területen válassza ki a következő oszlopokat:
-
Válassza ki az első orderID oszlopot.
-
Shift+Kattintson az utolsó, Szállító oszlopra.
-
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.
-
-
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.
-
Az Adatelőnézet területen válassza a tábla ikont () az előnézet bal felső sarkában.
-
Kattintson az Egyéni oszlop hozzáadása elemre.
-
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].
-
Az Új oszlop neve mezőbe írja be a Sor összege kifejezést.
-
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.
-
Az Adatelőnézet területen kattintson a jobb gombbal az OrderDate oszlopra , és válassza az Átalakítás > év lehetőséget.
-
Nevezze át az OrderDate oszlopot Year (Év) oszlopra:
-
Kattintson duplán az OrderDate oszlopra, és írja be a Year nevet, vagy
-
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
-
Az Adatelőnézet területen válassza az Év és Order_Details.ProductID lehetőséget.
-
Right-Click az egyik fejlécet, és válassza a Csoportosítás szempontja lehetőséget.
-
A Csoportosítási szempont párbeszédpanelen tegye a következőket:
-
Az Új oszlop neve mezőbe írja be a Total Sales (Összes eladás) címet.
-
A Művelet legördülő listában válassza ki az Összeg elemet.
-
Az Oszlop legördülő listában válassza a Line Total (Sor végösszege) elemet.
-
-
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 |
(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
-
Az Excel-munkafüzetben lépjen a Termékek lekérdezésre a Termékek munkalapon.
-
Jelöljön ki egy cellát a lekérdezésben, majd válassza a Lekérdezés > Egyesítés lehetőséget.
-
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.
-
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.
-
Az Adatvédelmi szintek párbeszédpanelen tegye a következőket:
-
Válassza a Szervezeti értéket adatvédelmi szintként mindkét adatforráshoz.
-
Válassza a Save (Mentés) lehetőséget.
-
-
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).
-
Az Adatelőnézet területen válassza a Kibontás ikon () elemet a NewColumn elem mellett.
-
A Kibontás legördülő listában:
-
Az összes oszlop törléséhez válassza az (Összes oszlop kijelölése) lehetőséget.
-
Válassza az Év és a Teljes értékesítés lehetőséget.
-
Kattintson az OK gombra.
-
-
Nevezze át a két oszlopot, adja nekik a Year és a Total Sales nevet.
-
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.
-
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.
-
Válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
-
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}}) |