Egyéni függvények használata

Ha olyan helyzetben találja magát, hogy ugyanazokat az átalakításokat kell alkalmaznia különböző lekérdezésekre vagy értékekre, hasznos lehet létrehozni egy egyéni Power Query-függvényt, amelyet annyiszor használhat, ahányszor csak szükséges. 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.

A Power Query felhasználói felülete olyan funkciókat kínál, amelyekkel felgyorsíthatja, egyszerűsítheti és javíthatja az egyéni függvények létrehozásának és kezelésének folyamatát, vagy manuálisan is létrehozhat saját, egyéni Power Query függvényt a Power Query M képletnyelv használatával.

Először bemutatjuk az egyéni függvények felhasználói felületi kóddal való létrehozásánakalapvető lépéseit, majd a felület használatával az összetett műveletek újrafelhasználható függvénysé alakítását.

Egyéni függvény létrehozása kódból a felhasználói felületen

1. Az összekötő eszközt használva csatlakozhat az adataihoz, ahol tárolva vannak. Amikor kiválasztotta az adatokat, válassza az Adatok átalakítása vagy Szerkesztés gombot. Ez a Power Query-felületre viszi.
2. Kattintson a jobb gombbal egy üres pontra a bal oldali Lekérdezések panelen.
3. Válassza a "Üres lekérdezés" lehetőséget.
4. Az új üres lekérdezési ablakban válassza a Kezdőlap menüt, majd Speciális szerkesztő.
5. Cserélje le a sablont az egyéni függvényre. Például:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Válassza a Készlehetőséget.

Az egyéni függvények Power Query M képletnyelvvel történő fejlesztéséről a következő cikkben talál további információt: A Power Query M Functionsismertetése. A következő szakaszokban oktatóanyagok ismertetik, hogyan fejleszthet egyéni függvényeket kódírás nélkül a Power Query felhasználói felületén, és útmutatást ad arról, hogyan hívhatja meg az egyéni függvényt a lekérdezésben.

Egyéni függvény létrehozása táblázathivatkozási oktatóanyagból

Ezt a példát követve letöltheti a cikkben használt mintafájlokat az alábbi letöltési hivatkozásból. Az egyszerűség kedvéért ez a cikk a Mappa összekötőt használja. A Mappaösszekötőről további információt Mappacímű témakörben talál. A példa célja egy egyéni függvény létrehozása, amely az adott mappában lévő összes fájlra alkalmazható, mielőtt az összes fájl összes adatát egyetlen táblába egyesítené.

Először a Mappaösszekötő felülettel navigáljon arra a mappára, amelyben a fájlok találhatók, és válassza az Adatok átalakítása vagy Szerkesztéslehetőséget. Ezek a lépések végigvezetik a Power Query felületére. A Tartalom mezőben kattintson a jobb gombbal az Ön által választott bináris értékre, és válassza a Hozzáadás új lekérdezésként opciót. Ebben a példában a lista első fájljának kiválasztására került sor, amely történetesen a fájl április 2019.csv.

Ez a beállítás hatékonyan hoz létre egy új lekérdezést egy navigációs lépéssel közvetlenül a fájlhoz binárisként, és az új lekérdezés neve a kijelölt fájl elérési útja. Nevezze át ezt a lekérdezést Sample File.

Hozzon létre egy új paramétertFájlparaméter és bináristípusával. Használja a mintafájl- lekérdezést Alapértelmezett érték és Aktuális érték.

Kattintson a jobb gombbal fájlparaméterre a Lekérdezések panelen. Válassza a Hivatkozás lehetőséget.

Nevezze át az újonnan létrehozott lekérdezést fájlparaméter (2)Mintafájl átalakítása.

Kattintson a jobb gombbal erre az új Mintafájl átalakítása lekérdezésre, és válassza a Függvény létrehozása lehetőséget.

