Olvasás angol nyelven

Megosztás a következőn keresztül:


Egyéni függvények használata

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.

Bár a Power Query M függvényeinek ismertetése szerint manuálisan is létrehozhat saját Egyéni Power Query-függvényt, a Power Query felhasználói felülete szolgáltatásokat kínál az egyéni függvények létrehozásának és kezelésének felgyorsítására, egyszerűsítésére és javítására.

Ez a cikk erre a felületre összpontosít, amely csak a Power Query felhasználói felületén keresztül érhető el, és hogyan lehet a legtöbbet kihozni belőle.

Fontos

Ez a cikk bemutatja, hogyan hozhat létre egyéni függvényt a Power Queryvel a Power Query felhasználói felületén elérhető gyakori átalakítások használatával. Az egyéni függvények létrehozásának alapfogalmaira összpontosít, valamint a Power Query dokumentációjában található további cikkekre mutató hivatkozásokra, amelyek további információt tartalmaznak a cikkben hivatkozott konkrét átalakításokról.

Egyéni függvény létrehozása táblahivatkozásból

Megjegyzés

Az alábbi példa a Power BI Desktop asztali felületével jött létre, és a Windows Excelben található Power Query-felülettel is követhető.

Ezt a példát követve letöltheti a cikkben használt mintafájlokat az alábbi letöltési hivatkozásró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 a Mappa területen 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 a Szerkesztés lehetőséget. Ezek a lépések végigvezetik a Power Query felületére. Kattintson a jobb gombbal a kívánt bináris értékre a Tartalom mezőben, és válassza a Hozzáadás új lekérdezésként lehetőséget. Ebben a példában a lista első fájljának kiválasztására került sor, amely történetesen az áprilisi 2019.csv.

Képernyőkép a mintafájlnak megfelelő fájl kiválasztásáról.

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 mintafájlként.

Képernyőkép az új fájlminta-lekérdezésről.

Hozzon létre egy új paramétert fájlparaméter néven és bináris típussal. Használja a Mintafájl lekérdezést alapértelmezett értékként és aktuális értékként.

Képernyőkép a kitöltött fájlparaméter-értékekkel.

Megjegyzés

Javasoljuk, hogy olvassa el a paraméterekről szóló cikket, hogy jobban megértse, hogyan hozhat létre és kezelhet paramétereket a Power Queryben.

Az egyéni függvények bármilyen paramétertípussal létrehozhatók. Nincs szükség arra, hogy az egyéni függvények bináris paraméterként rendelkezzenek.

A bináris paraméter típusa csak akkor jelenik meg a Paraméterek párbeszédpanel Típus legördülő menüjében, ha egy binárisra kiértékelt lekérdezéssel rendelkezik.

Egyéni függvény paraméter nélkül is létrehozható. Ez gyakran előfordul olyan helyzetekben, amikor a bemenet abból a környezetből következtethető ki, amelyben a függvényt meghívják. Például egy függvény, amely a környezet aktuális dátumát és idejét veszi fel, és létrehoz egy adott szöveges sztringet ezekből az értékekből.

Kattintson a jobb gombbal a Fájlparaméter elemre a Lekérdezések panelen. Válassza a Hivatkozás lehetőséget.

Képernyőkép a Fájlparaméter hivatkozás lehetőségével.

Nevezze át az újonnan létrehozott lekérdezést a Fájlparaméterből (2) a mintafájl átalakítására.

Képernyőkép az átnevezett transzformációs mintafájl-lekérdezésről.

Kattintson a jobb gombbal erre az új transzformációs mintafájl-lekérdezésre , és válassza a Függvény létrehozása lehetőséget.

Képernyőkép az Átalakítás mintafájl lekérdezéséhez használt Függvény létrehozása lehetőségről.

Ez a művelet hatékonyan létrehoz egy új függvényt, amely a mintafájl-átalakítás lekérdezéséhez kapcsolódik. A mintafájl-átalakítás lekérdezésében végrehajtott módosítások automatikusan replikálódnak az egyéni függvénybe. Az új függvény létrehozása során használja az Átalakítás fájlt függvénynévként.

Képernyőkép az Átalakítás fájlHoz tartozó Függvény létrehozása ablakról.

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:

  • Az átalakító mintafájl-lekérdezésben hivatkozott összes paraméter.
  • Az Átalakítás mintafájl lekérdezése, más néven minta lekérdezés.
  • Az újonnan létrehozott függvény, ebben az esetben az Átalakítás fájl.

Képernyőkép a Függvénycsoportról a Lekérdezések panelen.

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

Az új függvény létrehozása után válassza ki az Átalakítás mintafájl nevű lekérdezést. Ez a lekérdezés most már az Átalakítás fájlfüggvénnyel van összekapcsolva, így a lekérdezésen végrehajtott módosítások a függvényben is megjelennek. 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.

Képernyőkép a bináris legördülő menüről a CSV kiemelésével.

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ó.

Képernyőkép a CSV-mintaadatokról feldolgozás előtt.

Az átalakítás mintafájlra alkalmazandó következő átalakítási lépések a következők:

  1. Távolítsa el az első négy sort – Ez a művelet eltávolítja a fájl fejlécszakaszának részét képező sorokat.

    Képernyőkép, miután a felső sorok el lettek távolítva az Átalakítás mintafájlból.

    Megjegyzés

    Ha többet szeretne megtudni arról, hogy miként távolíthat el sorokat, vagy hogyan szűrhet egy táblázatot sorhelyzet szerint, válassza a Szűrés sor pozíció alapján lehetőséget.

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

    Képernyőkép az első sor fejlécként való használata után.

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. Az Átalakítás mintafájl lekérdezése a következő képhez hasonlóan néz ki.

