Bevezetés a Power Query M kód

A Power Query egy nagyon hatékony adatkivonási és -átalakítási eszköz, amely az Excel 2016 (vagy újabb), az Excel for Office 365 és a Power BI programba van beépítve.

A szalag Adat lapján, a Get & Transform Data szakaszban található.

Ez nagyon hatékony, ugyanakkor nagyon könnyen használható, és a lekérdezésszerkesztő szuper intuitív felhasználói felülettel rendelkezik egy Excel felhasználó számára. Számos átalakítási lépés könnyen elvégezhető a power query editor szalagról, és nem kell semmilyen kódot ismernie az adatok tisztításához és alakításához.

A felhasználóbarát szerkesztő kulisszái mögött az Excel az átalakítási folyamat minden egyes lépését lefordítja ezekből a szalagparancsokból a power query M kódnyelvre.

Ez a bejegyzés bemutatja a power query M kódnyelv alapjait, és feltételezi, hogy már ismeri a power query alapjait.

Mi az M kód?

Az M az data Mash-up rövidítése, mivel a power query lényege a különböző különböző adatforrásokhoz való csatlakozás és azok “pépesítése”.

A M kód a power query kulisszái mögött álló nyelv. Amikor létrehoz egy adattranszformációt a power query szerkesztő felhasználói felületén, az Excel megírja a megfelelő M kódot a lekérdezéshez.

Az M egy funkcionális nyelv, ami azt jelenti, hogy elsősorban olyan függvényekkel íródik, amelyeket az eredmények kiértékelésére és visszaadására hívnak meg. Az M kód nagyon nagy könyvtárnyi előre definiált függvény áll rendelkezésre, és saját függvényeket is létrehozhat.

Hol lehet Power Query M kódot írni?

Ha el akarja kezdeni az M kód írását vagy szerkesztését, tudnia kell, hogy hol teheti ezt meg. Két helyen van erre lehetőség, a képletsoron vagy a speciális szerkesztőben.

A képletsor

A szerkesztő felhasználói felületen létrehozott minden egyes lépéshez megtekintheti a megfelelő M kódot a képletsoron.

Ha nem látja a képletsávot, lépjen a Nézet lapra, és ellenőrizze, hogy a Képletsáv opció be van-e jelölve.

A lekérdezés bármelyik lépésének M-kódját szerkesztheti a képletbe kattintva és a meglévő kódot szerkesztve. Ha végzett, a módosításokat a jelölőnégyzetre kattintva vagy az Enter billentyűvel fogadhatja el. A módosításokat az X-re kattintva vagy az Esc billentyűvel is elvetheti.

A képletsorral teljesen új lépéseket is létrehozhat a lekérdezésben, ha a képletsor melletti fx szimbólumra kattint. Ez egy új lépést hoz létre, amely név szerint hivatkozik az előző lépésre, és ezután bármilyen M-kódot létrehozhat, amire szüksége van.

A speciális szerkesztő

A képletsor csak a lekérdezésben éppen kiválasztott lépés M-kódját mutatja, de a speciális szerkesztő az a hely, ahol a teljes lekérdezés M-kódját megtekintheti és szerkesztheti.

A speciális szerkesztőt a szerkesztő szalag két helyéről nyithatja meg. Vagy a Kezdőlap lapon, vagy a Nézet lapon nyomja meg a Speciális szerkesztő gombot.

A “speciális” elnevezés ellenére a szerkesztő a legalapvetőbb kódszerkesztő, amit látni fog, és (még) nem tartalmaz semmilyen intellisense automatikus kiegészítés, szintaxis-kiemelés vagy automatikus formázási funkciót.

A speciális szerkesztő megjeleníti a lekérdezés nevét, megmutatja a lekérdezés M-kódját, és figyelmeztetést jelenít meg az M-kódban található esetleges szintaxis-szabálytalanságokról. Ennyi!

Standard függvénykönyvtár

