Introduktion till Power Query M Code

Power Query är ett mycket kraftfullt verktyg för dataextraktion och omvandling som finns inbyggt i Excel 2016 (eller senare), Excel för Office 365 och Power BI.

Det finns på fliken Data i avsnittet Get & Transform Data i bandet.

Det är mycket kraftfullt men också mycket lätt att använda och frågeeditorn har ett superintuitivt användargränssnitt för en Excel-användare. Många omvandlingssteg kan enkelt utföras från bandet i power query editor och du behöver inte kunna någon kod för att rensa och forma dina data.

Hos den användarvänliga redigeraren översätter Excel varje steg i din omvandlingsprocess från dessa bandkommandon till kodspråket power query M.

Detta inlägg kommer att introducera dig till grunderna i power query M-kodspråket och förutsätter att du redan känner till grunderna i power query.

Innehållsförteckning

Vad är M-kod?

M står för data Mash-up, eftersom power query handlar om att ansluta till olika datakällor och ”Mashing” dem.

M-kod är språket bakom kulisserna i power query. När du skapar en datatransformation i Power Query Editor UI skriver Excel motsvarande M-kod för frågan.

M är ett funktionellt språk, vilket innebär att det främst skrivs med funktioner som anropas för att utvärdera och returnera resultat. M-kod levereras med ett mycket stort bibliotek av fördefinierade funktioner som finns tillgängliga och du kan också skapa egna.

Var kan du skriva Power Query M-kod?

Om du vill börja skriva eller redigera M-kod måste du veta var du kan göra det. Det finns två ställen där det är möjligt, i formellfältet eller i den avancerade redigeraren.

Formellfältet

För varje steg som skapas i redigeringsgränssnittet kan du se motsvarande M-kod i formellfältet.

Om du inte ser formelfältet går du till fliken Visa och ser till att alternativet Formelfältet är markerat.

Du kan redigera M-koden för varje steg i en fråga genom att klicka i formeln och redigera den befintliga koden. När du är klar kan du godkänna eventuella ändringar genom att antingen klicka på krysset eller trycka på Enter. Du kan också kasta ändringarna genom att klicka på X eller trycka på Esc.

Du kan också skapa helt nya steg i din fråga med hjälp av formelfältet genom att klicka på fx-symbolen bredvid formelfältet. Detta skapar ett nytt steg som hänvisar till det föregående steget med namnet och sedan kan du skapa vilken M-kod som helst.

Den avancerade redigeraren

Formellisten visar bara M-koden för det för tillfället valda steget i frågan, men det är i den avancerade redigeraren som du kan visa och redigera M-koden för hela frågan.

Du kan öppna den avancerade redigeraren från två ställen i redigeringsbandet. Från antingen fliken Hem eller fliken Visa trycker du på knappen Avancerad redigerare.

Trots den ”avancerade” benämningen är redigeraren den mest grundläggande kodredigeraren du kommer att se och innehåller (ännu) inga intellisense-funktioner för automatisk komplettering, syntaxmarkering eller automatisk formatering.

Den avancerade redigeraren visar fråknamnet, visar M-koden för frågan och visar en varning om eventuella syntaxbrott i M-koden. Det var allt!

Standardfunktionsbibliotek

Då M-kod är ett funktionellt språk handlar allt om funktionerna och M-kod levereras med ett stort bibliotek med fördefinierade funktioner som kallas standardbiblioteket.

Information om alla tillgängliga standardbiblioteksfunktioner finns på Microsofts webbsida Power Query M Reference, inklusive funktionssyntax och exempel.

Standardbiblioteket kan också utforskas från Power Query Editor med hjälp av nyckelordet #shared.

När det skrivs in i formellfältet kan du sedan utforska alla tillgängliga funktioner genom att klicka på ordet Funktion till höger om funktionens namn. Du hittar samma syntax och exempel som på referenswebbsidan.

Storlekskänslighet

En av de första sakerna som någon måste vara medveten om när man skriver M-kod är att det är ett språk med storlekskänslighet.

Detta innebär att x inte är samma sak som X eller att ”abc” inte är samma sak som ”ABC”. Detta gäller för alla värden, variabler, funktioner etc.

Uttryck och värden i Power Query

Power Query handlar om uttryck och värden.

Ett uttryck är något som kan utvärderas för att returnera ett värde i Power Query. 1 + 1 är ett uttryck som utvärderas till värdet 2.

