Power Query: a 2021-es végleges Kezdők útmutatója

Szia, Üdvözöljük a kezdők útmutatójában az UpSlide Power Query-hez.
Gondolkozott már azon azon, hogy mi a Power Query? Hogyan működik? Ha igen, akkor a megfelelő helyen vagy!
Pauline, a Power Query szakértője a Finance 3.1-től adta nekünk a legjobb tippeket és tanácsokat a cikk megírásához.
itt megtanulhatja a szoftver alapjait, sőt gyakorolhat is egy kicsit! Készen van?

ha most nincs ideje elolvasni, töltse le a PDF verziót, és élvezze később!

mi az a Power Query? Mikor kell használni?

a Power Query egy Excel-bővítmény, amely lehetővé teszi a felhasználók számára az adatok importálását, tisztítását és konszolidálását. A Power Query akkor használható, ha:

• nagy mennyiségű adat feldolgozása (ahol az Excel egymillió sorra korlátozódik)
• Adatok importálása különböző forrásokból (pl. Excel, CSV, SharePoint, SQL és Salesforce)
• több tábla, fájl vagy akár mappa manipulálása, tisztítása és kombinálása!

a Power Query felhasználóbarát és hatékony eszköz még a kezdők számára is, mivel az induláshoz nincs szüksége kódolási tapasztalatra. Ezenkívül nem változtatja meg az eredeti adatforrást. Az eredmény olyan, mint egy pillanatkép az átalakított adatokról, amelyeket az Excelbe hoztak. Mi olyan nagyszerű ebben? Nos, mivel a forrásadatokat nem közvetlenül importálják, ez nem nehezíti meg az Excel fájlt (azaz nem lassítja le a számítógépet!).

összefoglalva, a Power Query egy adatelemző eszköz, amely egyszerűbbé és hatékonyabbá teszi az ETL (Extract-Transform-Load) folyamatokat.

mi a különbség a Power Query és a Power Pivot között?

a Power Pivot gyakran társul a Power Query-hez, mert ez egy Microsoft Office-bővítmény, amely megkönnyíti az adatok elemzését. Érdekes módon azonban ez a két eszköz párhuzamosan működhet egymással! A Power Pivot egyszerűen az adatátalakítás következő lépése: ez lehetővé teszi a felhasználók számára, hogy adatmodelleket hozzanak létre a Power Query által korábban megtisztított táblákból.

míg hasonló, a Power Pivot inkább az adatok elemzésére, az időhöz kapcsolódó számításokra és a linkek generálására szolgál. Egyszerűen mondta:

• a Power Query lehetővé teszi a fájlok automatikus összevonását és újrafeldolgozását (azaz a jelentési folyamatok szabványosítását vagy több, azonos formátumú fájl konszolidálását).
• a Power Pivot lehetővé teszi több millió sorból álló adatbázisok elemzését az összesített információk kinyerésével a vállalkozás által meghatározott szabályok szerint.

mi a különbség a Power Query és a Power BI között?

a Power Query mögöttes lekérdezési technológiáját a Power BI is tartalmazza.

a Power BI Desktop szoftver kezelőfelülete lehetővé teszi a három technológia (Power Query, Power Pivot és Power View) előnyeit. A Power BI segítségével az adatelemzési lánc minden lépését elvégezheti, az adatok kinyerésétől kezdve a megosztható és elemezhető interaktív irányítópulton lévő vizuális elemek létrehozásáig.

> olvassa el cikkünket arról, hogyan exportálhatja a Power BI-t Excel-be

miért kiváló alternatívája a Power Query az Excel VBA-nak?

a Power Query könnyen helyettesítheti a VBA-t (Visual Basic for Applications), mivel lehetővé teszi:

• feldolgozza a táblázatokat egyszerűen a gombokra kattintva. Nincs szükség kódolási készségekre!
• a műveletek lépésről lépésre történő megjelenítése egyetlen makró futtatása nélkül.
• könnyen szerkesztheti a különböző lépések sorrendjét a húzással! Is felveheti és törölheti a felesleges lépéseket egy egyszerű kattintással.

(lásd a 2. Részt – 3. szakasz: hogyan kezdjük el a Power Query használatát?)