Mivel az M-kód egy funkcionális nyelv, minden a függvényekről szól, és az M-kódhoz egy nagy, előre definiált függvénykönyvtár tartozik, amelyet standard könyvtárnak nevezünk.

A Microsoft Power Query M Reference weboldalán az összes elérhető standard könyvtárfüggvényről talál információkat, beleértve a függvények szintaxisát és példáit.

A standard könyvtár a Power Query szerkesztőjéből is felfedezhető a #shared kulcsszó használatával.

A képletsorba beírva a függvény neve mellett jobbra található Function szóra kattintva az összes elérhető függvényt felfedezheti. Ugyanazt a szintaxist és példákat találja, mint a referencia weboldalon.

Nagy- és kisbetű érzékenység

Az egyik első dolog, amivel valakinek tisztában kell lennie, amikor M kódot ír, hogy ez egy nagy- és kisbetű érzékeny nyelv.

Ez azt jelenti, hogy x nem ugyanaz, mint X vagy “abc” nem ugyanaz, mint “ABC”. Ez minden értékre, változóra, függvényre stb. igaz.

Kifejezések és értékek a Power Query-ben

A Power Query a kifejezésekről és értékekről szól.

A kifejezés olyasmi, amit ki lehet értékelni, hogy a Power Query-ben értéket adjon vissza. Az 1 + 1 egy olyan kifejezés, amely a 2 értékre értékelődik ki.

Az érték egyetlen adat. Az értékek lehetnek egyedi értékek, például számok, szöveg, logikai, null, bináris, dátum, idő, dátumidő, datetime, datetimezone vagy időtartamok.

Az értékek az egyedi értékeknél összetettebb szerkezetűek is lehetnek, például listák, rekordok és táblázatok. Lehetnek olyan értékek is, amelyek listák, rekordok és táblázatok kombinációjából állnak. Listák listái, listák táblázatai, táblázatok táblázatai stb… mind lehetséges értékstruktúrák.

Egyszeres szó szerinti értékek

Az egyszeres szó szerinti értékek az összes többi érték alapvető építőkövei.

• A 123.45 egy számérték.
• “Hello World!” egy szöveges érték.
• true egy logikai érték.
• null az érték hiányát jelenti.

Egyszeri belső értékek

A belső értékek a különböző belső függvények segítségével épülnek fel.

• #time(hours, minutes, seconds)
• #date(years, months, days)
• #datetime(years, months, days, hours, minutes, seconds)
• #datetimezone( years, months, days, hours, minutes, seconds, offset-hours, offset-minutes)
• #duration(days, hours, minutes, seconds)

A 2018-12-31 dátumot például a #date(2018, 12, 31) intrinsic függvénnyel kell megalkotni.

Strukturált értékek

Listák

A lista értékek rendezett sorozata.

A listát görbe zárójelek segítségével definiálhatjuk. A {1, 2, 3} egy lista, amely az 1, 2 és 3 számokat tartalmazza. Mivel a sorrend fontos, ez nem ugyanaz a lista, mint a {3, 2, 1}.

{“Hello”, “World”} egy lista, amely a “Hello” és a “World” szöveget tartalmazza.

Listák listái is lehetségesek, így a {{1, 2}, {3, 4, 5}} egy lista két listából. Az első lista az 1 és 2 számot tartalmazza, a második lista pedig a 3, 4 és 5 számokat.

Sorrendi listákat hozhatunk létre a {x..y} formátummal. A {2..5} a {2, 3, 4, 5} listát fogja eredményezni. Ez működik szöveges karakterek esetén is. A {“a”…d”} az {“a”, “b”, “c”, “d”} listát fogja eredményezni.

Listát is készíthetünk elemek nélkül, a {} az üres lista.

Mivel a listák rendezettek, a listában lévő elemekre nulla alapú indexszámmal hivatkozhatunk. A {1, 2, 3}{2} kiértékelődik 3-ra, mivel ez a lista 2. eleme (nulla index alapján).

Rekordok

