A Power Query egy hatékony üzleti intelligencia eszköz az Excelben, amely lehetővé teszi az adatok importálását, tisztítását és átalakítását az Ön igényei szerint. Ha valaha is órákat töltött az adatok megtisztításával (mi van), vagy küszködött a különböző forrásokból származó adatok egyesítésével (nekünk is van), ez a funkció megkönnyíti az életét.
A Power Query Excel programban való használatának megtanulásának legjobb módja, ha néhány gyakori feladatot hajt végre, például adatokat importál, és a Power Query-szerkesztőt használja a kapott táblák átalakításához és az adatok kombinálásához.
Ez a cikk azzal segíti a kezdést, hogy világosan megmutatja. A Power Query elsajátításával a következő szintre emelheti munkáját.
Kész vagy? Csináljuk.
Tartalomjegyzék
Hogyan tudhatja meg, hogy hozzáfér-e a Power Query-hez
A Power Query használatának megkezdése előtt meg kell győződnie arról, hogy rendelkezik az Excel olyan verziójával, amely rendelkezik ezzel a funkcióval.
Íme a Power Query rövid áttekintése verziók szerint:
Excel 2010 és Excel 2013: bővítményként érhető el, amelyet le kell tölteni és telepíteni kell.
Excel 2016: „Get & Transform” néven integrálva az Excelbe.
Excel 2019: jobb teljesítmény és funkcionalitás.
Excel 2021 és Excel for Microsoft 365: integráció felhő adatforrásokkal.
Excel for Mac: csak a Microsoft 365 16.69-es és újabb verzióitól érhető el.
Annak ellenőrzéséhez, hogy az eszköz integrálva van-e az Excel-munkafüzetbe (2016 vagy újabb), lépjen az Adatok lapra, és keresse meg az „Adatok lekérése és átalakítása” részt ezen a képen:
Ha Excel 2010 vagy 2013 verziója van Windows rendszeren, letöltheti , telepítheti és engedélyezheti a Power Query bővítményt.
Adatok importálása a Power Query segítségével
A Power Query eszköz lehetővé teszi számos adatforrás importálását és csatlakozását:
Fájlok, beleértve a CSV, XML és JSON formátumokat
Web URL-ek vagy API-végpontok
Adatbázisok, beleértve az Access, SQL Server, Oracle és a legtöbb modern adatbázist
Azure-szolgáltatások, SharePoint-fájlok és OData-hírcsatornák
A legegyszerűbb adatforrás egy egyszerű CSV-fájl, amely több sornyi adatot tartalmaz. Hozzon létre egy szöveges fájlt, és adja hozzá ezt a négy kereszt- és vezetéknevet, vesszővel elválasztva:
Joe, Bloggs
Anne, Ryan
James, Stewart
Mary, Brown
Ügyeljen arra, hogy a fájlt .CSV kiterjesztéssel mentse. A fájl így fog kinézni:
Az adatok Power Query segítségével történő betöltéséhez kövesse az alábbi lépéseket:
Lépjen az Adatok fülre.
Kattintson a „Fájlok letöltése” gombra az Adatok letöltése és átalakítása lapon.
Válassza ki a mintafájlt.
Előfordulhat, hogy várnia kell néhány másodpercet, amíg az eszköz behúzza az adatokat az előnézeti ablaktáblába. A Power Query meglehetősen sok gépi erőforrást használ még kis import esetén is, ezért kérjük, legyenek türelmesek.
Tipp: a teljesítmény felgyorsítása érdekében állítsa le a többi alkalmazást.
A Power Query megjeleníti a „Szöveg/CSV import” ablakot az adatok előnézetével.
Ha az adatok helyesnek tűnnek, folytathatja az adatok betöltését vagy átalakítását:
a Betöltés gomb közvetlenül az Excelbe tölti be az adatokat.
az Adatok átalakítása gombbal módosíthatja az adatokat a betöltése előtt.
Észrevette, hogy a mintafájlnak nem volt fejléce? Ha közvetlenül az Excelbe tölti be, a fejlécek „1. oszlop” és „2. oszlop” lesznek.
Ehelyett behúzhatja az adatokat a Power Query szerkesztőbe, és végrehajthat néhány módosítást. Ehhez kattintson az „Adatok átalakítása” gombra.
Megnyílik a Power Query szerkesztő, amely a következő részhez vezet.
A Power Query szerkesztő használata
Ebben a részben a Power Query szerkesztővel való munka néhány lényeges szempontját fogjuk megvizsgálni.
Adatok előnézete
Az Adatok előnézete területen megtekintheti az importált adatokat, és kezelheti azokat. Szűrheti a sorokat, módosíthatja az oszlop adattípusait, és igényeinek megfelelően alakíthatja át az adatokat.
Ha végzett, az Excel betölti az átalakított adatokat egy munkalapba.
A mintafájl használatával az oszlopneveket közvetlenül ezen a panelen szerkesztheti.
Ehhez tegye a következőket:
Kattintson a jobb gombbal az oszlop fejlécére.
A legördülő menüben kattintson az „Átnevezés” elemre.
Írja be a kívánt neveket a fejlécek mezőibe.
A változtatások végrehajtása során az előnézeti ablaktábla jobb oldalán található „Alkalmazott lépések” szakasz minden egyes átalakításhoz igazodik, lehetővé téve az adatfeldolgozási lépések nyomon követését és szükség szerinti módosítását.
Tulajdonságok
A lekérdezések tulajdonságainak beállítása elengedhetetlen a munka rendszerezéséhez és nyomon követéséhez.
A Lekérdezési beállítások panel a Power Query ablak jobb oldalán található.
Kattintson az „Összes tulajdonság” hivatkozásra.
Szerkessze a lekérdezés nevét, hogy leíró jellegű legyen.
További információért adjon hozzá leírást.
Könnyű elfelejteni, hogy a múlt heti lekérdezések mire lettek beállítva. Ebben az esetben a lekérdezés neve megváltozott, és hozzáadásra került egy leírás, amely megadja a lekérdezés célját.
Ha többet szeretne megtudni a rendelkezésre álló lehetőségekről, nézze meg ezt.
Hogyan kell dolgozni több adatforrással
A Power Query Editor két elsődleges módszert kínál a több forrásból származó adatok kombinálására: a hozzáfűzést és az egyesítést.
A hozzáfűzés függőlegesen összekapcsolja a táblázatokat, egymásra helyezve őket. Az összevonás lehetővé teszi a táblázatok összekapcsolását a közös oszlopok alapján.
Ha látni szeretné, hogyan működik ez, hozzon létre egy második kis CSV-fájlt, amelynek szerkezete megegyezik az elsővel. Ezúttal két nevet (utó- és vezetéknév) írjon be a fájlba:
Joe, Bloggs
Laura, Cane
Figyelje meg, hogy a nevek egyike megismétlődik az előző fájlból.
Most kövesse az alábbi lépéseket az adatok Microsoft Excel-munkafüzetbe való behúzásához.
Zárja be a Power Query Editor ablakot, és mentse a módosításokat.
Új munkalap hozzáadása.
Kövesse az előző szakasz lépéseit a második fájl importálásához.
Kattintson az Átalakítás gombra.
Nevezze át az oszlopfejléceket „Keresztnév” és „Vezetéknév” névre.
Az Excel munkalap jobb oldali ablaktáblája az aktuális lekérdezéseket és kapcsolatokat mutatja. Most két lekérdezést kell látnia a panelen.
Adatok hozzáfűzése a Power Query segítségével
Ebben a szakaszban a második CSV-mintafájlt csatolja az elsőhöz.
Kövesse az alábbi lépéseket az adatok hozzáfűzéséhez:
Kattintson a jobb gombbal az első táblára (Ügyfélnevek) a Lekérdezések és kapcsolatok panelen.
Válassza a „Szerkesztés” lehetőséget a táblázat megnyitásához a Power Query szerkesztőben.
Kattintson a Lekérdezések hozzáfűzése lehetőségre az Összevonás részben.
Válassza ki a második táblázatot a hozzáfűzendő táblázatok legördülő listájából.
Az eredmény két extra sor lesz az első táblázatban.
Figyeljük meg, hogy egy sor ismétlődik – így működik az append művelet. Hozzáadja az adatokat egyik táblából a másikhoz, függetlenül attól, hogy az adatok már léteznek-e.
Az alábbi lépésekkel távolíthatja el az ismétlődő adatokat a Power Query szerkesztőben :
Kattintson a „Keresztnév” oszlopfejlécre.
Tartsa lenyomva a „Ctrl” billentyűt (vagy a „Cmd” billentyűt Mac gépen).
Kattintson mindkét oszlopfejlécre.
Kattintson a „Sorok eltávolítása” lehetőségre a Power Query szerkesztő szalag Kezdőlap lapján.
A legördülő menüből válassza az „Ismétlődések eltávolítása” lehetőséget.
Adatok egyesítése a Power Query segítségével
Az adatok egyesítésekor ki kell választania a megfelelő oszlopokat a lekérdezések között.
Tegyük fel, hogy van két vesszővel tagolt fájlja, amelyek közül az egyik az ügyfelek telefonszámait, a másik pedig a címeket tartalmazza. A két fájl így néz ki:
Kövesse az előző szakaszokban található utasításokat az egyes fájlok saját teljesítménylekérdezésbe való betöltéséhez. Ezután gyakorolhatja a táblák összevonását.
Mintaadataink esetében mindkét oszlopot (Keresztnév és Vezetéknév) megfelelő oszlopként kell kiválasztani. Több oszlop kiválasztásához tartsa lenyomva a Ctrl billentyűt.
Kovesd ezeket a lepeseket:
Kattintson a jobb gombbal az első táblázatra a Lekérdezések és kapcsolatok panelen.
Válassza a „Szerkesztés” lehetőséget a táblázat megnyitásához a Power Query szerkesztőben.
Az „Összevonás” csoportban válassza a „Lekérdezések egyesítése” lehetőséget.
Válassza ki a második táblázatot a táblázatok legördülő listájából.
Kattintson az első táblázat első oszlopára.
Tartsa lenyomva a Ctrl billentyűt, és kattintson az első táblázat második oszlopára.
Tartsa lenyomva a Ctrl billentyűt, és kattintson a második táblázat mindkét oszlopára.
Az alapértelmezett csatlakozási operátor maradjon „Külső balra”.
Kattintson az „OK” gombra a módosítások alkalmazásához.
A „Left Outer” csatlakozási operátor gondoskodik a duplikált adatok kiküszöböléséről.
A Power Query szerkesztőben megjelenik egy új „Táblázat” vagy „Beágyazott táblázat” nevű oszlop, amely a második lekérdezés egyesített adatait tartalmazza.
Kattintson a kibontás ikonra (két nyíl) az oszlop fejlécében.
Törölje a jelet az „eredeti oszlop használata előtagként” négyzetből (ez elkerüli a hosszú oszlopneveket).
Kattintson az „OK” gombra.
Kattintson a „Bezárás és betöltés” gombra.
Az összevont táblázat négy oszlopból állt, a telefonszám- és címadatok egyesítve. Lehet, hogy még van egy kis rendet csinálnod.
Például érdemes lehet szerkeszteni a táblázat nevét (Merge1) és a cím oszlopot az alábbi képen.
5 módszer az adatok átalakítására a Power Query segítségével
Az Excel-fájlban lévő adatok átalakítása egyszerűvé válik a Power Query segítségével, amely lehetővé teszi az adatok hatékony tisztítását, rendszerezését és kezelését.
Az adatok átalakításának néhány leggyakoribb módja:
Adatok bővítése és tömörítése
Adatok rendezése
Adatok formázása
Oszlopok hozzáadása és eltávolítása
Adattípusok módosítása
1. Adatok kiterjesztése és tömörítése
Az adatok bővítése és tömörítése fontos az adatkészletek kezelhetőbbé tételéhez. A Power Query lehetővé teszi a táblázatok kibontását vagy összecsukását a rendezettebb nézet érdekében.
Az előző szakasz bemutatja, hogyan bővíthető a kapcsolódó táblák adatainak felvételével.
Sűrítheti is úgy, hogy csak az összesített adatokat tartalmazzon az Ön által választott összesítésen, például összegen, számon vagy százalékon.
2. Adatok rendezése
Az adatok rendezéséhez gyorsan módosíthatja a sorrendet növekvő vagy csökkenő értékekkel.
Kattintson az oszlop neve melletti lefelé mutató nyílra, és válassza a „Növekvő rendezés” vagy „Csökkenő rendezés” lehetőséget.
3. Adatok formázása
A Power Query szerkesztő Átalakítás lapja számos lehetőséget kínál.
Például a Formátum legördülő menüben egy oszlopban vagy a teljes táblázatban lévő összes adatot nagy- vagy kisbetűre módosíthatja.
4. Oszlopok hozzáadása és eltávolítása
A Power Query leegyszerűsíti az oszlopok hozzáadásának, törlésének és módosításának folyamatát.
Egy oszlop eltávolításához egyszerűen kattintson a jobb gombbal a nem kívánt oszlop fejlécére, és válassza az „Eltávolítás” lehetőséget. Ezzel véglegesen törli az oszlopot és annak összes adatát a táblázatból.
Új oszlop hozzáadásához válassza ki azt a meglévő oszlopot, ahol meg szeretné jeleníteni az újat. Ezután használja az „Oszlop hozzáadása a példákból” funkciót egyéni oszlop létrehozásához vagy két vagy több oszlop közötti számítások elvégzéséhez.
5. Adattípusok módosítása
A következetes és pontos adattípusokkal végzett munka kulcsfontosságú a pontos elemzéshez. A Power Query lehetővé teszi az adattípusok egyszerű megváltoztatását:
Kattintson a jobb gombbal az oszlop fejlécére.
Válassza a „Típus módosítása” lehetőséget.
Válassza ki a kívánt adattípust a rendelkezésre álló listából.
Az adattípusok megváltoztatása különösen hasznos lehet százalékos adatok kezelésekor.
Előfordulhat például, hogy a tizedespontokat százalékossá kell konvertálnia, vagy a szövegalapú százalékokat numerikussá kell konvertálnia a könnyebb számítás és összehasonlítás érdekében.
Képletekkel és M nyelvvel végzett munka
Amikor a Power Queryt Excelben használja, találkozni fog az M nyelvvel, amely a fejlett képletek létrehozásának, az adatok kezelésének és a hatékony lekérdezések létrehozásának a szíve. Ebben a részben a következőkről fog tudni:
a Formula bár
M kód
az M Nyelv szintaxisának lényeges elemei
Formula bár
Az Excel Power Query szerkesztőjének képletsora az a terület, ahol beviheti a képleteket és az M-kódot.
Használatához nyissa meg a Power Query szerkesztőt úgy, hogy kiválaszt egy lekérdezést a Lekérdezések és kapcsolatok panelen. A Képletsáv az adatok előnézeti ablaktáblája felett található.
M kód írása és módosítása
Sokoldalú és hatékony lekérdezések létrehozásához az Excelben meg kell írnia és módosítania kell az M-kódként ismert Power Query nyelvet. Íme néhány tipp az induláshoz:
Az Alkalmazott lépések ablaktáblával lépésről lépésre írhat kódot, ami megkönnyíti a lekérdezés logikájának nyomon követését és módosítását.
Dinamikus és automatizált jelentések létrehozásához kombinálja az M-kódot egy Excel-táblával, pivot-táblázatokkal és külső adatforrásokkal, például Azure-val és szöveges fájlokkal.
Módosítsa a Lekérdezési beállításokat az adatbetöltési beállítások finomításához és a teljesítmény javításához.
Ahogy megismeri az M kódot, rá fog jönni, hogy számos folyamatot automatizálhat és optimalizálhat, így az Excelben végzett adatelemzés hatékonyabbá és hatékonyabbá válik.
A lekérdezés mögötti kód megtekintéséhez kövesse az alábbi lépéseket:
Nyissa meg a lekérdezést a Power Query szerkesztőben.
Kattintson a „Speciális szerkesztő” elemre a Kezdőlap lapon.
Ez a kód a minta egyesített táblázatunk mögött (a tiéd eltérő lehet):
Mi az M nyelvi szintaxis?
Az M nyelv szintaxisának megértése elengedhetetlen a hatékony Power Query képletek létrehozásához. Tartsa szem előtt a következő pontokat:
M A nyelv megkülönbözteti a kis- és nagybetűket, ezért legyen óvatos a nagybetűkkel.
Használjon szögletes zárójelet [] az oszlopokra, rekordokra és listákra való hivatkozáshoz.
A lekérdezés egy korábbi lépésére való hivatkozáshoz használja a változó nevét (pl. #"Lépés neve").
Adjon ki egy lekérdezési képlet lépést az „in” utasítással.
A tapasztalatszerzés során új módszereket fedezhet fel az adatok átalakítására és elemzésére, így Excel-munkafüzetei még értékesebb eszközzé válnak.
Nézze meg az alábbi videót, ideje elővenni a súrolókefét és megtisztítani a piszkos adatokat.
Az adatkapcsolatok frissítése
A Power Query egyik legfontosabb funkciója a Power Query kapcsolat egyszerű frissítésének képessége.
Ha az adatforrások frissültek, és szüksége van a munkafüzet legfrissebb információira, frissítheti az adatkapcsolatokat a Lekérdezések és kapcsolatok panelen.
Ha nem látja a munkalap jobb oldalán lévő ablaktáblát, megnyithatja az Adattáblázatban, és a „Lekérdezések és kapcsolatok” elemre kattintva.
Amikor a Lekérdezések és kapcsolatok ablaktábla meg van nyitva, az összes lekérdezést felsorolja. Kovesd ezeket a lepeseket:
Kattintson a jobb gombbal a frissíteni kívánt lekérdezésre.
A helyi menüben válassza a Frissítés lehetőséget.
Ezzel frissíti a munkafüzetet a csatlakoztatott adatforrások legfrissebb adataival.
A Power Query integrálása az adatmodellbe
Az Excel adatmodellje egy hatékony szolgáltatás, amely lehetővé teszi üzleti intelligencia eszközök, például pivot táblák létrehozását adatkapcsolatok alapján.
Az adatmodell alapvetően egy rejtett adatbázis, amely az importált adatokat és a táblák közötti kapcsolatokat tartalmazza.
Ez a Power Pivot funkció része, amely be van építve az Excel jelenlegi verzióiba. Előfordulhat, hogy a Power Pivot bővítményként telepítenie kell a korábbi verziók eléréséhez).
A Power Query adatkapcsolatait az alábbi lépésekkel integrálhatja az adatmodellbe:
Töltse be adatait a Power Query használatával az előző szakaszokban leírtak szerint.
Ahelyett, hogy közvetlenül a munkafüzetbe töltené be, válassza a Betöltés menü Betöltés pontját.
Jelölje be az Adatok hozzáadása az Adatmodellhez jelölőnégyzetet az Adatok importálása párbeszédpanelen.
Automatizálás a Power Query és a VBA segítségével
A VBA (Visual Basic for Applications) integrációval egyéni adatkapcsolatokat hozhat létre, és automatizálhatja az adatátalakítási lépéseket a Power Queryben.
Használhatja meglévő VBA-készségeit fejlett adatmodellek felépítéséhez, ha olyan platformokból származó adatokat épít be, mint a Microsoft Access, a SharePoint és az Analysis Services.
Bár a Power Query nem támogatja a natív VBA-t, a VBA-kódot használhatja a Power Query-lekérdezések közvetett megnyitására, frissítésére és manipulálására.
Ez lehetővé teszi hatékony, automatizált adatmunkafolyamatok létrehozását és az adatelemzési folyamat egyszerűsítését.
Íme néhány példakód, amely frissíti az aktuális munkafüzetben lévő power lekérdezést.
:
Dim wb munkafüzetként, pq objektumként
Set wb = ThisWorkbook
Set pq = wb.Queries ("Ügyfélnevek")
pq.Frissítés
Íme a kód egy Excel VBA makróban:
A Power Query speciális funkciói
Az Excel Power Query számos fejlett funkciót kínál az adatelemzési folyamat egyszerűsítésére és javítására. Ha megtanulja, hogyan kell hatékonyan használni ezeket a funkciókat, adatelemzési készségeit teljesen új szintre emelheti.
A beépített adatelemző eszközök a következők:
Feltételes oszlopok összetett logika alkalmazásához az adatátalakításokhoz.
Csoportosítási és összesítő funkciók az adatok értelmes összefoglalásához.
Pivot és Unpivot műveletek az adatok átformálásához a könnyebb elemzés érdekében.
Ezen szolgáltatások közül sok megtalálható a Power Query szerkesztő Átalakítás lapján.
Ezenkívül a Power Queryt a Business Intelligence-hez csatlakoztathatja a fejlettebb adatelemzési és megjelenítési lehetőségek érdekében.
Együttműködés és megosztás
Az adatkapcsolatok, lekérdezések és átalakítások megosztása a csapattagokkal elengedhetetlen a hatékony együttműködéshez.
Megoszthat egy munkafüzetet lekérdezésekkel, ha elmenti azt hálózati meghajtóra, SharePointba vagy más megosztott tárolóhelyre.
Az M-kódot egyetlen lekérdezés mögé is exportálhatja az alábbi lépésekkel:
Szerkessze a lekérdezést a Power Query szerkesztőben.
Kattintson a „Speciális szerkesztő” elemre a Kezdőlap lapon.
Másolja a teljes M-kódot a Speciális szerkesztő ablakba.
Nyisson meg egy szövegszerkesztőt (pl. Jegyzettömböt), és illessze be a kimásolt M kódot a szövegszerkesztőbe.
Mentse el a szöveges fájlt és küldje el kollégájának.
Zárjuk le
Számos funkciójával és felhasználóbarát felületével a Power Query alapvető eszköz a külső adatokkal hatékonyan dolgozni vágyó Excel-felhasználók számára.
Ez az értékes funkció segít leegyszerűsíteni a jelentések készítésének folyamatát, az adattisztítást és az adattáblázatok összetett elemzését. Használata
A Power Query végül időt takarít meg, és növeli a hatékonyságot az Excel-fájlokkal való munka során.
Ismerje meg, hogyan töltheti le és telepítheti a DAX Studio és a Tabular Editor 3 alkalmazást, és hogyan konfigurálhatja őket a LuckyTemplates és az Excel programban való használatra.
Ez a blog tartalmazza a Shape Map vizualizációt a LuckyTemplates térbeli elemzéséhez. Megmutatom, hogyan használhatja hatékonyan ezt a vizualizációt annak funkcióival és elemeivel.
Ebben az oktatóanyagban egy egyedülálló ötletet mutatok be a pénzügyi jelentésekkel kapcsolatban, amely az eredmények hozzárendelését jelenti a LuckyTemplates-en belüli táblázatsablonok előre meghatározásához.
Hozzon létre DAX-mértékeket a LuckyTemplates alkalmazásban meglévő mértékek vagy képletek használatával. Ezt nevezem mértékelágazási technikának.
Ebben a blogban fedezze fel a LuckyTemplates adatkészletet, a leghatékonyabb függvényhívást, amely M- és DAX-függvények ezreit juttatja a keze ügyébe.
A mai oktatóanyagban megosztok néhány adatmodellezési technikát a DAX-mérések jobb rendszerezéséhez a hatékonyabb munkafolyamat érdekében.
A LuckyTemplates egy nagyszerű eszköz a pénzügyi jelentésekhez. Itt található egy oktatóanyag, amely bemutatja, hogyan hozhat létre személyre szabott táblázatokat a LuckyTemplates pénzügyi irányítópultjához.
Ez az oktatóanyag megvitatja a Power Query nyelvi folyamatot, és azt, hogyan segíthet zökkenőmentes és hatékony adatjelentések létrehozásában.
Az egyik kedvenc technikámról fogok beszélni a LuckyTemplates egyéni ikonok körül, amely dinamikusan használja az egyéni ikonokat a LuckyTemplates vizualizációkban.
Ebben a blogban megmutatom, hogyan hozhat létre LuckyTemplates táblázatokat olyan képlet segítségével, amely egyesíti az UNION és a ROW függvényt.
