Johdanto Power Query M-koodiin

Power Query on erittäin tehokas tietojen poiminta- ja muunnostyökalu, joka on sisäänrakennettu Excel 2016:een (tai uudempaan), Excel for Office 365:een ja Power BI:hen.

Se löytyy Data-välilehdeltä nauhan Get & Transform Data -osiosta.

Se on erittäin tehokas, mutta myös erittäin helppokäyttöinen, ja kyselyeditorissa on Excel-käyttäjälle erittäin intuitiivinen käyttöliittymä. Monet muunnosvaiheet voidaan helposti suorittaa Power Query Editor -nauhalta, eikä sinun tarvitse osata mitään koodia puhdistaaksesi ja muokataksesi tietojasi.

Käyttäjäystävällisen editorin kulissien takana Excel kääntää muunnosprosessisi jokaisen vaiheen noista nauhan komennoista Power Query M -koodikielelle.

Tässä postauksessa esitellään Power Queryn M-koodikielen perusteet ja oletetaan, että tunnet jo Power Queryn perusteet.

Sisällysluettelo

Mitä on M-koodi?

M tulee sanoista data Mash-up, sillä power query tarkoittaa yhteyden luomista erilaisiin eri tietolähteisiin ja niiden ”Mashing” yhdistämistä.

M-koodi on kieli power queryn kulissien takana. Kun luot tietomuunnoksen power query -editorin käyttöliittymässä, Excel kirjoittaa vastaavaa M-koodia kyselyä varten.

M on funktionaalinen kieli, mikä tarkoittaa, että se kirjoitetaan pääasiassa funktioilla, joita kutsutaan arvioimaan ja palauttamaan tuloksia. M-koodissa on käytettävissä erittäin suuri kirjasto valmiita funktioita, ja voit myös luoda omia funktioita.

Missä voit kirjoittaa Power Query M-koodia?

Jos haluat aloittaa M-koodin kirjoittamisen tai muokkaamisen, sinun on tiedettävä, missä voit tehdä sen. Se on mahdollista kahdessa paikassa, kaavapalkissa tai edistyneessä editorissa.

Kaavapalkki

Kunkin editorin käyttöliittymässä luodun vaiheen osalta voit tarkastella vastaavaa M-koodia kaavapalkissa.

Jos et näe kaavapalkkia, siirry Näkymä-välilehdelle ja varmista, että Kaavapalkki-vaihtoehto on valittuna.

Voit muokata minkä tahansa kyselyn vaiheen M-koodia napsauttamalla kaavaa ja muokkaamalla olemassa olevaa koodia. Kun olet valmis, voit hyväksyä muutokset joko napsauttamalla valintamerkkiä tai painamalla Enter-näppäintä. Voit myös hylätä muutokset napsauttamalla X:ää tai painamalla Esc-näppäintä.

Voit myös luoda kokonaan uusia vaiheita kyselyyn kaavarivillä napsauttamalla kaavarivin vieressä olevaa fx-symbolia. Tämä luo uuden askeleen, joka viittaa edelliseen askeleeseen nimellä, ja sen jälkeen voit luoda minkä tahansa tarvitsemasi M-koodin.

Lisäeditori

Kaavapalkissa näkyy vain kyselyn kulloinkin valitun askeleen M-koodi, mutta lisäeditori on paikka, jossa voit tarkastella ja muokata koko kyselyn M-koodia.

Editorin lisäeditori voidaan avata kahdesta kohdasta editorin nauhassa. Paina joko Koti-välilehdeltä tai Näytä-välilehdeltä Edistynyt editori -painiketta.

Editori on ”edistynyt”-nimikkeestään huolimatta yksinkertaisin näkemäsi koodieditori, eikä se sisällä (vielä) mitään intellisense-automaattista täydennystä, syntaksin korostusominaisuuksia tai automaattisia muotoiluominaisuuksia.

Edistyneessä editorissa näytetään kyselyn nimi, näytetään kyselyn M-koodi ja näytetään varoitus mahdollisista syntaksisääntöjen rikkoontumisista M-koodissa. Siinä kaikki!

Vakiotoimintokirjasto

Koska M-koodi on funktionaalinen kieli, kyse on funktioista, ja M-koodin mukana tulee suuri kirjasto valmiita funktioita, joita kutsutaan vakiokirjastoksi.

