Ebben az oktatóanyagban a Power Query Lekérdezésszerkesztőjével adatokat importálhat egy termékinformációkat tartalmazó helyi Excel-fájlból, valamint egy termékrendelési adatokat tartalmazó OData-adatcsatornából. Átalakítási és összesítési lépéseket fog végezni, és adatokat fog kombinálni a két forrásból a "Total Sales per Product and Year" (Összes eladás termékenként és évenként) jelentés elkészítéséhez.
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.
1. feladat: Termékek importálása egy Excel-munkafüzetbe
Ebben a feladatban termékeket importál a Termékek és Orders.xlsx (fent letöltött és átnevezett) fájlból egy Excel-munkafüzetbe, sorokat léptet elő oszlopfejléccé, 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>beolvasása>fájlból>munkafüzetből lehetőséget.
- Az Adatimportálás párbeszédpanelen keresse meg és keresse meg a letöltött Products.xlsx fájlt, majd válassza a Megnyitás gombot.
- A Kezelő ablaktáblában kattintson duplán a Termékek táblára. Megjelenik a Power Query-szerkesztő.
2. lépés: A lekérdezési lépések vizsgálata
Alapértelmezés szerint a Power Query az Ön kényelme érdekében automatikusan felvesz számos lépést. További információkért vizsgálja meg a lekérdezésbeállítások ablaktábla Alkalmazott lépések csoportjában található egyes lépéseket.
- Kattintson a jobb gombbal a Forrás lépésre, és válassza a Beállítások szerkesztése parancsot. 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 parancsot. Ez a lépés akkor jött létre, amikor Ön kijelölte a táblát a Navigáció párbeszédpanelen.
- Kattintson a jobb gombbal a Módosított típus lépésre, és válassza a Beállítások szerkesztése parancsot. Ezt a lépést a Power Query hozta létre, amely kikövetkeztette az egyes oszlopok adattípusait. 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 Adatok előnézete nézetben válassza ki a ProductID, a ProductName, a CategoryID és a QuantityPerUnit oszlopot (használja a Ctrl+Click vagy a Shift+Kattintás billentyűkombinációt).
- Válassza az Oszlopok>eltávolítása lehetőséget, távolítsa el a többi oszlopot.
4. lépés: A termékek lekérdezés 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 megjelenik egy új Excel-munkalapon.
Összegzés: Az 1. feladatban létrehozott Power Query-lépések
Amikor lekérdezési műveleteket végez a Power Query szolgáltatásban, a lekérdezés lépései létrejönnek, és megjelennek a Lekérdezés beállításai ablaktáblában, az Alkalmazott lépések listában. 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 a Power Query-képletek létrehozása az Excelben című témakörben olvashat bővebben.
| 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) |
| A Termékek tábla kijelölése | Navigálás: | = Source{[Item="Products",Kind="Table"]}[Data] |
| A 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ítása | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
2. feladat: Rendelési adatok importálása OData-adatcsatornából
Ebben a feladatban adatokat importál az Excel-munkafüzetbe a http://services.odata.org/Northwind/Northwind.svc Northwind minta OData-adatcsatornájából , kibővíti a Order_Details táblát, eltávolít oszlopokat, kiszámítja a sor végösszegét, átalakít egy OrderDate mezőt, csoportosítja a sorokat ProductID és Year 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>beolvasása> azOData-adatcsatornábóllehetőséget>.
- Az OData-adatcsatorna párbeszédpanelen írja be a Northwind OData-adatcsatorna URL-címét.
- Kattintson az OK gombra.
- A Kezelő ablaktáblában kattintson duplán a Rendelések táblára.
2. lépés: Egy Order_Details tábla kibontása
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. Amikor a lekérdezés fut, a program a kapcsolódó táblából (Order_Details) származó sorokat sorokká kombinálja az elsődleges táblával (Rendelések).
A Power Query alkalmazásban egy kapcsolódó táblát tartalmazó oszlop cellájában a Rekord vagy Táblázat érték szerepel. Ezeket strukturált oszlopoknak nevezzük. A rekord egyetlen kapcsolódó rekordot jelöl, és egy-az-egyhez kapcsolatot képez az aktuális adatokkal vagy az elsődleges táblával. A tábla egy kapcsolódó tábla, és egy-a-többhöz kapcsolatot képez az aktuális vagy elsődleges táblával. A strukturált oszlop relációs modellt alkalmazó adatforrás kapcsolatát jelöli. A strukturált oszlop például olyan entitást jelöl, amely egy OData-adatcsatornában idegen kulccsal van társítva, vagy egy SQL Server-adatbázisban idegen kulccsal van társítva.
Miután kibővítette a Order_Details táblát, három új oszlop és további sorok kerülnek be a Rendelések táblába: a beágyazott vagy kapcsolódó tábla minden sorához egy.
Görgessen vízszintesen az Order_Details oszlopra az Adatok előnézete nézetben.
A Order_Details oszlopban válassza a kibontás ikont (
A Kibontás legördülő listában tegye a következőket:
Válassza a (Válassza ki az összes oszlopot) lehetőséget az összes oszlop törléséhez.
Válassza a Termékazonosító,az Egységár és a Mennyiség mezőt.
Kattintson az OK gombra.
Megjegyzés
A Power Query szolgáltatásban kibonthatja az oszlopokhoz csatolt táblákat, és összesítheti a csatolt tábla oszlopait, mielőtt kibővítené a főtá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 Adatok előnézetében válassza ki az alábbi oszlopokat:
- Jelölje ki az első oszlopot (OrderID).
- Shift+Kattintson az utolsó oszlopra, Szállító.
- 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 egy kijelölt oszlop fejlécére, és válassza a További oszlopok eltávolítása parancsot.
4. lépés: A sor végösszegének kiszámítása minden Order_Details 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 Adatok előnézetében válassza a villámnézet bal felső sarkában lévő táblaikont (
- Kattintson az Egyéni oszlop hozzáadása elemre.
- Az Egyéni oszlop párbeszédpanel Egyéni oszlop képletmezőjébe írja be a következőt: [Order_Details.Egységár] * [Order_Details.Mennyiség].
- Az Új oszlop neve mezőbe írja be a Line Total (Sor végö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 Adatok előnézete ablakban kattintson a jobb gombbal az OrderDate oszlopra, és válassza az Év átalakítása> parancsot.
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 (RendelésDátuma) oszlopban válassza az Átnevezés lehetőséget, és írja be a Year (Év) szót.
6. lépés: Sorok csoportosítása ProductID (Termékazonosító) és Year (Év) szerint
Az Adatok előnézetében válassza az Év és a Order_Details.Termékkód elemet.
Right-Click az egyik fejlécre, és válassza a Csoportosítás 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 az értékesítési adatokat importálja az Excelbe, nevezze át a lekérdezést:
- A Lekérdezés beállításai ablaktábla Név mezőjébe írja be a Total Sales (Összes eladás) kifejezést.
Eredmények: A 2. feladat végső 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.
Összegzés: A Power Query 2. feladatban létrehozott lépései
Amikor lekérdezési műveleteket végez a Power Query szolgáltatásban, a lekérdezés lépései létrejönnek, és megjelennek a Lekérdezés beállításai ablaktáblában, az Alkalmazott lépések listában. 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 bővebben a "További tudnivalók a Power Query-képletekről" című témakörben olvashat.
| 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, [Implementation="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]) |
| Módosítsa a nevet egy kifejezőbb névre, Lne Total | Átnevezett oszlopok | = 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(#"Csoportosított sorok",{{"Year"; Date.Year, Int64.Type}}) |
| Váltás erre: kifejezőbb neveket – RendelésDátuma és Év mező |
1. oszlop átnevezése |
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}}) |
3. feladat: A Products (Termékek) és a Total Sales (Összes eladás) lekérdezés kombinálása
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 egyesíti a Products (Termékek) és a Total Sales (Összes eladás) lekérdezést egy egyesítő lekérdezés és a Kibontás művelet használatával, majd betölti a Total Sales per Product (Összes eladás termékenként) lekérdezést az Excel adatmodelljébe.
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 keresse meg a Termékek munkalapon a Termékek lekérdezést.
Jelöljön ki egy cellát a lekérdezésben, majd válassza a Lekérdezés>egyesítése lehetőséget.
Az Egyesítés párbeszédpanelen jelölje ki a Termékek elemet elsődleges táblaként, és válassza a Total Sales (Összes eladás) elemet másodlagos vagy kapcsolódó egyesítendő lekérdezésként. A Total Sales egy új strukturált oszlop lesz, amely egy kibontó ikonnal rendelkezik.
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.
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. Az adatvédelmi szintekről az Adatvédelmi szintek beállítása című témakörben talál további információt.
Eredmény
Az egyesítési művelet lekérdezést hoz létre. A lekérdezés eredménye tartalmazza az elsődleges tábla (Termékek) összes oszlopát, valamint egy strukturált oszlopot a kapcsolódó táblába (Total Sales). Válassza a Kibontás ikont, ha új oszlopokat szeretne felvenni az elsődleges táblába a másodlagos vagy kapcsolódó táblából.
2. lépés: Egyesített oszlop bővítése
Ebben a lépésben kibővíti az egyesített oszlopot a ÚjOszlop névvel, hogy két új oszlopot hozzon létre a Termékek lekérdezésben: Év és Összes eladás.
Az Adatok előnézetében válassza a Kibontás ikon (
lehetőséget a NewColumn elem mellett.A Kibontás legördülő listában:
- Válassza a (Válassza ki az összes oszlopot) lehetőséget az összes oszlop törléséhez.
- Válassza ki az Év és a Teljes értékesítés mezőt.
- 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 érték el a legnagyobb forgalmat, válassza a Csökkenő rendezés az összes eladás szerint 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 betöltése egy Excel-adatmodellbe
Ebben a lépésben egy lekérdezést tölt be egy Excel-adatmodellbe, hogy a lekérdezés eredményéhez kapcsolt jelentést hozzon létre. Miután betöltötte az adatokat az Excel-adatmodellbe, a Power Pivottal tovább folytathatja az adatelemzést.
- Válassza a Kezdőlap>, Bezárás & Betöltés lehetőséget.
- Az Adatimportálás párbeszédpanelen válassza az Adatok felvétele az adatmodellbe lehetőséget. A párbeszédpanel használatáról a kérdőjelre (?) kattintva tájékozódhat.
Eredmény
Total Sales per Product (Összes eladás termékenként ) lekérdezése van, amely a Products.xlsx fájl és a Northwind OData-adatcsatorna adatait kombinálja. Ez a lekérdezés egy Power Pivot-modellre vonatkozik. A lekérdezés módosításai az adatmodellben létrejövő táblát is módosítják és frissítik.
Összegzés: A 3. feladatban létrehozott Power Query-lépések
Amikor egyesítő lekérdezési műveleteket végez a Power Query alkalmazásban, a lekérdezés lépései létrejönnek, és megjelennek a Lekérdezés beállításai ablaktáblában, az Alkalmazott lépések listában. 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 bővebben a "További tudnivalók a Power Query-képletekről" című témakörben olvashat.
| 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 | Expanded Total Sales | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Két oszlop átnevezése | Átnevezett oszlopok | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year"; "Year"}; {"Total Sales.Total Sales"; "Total Sales"}}) |
| Összes eladás rendezése növekvő sorrendben | Rendezett sorok | = Table.Sort(#"Átnevezett oszlopok",{{"Total Sales"; Order.Ascending}}) |