A rekord a mezők rendezett sorozata. Minden mező egy mezőnévből áll, amely egyedileg azonosítja a mezőt, és egy mezőértékből, amely bármilyen típusú érték lehet.

Egy rekordot szögletes zárójelek segítségével határozhat meg. egy rekord két mezővel. A rekord első mezőjének mezőneve FirstName, értéke pedig “John”. A rekord második mezőjének mezőneve Age és értéke 38.

Rekordokból álló rekordok is lehetségesek, ] egy rekord egy mezővel, amelynek mezőneve Person és mezőértéke egy rekord.

Empty records are also possible, is the empty record.

A rekordban lévő mezőértékre a mezőnévvel lehet hivatkozni. kiértékelése “John” lesz.

Táblák

A táblázat sorok rendezett sorozata, ahol minden sor egy lista.

Táblákat csak intrinsic függvény segítségével lehet felépíteni. Egy táblázatot a #table() függvény segítségével az oszlopcímek és a sorok listájából lehet létrehozni.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) Egy táblázatot hoz létre 2 oszloppal, 3 sorral és a Betűk és Számok oszlopcímekkel.

A #table() intrinsic függvényben üres listák segítségével üres táblázatot lehet létrehozni. A #table({}, {}) egy üres táblázatot hoz létre.

A táblázatban bármely értékre hivatkozhatunk a nulla alapú sorindexszel és az oszlopcím nevével. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} “C”-re fog kiértékelődni, mivel ez a Letters oszlop 2. sora (nullás index alapján).

Kifejezések

A kifejezések bármi, ami értékre értékelhető. Ez magára az értékekre is igaz. Például az 1 kifejezés az 1 értékre értékelődik ki.

Bár a kifejezésekre általában úgy gondolunk, mint amelyek összetettebb műveletekből vagy függvényekből állnak.

Például:

• Az 1 + 1 kifejezés 2-re értékelődik ki.
• A 3 > 2 kifejezés igazra értékelődik ki.
• A “Hello ” & “World” kifejezés “Hello World”-re értékelődik.
• A Text.Upper(“Hello World”) kifejezés “HELLO WORLD”-ra értékelődik.

Operátorok

A standard könyvtár mellett az M kódnak is van egy listája speciális függvényeknek, amelyeket operátoroknak neveznek. Ezek pontosan két értéket (vagy értékre kiértékelő kifejezéseket) vesznek fel, és egyetlen értéket adnak vissza.

Aritmetika

A M kód rendelkezik az alapvető aritmetikai operátorokkal, amelyeket a hagyományos Excel +, -, * és / operátorokból várhatunk és ismerhetünk. Ezek segítségével értékeket adhatunk össze, vonhatunk ki, szorozhatunk és oszthatunk.

A számokon kívül számos más típusú értékkel is használhatóak. Például egy dátumhoz hozzáadhat egy időtartamot.

#date(2018,12,25) + #duration(7, 0, 0, 0, 0, 0) értéke 2019-01-01 lesz.

Összehasonlítás

Az M kódban az <, >, <=, >=, =, <> összehasonlító operátorok segítségével összehasonlíthatja az értékeket.

• x < y akkor lesz igaz, ha x kisebb, mint y.
• x > y akkor lesz igaz, ha x nagyobb, mint y.
• x <= y igaznak fog értékelődni, ha x kisebb vagy egyenlő y-val.
• x >= y igaznak fog értékelődni, ha x nagyobb vagy egyenlő y-val.
• x = y igaznak fog értékelődni, ha x egyenlő y-val.
• x <> y igaznak fog értékelődni, ha x nem egyenlő y-val.

Ezek különböző típusú értékekkel használhatók. Például két listát hasonlíthat össze az equal operátorral.

{1,2,3,4} = {1,2,3} hamis értéket fog adni, mivel a listák nem azonosak.

Összekapcsolás és összevonás

Szövegeket kapcsolhat össze és listákat, rekordokat és táblázatokat vonhat össze az ampersand & operátorral.

Például:

"Hello " & "World" “Hello World” értéket fog adni.