Tietoa kaikista käytettävissä olevista vakiokirjaston funktioista, mukaan lukien funktioiden syntaksit ja esimerkit, on Microsoftin Power Query M Reference -verkkosivulla.

Vakiokirjastoon voi tutustua myös Power Query -editorista käyttämällä avainsanaa #shared.

Kun kirjoitat kaavapalkkiin, voit tutustua kaikkiin käytettävissä oleviin funktioihin napsauttamalla funktion nimen oikealla puolella olevaa sanaa Function. Löydät saman syntaksin ja samat esimerkit kuin referenssisivulta.

Isojen ja pienten kirjainten herkkyys

Yksi ensimmäisistä asioista, joista jonkun on oltava tietoinen kirjoittaessaan M-koodia, on se, että se on isojen ja pienten kirjainten herkkä kieli.

Tämähän tarkoittaa sitä, että ”x” ei ole sama asia kuin ”X” tai ”abc” ei ole sama asia kuin ”ABC”. Tämä pätee kaikkiin arvoihin, muuttujiin, funktioihin jne.

Lausekkeet ja arvot Power Queryssä

Power Queryssä on kyse lausekkeista ja arvoista.

Lauseke on jotain, joka voidaan arvioida palauttamaan arvo Power Queryssä. 1 + 1 on lauseke, joka evaluoituu arvoksi 2.

Arvo on yksittäinen tieto. Arvot voivat olla yksittäisiä arvoja, kuten numeroita, tekstiä, loogisia arvoja, nolla-arvoja, binääriarvoja, päivämääriä, kellonaikoja, datetime-arvoja, datetimezone-arvoja tai kestoja.

Arvot voivat olla myös monimutkaisempia rakenteita kuin yksittäiset arvot, kuten luettelot, tietueet ja taulukot. Arvoja voi olla myös listojen, tietueiden ja taulukoiden yhdistelmiä. Luetteloiden luettelot, luetteloiden taulukot, taulukoiden taulukot jne… ovat kaikki mahdollisia arvorakenteita.

Yksittäiset kirjainarvot

Yksittäiset kirjainarvot ovat kaikkien muiden arvojen perusrakenne.

123.45 on lukuarvo.
”Hello World!” on tekstiarvo.
true on looginen arvo.
null edustaa arvon puuttumista.

Yksittäiset sisäiset arvot

Sisäiset arvot rakennetaan käyttämällä erilaisia sisäisiä funktioita.

#time(tunnit, minuutit, sekunnit)
#date(vuodet, kuukaudet, päivät)
#datetime(vuodet, kuukaudet, päivät, tunnit, minuutit, sekunnit)
#datetimezone( vuodet, kuukaudet, päivät, tunnit, minuutit, sekunt, offset-tunnit, offset-minuutit)
#duration(päivät, tunnit, minuutit, sekunnit)

Esimerkiksi, jos haluat konstruoida päivämäärän 2018-12-31, sinun on konstruoitava se käyttämällä #date(2018, 12, 31) intrinsic-funktiota.

Strukturoidut arvot

Luettelot

Luettelo on järjestetty arvojen sarja.

Luettelon voi määritellä käyttämällä sulkeita. {1, 2, 3} on lista, joka sisältää luvut 1, 2 ja 3. Koska järjestys on tärkeä, tämä ei ole sama lista kuin {3, 2, 1}.

{”Hello”, ”World”} on lista, joka sisältää tekstin ”Hello” ja ”World”.

Luetteloiden luettelot ovat myös mahdollisia, joten {{1, 2}, {3, 4, 5}} on kahden listan luettelo. Ensimmäinen lista sisältää luvut 1 ja 2 ja toinen lista sisältää luvut 3, 4 ja 5.

Voit luoda peräkkäisiä listoja käyttämällä muotoa {x..y}. {2..5} tuottaa listan {2, 3, 4, 5}. Tämä toimii myös tekstimerkkien kohdalla. {”a”.. ”d”} tuottaa listan {”a”, ”b”, ”c”, ”d”}.

Voit myös laatia listan, jossa ei ole yhtään kohtaa, {} on tyhjä lista.

Koska listat ovat järjestettyjä, voimme viitata listan kohtiin nollaan perustuvalla indeksiluvulla. {1, 2, 3}{2} evaluoituu arvoksi 3, koska tämä on listan 2. kohde (nollaindeksiin perustuen).