Ett värde är en enskild data. Värden kan vara enskilda värden som t.ex. siffror, text, logiska värden, nollvärden, binära värden, datum, tid, datumtid, datumtidszon eller varaktigheter.

Värden kan också ha mer komplexa strukturer än enskilda värden, t.ex. listor, poster och tabeller. Du kan också ha värden som är en kombination av listor, poster och tabeller. Listor med listor, tabeller med listor, tabeller med tabeller etc… är alla möjliga värdestrukturer.

Enkla bokstavsvärden

Enkla bokstavsvärden är den grundläggande byggstenen för alla andra värden.

123,45 är ett siffervärde.
”Hello World!” är ett textvärde.
true är ett logiskt värde.
null representerar frånvaron av ett värde.

Enskilda inneboende värden

Inneboende värden konstrueras med hjälp av olika inneboende funktioner.

#time(timmar, minuter, sekunder)
#date(år, månader, dagar)
#datetime(år, månader, dagar, timmar, minuter, sekunder)
#datetimezone( år, månader, dagar, timmar, minuter, sekunder, offset-hours, offset-minutes)
#duration(days, hours, minutes, seconds)

För att konstruera datumet 2018-12-31 måste du till exempel konstruera det med hjälp av den inneboende funktionen #date(2018, 12, 31).

Strukturerade värden

Listor

En lista är en ordnad sekvens av värden.

Du kan definiera en lista med hjälp av parenteser. {1, 2, 3} är en lista som innehåller siffrorna 1, 2 och 3. Eftersom ordningen är viktig är detta inte samma lista som {3, 2, 1}.

{”Hello”, ”World”} är en lista som innehåller texten ”Hello” och ”World”.

Listor av listor är också möjliga, så {{1, 2}, {3, 4, 5}} är en lista av två listor. Den första listan innehåller siffrorna 1 och 2 och den andra listan innehåller siffrorna 3, 4 och 5.

Du kan skapa sekventiella listor med hjälp av formatet {x..y}. {2..5} ger listan {2, 3, 4, 5}. Detta fungerar även för texttecken. {”a”… ”d”} ger listan {”a”, ”b”, ”c”, ”d”}.

Du kan också ha en lista utan objekt, {} är den tomma listan.

Då listor är ordnade kan vi referera till objekt i listan med ett nollbaserat indexnummer. {1, 2, 3}{2} kommer att utvärderas till 3 eftersom detta är det andra objektet i listan (baserat på ett nollindex).

Records

En Record är en ordnad sekvens av Fields. Varje fält består av ett fältnamn som unikt identifierar fältet och ett fältvärde som kan vara vilken typ av värde som helst.

Du kan definiera en post med hjälp av hakparenteser. är en post med två fält. Det första fältet i posten har fältnamnet FirstName och värdet ”John”. Det andra fältet i posten har fältnamnet Ålder och värdet 38.

Poster av poster är också möjliga, ] är en post med ett fält med fältnamnet Person och ett fältvärde som är en post.

Tomma poster är också möjliga, är den tomma posten.

Du kan referera till fältvärdet i en post genom dess fältnamn. kommer att utvärderas till ”John”.

Tabeller

En tabell är en ordnad sekvens av rader där varje rad är en lista.

Tabeller kan endast konstrueras med en inneboende funktion. Du kan konstruera en tabell med hjälp av funktionen #table() från en lista med kolumnrubriker och en lista med rader.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) skapar en tabell med 2 kolumner, 3 rader och kolumnrubrikerna bokstäver och siffror.

Det är möjligt att skapa en tom tabell med hjälp av tomma listor i den inneboende funktionen #table(). #table({}, {}) skapar en tom tabell.

Du kan referera till vilket värde som helst i en tabell med det nollbaserade radindexet och namnet på kolumnrubriken. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} kommer att utvärderas till ”C” eftersom detta är den andra raden (baserat på ett nollindex) i kolumnen Bokstäver.

Uttryck

Uttryck är allt som kan utvärderas till ett värde. Detta gäller för själva värdena. Till exempel utvärderar uttrycket 1 till värdet 1.

Men man tänker vanligen på uttryck som består av mer komplexa operationer eller funktioner.

Till exempel:

Uttrycket 1 + 1 utvärderar till 2.
Uttrycket 3 > 2 utvärderar till sant.
Uttrycket ”Hello ” & ”World” utvärderas till ”Hello World”.
Uttrycket Text.Upper(”Hello World”) utvärderas till ”HELLO WORLD”.

Operatorer

Samtidigt som standardbiblioteket har M-koden också en lista med specialfunktioner som kallas operatörer. Dessa tar exakt två värden (eller uttryck som utvärderas till ett värde) och returnerar ett enda värde.

Aritmetik

M-kod har de grundläggande aritmetiska operatörerna som du förväntar dig och som du är van vid från regular Excel +, -, * och /. Med hjälp av dessa kan du addera, subtrahera, multiplicera respektive dividera värden.

Dessa kan användas med olika typer av värden som är andra än bara siffror. Du kan till exempel lägga till en varaktighet till ett datum.

#date(2018,12,25) + #duration(7, 0, 0, 0, 0) kommer att utvärderas till 2019-01-01.

Jämförelse

Du kan jämföra värden i M-kod med hjälp av jämförelseoperatorerna <, >, <=, >=, =, <>.

x < y utvärderas till sant om x är mindre än y.
x > y utvärderas till sant om x är större än y.
x <= y kommer att utvärderas till sant om x är mindre än eller lika med y.
x >= y kommer att utvärderas till sant om x är större än eller lika med y.
x = y blir sann om x är lika med y.
x <> y blir sann om x inte är lika med y.

Dessa kan användas med olika typer av värden. Du kan till exempel jämföra två listor med operatorn equal.

{1,2,3,4} = {1,2,3} kommer att utvärderas till falskt eftersom listorna inte är likadana.

Sammankoppling och sammanslagning

Du kan sammankoppla text och sammanfoga listor, poster och tabeller med hjälp av ampersand &-operatorn.

Till exempel:

"Hello " & "World" kommer att utvärderas till ”Hello World”.

{1,2,3} & {3,4,5} kommer att utvärderas till {1,2,3,3,3,4,5}.

Logisk

Du kan utföra operationer på boolska värden (eller uttryck som utvärderas till boolska värden) med operatörerna not, och och och eller.

Kommentera kod

Som du kan förvänta dig av alla programmeringsspråk är det möjligt att lägga till kommentarer till din kod.

Det finns två typer av kommentarer möjliga i M-kod. Enradiga kommentarer och flerradiga kommentarer.

Enradiga kommentarer

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

En enradig kommentar kan skapas genom att kommentaren föregås av två snedstreck //. Allt på samma rad före detta kommer att tolkas som M-kod, allt efter detta kommer att tolkas som en kommentar.

Flerradskommentarer

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

En flerradskommentar kan skapas genom att placera kommentaren mellan tecknen /* och */. Allt utanför dessa tolkas som M-kod. Allt mellan dessa tolkas som en kommentar.

Let-uttalande

Med let-uttalandet kan en uppsättning värden utvärderas och tilldelas variabelnamn som sedan används i ett efterföljande uttryck som följer på in-uttalandet.

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

Detta uttryck består av tre uttryck som utvärderas efter let-uttalandet. Varje uttryck är separerat med ett kommatecken utom det sista före in-satsen. I det här exemplet kommer hela let- och in-uttrycket att utvärderas till 3.

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

Du kanske tänker att uttrycken i ett let-uttalande måste visas i den ordning de ska utvärderas. Detta är inte fallet! Ovanstående kod är helt giltig och kommer också att utvärdera till 3. M-kodutvärderaren kommer automatiskt att beräkna i vilken ordning beräkningarna behövs baserat på uttrycksberoenden.

Det är uppenbarligen lättare för en person att läsa M-koden om den är skriven i utvärderingsordning, men det finns också en annan fördel. Uttryck kommer att visas som separata steg i fönstret Tillämpade steg. När de skrivs i fel ordning kommer uttrycken att visas som ett kombinerat steg.

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

Du kan också utvärdera uttryck inom in delen av ett let… in… uttryck.

Variabelnamn

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

Du kan tilldela dina uttryck i stort sett vilket namn som helst med hjälp av #”” tecknen. Du kan till och med använda mellanslagstecken och andra specialtecken. Att använda reserverade nyckelord är det enda undantaget.

Variabelnamnen är det som kommer att visas i de tillämpade stegen i frågeeditorn, så att kunna använda mellanslagstecken är en bra funktion.

Each Uttalanden

Ett Each-uttryck är ett kort förkortat uttryck för att deklarera funktioner som tar en enda parameter med namnet _ (understrykning).

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

I det här exemplet skapar vi en ny kolumn som multiplicerar kolumnen Numbers med 2 för varje rad.

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

Vi kan skapa samma fråga med hjälp av underscore-syntaxen som semantiskt sett är likvärdig med each-uttrycket. Båda frågorna kommer att fungera på samma sätt.

If Then Else Statements

M-kod är ganska sparsam jämfört med andra språk när det gäller logiska uttryck. Det finns inga select case- eller loop-utsagor tillgängliga. Det finns endast ett if… then… else… uttryck tillgängligt.

if then else

Syntaxen är enkel och liknar de flesta andra programmeringsspråk. Den kan visas helt på en rad, eller så kan den presenteras på separata rader för att underlätta läsningen.

Try Otherwise Statements

Fel kan uppstå när man försöker utföra operationer som kräver särskilda typer av data. Om man till exempel försöker multiplicera ett tal med ett textvärde uppstår ett fel.

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

Fel kan undvikas genom att använda uttrycket try… otherwise…..

Detta gör att du undviker fel i dina frågeresultat och kan ersätta fel med valfritt värde eller uttryck.

Funktioner

En funktion är en mappning från en uppsättning parametervärden till ett värde. Tillsammans med funktionerna i standardbiblioteket kan du med M-kod skapa egna funktioner.

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

Denna fråga definierar en funktion som multiplicerar två tal. Därefter anropar och utvärderar frågan funktionen med värdena 2 och 3 vilket utvärderar till 6.

Funktioner med valfria parametrar

Det finns två typer av funktionsparametrar, en obligatorisk parameter och en valfri parameter.

Obligatoriska parametrar måste alltid anges när en funktion åberopas.

Optionella parametrar behöver inte anges när en funktion åberopas. Om den valfria parametern saknas kommer det värde som skickas till funktionen att vara noll.

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

Denna funktion har ett valfritt andra argument. Därefter anropar och utvärderar frågan funktionen genom att endast använda den obligatoriska parametern med värdet 2, som utvärderas till 2. Lägg märke till att funktionen måste ta hänsyn till att y är noll, annars kan ett saknat valfritt argument leda till att funktionen utvärderas till ett fel.

Rekursiva funktioner

Det är också möjligt att skriva en funktion som hänvisar till sig själv med hjälp av @-operatorn för scoping.

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 är ett exempel på en funktion som är definierad rekursivt. Nästa tal i sekvensen definieras som summan av de två föregående talen. För att få fram det nionde talet måste du alltså känna till det (n-1)-te och (n-2)-te talet.

Denna funktion hittar det nionde Fibonacci-talet genom att addera det (n-1)-te och (n-2)-te Fibonacci-talet.

Förfrågan utvärderar till 13, eftersom 13 är det sjunde Fibonacci-talet.

Här är några fler användbara exempel där du kan använda rekursiva funktioner för att replikera Excels TRIM-funktion för att ta bort överflödiga mellanslag mellan ord eller för att hitta och ersätta värden baserat på en lista.

Frågefunktioner

De ovanstående exemplen definierade en funktion i en fråga och anropade och utvärderade sedan funktionen i frågan.

Det är också möjligt att skapa en fråga som är en funktion (en frågefunktion) och som kan anropas och utvärderas från andra frågor.

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

Det här är det allmänna formatet som behövs för att skapa en frågefunktion. Observera att du behöver ett låt… i… uttalande inom låt… i… uttalandet för frågefunktionen för att kunna utföra flera steg.

Slutsatser

Power query är en fantastisk funktion i Excel som kan hjälpa dig att automatisera och förenkla din dataimport och omvandling.

Du kan göra mycket med hjälp av bara det grafiska peka-och-klicka-gränssnittet utan att någonsin röra M-koden. Men när dina krav blir mer avancerade kan det komma en tid då du behöver redigera den M-kod du skapat eller skriva en egen från grunden.

Alla nya programmeringsspråk är oläsbara om du inte kan grunderna först. Den här guiden kommer förhoppningsvis att hjälpa dig att komma igång med M-koden så att du kan börja skapa mer avancerade frågor.