Ez a művelet hatékonyan létrehoz egy új függvényt, amely a Mintafájl átalakítása lekérdezéshez kapcsolódik. A Mintafájl átalakítása lekérdezésen végzett módosításokat automatikusan replikáljuk az egyéni függvényhez. Az új függvény létrehozása során használja az Átalakító fájlt a függvénynévként.

A függvény létrehozása után figyelje meg, hogy létrejön egy új csoport a függvény nevével. Ez az új csoport a következőket tartalmazza:

• A Mintafájl átalakítása során a lekérdezésbe hivatkozott összes paraméter.
• A Mintafájl átalakítása lekérdezés, más néven minta lekérdezés.
• Az újonnan létrehozott függvény, ebben az esetben Fájl átalakítása.

Átalakítások alkalmazása minta lekérdezésre

Az új függvény létrehozása után válassza ki a Mintafájl átalakításanevű lekérdezést. Ez a lekérdezés most az Átalakító fájl függvénnyel van összekapcsolva, így a lekérdezésen végrehajtott módosítások megjelennek a függvényben. Ezt a kapcsolatot nevezzük egy függvényhez csatolt minta lekérdezés fogalmának.

A lekérdezés első olyan átalakításának kell történnie, amely értelmezi a binárist. Kattintson a jobb gombbal a binárisra az előnézeti panelen, és válassza a CSV lehetőséget a bináris CSV-fájlként való értelmezéséhez.

A mappában lévő összes CSV-fájl formátuma megegyezik. Mindegyiknek van egy fejléce, amely az első négy sorra terjed ki. Az oszlopfejlécek az 5. sorban találhatók, és az adatok a 6. sorból indulnak lefelé, ahogy a következő képen is látható.

A átalakító mintafájlon a következő átalakítási lépéseket kell alkalmazni:

1. Az első négy sor eltávolítása– Ez a művelet eltávolítja a fájl fejlécszakaszának részét képező sorokat.

   

   Jegyzet

   Ha többet szeretne megtudni arról, hogyan távolíthat el sorokat, vagy hogyan szűrhet egy táblázatot sorhelyzet szerint, lépjen Szűrés sor pozíció szerint.

2. Fejlécek előre helyezése– A végleges táblázat fejlécei most már a táblázat első sorában vannak. A következő képen látható módon előléptetheti őket.

   

Az oszlopfejlécek előléptetése után a Power Query alapértelmezés szerint automatikusan hozzáad egy új Módosított típus lépést, amely automatikusan észleli az egyes oszlopok adattípusait. A(z) Mintafájlhoz tartozó lekérdezés úgy néz ki, mint a következő kép.

Egyéni függvény meghívása új oszlopként

Miután létrejött az egyéni függvény, és az összes átalakítási lépés be van építve, visszatérhet az eredeti lekérdezéshez, ahol a mappából származó fájlok listája található (CSV-fájlok ebben a példában). A menüszalagon lévő Oszlop hozzáadása fül alatt válassza a Egyéni függvény meghívása lehetőséget az Általános csoportból. Az Egyéni függvény meghívása ablakban adja meg Kimeneti táblaÚj oszlopnév. Válassza ki saját függvényének nevét, átalakítófájlt, a Függvénylekérdezés legördülő listából. Miután kiválasztotta a függvényt a legördülő menüből, megjelenik a függvény paramétere, és kiválaszthatja, hogy a tábla melyik oszlopát használja a függvény argumentumaként. Válassza ki a Tartalom oszlopot a fájlparaméterátadandó értékként/ argumentumként.

Miután kiválasztotta OK, létrejön egy új, Kimeneti tábla nevű oszlop. Ez az oszlop Táblázat értékeit tartalmazza a celláiban, ahogyan az a következő képen is látható. Az egyszerűség kedvéért távolítsa el az összes oszlopot ebből a táblából, kivéve Név és Kimeneti tábla.