Tietueet

Tietue on järjestetty kenttien sarja. Jokainen kenttä koostuu kentän nimestä, joka yksilöi kentän yksikäsitteisesti, ja kentän arvosta, joka voi olla minkä tahansa tyyppinen arvo.

Tietueen voi määritellä käyttämällä hakasulkeita. on tietue, jossa on kaksi kenttää. Tietueen ensimmäisen kentän kentän nimi on FirstName ja arvo on ”John”. Tietueen toisen kentän kentän nimi on Ikä ja arvo 38.

Tietueiden tietueet ovat myös mahdollisia, ] on tietue, jossa on yksi kenttä, jonka kentän nimi on Henkilö ja kentän arvo, joka on tietue.

Tyhjät tietueet ovat myös mahdollisia, on tyhjä tietue.

Voit viitata tietueen kentän arvoon sen kentän nimellä. evaluoituu ”John”.

Taulukot

Taulukko on järjestetty rivijono, jossa jokainen rivi on lista.

Taulukoita voi muodostaa vain sisäisen funktion avulla. Voit rakentaa taulukon käyttämällä #taulukko()-funktiota sarakeotsikoiden luettelosta ja rivien luettelosta.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) luo taulukon, jossa on 2 saraketta, 3 riviä ja sarakeotsikkoina kirjaimet ja numerot.

On mahdollista luoda tyhjä taulukko käyttämällä tyhjiä luetteloita #taulukko()-intrinsic-funktiossa. #taulukko({}, {}) tuottaa tyhjän taulukon.

Voit viitata mihin tahansa arvoon taulukossa nollaan perustuvalla rivi-indeksillä ja sarakeotsikon nimellä. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} evaluoituu arvoksi ”C”, koska tämä on (nollaindeksiin perustuva) Letters-sarakkeen 2. rivi.

Lausekkeet

Lausekkeet ovat mitä tahansa, joka voidaan evaluoida arvoksi. Tämä pätee myös itse arvoihin. Esimerkiksi lauseke 1 evaluoituu arvoon 1.

Vaikka yleensä ajatellaan, että lausekkeet koostuvat monimutkaisemmista operaatioista tai funktioista.

Esimerkiksi:

Lauseke 1 + 1 evaluoituu arvoon 2.
Lauseke 3 > 2 evaluoituu arvoon true.
Lauseke ”Hello ” & ”World” evaluoituu muotoon ”Hello World”.
Lauseke Text.Upper(”Hello World”) evaluoituu muotoon ”HELLO WORLD”.

Operaattorit

M-koodissa on vakiokirjaston lisäksi myös lista erikoisfunktioita, joita kutsutaan operaattoreiksi. Nämä ottavat täsmälleen kaksi arvoa (tai lausekkeita, jotka evaluoituvat arvoksi) ja palauttavat yhden arvon.

Aritmeettiset operaattorit

M-koodin mukana tulee aritmeettiset perusoperaattorit, joita odotat ja joihin olet tottunut tavallisesta Excelistä +, -, * ja /. Näiden avulla voit vastaavasti lisätä, vähentää, kertoa ja jakaa arvoja.

Nämä operaattorit soveltuvat moniin muihinkin eri tyyppisiin arvoihin kuin pelkkiin numeroihin. Voit esimerkiksi lisätä päivämäärään keston.

#päivämäärä(2018,12,25) + #kesto(7, 0, 0, 0, 0) saadaan arvoksi 2019-01-01.

Vertailu

Voit verrata arvoja M-koodissa käyttämällä vertailuoperaattoreita <, >, <=, >=, =, <>.

x < y saa arvon true, jos x on pienempi kuin y.
x > y saa arvon true, jos x on suurempi kuin y.
x <= y on tosi, jos x on pienempi tai yhtä suuri kuin y.
x >= y on tosi, jos x on suurempi tai yhtä suuri kuin y.
x = y arvottuu todeksi, jos x on yhtä suuri kuin y.
x <> y arvottuu todeksi, jos x ei ole yhtä suuri kuin y.

Näitä voidaan käyttää erityyppisten arvojen kanssa. Voit esimerkiksi verrata kahta listaa yhtäläisyysoperaattorilla.