a Power Query azonban nem helyettesítheti a VBA-t az Excel felületével való interakció, a folyamatok automatizálása, új fájlok létrehozása, e-mailek küldése stb.

Ingyenes A Microsoft Power Query? Van egy Power Query for Mac?

igen. A Power Query az Excel ingyenes kiterjesztése. Ez a bővítmény másik fő előnye.
(olvassa el a következő kérdést, hogy megtudja, hogyan szerezheti be az Excel asztalon)

Power Query for Mac? Igen, nagy hír: a Query editor Most kompatibilis az Excel for Mac programmal! De az integráció jelenleg meglehetősen korlátozott, mivel csak a fájl elérési útját változtathatja meg, és frissítheti a meglévő lekérdezéseket.

2. rész: kezdje el a Power Query használatát!

hogyan lehet letölteni a Power Query-t?

a Power Query 2010 óta elérhető az Excel számára.

a Power Query hozzáadása az Excel verziójának megfelelően*:

• jó hír, hogy nem kell letöltenie a Power Query alkalmazást az Excel 2019, 2016 vagy az Office 365 alkalmazáshoz. Megtalálhatja az ” adatok “szalagon a”Get & Transform” gomb alatt.
• Excel 2013 és 2010 esetén le kell töltenie a Power Query bővítményt. Ez egy új, “Power Query” nevű dedikált lapon jelenik meg (az Excel 2010-ben professzionális plusz szoftverbiztonsági licenccel kell rendelkeznie).

*itt van, hogyan ellenőrizheti az Excel verzióját:

menj a fájlhoz > fiók > az Excel-ről. Ezután megjelenik egy ablak. Olvassa el a zárójelben az első két számot. Az alábbi példában az Excel verziója 2016.

további információkért itt található egy másik részletes cikk a Power Query Excelben történő telepítéséről és aktiválásáról.

hogyan aktiválhatom a Power Query-t az Excelben?

az Excel verziójától függően (lásd a fenti kérdést: Hol van a Power Query az Excelben?) csak rá kell kattintania a “Get data” nevű gombra a “Get & Transform” szakaszban az “Adatok” lapon az Excel 2016 és azon túl. A 2013 előtti Excel verziókhoz egyszerűen lépjen a Power Query menüszalagra.

kérjük, vegye figyelembe, hogy amikor belép a Power Query felületre, akkor már nem lesz képes kölcsönhatásba lépni az Excel programmal. Ha vissza kell térnie az Excelbe, akkor be kell zárnia a Power Query szerkesztőt.

hogyan működik a Power Query?

az Excelben csak akkor férhet hozzá a következő funkciókhoz ,ha rákattint a “lekérdezések & kapcsolatok “elemre”:

Adatszalag: adatforrások keresése (“Power Query” menüszalag az Excel 2010 és 2013 számára).

lekérdezések & kapcsolatok ablaktábla: az összes létrehozott kapcsolat (lekérdezés) gyors áttekintése.

miután elindította a Power Query alkalmazást, a Power Query szerkesztőre kerül, amely 4 különálló elemből áll:

fő szalag: gombjainak köszönhetően a semmiből hozhat létre lekérdezéseket (ha ismeri az M nyelvet, akkor a speciális szerkesztőhöz is hozzáférhet, hogy manuálisan módosítsa őket).

lekérdezések: az adatforrásokhoz és adatfolyamokhoz való összes kapcsolat nyomon követése.

Adattábla előnézete: az adatok eredményének megjelenítése egy adott lépés után.

lekérdezési Beállítások (Alkalmazott lépések): az összes megtett lépés közötti navigáláshoz. A Power Query automatikusan rögzíti az összes átalakítást.

• a 4. szakaszban megtudhatja, hogyan működik a Power Query alapvetően egy adattábla átalakításával.
• az 5.szakaszban még érdekesebbé válik, mivel megtudhatja, hogyan folytathatja a különböző forrásokból származó adatokat és egyesítheti a lekérdezéseket.

Hogyan kezdjük el a Power Query használatát?

képzelje el a következő helyzetet:

Ön egy számítógépeket és okostelefonokat értékesítő technológiai cég pénzügyi irányítója. Jelenleg 3 országban működik: Franciaországban, az Egyesült Királyságban és Kínában. Minden hónapban konszolidált jelentést készít a menedzsere számára, olyan KPI-kkel, mint a teljes bevétel, az országonkénti bevétel vagy a termékenkénti bevétel. Ehhez minden ország vezetője – Dennis, Douglas és Karen – küld egy fájlt.

probléma: minden fájl felsorolja az eladásokat az ország saját címletében. Túl időigényes ezeket euróra váltani, majd a zárójelentésbe összevonni.

mi lenne, ha automatizálná a folyamatot és néhány perc alatt kiszámítaná a jelentést a Power Query használatával?

nem lenne félelmetes? Nos, a Power Query segítségével megteheti! Kezdjük azzal, hogy elkészítjük az első táblázatot, amely a következő: 6405>

először is adatokra van szüksége. Töltse le Power Query példánkat, és mentse el a helyi szerverre.

Ezután kövesse az alábbi egyszerű lépéseket:

• csomagolja ki a fájlt, és mentse el a helyi szerverre.
• most nyisson meg egy új munkalapot az Excelben, és lépjen az adatok fülre > Adatok beolvasása > fájlból > munkafüzetből, az alábbiak szerint.

(ha az Excel 2013-as vagy 2010-es verzióját használja, egyszerűen lépjen a Power Query lapra)

• LÉPJEN arra az elérési útra, ahová az imént letöltött kicsomagolt fájlokat mentette, majd válassza ki az “árfolyamok”nevű Excel fájlt.

• kattintson adatok átalakítása.

azt is észrevehette, hogy néhány alkalmazott lépés már megjelent a lekérdezési Beállítások ablaktáblában. Ezek automatikusan megtörténnek.

most gyakoroljunk egy kicsit, kezdve az adattábla tisztításával:

• távolítsa el a felső 3 sort, menjen a sorok eltávolításához > távolítsa el a felső sorokat, és írja be a “3”szót. Azonnal látni fogja az átalakítást az Adattábla előnézetében, valamint az alkalmazott lépésekben (lekérdezési Beállítások).

• megszabadulni az üres soroktól: távolítsa el a sorokat > távolítsa el az üres sorokat (az alábbiak szerint).

• az első sor fejlécként történő népszerűsítése: kattintson az “első sor használata fejlécként”elemre. Mint észrevehette, a Power Query automatikusan hozzáadott egy lépést egyes adatok típusának megváltoztatásához (az első oszlop értékei most dátumként vannak formázva).

• távolítsa el az üres oszlopokat: kattintson az “Oszlopok kiválasztása” gombra, és törölje a 7., 8., 9. és 10. oszlop jelölését.

jól csinálod! Még három lépés, és minden készen áll!

• az oszlopok visszavonása: Válassza ki az” árfolyam “oszlopot, majd az átalakítás lapon LÉPJEN az Unpivot oszlopok > Unpivot egyéb oszlopok elemre (kiválaszthatja az öt másik oszlopot is, majd az”Unpivot oszlopok” lehetőséget).

• nevezze át az oszlopokat: kattintson a fejléccellákra, írja be az elsőbe a “dátum”, a másodikba a “pénznem”, a harmadikba pedig a “kamatláb” szót.

• szűrő alkalmazása: csakúgy, mint az Excelben, kattintson a “dátum” oszlop legördülő menüjére, majd válassza a “Dec-18″lehetőséget.

• a Kezdőlap lapon kattintson a Bezárás és Betöltés gombra a döntő asztal új lapra történő betöltéséhez.

szép munka! Ön alakította az első adattáblát.

menjünk tovább a Power Query

(újra)képzelje el a következő helyzetet:

a Power Query képességeinek köszönhetően Ön most a technológiai cég vezető pénzügyi irányítója. Most konszolidálnia kell a 2018.decemberi adatokat, amelyeket az országvezetők küldtek neked.

probléma: minden fájl felsorolja az eladásokat az ország saját címletében. Időigényes ezeket euróra váltani, még inkább, hogy aztán konszolidáljuk őket a zárójelentésben.

mi lenne, ha automatizálná a folyamatot, és csak néhány perc alatt kiszámítaná a jelentést a Power Query használatával?

