Introduktion til Power Query M Code

Power Query er et meget kraftfuldt værktøj til udtrækning og transformation af data, der er indbygget i Excel 2016 (eller nyere), Excel til Office 365 og Power BI.

Det findes under fanen Data i afsnittet Få & Transformér data på farvebåndet.

Det er meget kraftfuldt og også meget nemt at bruge, og forespørgselseditoren har en super intuitiv brugergrænseflade for en Excel-bruger. Mange transformationstrin kan nemt udføres fra power query editor-båndet, og du behøver ikke at kende nogen kode for at rense og forme dine data.

Bag kulisserne i den brugervenlige editor oversætter Excel hvert trin i din transformationsproces fra disse båndkommandoer til power query M-kodesproget.

Dette indlæg vil introducere dig til det grundlæggende i power query M-kodesproget og vil antage, at du allerede kender det grundlæggende i power query.

Indholdsfortegnelse

Hvad er M-kode?

M står for data Mash-up, da power query handler om at forbinde til forskellige datakilder og “Mashing” dem sammen.

M kode er sproget bag kulisserne i power query. Når du opretter en datatransformation i Power Query Editor UI, skriver Excel den tilsvarende M-kode til forespørgslen.

M er et funktionelt sprog, hvilket betyder, at det primært er skrevet med funktioner, der kaldes for at evaluere og returnere resultater. M-kode leveres med et meget stort bibliotek af foruddefinerede funktioner til rådighed, og du kan også oprette dine egne.

Hvor kan du skrive Power Query M-kode?

Hvis du vil begynde at skrive eller redigere M-kode, er du nødt til at vide, hvor du kan gøre det. Der er to steder, hvor det er muligt, nemlig i formellinjen eller i den avancerede editor.

Formellinjen

For hvert trin, der oprettes i editorens brugergrænseflade, kan du se den tilsvarende M-kode i formellinjen.

Hvis du ikke kan se formelbjælken, skal du gå til fanen Visning og sikre dig, at indstillingen Formelbjælke er markeret.

Du kan redigere M-koden for et hvilket som helst trin i en forespørgsel ved at klikke ind i formlen og redigere den eksisterende kode. Når du er færdig, kan du acceptere eventuelle ændringer ved enten at klikke på markeringen eller trykke på Enter. Du kan også kassere dine ændringer ved at klikke på X’et eller trykke på Esc.

Du kan også oprette helt nye trin i din forespørgsel med formelbjælken ved at klikke på fx-symbolet ud for formelbjælken. Dette vil oprette et nyt trin, der henviser til det foregående trin ved navn, og derefter kan du oprette enhver M-kode, du har brug for.

Den avancerede editor

Formellinjen viser kun M-koden for det aktuelt valgte trin i forespørgslen, men i den avancerede editor kan du se og redigere M-koden for hele forespørgslen.

Du kan åbne den avancerede editor fra to steder i editorbåndet. Fra enten fanen Hjem eller fanen Visning skal du trykke på knappen Avanceret editor.

Trods det “avancerede” navn er editoren den mest grundlæggende kodeeditor, du vil se, og den indeholder (endnu) ikke nogen intellisense-funktioner til automatisk komplettering, syntaksfremhævning eller automatisk formatering.

Den avancerede editor viser forespørgselsnavnet, viser M-koden for forespørgslen og viser en advarsel om eventuelle syntaksovertrædelser i M-koden. Det var det!

Standardfunktionsbibliotek

Da M-kode er et funktionelt sprog, handler det hele om funktionerne, og M-kode leveres med et stort bibliotek af foruddefinerede funktioner kaldet standardbiblioteket.

Informationer om alle de tilgængelige standardbiblioteksfunktioner findes på Microsofts webside Power Query M Reference, herunder funktionssyntaks og eksempler.

Standardbiblioteket kan også udforskes fra Power Query-editoren ved hjælp af nøgleordet #shared.

Når det er indtastet i formellinjen, kan du derefter udforske alle de tilgængelige funktioner ved at klikke på ordet Funktion til højre for funktionens navn. Du finder den samme syntaks og de samme eksempler som på referencewebsiden.

Case Sensitivity

En af de første ting, man skal være opmærksom på, når man skriver M-kode, er, at det er et sprog, der er case sensitive.

Det betyder, at x ikke er det samme som X, eller at “abc” ikke er det samme som “ABC”. Dette gælder for alle værdier, variabler, funktioner osv.

Udtryk og værdier i Power Query

Power query handler om udtryk og værdier.