A függvényt a tábla minden egyes sorára alkalmazták a Tartalom oszlop értékeit használva a függvény argumentumaként. Most, hogy az adatok a keresett alakzattá alakulnak, kibonthatja a Kimeneti tábla oszlopot a Kibontás ikonra kattintva. Ne használjon előtagot a kibontott oszlopokhoz.

A Név vagy Dátum oszlop értékeinek ellenőrzésével ellenőrizheti, hogy a mappában lévő összes fájlból vannak-e adatok. Ebben az esetben a Dátum oszlop értékeit ellenőrizheti, mivel minden fájl csak egy adott év egyetlen hónapjának adatait tartalmazza. Ha egynél több elemet lát, az azt jelenti, hogy több fájl adatait sikeresen egyesítette egyetlen táblába.

Új paraméter hozzáadása meglévő egyéni függvényhez

Képzelje el, hogy egy új követelmény van a jelenleg létrehozott követelményeken felül. Az új követelmény megköveteli, hogy a fájlok egyesítése előtt szűrje a bennük lévő adatokat, hogy csak azokat a sorokat kapja meg, amelyekben az Ország egyenlő Panama.

Ennek a követelménynek a elvégzéséhez hozzon létre egy új, Market nevű paramétert a szöveges adattípussal. Az Jelenlegi értékmezőbe írja be a Panamaértéket.

Ezzel az új paraméterrel válassza ki a Mintafájl átalakítása lekérdezést, és szűrje a Ország mezőt a Piac paraméter értékével.

Ha ezt az új lépést alkalmazza a lekérdezésre, automatikusan frissíti a Átalakító fájl függvényt, amely most két paramétert igényel a átalakító mintafájl által használt két paraméter alapján.

De a CSV-fájlok lekérdezés mellett egy figyelmeztető jel látható. A függvény frissítése után két paraméterre van szükség. A függvény meghívásának lépése tehát hibaértékeket eredményez, mivel a Meghívott egyéni függvény lépés során csak az egyik argumentum lett átadva az Átalakító fájl függvénynek.

A hibák kijavításához kattintson duplán a Egyéni függvény meghívása elemre a Alkalmazott lépések közül, hogy megnyissa az Egyéni függvény meghívása ablakot. A Market paraméterben kézzel adja meg a Panamaértékét.

Most már visszatérhet az Alkalmazott lépésekBővített kimeneti táblához. Ellenőrizze a lekérdezést, hogy csak azok a sorok jelenjenek meg a CSV-fájlok lekérdezés végleges eredményhalmazában, amelyekben Ország egyenlő Panama.

Egyéni függvény létrehozása újrafelhasználható logikai elemből

Ha több olyan lekérdezése vagy értéke van, amely ugyanazokat az átalakításokat igényli, létrehozhat egy egyéni függvényt, amely újrafelhasználható logikai elemként működik. Később ezt az egyéni függvényt az Ön által választott lekérdezésekre vagy értékekre lehet meghívni. Ez az egyéni függvény időt takaríthat meg, és segíthet az átalakítások központi helyen történő kezelésében, amelyet bármikor módosíthat.

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, ahogy az alábbi mintatáblában is látható:

Először egy olyan paraméterrel kell rendelkeznie, amely egy példaként szolgáló értékkel rendelkezik. Ebben az esetben ez az érték: PTY-CM1090-LAX.

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:

• Eredet = PTY
• cél = LAX
• Légitársaság = CM
• FlightID = 1090

Az alábbi M-kód bemutatja az átalakítások készletét.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Ezt követően a lekérdezést függvénysé alakíthatja úgy, hogy a jobb gombbal kattint a lekérdezésre, majd kiválasztja Függvény létrehozásalehetőséget. Végül meghívhatja az egyéni függvényt bármelyik lekérdezésbe vagy értékbe, ahogy a következő képen is látható.

Néhány további átalakítás után láthatja, hogy elérte a kívánt kimenetet, és egy egyéni függvényből alkalmazta az ilyen átalakítás logikáját.