{1,2,3} & {3,4,5} a {1,2,3,3,3,4,5} értéket fogja kiértékelni.

Logikai

A nem, és és vagy operátorokkal Boole-értékekkel (vagy Boole-értékekre kiértékelődő kifejezésekkel) végezhet műveleteket.

A kód kommentálása

Amint az bármely programozási nyelvtől elvárható, lehetőség van arra, hogy megjegyzéseket fűzzünk a kódunkhoz.

Az M kódban kétféle megjegyzés lehetséges. Egysoros és többsoros megjegyzések.

Egysoros megjegyzések

M code goes hereM code goes here //This is a single line commentM code goes here

Az egysoros megjegyzést úgy hozhatjuk létre, hogy a megjegyzést két // előremenő perjel karakterrel előzzük meg. Minden, ami előtte van egy sorban, M kódként, minden, ami utána van, megjegyzésként értelmeződik.

Többsoros megjegyzések

M code goes here /*This is a commenton multiple lines*/ M code goes here

Egy többsoros megjegyzés úgy hozható létre, hogy a megjegyzést a /* és */ karakterek közé helyezzük. Az ezeken kívül eső részek M kódként lesznek értelmezve. Bármi, ami ezek között van, megjegyzésként lesz értelmezve.

Let utasítás

A let utasítás lehetővé teszi egy értékkészlet kiértékelését és változónevekhez rendelését, majd felhasználását egy következő kifejezésben, amely az in utasítást követi.

let a = 1, b = 2, c = a + bin c

Ez a kifejezés három kifejezésből áll, amelyek a let utasítás után kerülnek kiértékelésre. Az egyes kifejezéseket vessző választja el egymástól, kivéve az in utasítás előtti utolsót. Ebben a példában a teljes let és in kifejezés kiértékelése 3 lesz.

let c = a + b, b = 2, a = 1in c

Azt gondolhatja, hogy a let utasításon belüli kifejezéseknek a kiértékelésük sorrendjében kell megjelenniük. Ez nem így van! A fenti kód tökéletesen érvényes, és szintén kiértékelődik 3-ra. Az M kód kiértékelője automatikusan kiszámítja a szükséges számítások sorrendjét a kifejezések függősége alapján.

Az ember számára nyilvánvalóan könnyebb elolvasni az M kódot, ha az kiértékelés sorrendjében van leírva, de van egy másik előnye is. A kifejezések különálló lépésekként jelennek meg az Alkalmazott lépések ablakban. Ha nem sorrendben írjuk, a kifejezések egyetlen kombinált lépésként jelennek meg.

let a = 1, b = 2in a + b

A kifejezéseket a let… in… kifejezés in részében is kiértékelhetjük.

Változónevek

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

A #”” karakterek segítségével szinte bármilyen nevet adhatunk a kifejezésekhez. Használhatsz még szóközöket és egyéb speciális karaktereket is. A fenntartott kulcsszavak használata az egyetlen kivétel.

A változók nevei azok, amelyek a lekérdezésszerkesztő Alkalmazott lépéseiben megjelennek, ezért a szóköz karakterek használata nagyszerű lehetőség.

Each-kifejezések

A each kifejezés egy rövidítés az egyetlen _ (aláhúzás) nevű paramétert fogadó függvények deklarálására.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

Ebben a példában egy új oszlopot hozunk létre, amely minden sorban megszorozza a Számok oszlopot 2-vel.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Az aláhúzás szintaxissal is létrehozhatjuk ugyanezt a lekérdezést, amely szemantikailag egyenértékű az each utasítással. Mindkét lekérdezés ugyanúgy fog működni.

If Then Else Statements

Az M kód más nyelvekhez képest elég gyér, ha logikai kifejezésekről van szó. Nincsenek select case vagy loop utasítások. Csak if… then… else… kifejezés áll rendelkezésre.

if then else 

A szintaxis egyszerű, és olyan, mint a legtöbb más programozási nyelven. Megjelenhet az egész egy sorban, vagy a könnyebb olvashatóság érdekében külön sorokban is bemutatható.

