Applies ToMicrosoft 365-höz készült Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

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

  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.

    A többi oszlop elrejtése

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 (Kibontás).

  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.

      Az Order_Details (Rendelés_részletei) tábla csatolásának bővítése

      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 (Tábla ikon) 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.

Sor végösszegének kiszámítása minden Order_Details (Rendelés_részletei) sorhoz

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.

    Csoportosítási szempont párbeszédpanel összesítő műveletekhez

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.

Összforgalom

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

    Az Egyesítés párbeszédpanel

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.

Végleges egyesítés

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 (Kibontás) 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

Táblacsatolás bővítése

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}})

Lásd még

az Excelhez készült Power Query súgója

További segítségre van szüksége?

További lehetőségeket szeretne?

Fedezze fel az előfizetés előnyeit, böngésszen az oktatóanyagok között, ismerje meg, hogyan teheti biztonságossá eszközét, és így tovább.

A közösségek segítségével kérdéseket tehet fel és válaszolhat meg, visszajelzést adhat, és részletes ismeretekkel rendelkező szakértőktől hallhat.