Megjegyzés

Ha többet szeretne megtudni az élőfejek előléptetéséről és lefokozásáról, lépjen az oszlopfejlécek előléptetésére vagy lefokozására.

Képernyőkép a végső átalakító minta lekérdezésről.

Figyelemfelhívás

Az Átalakítás fájlfüggvény az Átalakítás mintafájl-lekérdezésben végrehajtott lépésekre támaszkodik. Ha azonban megpróbálja manuálisan módosítani az Átalakítás fájlfüggvény kódját, a rendszer figyelmeztetést kap, amely felolvassaThe definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

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ó (ebben a példában CSV-fájlok ). A menüszalag Oszlop hozzáadása lapján válassza az Egyéni függvény meghívása lehetőséget az Általános csoportban. Az Egyéni függvény meghívása ablakban adja meg a Kimeneti táblát új oszlopnévként. A Függvénylekérdezés legördülő listából válassza ki a függvény nevét, az Átalakítás fájlt. 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éterhez átadandó értékként/ argumentumként.

Képernyőkép az Egyéni függvény meghívása gomb kiemelésével az Egyéni függvény meghívása párbeszédpanel beállításaival.

Az OK gomb kiválasztása után létrejön egy kimeneti tábla nevű új oszlop. Ez az oszlop táblázatértékeket tartalmaz 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 a Név és a Kimeneti tábla kivételével.

Képernyőkép az egyéni függvény meghívásával, és csak a Név és a Kimeneti tábla oszlop maradt meg.

Megjegyzés

Ha többet szeretne megtudni arról, hogyan választhat ki vagy távolíthat el oszlopokat egy táblából, válassza az Oszlopok kiválasztása vagy eltávolítása lehetőséget.

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, a Kibontás ikonra kattintva kibonthatja a Kimeneti táblázat oszlopot. Ne használjon előtagot a kibontott oszlopokhoz.

Képernyőkép a kibontott kimeneti tábla párbeszédpanelről, amelyen az összes táblaoszlop ki van jelölve.

A Név vagy Dátum oszlop értékeinek ellenőrzésével ellenőrizheti, hogy rendelkezik-e adatokkal a mappában lévő összes fájlból. 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.

Képernyőkép arról a dátum legördülő listáról, amely ellenőrzi, hogy a végleges tábla minden fájlból tartalmaz adatokat.

Megjegyzés

Amit eddig olvasott, az alapvetően ugyanaz a folyamat, amely a Fájlok egyesítése felületen történik, de manuálisan történik.

Javasoljuk, hogy olvassa el a Fájlok egyesítése áttekintése és a CSV-fájlok egyesítése című cikket is, hogy jobban megismerje a Fájlok egyesítése funkció működését a Power Queryben, valamint az egyéni függvények által játszott szerepkört.

Ú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, ahol az ország egyenlő Panamában.

Ennek a követelménynek a elvégzéséhez hozzon létre egy piac nevű új paramétert a szöveges adattípussal. Az Aktuális érték mezőben adja meg a Panama értéket.

Képernyőkép az új paraméterről a szükséges értékekkel.

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

Képernyőkép a Szűrősorok párbeszédpanelről az ország szűrése oszlopmal az új Piac paraméter használatával.

Megjegyzés

Ha többet szeretne megtudni az oszlopok értékek szerinti szűréséről, lépjen az Értékek szűrése elemre.

Ha ezt az új lépést alkalmazza a lekérdezésre, automatikusan frissíti az Átalakítás fájlfüggvényt, amelyhez most két paraméterre van szükség az átalakító mintafájl által használt két paraméter alapján.

Képernyőkép a függvényről, amely most két paraméterrel lett frissítve.

A CSV-fájlok lekérdezése mellett azonban 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 az Egyéni függvény meghívása lépés során csak az egyik argumentum lett átadva az Átalakítás fájlfüggvénynek.

Képernyőkép a függvényfrissítés után megjelenő hibaüzenetről.

A hibák kijavításához kattintson duplán az Egyéni függvény meghívása ablak megnyitásához az Alkalmazott lépésekben a Meghívott egyéni függvény elemre. A Market paraméterben adja meg manuálisan a Panama értéket.

Képernyőkép a frissített meghívott egyéni függvényargumentumokról.

Most már visszatérhet a Bővített kimeneti táblához az alkalmazott lépésekben. Ellenőrizze a lekérdezést annak ellenőrzéséhez, hogy csak azok a sorok jelenjenek meg a CSV-fájlok lekérdezés végleges eredményhalmazában, amelyekben az ország egyenlő Panamában.

Képernyőkép a végleges kimeneti tábláról a frissített argumentumok után.

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 ez az egyéni függvény meghívható a választott lekérdezésekkel vagy értékekkel. 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ó:

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Képernyőkép a kódok eredeti listájáról.

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

Képernyőkép a Paraméterek kezelése párbeszédpanelről a megadott mintaparaméter-kódértékekkel.

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

Képernyőkép a minta átalakító lekérdezésről, amelynek minden része a saját oszlopában található.

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"

Megjegyzés

Ha többet szeretne megtudni a Power Query M képletnyelvéről, lépjen a Power Query M képletnyelvére.

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, ahogy a következő képen is látható.

Képernyőkép az Egyéni függvény meghívása értékekkel rendelkező kódok listájáról.

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.

Képernyőkép az egyéni függvények meghívása utáni végső kimeneti lekérdezésről.