Et udtryk er noget, der kan evalueres for at returnere en værdi i power query. 1 + 1 er et udtryk, der evalueres til værdien 2.

En værdi er et enkelt stykke data. Værdier kan være enkeltværdier som f.eks. tal, tekst, logiske værdier, nulværdier, binære værdier, datoer, tidspunkter, datotidspunkter, datotidszoner eller varigheder.

Værdier kan også have mere komplekse strukturer end enkeltværdier som f.eks. lister, poster og tabeller. Du kan også have værdier, der er en kombination af lister, poster og tabeller. Lister af lister, tabeller af lister, tabeller af tabeller osv… er alle mulige værdistrukturer.

Enkle bogstavværdier

Enkle bogstavværdier er den grundlæggende byggesten for alle de andre værdier.

123,45 er en talværdi.
“Hello World!” er en tekstværdi.
true er en logisk værdi.
null repræsenterer fraværet af en værdi.

Enkeltstående intrinsiske værdier

Intrinsiske værdier konstrueres ved hjælp af de forskellige intrinsiske funktioner.

#time(timer, minutter, sekunder)
#date(år, måneder, dage)
#datetime(år, måneder, dage, timer, minutter, sekunder)
#datetimezone( år, måneder, dage, timer, minutter, sekunder, offset-hours, offset-minutes)
#duration(dage, timer, minutter, sekunder)

For eksempel skal du konstruere datoen 2018-12-31 ved hjælp af den indbyggede funktion #date(2018, 12, 31) for at konstruere den.

Strukturerede værdier

Lister

En liste er en ordnet rækkefølge af værdier.

Du kan definere en liste ved hjælp af parenteser. {1, 2, 3} er en liste, der indeholder tallene 1, 2 og 3. Da rækkefølgen er vigtig, er dette ikke den samme liste som {3, 2, 1}.

{“Hello”, “World”} er en liste, der indeholder teksten “Hello” og “World”.

Lister af lister er også mulige, så {{1, 2}, {3, 4, 5}} er en liste af to lister. Den første liste indeholder tallet 1 og 2, og den anden liste indeholder tallene 3, 4 og 5.

Du kan oprette sekventielle lister ved hjælp af formatet {x..y}. {2..5} vil give listen {2, 3, 4, 5}. Dette virker også for teksttegn. {“a”… “d”} vil producere listen {“a”, “b”, “c”, “d”}.

Du kan også have en liste uden elementer, {} er den tomme liste.

Da lister er ordnede, kan vi referere til elementer i listen med et nulbaseret indeksnummer. {1, 2, 3}{2} vil blive evalueret til 3, da dette er det 2. element i listen (baseret på et nul-indeks).

Records

En Record er en ordnet sekvens af Fields (felter). Hvert felt består af et feltnavn, som entydigt identificerer feltet, og en feltværdi, som kan være en hvilken som helst type værdi.

Du kan definere en post ved hjælp af firkantede parenteser. er en post med to felter. Det første felt i posten har et feltnavn FirstName og værdien “John”. Det andet felt i posten har feltnavnet Alder og værdien 38.

Rekorder af poster er også mulige, ] er en post med ét felt med feltnavnet Person og en feltværdi, som er en post.

Tomme poster er også mulige, er den tomme post.

Du kan referere til feltværdien i en post ved dens feltnavn. vil evaluere til “John”.

Tabeller

En tabel er en ordnet rækkefølge af rækker, hvor hver række er en liste.

Tabeller kan kun konstrueres ved hjælp af en indbygget funktion. Du kan konstruere en tabel ved hjælp af funktionen #table() ud fra en liste med kolonneoverskrifter og en liste med rækker.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) vil oprette en tabel med 2 kolonner, 3 rækker og kolonneoverskrifterne Bogstaver og Tal.

Det er muligt at oprette en tom tabel ved hjælp af tomme lister i den intrinsiske funktion #table(). #table({}, {}) vil producere en tom tabel.

Du kan referere til enhver værdi i en tabel med det nulbaserede rækkeindeks og navnet på kolonneoverskriften. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} vil blive evalueret til “C”, da dette er den 2. række (baseret på et nul-indeks) i kolonnen Bogstaver.

Udtryk

Udtryk er alt, der kan evalueres til en værdi. Dette gælder for selve værdierne. F.eks. evalueres udtrykket 1 til værdien 1.

Men man tænker typisk på udtryk som værende sammensat af mere komplekse operationer eller funktioner.

For eksempel:

Udtrykket 1 + 1 evalueres til værdien 2.
Udtrykket 3 > 2 evalueres til sandt.
Udtrykket “Hello ” & “World” evaluerer til “Hello World”.
Udtrykket Text.Upper(“Hello World”) evaluerer til “HELLO WORLD”.

Operatorer

Sammen med standardbiblioteket har M-kode også en liste over specielle funktioner, der kaldes operatører. Disse tager præcis to værdier (eller udtryk, der evalueres til en værdi) og returnerer en enkelt værdi.

Aritmetik

M-kode leveres med de grundlæggende aritmetiske operatorer, som du forventer og er vant til fra almindelig Excel +, -, * og /. Disse giver dig mulighed for henholdsvis at addere, subtrahere, gange og dividere værdier.

Disse kan bruges med forskellige andre typer af værdier end blot tal. Du kan f.eks. tilføje en varighed til en dato.

#date(2018,12,25) + #duration(7, 0, 0, 0, 0) vil blive evalueret til 2019-01-01.

Sammenligning

Du kan sammenligne værdier i M-kode ved hjælp af sammenligningsoperatorerne <, >, <=, >=, =, <>.

x < y vil blive evalueret til sandt, hvis x er mindre end y.
x > y vil blive evalueret til sandt, hvis x er større end y.
x <= y vil blive evalueret til sandt, hvis x er mindre end eller lig med y.
x >= y vil blive evalueret til sandt, hvis x er større end eller lig med y.
x = y vil blive evalueret til sandt, hvis x er lig med y.
x <> y vil blive evalueret til sandt, hvis x ikke er lig med y.

Disse kan bruges med forskellige typer af værdier. Du kan f.eks. sammenligne to lister med operatoren equal.

{1,2,3,4} = {1,2,3} vil blive evalueret til falsk, da listerne ikke er ens.

Sammenkædning og sammenlægning

Du kan sammenkæde tekst og sammenlægge lister, poster og tabeller ved hjælp af operatoren ampersand &.

Til eksempel:

"Hello " & "World" vil blive evalueret til “Hello World”.

{1,2,3} & {3,4,5} vil blive evalueret til {1,2,3,3,3,4,5}.

Logisk

Du kan udføre operationer på boolske værdier (eller udtryk, der evalueres til boolske værdier) med operatørerne not, og og og eller.

Kommentarer til kode

Som du kan forvente af ethvert programmeringssprog, er det muligt at tilføje kommentarer til din kode.

Der er to typer kommentarer mulige i M-kode. Enkeltlinjekommentarer og flerlinjekommentarer.

Enkeltlinjekommentarer

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

En enkeltlinjekommentar kan oprettes ved at sætte to skråstregtegn // foran kommentaren. Alt på samme linje før dette vil blive fortolket som M-kode, alt efter dette vil blive fortolket som en kommentar.

Kommentarer på flere linjer

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

En kommentar på flere linjer kan oprettes ved at placere kommentaren mellem /* og */-tegnene. Alt uden for disse vil blive fortolket som M-kode. Alt mellem disse vil blive fortolket som en kommentar.

Let-erklæring

Med let-erklæringen kan et sæt værdier evalueres og tildeles til variabelnavne, som derefter anvendes i et efterfølgende udtryk, der følger efter in-erklæringen.

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

Dette udtryk består af tre udtryk, der evalueres efter let-erklæringen. Hvert udtryk er adskilt af et komma, bortset fra det sidste udtryk før in-erklæringen. I dette eksempel vil hele let- og in-udtrykket blive evalueret til 3.

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

Du tænker måske, at udtrykkene i en let-erklæring skal optræde i den rækkefølge, de skal evalueres. Dette er ikke tilfældet! Ovenstående kode er fuldt ud gyldig og vil også evaluere til 3. M-kodeevaluatoren vil automatisk beregne den nødvendige rækkefølge af beregninger på baggrund af udtryksafhængigheder.

Det er naturligvis lettere for en person at læse M-koden, hvis den er skrevet i evalueringsrækkefølge, men der er også en anden fordel. Udtryk vil blive vist som separate trin i vinduet Anvendte trin. Når de er skrevet i forkert rækkefølge, vises udtrykkene som ét samlet trin.

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

Du kan også evaluere udtryk inden for in-delen af et let… i… udtryk.

Variabelnavne

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

Du kan tildele dine udtryk stort set alle navne ved hjælp af #””-tegnene. Du kan endda bruge mellemrumstegn og andre specialtegn. Brug af reserverede nøgleord er den eneste undtagelse.

Variabelnavnene er det, der vises i de anvendte trin i forespørgselseditoren, så det er en god funktion at kunne bruge mellemrumstegn.

Each-udtryk

Et each-udtryk er en forkortelse til at deklarere funktioner, der tager en enkelt parameter med navnet _ (understregning).

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

I dette eksempel opretter vi en ny kolonne, som multiplicerer kolonnen Tal med 2 for hver række.

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

Vi kan oprette den samme forespørgsel ved hjælp af syntaksen underscore, som semantisk set svarer til each-udsagnet. Begge forespørgsler vil fungere på samme måde.

If Then Else Statements

M-kode er ret sparsom i forhold til andre sprog, når det gælder logiske udtryk. Der er ingen select case- eller loop-udsagn til rådighed. Der er kun et if… then… else… udtryk til rådighed.

if then else

Syntaksen er ligetil og ligner de fleste andre programmeringssprog. Den kan vises på én linje, eller den kan præsenteres på separate linjer for at lette læsningen.

Try Otherwise-statements

Fejl kan opstå, når man forsøger at udføre operationer, der kræver bestemte typer af data. Hvis man f.eks. forsøger at gange et tal med en tekstværdi, vil det resultere i en fejl.

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

Fejl kan undgås ved at bruge try… otherwise… udtrykket.

Derved undgås fejl i dine forespørgselsresultater, og du kan erstatte fejl med en hvilken som helst værdi eller et hvilket som helst udtryk.

Funktioner

En funktion er en afbildning fra et sæt af parameterværdier til en værdi. Sammen med funktionerne i standardbiblioteket giver M-kode dig mulighed for at oprette dine egne funktioner.

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

Denne forespørgsel definerer en funktion, der multiplicerer to tal. Derefter kalder og evaluerer forespørgslen funktionen med værdierne 2 og 3, som evalueres til 6.

Funktioner med valgfrie parametre

Der findes to typer funktionsparametre, en krævet parameter og en valgfri parameter.

Krævede parametre skal altid angives, når en funktion påberåbes.

Optionelle parametre behøver ikke at blive angivet, når en funktion påberåbes. Hvis den valgfrie parameter mangler, vil den værdi, der overføres til funktionen, være nul.

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

Denne funktion har et valgfrit andet argument. Derefter kalder og evaluerer forespørgslen funktionen ved kun at bruge den obligatoriske parameter med værdien 2, som evalueres til 2. Bemærk, at funktionen skal tage højde for, at y er nul, ellers kan et manglende valgfrit argument resultere i, at funktionen evalueres til en fejl.

Rekursive funktioner

Det er også muligt at skrive en funktion, der refererer til sig selv ved at bruge @ scoping-operatoren.

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

Fibonacci-sekvensen er et eksempel på en funktion, der er defineret rekursivt. Det næste tal i sekvensen er defineret som summen af de to foregående tal. Så for at få det niende tal skal du kende det (n-1)-te og (n-2)-te tal.

Denne funktion finder det niende Fibonacci-tal ved at addere det (n-1)-te og (n-2)-te Fibonacci-tal.

Forsøgningen evalueres til 13, da 13 er det syvende Fibonacci-tal.

Her er nogle flere nyttige eksempler, hvor du kan bruge rekursive funktioner til at replikere Excels TRIM-funktion til at fjerne overskydende mellemrum mellem ord eller bulk finde og erstatte værdier baseret på en liste.

Query-funktioner

De ovenstående eksempler definerede en funktion i en forespørgsel og kaldte og evaluerede derefter funktionen i forespørgslen.

Det er også muligt at oprette en forespørgsel, der er en funktion (en forespørgselsfunktion), og som kan kaldes og evalueres fra andre forespørgsler.

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

Dette er det generelle format, der er nødvendigt for at oprette en forespørgselsfunktion. Bemærk, at du skal bruge en let… i… erklæring inden for let… i… erklæringen i forespørgselsfunktionen for at kunne udføre flere trin.

Konklusioner

Power query er en fantastisk funktion i Excel, der kan hjælpe dig med at automatisere og forenkle din dataimport og -transformation.

Du kan gøre meget ved hjælp af blot den grafiske peg-og-klik-grænseflade uden nogensinde at røre M-koden. Men efterhånden som dine krav bliver mere avancerede, kan der komme et tidspunkt, hvor du bliver nødt til at redigere den M-kode, du har oprettet, eller skrive din egen kode fra bunden.

Alle nye programmeringssprog er ulæselige, hvis du ikke kender de grundlæggende ting først. Denne vejledning vil forhåbentlig få dig i gang med M-kode, så du kan begynde at oprette mere avancerede forespørgsler.