Try Otherwise utasítások

Hibák akkor fordulhatnak elő, ha olyan műveleteket próbálunk végrehajtani, amelyek bizonyos típusú adatokat igényelnek. Ha például egy számot próbálunk megszorozni egy szöveges értékkel, az hibát eredményez.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

A hibák elkerülhetők a try… otherwise… kifejezés használatával.

Ezzel elkerülheti a hibákat a lekérdezés eredményeiben, és lehetővé teszi, hogy a hibákat bármilyen értékkel vagy kifejezéssel helyettesítse.

Funkciók

A függvény a paraméterértékek egy halmazának egy értékre való leképezése. A szabványos könyvtár függvényei mellett az M kód lehetővé teszi saját függvények létrehozását is.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

Ez a lekérdezés egy olyan függvényt definiál, amely két számot szoroz meg. Ezután a lekérdezés meghívja és kiértékeli a függvényt a 2 és 3 értékekkel, ami 6 értéket ad.

Függvények opcionális paraméterekkel

A függvény paramétereinek két típusa van, a kötelező paraméter és az opcionális paraméter.

A kötelező paramétereket mindig meg kell adni a függvény meghívásakor.

A opcionális paramétereket nem kell megadni a függvény meghívásakor. Ha az opcionális paraméter hiányzik, akkor a függvénynek átadott érték nulla lesz.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Ez a függvény opcionális második argumentummal rendelkezik. Ezután a lekérdezés meghívja és kiértékeli a függvényt csak a szükséges paraméterrel, amelynek értéke 2. Vegyük észre, hogy a függvénynek figyelembe kell vennie, hogy y null, különben a hiányzó opcionális argumentum hibás kiértékelést eredményezhet.

Rekurzív függvények

A @ scoping operátor használatával olyan függvényt is lehet írni, amely önmagára hivatkozik.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

A Fibonacci-sorozat egy példa a rekurzívan definiált függvényre. A sorozat következő száma a két előző szám összegeként van definiálva. Tehát ahhoz, hogy megkapjuk az n-edik számot, ismernünk kell az (n-1)-edik és az (n-2)-edik számot.

Ez a függvény az (n-1)-edik és az (n-2)-edik Fibonacci-szám összeadásával találja meg az n-edik Fibonacci-számot.

A lekérdezés kiértékelése 13, mivel a 13 a 7. Fibonacci-szám.

Itt van még néhány hasznos példa, ahol rekurzív függvényekkel megismételheti az Excel TRIM funkcióját a szavak közötti felesleges szóközök eltávolítására, vagy tömeges értékkeresésre és -helyettesítésre egy lista alapján.

Lekérdezési függvények

A fenti példákban egy függvényt definiáltunk egy lekérdezésen belül, majd a függvényt a lekérdezésen belül hívtuk meg és értékeltük ki.

Egy olyan lekérdezést is létrehozhatunk, amely egy függvény (lekérdezési függvény), és más lekérdezésekből hívható és értékelhető.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

Ez az általános formátum szükséges egy lekérdezési függvény létrehozásához. Vegye figyelembe, hogy a lekérdezési függvény let… in… utasításán belül egy let… in… utasításra lesz szüksége a több lépés végrehajtásához.

Következtetések

A Power query egy nagyszerű funkció az Excelben, amely segít automatizálni és egyszerűsíteni az adatok importálását és átalakítását.

A grafikus point and click felület segítségével sok mindent elvégezhet anélkül, hogy az M kódhoz hozzá kellene nyúlnia. De ahogy az Ön igényei egyre fejlettebbé válnak, eljön az idő, amikor szükség lehet az Ön által létrehozott M kód szerkesztésére vagy saját kód írására a semmiből.

Minden új programozási nyelv olvashatatlan, ha előbb nem ismeri az alapokat. Ez az útmutató remélhetőleg megismerteti Önt az M kóddal, így elkezdheti a fejlettebb lekérdezések létrehozását.