{1,2,3,4} = {1,2,3} evaluoituu vääräksi, koska listat eivät ole samat.

Ketjuttaminen ja yhdistäminen

Voit ketjuttaa tekstiä ja yhdistää listoja, tietueita ja taulukoita ampersand-operaattorilla &.

Esimerkiksi:

"Hello " & "World" evaluoituu muotoon: ”Hei maailma”.

{1,2,3} & {3,4,5} evaluoituu muotoon {1,2,3,3,4,5}.

Looginen

Voit suorittaa operaatioita Boolen arvoille (tai lausekkeille, jotka evaluoituvat Boolen arvoihin) operaattoreilla not, and ja tai.

Koodin kommentointi

Kuten mistä tahansa ohjelmointikielestä voi odottaa, koodiin voi lisätä kommentteja.

M-koodissa on mahdollista käyttää kahdenlaisia kommentteja. Yksiriviset kommentit ja moniriviset kommentit.

Yksiriviset kommentit

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

Yksirivinen kommentti voidaan luoda edeltämällä kommenttia kahdella etuviivamerkillä //. Kaikki, mikä on samalla rivillä ennen tätä, tulkitaan M-koodiksi, kaikki sen jälkeinen tulkitaan kommentiksi.

Moniriviset kommentit

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

Monirivinen kommentti voidaan luoda sijoittamalla kommentti /*- ja */ -merkkien väliin. Kaikki näiden ulkopuolella oleva tulkitaan M-koodiksi. Kaikki näiden välissä oleva tulkitaan kommentiksi.

Let-lause

Let-lauseen avulla joukko arvoja voidaan arvioida ja määrittää muuttujien nimiin, joita sitten käytetään myöhemmässä lausekkeessa, joka seuraa in-lauseen jälkeen.

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

Tämä lauseke koostuu kolmesta lausekkeesta, jotka arvioidaan let-lauseen jälkeen. Jokainen lauseke on erotettu toisistaan pilkulla lukuun ottamatta viimeistä lauseketta ennen in-lauseketta. Tässä esimerkissä koko let- ja in-lausekkeen arvoksi tulee 3.

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

Saatat ajatella, että let-lauseen sisällä olevien lausekkeiden on oltava siinä järjestyksessä, missä ne on arvioitava. Näin ei kuitenkaan ole! Yllä oleva koodi on täysin kelvollinen ja evaluoi myös tulokseksi 3. M-koodin evaluoija laskee automaattisesti tarvittavan laskemisjärjestyksen lausekkeiden riippuvuuksien perusteella.

M-koodin lukeminen on luonnollisesti helpompaa, jos se on kirjoitettu evaluointijärjestyksessä, mutta sillä on myös toinen etu. Lausekkeet näkyvät erillisinä vaiheina Applied Steps -ikkunassa. Kun lausekkeet kirjoitetaan järjestyksessä, ne näkyvät yhtenä yhdistettynä askeleena.

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

Voit evaluoida lausekkeita myös let… in… -lausekkeen in-osan sisällä.

Muuttujien nimet

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

Voit antaa lausekkeille lähes minkä tahansa nimen käyttämällä #”” -merkkejä. Voit käyttää jopa välilyönti- ja muita erikoismerkkejä. Varattujen avainsanojen käyttäminen on ainoa poikkeus.

Muuttujien nimet näkyvät kyselyeditorin Sovelletuissa vaiheissa, joten välilyöntimerkkien käyttäminen on loistava ominaisuus.

Each-lausekkeet

Each-lauseke on lyhenne sellaisten funktioiden ilmoittamiselle, jotka ottavat yhden parametrin, jonka nimi on _ (alleviivaus).

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

Tässä esimerkissä luomme uuden sarakkeen, joka kertoo Numbers-sarakkeen kahdella jokaisella rivillä.

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

Voidaan luoda sama kysely käyttämällä underscore-syntaksia, joka vastaa semanttisesti each-lauseketta. Molemmat kyselyt toimivat samalla tavalla.

If Then Else -lausekkeet

M-koodi on muihin kieliin verrattuna melko niukkaa loogisten lausekkeiden osalta. Käytettävissä ei ole select case- tai loop-lauseita. Käytettävissä on vain if… then… else… -lausekkeita.

if then else

