Bizonyára jól ismeri a paraméteres lekérdezések használatát az SQL és a Microsoft Query esetén. A Power Query paraméterei azonban lényegesebb különbségeket mutatnak:
- A paraméterek a lekérdezés bármely lépésében használhatók. Az adatszűrőn kívül a paraméterek többek között a fájl elérési útjának vagy a kiszolgáló nevének megadására is használhatók.
- A paraméterek nem kérik a bevitelt. Ehelyett az értéküket gyorsan módosíthatja a Power Query használatával. Akár tárolhatja és lekérheti cellák értékeit az Excelben.
- A paramétereket egy egyszerű paraméteres lekérdezés menti, de elkülönülnek az adatlekérdezésektől. Miután létrehozta, szükség szerint hozzáadhat egy paramétert a lekérdezésekhez.
Megjegyzés Ha a paraméteres lekérdezések létrehozásának másféle módját szeretné használni, olvassa el a Paraméteres lekérdezés létrehozása a Microsoft Queryben című témakört.
Paraméter létrehozása
Paraméterek használatával automatikusan módosíthatja a lekérdezés egy értékét, és elkerülheti, hogy minden alkalommal szerkessze a lekérdezést. Csak módosítsa a paraméter értékét. A létrehozott paramétereket a rendszer egy speciális paraméteres lekérdezésben menti, amelyet közvetlenül az Excelből kényelmesen módosíthat.
Adatok> kijelölése Adatok >lekéréseMás források>Indítsa el a Power Query-szerkesztőt.
A Power Query-szerkesztő válassza a Kezdőlap>Paraméterek > kezelése Új paraméterek lehetőséget.
A Paraméter kezelése párbeszédpanelen válassza az Új elemet.
Szükség szerint állítsa be az alábbiakat:
Név Ennek tükröznie kell a paraméter funkcióját, de a lehető röviden kell tartania. Leírás: Ez minden olyan részletet tartalmazhat, amely segít a paraméter helyes használatában. Kötelező Tegye a következők valamelyikét:
Bármilyen érték A paraméteres lekérdezésben bármilyen adattípusú értéket megadhat.
Értéklista Az értékeket egy adott listára korlátozhatja, ha beírja azokat a kis rácsba. Egy alapértelmezett értéket és alább egy aktuális értéket is ki kell választania.
Lekérdezés Válasszon ki egy listalekérdezést, amely egy Lista strukturált oszlopra hasonlít, vesszőkkel elválasztva, kapcsos zárójelek között van.
Egy Problémák állapota mező például három értékkel rendelkezhet: {"Új", "Folyamatban", "Lezárva"}. A listalekérdezést előzetesen létre kell hoznia. Ehhez nyissa meg a Speciális szerkesztőt (válassza a Kezdőlap>Speciális szerkesztő lehetőséget), távolítsa el a kódsablont, adja meg az értékek listáját lekérdezéslista formátumban, majd válassza a Kész lehetőséget.
Amikor befejezte a paraméter létrehozását, a listalekérdezés megjelenik a paraméterértékek között.Típus: Ez adja meg a paraméter adattípusát. Javasolt értékek Ha szeretné, felvehet egy értéklistát, vagy egy lekérdezést megadva javaslatokat jeleníthet meg bevitelre. Alapértelmezett érték Ez csak akkor jelenik meg, ha a Javasolt értékek beállítás értéke Értékek listája, és megadja, hogy melyik listaelem az alapértelmezett. Ebben az esetben ki kell választania egy alapértelmezett beállítást. Aktuális érték Ha üres, attól függően, hogy hol használja a paramétert, előfordulhat, hogy a lekérdezés nem ad vissza eredményt. Ha a Kötelező beállítás van kiválasztva, akkor az Aktuális érték nem lehet üres. A paraméter létrehozásához válassza az OK gombot.
Adatforrás módosítása paraméter használatával
Így kezelheti az adatforrások helyének változásait, és megelőzheti a frissítési hibákat. Ha például feltételez egy hasonló sémát és adatforrást, hozzon létre egy paramétert, amellyel egyszerűen módosítható az adatforrás, és megelőzhetők a frissítési hibák. A kiszolgáló, az adatbázis, a mappa, a fájlnév vagy a hely időnként megváltozik. Előfordulhat például, hogy egy adatbázis-kezelő időnként kicserél egy kiszolgálót, havonta egy CSV-fájl egy másik mappába kerül, vagy egyszerűen váltania kell egy fejlesztési, tesztelési vagy üzemi környezet között.
1. lépés: Paraméteres lekérdezés létrehozása
Az alábbi példában számos CSV-fájl található, amelyeket az importálási mappa művelettel (Select Data>Get Data From>FilesFrom> Folder) importál a C:\DataFilesCSV1 mappából. Néha azonban egy másik mappát (C:\DataFilesCSV2) is használhatnak a fájlok eldobásához. A lekérdezésekben paraméterekkel helyettesítheti a másik mappát is.
Válassza a Kezdőlap>Paraméterek> kezeléselehetőséget, az új paramétert.
Adja meg a következő adatokat a Paraméter kezelése párbeszédpanelen:
Név CSVFileDrop Leírás: Alternatív fájllerakási hely Kötelező Igen Típus: Text (Szöveg) Javasolt értékek Bármilyen érték Aktuális érték C:\DataFilesCSV1 Kattintson az OK gombra.
2. lépés: A paraméter hozzáadása az adatlekérdezéshez
- Ha paraméterként szeretné beállítani a mappa nevét, a Lekérdezés beállításai párbeszédpanel Lekérdezési lépések területén válassza a Forrás, majd a Beállítások szerkesztése lehetőséget.
- Győződjön meg arról, hogy az Elérési út beállítás értéke Paraméter, majd válassza ki az imént létrehozott paramétert a legördülő listából.
- Kattintson az OK gombra.
3. lépés: A paraméter értékének frissítése
A mappa helye megváltozott, így most egyszerűen frissítheti a paraméteres lekérdezést.
- Válassza az Adatkapcsolatok>& a Lekérdezések> lapot, kattintson a jobb gombbal a paraméteres lekérdezésre, majd válassza a Szerkesztés parancsot.
- Adja meg az új helyet az Aktuális érték mezőben, például C:\DataFilesCSV2.
- Válassza a Kezdőlap>, Bezárás & Betöltés lehetőséget.
- Az eredmények megerősítéséhez vegyen fel új adatokat az adatforrásba, majd frissítse az adatlekérdezést a frissített paraméterrel (Az összes frissítése> kijelölése).
Adatok szűrése paraméter használatával
Időnként előfordulhat, hogy könnyen módosítani szeretné egy lekérdezés szűrőjét úgy, hogy különböző eredményeket kapjon anélkül, hogy szerkesztenie kellene a lekérdezést, vagy ugyanabból a lekérdezésről némileg eltérő másolatokat kellene készítenie. Ebben a példában egy dátum módosítását szemléltetjük, hogy kényelmesen lehessen módosítani egy adatszűrőt.
Lekérdezés megnyitásához keresse meg a korábban a Power Query-szerkesztőből betöltött egyet, jelöljön ki egy cellát az adatok között, majd válassza a Lekérdezés>szerkesztése lehetőséget. További információt a Lekérdezés létrehozása, betöltése és szerkesztése az Excelben című témakörben talál.
Az adatok szűréséhez válassza bármelyik oszlopfejlécben a szűrőnyilat, majd válasszon egy szűrőparancsot, például Dátum/Idő szűrők>utána. Megjelenik a Sorok szűrése párbeszédpanel.
Válassza az Érték mezőtől balra lévő gombot, majd tegye a következők valamelyikét:
- Ha egy meglévő paramétert szeretne használni, válassza a Paraméter lehetőséget, majd a jobb oldali listából válassza ki a kívánt paramétert.
- Új paraméter használatához válassza az Új paraméter lehetőséget, majd hozzon létre egy paramétert.
Adja meg az új dátumot az Aktuális érték mezőben, majd válassza a Kezdőlap>Bezárás & Betöltés lehetőséget.
Az eredmények megerősítéséhez vegyen fel új adatokat az adatforrásba, majd frissítse az adatlekérdezést a frissített paraméterrel (Az összes frissítése> kijelölése). Például új eredmények megjelenítéséhez módosítsa a szűrő értékét egy másik dátumra.
Adja meg az új dátumot az Aktuális érték mezőben.
Válassza a Kezdőlap>, Bezárás & Betöltés lehetőséget.
Az eredmények megerősítéséhez vegyen fel új adatokat az adatforrásba, majd frissítse az adatlekérdezést a frissített paraméterrel (Az összes frissítése> kijelölése).
Adatok szűrése cellaértékkel
Ebben a példában a lekérdezési paraméter értékét a munkafüzet egyik cellájából olvassuk be. Nem kell módosítania a paraméteres lekérdezést, csak frissíteni kell a cella értékét. Előfordulhat például, hogy első betű szerint szeretne szűrni egy oszlopot, de az értéket könnyen módosíthatja A-tól Z-ig bármilyenre.
A szűrni kívánt lekérdezést tartalmazó munkafüzet azon munkalapján, amelyhez a szűrni kívánt lekérdezés be van töltve, hozzon létre egy két cellát (egy fejlécet és egy értéket) tartalmazó Excel-táblázatot.
SajátSzűrő Cs Jelöljön ki egy cellát az Excel-táblázatban, majd válassza az Adatok>lekérése>táblázatból/tartományból lehetőséget. Megjelenik a Power Query-szerkesztő.
A jobb oldali Lekérdezés beállításai panel Név mezőjében módosítsa a lekérdezés nevét, például SzűrőCellaÉrtéke.
Ha nem magát a táblázatot, hanem a táblázatban található értéket szeretné átadni, kattintson a jobb gombbal az értékre az Adatvillámnézetben, majd válassza a Leásás parancsot.
Figyelje meg, hogy a képlet a következőre módosult= #"Changed Type"{0}[MyFilter]
Amikor a 10. lépésben Excel-táblázatot használ szűrőként, a Power Query a tábla értékére hivatkozik szűrési feltételként. Az Excel-táblázatra mutató közvetlen hivatkozás hibát okozna.Válassza a Kezdőlap>, Bezárás & Betöltés>,Bezárás & Betöltés lehetőséget. Létrejött egy, a 12. lépésben használt "FilterCellValue" lekérdezési paraméter.
Az Adatimportálás párbeszédpanelen válassza a Csak kapcsolat létrehozása lehetőséget, majd az OK gombot.
Nyissa meg a szűrni kívánt lekérdezést a korábban a Power Query-szerkesztőből betöltött FilterCellValue tábla értékével: jelöljön ki egy cellát az adatok között, majd válassza a Lekérdezés>szerkesztése lehetőséget. További információt a Lekérdezés létrehozása, betöltése és szerkesztése az Excelben című témakörben talál.
Az adatok szűréséhez válassza bármelyik oszlopfejlécben a szűrőnyilat, majd válasszon egy szűrőparancsot, például a "Szövegszűrők>kezdete". Megjelenik a Sorok szűrése párbeszédpanel.
Írjon be egy tetszőleges értéket az Érték mezőbe, például "G", majd válassza az OK gombot. Ebben az esetben az érték egy ideiglenes helyőrzője a következő lépésben megadott FilterCellValue táblázat értékének.
A teljes képlet megjelenítéséhez válassza a szerkesztőléc jobb oldalán található nyilat. Íme egy példa egy szűrési feltételre egy képletben:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Jelölje ki a szűrő értékét. A képletben válassza a "G" lehetőséget.
Az M Intellisense használatával írja be a létrehozott FilterCellValue táblázat első néhány betűjét, majd válassza ki a megjelenő listából.
Válassza a Kezdőlap>, Bezárás>, Bezárás & Betöltés lehetőséget.
Eredmény
A lekérdezés ezután a létrehozott Excel-táblázat értékeit használja a lekérdezés eredményeinek szűréséhez. Új érték használatához az 1. lépésben szerkessze az eredeti Excel-táblázat tartalmát, a "G" betűt "V"-re módosítsa, majd frissítse a lekérdezést.
A paraméteres lekérdezések használatának szabályozása
Megadhatja, hogy a paraméteres lekérdezések engedélyezve legyenek-e.
- A Power Query-szerkesztőben válassza a Fájlbeállítások>és a Beállítások>Lekérdezés beállításai>lehetőségetPower Query-szerkesztő.
- A bal oldali ablaktábla GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.
- A jobb oldali ablaktábla Paraméterek területén jelölje be a Paraméterezés engedélyezése mindig az adatforrás és az átalakítás párbeszédpanelen jelölőnégyzetet, vagy törölje belőle a jelet.