most meglátjuk, hogyan lehet konszolidálni a különböző adatforrásokat, és egyesíteni őket az előző szakaszban készített táblázattal.

először is importáljuk az egész mappát, beleértve a három különböző forrásfájlt:

• ha még mindig a Power Query programban van, lépjen az új forrás > Fájl > mappába.

(ha visszatért az Excelbe, menjen az Adatok beolvasása > mappából)

• válassza ki a korábban letöltött és mentett “Sales_201812” mappát. Ezután kattintson az OK gombra.

új ablak jelenik meg.

ezután egyesítenie kell a lekérdezéseket:

• válassza a” Combine & Edit ” lehetőséget a legördülő menüben (az új ablak alján).

• válassza ki a “Dec-18” példatáblát (kérjük, vegye figyelembe, hogy az összes lapot ugyanúgy kell megnevezni).

• az első oszlopban ellenőrizheti, hogy az összes forrás szerepel-e (kattintson a legördülő menüre), majd kattintson az OK gombra.

most a Power Query szerkesztőben van.

• menjen a transzformáció fülre > cserélje ki az értékeket.

• most cserélje ki az ” argentinok “szót”EUR” – ra.

• menjen a Kezdőlap fülre > lekérdezések egyesítése.

új ablak jelenik meg:

1. válassza a “pénznem” oszlopot.
2. válassza ki az “árfolyamok” fájlt a legördülő menüben.
3. válassza a “pénznem” oszlopot.
4. kattintson az OK gombra.

• most meg kell jelenítenie az éppen letöltött árfolyamadatokat:
  1. válassza az “árfolyam” oszlopot.
  2. kattintson a Kibontás gombra.
  3. csak az “arány”ellenőrzése.
  4. törölje az “eredeti használata …”jelölőnégyzetet.
  5. kattintson az OK gombra.

Gratula! Csak egy másik adatforrást töltött be, egyesítette a lekérdezéseket, és lekérte a megfelelő árfolyamot. Nincs több VLOOKUP vagy INDEX + mérkőzés!

a befejezéshez létrehozunk egy” egységár EUR ” oszlopot, amely az összes árat euróra konvertálja:

• LÉPJEN az Oszlop hozzáadása fülre > Egyéni oszlop.

• megjelenik egy új ablak. Írjuk be az ár képletét euróban:
  1. nevezze el “egységár EUR” – nak.
  2. kattintson duplán az “egységár helyi pénznem” elemre (vagy válassza ki, majd kattintson a Beszúrás gombra).
  3. Típus “/”.
  4. kattintson duplán a “Rate”gombra.
  5. kattintson az OK gombra.

• miután megjelenik az oszlop, kattintson a típus szimbólumára (közvetlenül a név előtt), majd válassza a “decimális szám”lehetőséget.

ugyanezt kell tennünk a “bevétel” oszlop létrehozásához:

• LÉPJEN az Oszlop hozzáadása fülre > Egyéni oszlop.
• megjelenik egy új ablak. Írja be a bevételi képletet:
  1. nevezze el “bevétel” – nek.
  2. kattintson duplán az “egységár EUR” elemre (vagy válassza ki, majd kattintson a Beszúrás gombra).
  3. írja be a “*”szót.
  4. kattintson duplán a “mennyiség”elemre.
  5. kattintson az OK gombra.

• ismét változtassa meg az oszlop típusát “decimális számra”.

a befejezéshez exportáljuk egy új adattáblába az Excel programban!

• ugrás a kezdőlapra > Bezárás & Betöltés > Bezárás & Betöltés.

most már teljes mértékben kihasználhatja az Excel funkcióit az adatok elemzéséhez, a pivot tábla készítéséhez stb.

Wow! Ha sikerült a végére, gratulálok! Most már tudja, hogyan működik a Power Query.

most rajtad a sor!

szeretném átadni neked: megértette a fenti kérdéseket a Power Query-vel kapcsolatban?
letöltötted az oktatófájljainkat, és követted a fenti irányelveket, hogy kipróbáld magad?
tudassa velünk, így egy gyors megjegyzést alább most!

További információ a Power Query – ről: