Bizonyára ismeri a paraméteres lekérdezéseket az SQL-ben vagy a Microsoft Queryben való használatukkal. A Power Query paraméterek azonban fő eltéréseket mutatnak:
-
A paraméterek bármely lekérdezési lépésben használhatók. Amellett, hogy adatszűrőként működik, a paraméterek olyan dolgok megadására is használhatók, mint a fájl elérési útja vagy a kiszolgáló neve.
-
A paraméterek nem kérnek bemenetet. Ehelyett gyorsan módosíthatja az értéküket a Power Query használatával. Az Excelben akár cellákból is tárolhatja és lekérheti az értékeket.
-
A paraméterek egy egyszerű paraméteres lekérdezésben vannak mentve, de elkülönülnek a használt adat-lekérdezésektől. A létrehozás után szükség szerint hozzáadhat egy paramétert a lekérdezésekhez.
Megjegyzés Ha másik módszert szeretne a paraméteres lekérdezések létrehozására, olvassa el a Paraméteres lekérdezés létrehozása a Microsoft Queryben című témakört.
A paraméterrel automatikusan módosíthatja egy lekérdezés értékét, és elkerülheti, hogy minden alkalommal szerkessze a lekérdezést az érték módosításához. Csak módosítania kell a paraméter értékét. Miután létrehozott egy paramétert, a rendszer egy speciális paraméteres lekérdezésbe menti, amelyet kényelmesen módosíthat közvetlenül az Excelből.
-
Válassza az Adatok > Adatok lekérése > Egyéb források > Indítás Power Query-szerkesztő lehetőséget.
-
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 lehetőséget.
-
Igény szerint állítsa be a következőket:
Név
Ennek tükröznie kell a paraméter függvényét, de a lehető legrövidebbnek kell lennie.
Ismertetés
Ez tartalmazhat minden olyan részletet, amely segíthet a felhasználóknak a paraméter helyes használatában.
Kötelező
Tegye a következők valamelyikét:
Bármely érték A paraméteres lekérdezésben bármilyen adattípusú értéket megadhat. Értékek listája Az értékeket egy adott listára korlátozhatja, ha beírja őket a kis rácsba. Emellett ki kell jelölnie egy alapértelmezett értéket és egy aktuális értéket is. Lekérdezés Jelöljön ki egy lista lekérdezést, amely a Lista strukturált oszlophoz hasonlít, vesszővel elválasztva és kapcsos zárójelek között. A Problémák állapotmező például három értékkel rendelkezhet: {"Új", "Folyamatban", "Lezárt"}. A lista lekérdezését előbb létre kell hoznia a Speciális szerkesztő megnyitásával (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 a lekérdezéslista formátumában, majd válassza a Kész lehetőséget. Miután befejezte a paraméter létrehozását, a lista lekérdezése megjelenik a paraméterértékekben.Típus
Ez a paraméter adattípusát határozza meg.
Javasolt értékek
Ha szükséges, adjon hozzá egy értéklistát, vagy adjon meg egy lekérdezést a bemenetre vonatkozó javaslatok megadásához.
Alapértelmezett érték
Ez csak akkor jelenik meg, ha a Javasolt értékekértéklistára van állítva, és megadja, hogy melyik listaelem az alapértelmezett. Ebben az esetben az alapértelmezett értéket kell választania.
Aktuális érték
Attól függően, hogy hol használja a paramétert, ha ez üres, előfordulhat, hogy a lekérdezés nem ad vissza eredményt. Ha a Kötelező beállítás van kiválasztva, az Aktuális érték nem lehet üres.
-
A paraméter létrehozásához válassza az OK gombot.
Az alábbiakban bemutatjuk, hogyan kezelheti az adatforrások helyének módosításait, és hogyan előzheti meg a frissítési hibákat. Ha például hasonló sémát és adatforrást feltételez, hozzon létre egy paramétert az adatforrás egyszerű módosításához és az adatfrissítési hibák megelőzéséhez. Előfordulhat, hogy a kiszolgáló, az adatbázis, a mappa, a fájlnév vagy a hely megváltozik. Előfordulhat, hogy egy adatbázis-kezelő időnként felcserél egy kiszolgálót, a CSV-fájlok havi csökkenése egy másik mappába kerül, vagy egyszerűen kell váltania a fejlesztési/tesztelési/éles környezet között.
1. lépés: Paraméteres lekérdezés létrehozása
A következő példában több CSV-fájlt importál a mappaimportálási művelettel ( Adatok kiválasztása> Adatok lekérése > fájlokból > mappából) a C:\DataFilesCSV1 mappából. Néha azonban egy másik mappát használnak a fájlok elvetésére szolgáló helyként( C:\DataFilesCSV2). A lekérdezések paraméterét a másik mappa helyettesítő értékeként használhatja.
-
Válassza a Kezdőlap > Paraméterek kezelése > Új paraméter lehetőséget.
-
Adja meg a következő adatokat a Paraméter kezelése párbeszédpanelen:
Név
CSVFileDrop
Ismertetés
Alternatív fájlelhelyés helye
Kötelező
Igen
Típus
Szöveg
Javasolt értékek
Bármely érték
Aktuális érték
C:\DataFilesCSV1
-
Válassza az OK gombot.
2. lépés: A paraméter hozzáadása az adat lekérdezéshez
-
A mappanév paraméterként való beállításához a Lekérdezés beállításai területen válassza a Lekérdezési lépések területen a Forrás, majd a Beállítások szerkesztése lehetőséget.
-
Győződjön meg arról, hogy a Fájl elérési útja beállítás Paraméter értékre van állítva, majd válassza ki az imént létrehozott paramétert a legördülő listából.
-
Válassza az OK gombot.
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 Adatok > Kapcsolatok & Lekérdezések > Lekérdezések lapot, kattintson a jobb gombbal a paraméteres lekérdezésre, majd válassza a Szerkesztés lehetőséget.
-
Írja be az új helyet az Aktuális érték mezőbe, 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 adjon hozzá új adatokat az adatforráshoz, majd frissítse az adat lekérdezést a frissített paraméterrel ( Adatok kiválasztása> Az összes frissítése).
Előfordulhat, hogy egyszerűen szeretné módosítani a lekérdezés szűrőjét, hogy különböző eredményeket kapjon anélkül, hogy szerkeszti a lekérdezést, vagy némileg eltérő másolatokat készít ugyanabból a lekérdezésből. Ebben a példában egy dátumot módosítunk, hogy kényelmesen módosítsunk egy adatszűrőt.
-
Lekérdezés megnyitásához keresse meg a Power Query-szerkesztő korábban betöltöttet, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
Az adatok szűréséhez válassza a szűrőnyilat bármelyik oszlopfejlécben, 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 található gombot, majd hajtsa végre az alábbi műveletek egyikét:
-
Meglévő paraméter használatához válassza a Paraméter lehetőséget, majd válassza ki a kívánt paramétert a jobb oldalon megjelenő listából.
-
Új paraméter használatához válassza az Új paraméter lehetőséget, majd hozzon létre egy paramétert.
-
-
Írja be az új dátumot az Aktuális érték mezőbe, majd válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
-
Az eredmények megerősítéséhez adjon hozzá új adatokat az adatforráshoz, majd frissítse az adat lekérdezést a frissített paraméterrel ( Adatok kiválasztása> Az összes frissítése). Módosítsa például a szűrő értékét egy másik dátumra az új eredmények megtekintéséhez.
-
Írja be az új dátumot az Aktuális érték mezőbe.
-
Válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
-
Az eredmények megerősítéséhez adjon hozzá új adatokat az adatforráshoz, majd frissítse az adat lekérdezést a frissített paraméterrel ( Adatok kiválasztása> Az összes frissítése).
Ebben a példában a lekérdezési paraméterben lévő érték a munkafüzet egy cellájából lesz beolvasva. Nem kell módosítania a paraméteres lekérdezést, csak frissítenie kell a cellaértéket. Például szűrni szeretne egy oszlopot az első betű alapján, de egyszerűen módosíthatja az értéket bármely betűre A-ról Z-re.
-
A szűrni kívánt lekérdezést tartalmazó munkafüzet munkalapján hozzon létre egy Excel-táblázatot két cellával: egy fejléccel és egy értékkel.
MyFilter
G
-
Jelöljön ki egy cellát az Excel-táblázatban, majd válassza az Adatok > 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 kifejezőbb névre, például FilterCellValue értékre.
-
Ha nem magát a táblát, hanem a táblát szeretné átadni, kattintson a jobb gombbal az értékre az Adatelőnézetben, majd válassza a Lehatolás lehetőséget.
Figyelje meg, hogy a képlet = #"Changed Type"{0}[MyFilter]
Ha az Excel-táblázatot szűrőként használja a 10. lépésben, Power Query szűrőfeltételként hivatkozik a Tábla értékre. Az Excel-táblázatra mutató közvetlen hivatkozás hibát okozhat.
-
Válassza a Kezdőlap > Bezárás & Betöltés > Bezárás & Betöltés célként lehetőséget. Most már rendelkezik egy "FilterCellValue" nevű lekérdezési paraméterrel, amelyet a 12. lépésben használ.
-
Az Adatok importálása párbeszédpanelen válassza a Csak kapcsolat létrehozása lehetőséget, majd kattintson az OK gombra.
-
Nyissa meg a szűrni kívánt lekérdezést a filterCellValue táblában lévő, korábban a Power Query-szerkesztő betöltött értékkel. Ehhez jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
Az adatok szűréséhez válassza a szűrőnyilat bármelyik oszlopfejlécben, majd válasszon egy szűrőparancsot, például a Szövegszűrők > Kezdődjön. Megjelenik a Sorok szűrése párbeszédpanel.
-
Írjon be bármilyen értéket az Érték mezőbe, például "G", majd kattintson az OK gombra. Ebben az esetben az érték a FilterCellValue tábla értékének ideiglenes helyőrzője, amelyet a következő lépésben ad meg.
-
A teljes képlet megjelenítéséhez kattintson a szerkesztőléc jobb oldalán található nyílra. Íme egy példa egy képlet szűrőfeltételére:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G")) -
Válassza 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ábla első néhány betűét, majd válassza ki a megjelenő listából.
-
Válassza a Kezdőlap > Bezárás > & Betöltés lehetőséget.
Eredmény
A lekérdezés most a létrehozott Excel-táblázatban lévő értéket használja a lekérdezés eredményeinek szűréséhez. Új érték használatához szerkessze az eredeti Excel-táblázatban lévő cellatartalmat az 1. lépésben, módosítsa a "G" értéket "V" értékre, majd frissítse a lekérdezést.
Szabályozhatja, hogy a paraméteres lekérdezések engedélyezettek-e vagy sem.
-
A Power Query-szerkesztő válassza a Fájl > Beállítások és beállítások > Lekérdezési beállítások > Power Query-szerkesztő lehetőséget.
-
A bal oldali panel GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.
-
A jobb oldali panel Paraméterek területén jelölje be a Paraméterezés engedélyezése az adatforrásokban és az átalakítási párbeszédpaneleken jelölőnégyzetet, vagy törölje a jelölést.