Syntaksi on suoraviivainen ja samanlainen kuin useimmissa muissa ohjelmointikielissä. Se voi esiintyä kokonaan yhdellä rivillä tai se voidaan esittää erillisillä riveillä lukemisen helpottamiseksi.

Try Otherwise -lausekkeet

Virheitä voi tapahtua, kun yritetään suorittaa operaatioita, jotka vaativat tietyntyyppisiä tietoja. Jos esimerkiksi yritetään kertoa luku tekstiarvolla, syntyy virhe.

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

Virheitä voidaan välttää käyttämällä try… otherwise… -lauseketta.

Siten vältät virheet kyselyn tuloksissa ja voit korvata virheet millä tahansa arvolla tai lausekkeella.

Funktiot

Funktio on kuvaus parametrin arvojen joukosta arvoon. Standardikirjaston funktioiden ohella M-koodin avulla voit luoda omia funktioita.

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

Tämä kysely määrittelee funktion, joka kertoo kaksi lukua. Tämän jälkeen kysely kutsuu ja arvioi funktiota arvoilla 2 ja 3, jolloin tulokseksi tulee 6.

Funktiot, joissa on valinnaisia parametreja

Funktioparametreja on kahdenlaisia, vaadittu parametri ja valinnainen parametri.

Velvoitettuja parametreja on aina määriteltävä, kun funktiota kutsutaan.

Valinnaisia parametreja ei tarvitse määritellä, kun funktiota kutsutaan. Jos valinnainen parametri puuttuu, funktiolle annettu arvo on nolla.

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

Tällä funktiolla on valinnainen toinen argumentti. Tämän jälkeen kysely kutsuu ja arvioi funktiota käyttäen vain vaadittua parametria, jonka arvo on 2. Huomaa, että funktion on otettava huomioon, että y on nolla, sillä muuten puuttuva valinnainen argumentti voisi johtaa siihen, että funktio arvioi virheen.

Rekursiiviset funktiot

On myös mahdollista kirjoittaa funktio, joka viittaa itseensä käyttämällä @-skopointioperaattoria.

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

Fibonaccin sekvenssi on esimerkkinä funktiosta, joka on määritelty rekursiivisesti. Jakson seuraava luku määritellään kahden edellisen luvun summana. Saadaksesi n:nnen luvun sinun on siis tiedettävä (n-1)s ja (n-2)s luku.

Tämä funktio löytää n:nnen Fibonaccin luvun laskemalla yhteen (n-1)s ja (n-2)s Fibonaccin luvut.

Kysely evaluoi 13:n, koska 13 on seitsemäs Fibonaccin luku.

Tässä on lisää hyödyllisiä esimerkkejä, joissa voit käyttää rekursiivisia funktioita kopioimaan Excelin TRIM-toimintoa ylimääräisten välilyöntien poistamiseksi sanojen väliltä tai irtotavarana etsiä ja korvata arvoja luettelon perusteella.

Kyselyfunktiot

Yllä olevissa esimerkeissä määriteltiin funktio kyselyn sisällä ja sitten kutsuttiin ja evaluoitiin funktiota kyselyn sisällä.

On myös mahdollista luoda kysely, joka on funktio (kyselyfunktio) ja jota voidaan kutsua ja evaluoida muista kyselyistä käsin.

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

Tämä on yleinen muoto, jota tarvitaan kyselyfunktioiden luomiseen. Huomaa, että tarvitset let… in… -lauseen sisällä let… in… -lauseen kyselyfunktiossa, jotta voit suorittaa useita vaiheita.

Johtopäätökset

Tehokysely on Excelin loistava ominaisuus, jonka avulla voit automatisoida ja yksinkertaistaa tietojen tuontia ja muuntamista.

Voit tehdä paljon pelkällä graafisella osoita ja napsauta -käyttöliittymällä ilman, että kosketat kertaakaan M-koodia. Mutta kun vaatimuksistasi tulee kehittyneempiä, saattaa tulla aika, jolloin sinun on muokattava luomaasi M-koodia tai kirjoitettava oma koodisi tyhjästä.

Mikä tahansa uusi ohjelmointikieli on lukukelvoton, jos et ensin tunne sen perusteita. Tämän oppaan avulla pääset toivottavasti alkuun M-koodin kanssa, jotta voit alkaa luoda edistyneempiä kyselyitä.