Ajánlott eljárások a Power Query használatakor
Ez a cikk néhány tippet és trükköt tartalmaz, hogy a lehető legtöbbet hozhassa ki az adatokból a Power Queryben.
A Power Query számos adatösszekötőt kínál. Ezek az összekötők az olyan adatforrásoktól, mint a TXT, a CSV és az Excel fájloktól az olyan adatbázisokig terjednek, mint a Microsoft SQL Server, valamint a népszerű SaaS-szolgáltatások, például a Microsoft Dynamics 365 és a Salesforce. Ha nem látja az adatforrást az Adatok lekérése ablakban, az ODBC- vagy OLEDB-összekötő használatával mindig csatlakozhat az adatforráshoz.
A feladathoz legjobb összekötő használata biztosítja a legjobb élményt és teljesítményt. Ha például az SQL Server-adatbázishoz való csatlakozáskor az SQL Server-összekötő helyett az SQL Server-összekötőt használja, nem csupán sokkal jobb Adatbeolvasási élményt nyújt, hanem az SQL Server-összekötő olyan funkciókat is kínál, amelyek javíthatják a felhasználói élményt és a teljesítményt, például a lekérdezések összecsukását. A lekérdezések összecsukásával kapcsolatos további információkért tekintse meg a lekérdezések kiértékelésének és a lekérdezés összecsukásának áttekintését a Power Queryben.
Minden adatösszekötő az adatok lekérése című témakörben ismertetett standard felhasználói élményt követi. Ennek a szabványosított felületnek van egy Data Preview nevű szakasza. Ebben a szakaszban egy felhasználóbarát ablak áll rendelkezésre az adatforrásból lekérni kívánt adatok kiválasztásához, ha az összekötő engedélyezi, és az adatok egyszerű előnézete. Akár több adatkészletet is kiválaszthat az adatforrásból a Kezelő ablakban, ahogyan az alábbi képen látható.
Megjegyzés
A Power Queryben elérhető összekötők teljes listájának megtekintéséhez lépjen a Power Query Csatlakozás orjaihoz.
Mindig javasoljuk, hogy a lekérdezés korai szakaszában vagy a lehető leghamarabb szűrje az adatokat. Egyes összekötők a lekérdezések összecsukásával fogják kihasználni a szűrők előnyeit a lekérdezések kiértékelésének és a lekérdezések összecsukásának áttekintésében leírtak szerint a Power Queryben. Ajánlott eljárás az eset szempontjából nem releváns adatok kiszűrésére is. Így jobban összpontosíthat a feladatra, ha csak az adatok előnézeti szakaszában releváns adatokat jelenít meg.
Az oszlopban található értékek külön listáját megjelenítő automatikus szűrőmenüvel kiválaszthatja a megtartani vagy kiszűrni kívánt értékeket. A keresősáv segítségével is megtalálhatja az oszlop értékeit.
Az olyan típusspecifikus szűrőket is kihasználhatja, mint például az előző dátum, dátum vagy akár dátum időzóna oszlopban.
Ezek a típusspecifikus szűrők segítenek létrehozni egy dinamikus szűrőt, amely mindig lekéri az előző x másodpercben, percben, órában, napban, hetekben, hónapokban, negyedévekben vagy években lévő adatokat az alábbi képen látható módon.
Megjegyzés
Ha többet szeretne megtudni az adatok oszlopból származó értékek alapján történő szűréséről, lépjen az Értékek szerinti szűrés elemre.
Bizonyos műveletekhez a teljes adatforrás beolvasására van szükség az eredmények visszaadásához, így a Power Query-szerkesztő lassan lesz megtekinthető. Ha például rendezést végez, lehetséges, hogy az első néhány sor a forrásadatok végén található. Az eredmények visszaadásához tehát a rendezési műveletnek először be kell olvasnia az összes sort.
Más műveleteknek (például szűrőknek) nem kell beolvasnia az összes adatot az eredmények visszaadása előtt. Ehelyett "streamelés" módon működnek az adatokon. Az adatok "streamelnek" a folyamat során, és az eredmények is vissza lesznek adva. A Power Query-szerkesztő az ilyen műveleteknek csak elegendő forrásadatot kell olvasniuk az előnézet feltöltéséhez.
Ha lehetséges, először végezze el az ilyen streamelési műveleteket, és végezze el a drágább műveleteket. Ez segít minimalizálni az előnézet megjelenítésére váró időt, amikor új lépést ad hozzá a lekérdezéshez.
Ha lassú az új lépések hozzáadása a lekérdezéshez a Power Query-szerkesztő, érdemes először elvégezni az "Első sorok megtartása" műveletet, és korlátozni a sorok számát, amelyeken dolgozik. Ezután, miután hozzáadta az összes szükséges lépést, távolítsa el az "Első sorok megtartása" lépést.
A Power Query egyes funkciói környezetfüggők a kijelölt oszlop adattípusához. Ha például dátumoszlopot választ ki, az Oszlop hozzáadása menü Dátum és idő oszlop csoportjában elérhető lehetőségek elérhetők lesznek. Ha azonban az oszlop nem rendelkezik adattípus-beállítással, akkor ezek a beállítások szürkítve jelennek meg.
Hasonló helyzet áll fenn a típusspecifikus szűrők esetében, mivel bizonyos adattípusokra jellemzőek. Ha az oszlopban nincs megadva a megfelelő adattípus, ezek a típusspecifikus szűrők nem lesznek elérhetők.
Fontos, hogy mindig a megfelelő adattípusokkal dolgozzon az oszlopokhoz. Strukturált adatforrások, például adatbázisok használata esetén az adattípus-információk az adatbázisban található táblaséma alapján lesznek beállítva. Strukturálatlan adatforrások, például TXT- és CSV-fájlok esetén azonban fontos, hogy az adatforrásból érkező oszlopokhoz megfelelő adattípusokat állítson be. A Power Query alapértelmezés szerint automatikus adattípus-észlelést biztosít strukturálatlan adatforrásokhoz. Erről a funkcióról és arról, hogyan segíthet az adattípusokban.
Megjegyzés
Az adattípusok fontosságáról és azok használatáról további információt az Adattípusok című témakörben talál.
Mielőtt megkezdené az adatok előkészítését és az új átalakítási lépések hozzáadását, javasoljuk, hogy engedélyezze a Power Query adatprofilozási eszközeit az adatokkal kapcsolatos információk egyszerű felderítéséhez.
Ezek az adatprofilozási eszközök segítenek jobban megérteni az adatokat. Az eszközök kis méretű vizualizációkat biztosítanak, amelyek oszloponként jelenítik meg az információkat, például:
- Oszlopminőség – Egy kis sávdiagramot és három mutatót biztosít, amely azt mutatja, hogy az oszlopban hány érték tartozik érvényes, hiba vagy üres értékek kategóriáiba.
- Oszlopeloszlás – Vizualizációkat biztosít az oszlopok neve alatt, amelyek az egyes oszlopok értékeinek gyakoriságát és eloszlását mutatják be.
- Oszlopprofil – Részletesebb képet nyújt az oszlopról és a hozzá tartozó statisztikákról.
Ezeket a funkciókat is használhatja, amelyek segítenek az adatok előkészítésében.
Megjegyzés
Az adatprofilozási eszközökkel kapcsolatos további információkért lépjen az Adatprofilozási eszközökre.
Javasoljuk, hogy dokumentálja a lekérdezéseket úgy, hogy átnevezi vagy leírást ad hozzá a lépésekhez, lekérdezésekhez vagy csoportokhoz, ahogy ön látja.
Bár a Power Query automatikusan létrehoz egy lépésnevet az alkalmazott lépések ablaktábláján, átnevezheti a lépéseket, vagy bármelyikhez hozzáadhat leírást.
Megjegyzés
Ha többet szeretne megtudni az alkalmazott lépések panelen található összes elérhető funkcióról és összetevőről, lépjen az Alkalmazott lépések listájára.
Teljes mértékben létrehozhat egyetlen lekérdezést, amely tartalmazza az összes szükséges átalakítást és számítást. Ha azonban a lekérdezés sok lépést tartalmaz, akkor érdemes lehet több lekérdezésre felosztani a lekérdezést, ahol az egyik lekérdezés a következőre hivatkozik. Ennek a megközelítésnek a célja az átalakítás fázisainak egyszerűsítése és leválasztása kisebb részekre, hogy könnyebben megérthetők legyenek.
Tegyük fel például, hogy van egy lekérdezése az alábbi képen látható kilenc lépéssel.
Ezt a lekérdezést két részre oszthatja az Egyesítés az Árak táblával lépésnél. Így könnyebben megértheti az értékesítési lekérdezésre az egyesítés előtt alkalmazott lépéseket. A művelet végrehajtásához kattintson a jobb gombbal az Egyesítés az Árak táblával lépésre, és válassza az Előző kinyerése lehetőséget.
Ekkor a rendszer egy párbeszédpanelen kéri, hogy adjon nevet az új lekérdezésnek. Ez gyakorlatilag két lekérdezésre osztja fel a lekérdezést. Egy lekérdezés az összes lekérdezést az egyesítés előtt fogja elvégezni. A másik lekérdezésnek lesz egy kezdeti lépése, amely az új lekérdezésre hivatkozik, és az eredeti lekérdezés többi lépését lefelé, az Egyesítés az Árak táblával lépésről .
A megfelelőnek látja a lekérdezés-hivatkozás használatát is. Érdemes azonban olyan szinten tartani a lekérdezéseket, amely első pillantásra nem tűnik ijesztőnek ennyi lépéssel.
Megjegyzés
Ha többet szeretne megtudni a lekérdezés-hivatkozásról, olvassa el a Lekérdezések panel ismertetése című témakört.
A munka rendszerezésének egyik nagyszerű módja, ha kihasználja a csoportok használatát a lekérdezések panelen.
A csoportok egyetlen célja, hogy a lekérdezések mappájaként szolgálva segítsék a munkáját rendezetten tartani. Csoportokon belül is létrehozhat csoportokat, ha valaha is szükség van rá. A lekérdezések csoportok közötti áthelyezése ugyanolyan egyszerű, mint az húzás.
Próbáljon meg olyan értelmes nevet adni a csoportoknak, amely értelmes az Ön és az esete számára.
Megjegyzés
A lekérdezések panelen található összes elérhető funkcióról és összetevőről a Lekérdezések panel ismertetése című témakörben olvashat bővebben.
Elsődleges fontosságú, hogy olyan lekérdezést hozzon létre, amely a jövőbeli frissítés során nem okoz problémát. A Power Query számos funkcióval teszi rugalmassá a lekérdezést a változásokhoz, és akkor is frissíthet, ha az adatforrás egyes összetevői módosulnak.
Ajánlott meghatározni a lekérdezés hatókörét, hogy mit kell tennie, és mit kell figyelembe vennie a struktúra, az elrendezés, az oszlopnevek, az adattípusok és a hatókör szempontjából relevánsnak ítélt egyéb összetevők tekintetében.
Néhány példa az átalakításokra, amelyek segíthetnek abban, hogy a lekérdezés rugalmasan alkalmazkodjon a változásokhoz:
Ha a lekérdezés dinamikus számú adatsort tartalmaz, de rögzített számú olyan sor van, amely az eltávolítandó láblécként szolgál, használhatja az Alsó sorok eltávolítása funkciót.
Megjegyzés
Ha többet szeretne megtudni az adatok sorhelyzet szerinti szűréséről, lépjen a Táblázat szűrése sorhelyzet szerint elemre.
Ha a lekérdezés dinamikus számú oszlopot tartalmaz, de csak bizonyos oszlopokat kell kijelölnie az adatkészletből, használhatja az Oszlopok kiválasztása funkciót.
Megjegyzés
Ha többet szeretne megtudni az oszlopok kiválasztásáról vagy eltávolításáról, válassza az Oszlopok kiválasztása vagy eltávolítása lehetőséget.
Ha a lekérdezés dinamikus oszlopszámmal rendelkezik, és csak az oszlopok egy részhalmazát kell feloldania, akkor csak a kijelölt oszlopok feloldása funkciót használhatja.
Megjegyzés
Ha többet szeretne megtudni az oszlopok leválasztására vonatkozó lehetőségekről, lépjen az Unpivot oszlopokra.
Ha a lekérdezés olyan lépéssel rendelkezik, amely módosítja egy oszlop adattípusát, de egyes cellák hibát eredményeznek, mivel az értékek nem felelnek meg a kívánt adattípusnak, eltávolíthatja a hibaértékeket eredményező sorokat.
Megjegyzés
A hibák kezeléséről és kezeléséről a Hibák kezelése című témakörben olvashat bővebben.
A dinamikus és rugalmas lekérdezések létrehozása ajánlott eljárás. A Power Query paraméterei segítenek dinamikusabbá és rugalmasabbá tenni a lekérdezéseket. A paraméterek segítségével egyszerűen tárolhatók és kezelhetők a különböző módokon újrafelhasználható értékek. Ezt azonban gyakrabban használják két forgatókönyvben:
Lépésargumentum – A paraméter a felhasználói felületről indított több átalakítás argumentumaként is használható.
Egyéni függvény argumentum – Létrehozhat egy új függvényt egy lekérdezésből, és az egyéni függvény argumentumaiként hivatkozhat paraméterekre.
A paraméterek létrehozásának és használatának fő előnyei a következők:
Az összes paraméter központosított nézete a Paraméterek kezelése ablakban.
A paraméter újrafelhasználhatósága több lépésben vagy lekérdezésben.
Egyszerűvé és egyszerűvé teszi az egyéni függvények létrehozását.
Az adatösszekötők egyes argumentumaiban paramétereket is használhat. Létrehozhat például egy paramétert a kiszolgáló nevéhez, amikor az SQL Server-adatbázishoz csatlakozik. Ezután használhatja ezt a paramétert az SQL Server-adatbázis párbeszédpanelén.
Ha módosítja a kiszolgáló helyét, mindössze frissítenie kell a kiszolgáló nevének paraméterét, és a lekérdezések frissülnek.
Megjegyzés
A paraméterek létrehozásáról és használatáról a Paraméterek használata című témakörben olvashat bővebben.
Ha olyan helyzetben találja magát, hogy ugyanazokat az átalakításokat kell alkalmaznia a különböző lekérdezésekre vagy értékekre, létrehozhat egy egyéni Power Query-függvényt, amely ahányszor csak szüksége van, hasznos lehet. Az egyéni Power Query-függvények a bemeneti értékek készletéből egyetlen kimeneti értékre való leképezések, és natív M-függvényekből és operátorokból jönnek létre.
Tegyük fel például, hogy több lekérdezése vagy értéke is ugyanazt az átalakítást igényli. Létrehozhat egy egyéni függvényt, amely később meghívható a választott lekérdezések vagy értékek alapján. Ez az egyéni függvény időt takarít meg, és segít az átalakítások központi helyen történő kezelésében, amelyet bármikor módosíthat.
A Power Query egyéni függvényei meglévő lekérdezésekből és paraméterekből hozhatók létre. Képzeljen el például egy olyan lekérdezést, amely több kóddal rendelkezik szöveges sztringként, és olyan függvényt szeretne létrehozni, amely dekódolja ezeket az értékeket.
Először egy olyan paraméterrel kell rendelkeznie, amely egy példaként szolgáló értékkel rendelkezik.
Ebből a paraméterből létrehoz egy új lekérdezést, amelyben alkalmazza a szükséges átalakításokat. Ebben az esetben a PTY-CM1090-LAX kódot több összetevőre szeretné felosztani:
- Origin = PTY
- Cél = LAX
- Légitársaság = CM
- FlightID = 1090
Ezt követően a lekérdezést függvénysé alakíthatja úgy, hogy a jobb gombbal kattint a lekérdezésre, és kiválasztja a Függvény létrehozása lehetőséget. Végül meghívhatja az egyéni függvényt bármelyik lekérdezésbe vagy értékbe, ahogyan az az alábbi képen látható.
Néhány további átalakítás után láthatja, hogy elérte a kívánt kimenetet, és kihasználta az ilyen átalakítás logikáját egy egyéni függvényből.
Megjegyzés
Az Egyéni függvények című cikkből megtudhatja, hogyan hozhat létre és használhat egyéni függvényeket a Power Queryben.