Power query is een zeer krachtig hulpprogramma voor gegevensextractie en -transformatie dat is ingebakken in Excel 2016 (of later), Excel voor Office 365 en Power BI.

Het is te vinden op het tabblad Gegevens in het gedeelte Get & Transform Data van het lint.

Het is zeer krachtig en ook zeer eenvoudig te gebruiken en de query-editor heeft een superintuïtieve gebruikersinterface voor een Excel-gebruiker. Veel transformatiestappen kunnen eenvoudig worden uitgevoerd vanuit het lint van de power query-editor en u hoeft geen code te kennen om uw gegevens op te schonen en vorm te geven.

Achter de schermen van de gebruiksvriendelijke editor vertaalt Excel elke stap in uw transformatieproces van die lintopdrachten naar de codetaal van de power query M.

Dit bericht laat u kennismaken met de basisbeginselen van de power query M-code-taal en gaat ervan uit dat u de basisbeginselen van power query al kent.

Inhoudsopgave

Wat is M-code?

De M staat voor data Mash-up, want bij Power Query gaat het erom verbinding te maken met verschillende gegevensbronnen en deze te “Mashen”.

M-code is de taal achter de schermen van Power Query. Wanneer u een gegevenstransformatie in de power query editor UI maakt, schrijft Excel de bijbehorende M-code voor de query.

M is een functionele taal, wat betekent dat het voornamelijk wordt geschreven met functies die worden aangeroepen om te evalueren en resultaten terug te geven. M-code wordt geleverd met een zeer grote bibliotheek van vooraf gedefinieerde functies beschikbaar en u kunt ook uw eigen.

Waar kunt u schrijven Power Query M Code?

Als u wilt beginnen met het schrijven of bewerken van M-code, zult u moeten weten waar u dit kunt doen. Er zijn twee plaatsen waar dit mogelijk is, in de formulebalk of in de geavanceerde editor.

De formulebalk

Voor elke stap die in de editor UI is gemaakt, kunt u de bijbehorende M-code in de formulebalk bekijken.

Als u de formulebalk niet ziet, gaat u naar het tabblad Beeld en controleert u of de optie Formulebalk is aangevinkt.

U kunt de M-code voor elke stap in een query bewerken door in de formule te klikken en de bestaande code te bewerken. Als u klaar bent, kunt u de wijzigingen accepteren door op het vinkje te klikken of op Enter te drukken. U kunt ook uw wijzigingen ongedaan maken door op de X te klikken of op Esc te drukken.

U kunt ook geheel nieuwe stappen in uw query maken met de formulebalk door op het fx symbool naast de formulebalk te klikken. Hierdoor wordt een nieuwe stap gemaakt die bij naam naar de vorige stap verwijst, waarna u elke gewenste M-code kunt maken.

De geavanceerde editor

De formulebalk toont alleen de M-code voor de op dat moment geselecteerde stap in de query, maar in de geavanceerde editor kunt u de M-code voor de gehele query bekijken en bewerken.

U kunt de geavanceerde editor openen vanaf twee plaatsen in het lint van de editor. Druk op het tabblad Start of Beeld op de knop Geavanceerde editor.

Ondanks de naam “geavanceerd” is de editor de meest eenvoudige code-editor die u zult zien en bevat deze (nog) geen functies voor automatisch aanvullen, syntax highlighting of automatische opmaak.

De geavanceerde editor geeft de naam van de query weer, toont de M-code voor de query en geeft een waarschuwing weer voor eventuele syntaxisovertredingen in de M-code. Dat is het!

Standaard Functie Bibliotheek

Omdat M-code een functionele taal is, draait het allemaal om de functies en M-code wordt geleverd met een grote bibliotheek van vooraf gedefinieerde functies, genaamd de standaard bibliotheek.

Informatie over alle beschikbare standaard bibliotheek functies is te vinden op Microsoft’s Power Query M Reference webpagina, inclusief functie syntaxis en voorbeelden.

De standaardbibliotheek kan ook worden verkend vanuit de Power Query-editor met behulp van het trefwoord #shared.

Wanneer u deze invoert in de formulebalk, kunt u vervolgens alle beschikbare functies verkennen door op het woord Function te klikken, rechts van de naam van de functie. U vindt dezelfde syntaxis en voorbeelden als op de referentie webpagina.

Case Sensitivity

Eén van de eerste dingen waar iemand zich bewust van moet zijn bij het schrijven van M-code is dat het een hoofdlettergevoelige taal is.

Dit betekent dat x niet hetzelfde is als X of “abc” niet hetzelfde is als “ABC”. Dit geldt voor alle waarden, variabelen, functies, enz.

Expressies en waarden in Power Query

Power query is alles over Expressies en waarden.

Een expressie is iets dat kan worden geëvalueerd om een waarde terug te keren in power query. 1 + 1 is een uitdrukking die evalueert tot de waarde 2.

Een waarde is een enkel stuk van gegevens. Waarden kunnen enkelvoudige waarden zijn, zoals getallen, tekst, logisch, null, binair, datum, tijd, datetime, datetimezone of durations.

Waarden kunnen ook complexere structuren hebben dan enkelvoudige waarden, zoals lijsten, records en tabellen. U kunt ook waarden hebben die een combinatie zijn van lijsten, records en tabellen. Lijsten van lijsten, tabellen van lijsten, tabellen van tabellen, enz… zijn alle mogelijke waardestructuren.

Enkelvoudige letterlijke waarden

Eenvoudige letterlijke waarden zijn de basisbouwsteen van alle andere waarden.

  • 123,45 is een getalswaarde.
  • “Hello World!” is een tekstwaarde.
  • true is een logische waarde.
  • null staat voor de afwezigheid van een waarde.

Single Intrinsic Values

Intrinsieke waarden worden geconstrueerd met behulp van de verschillende intrinsieke functies.

  • #time(uren, minuten, seconden)
  • #date(jaren, maanden, dagen)
  • #datetime(jaren, maanden, dagen, uren, minuten, seconden)
  • #datetimezone( jaren, maanden, dagen, uren, minuten, seconden, offset-uren, offset-minuten)
  • #duration(dagen, uren, minuten, seconden)

Om bijvoorbeeld de datum 2018-12-31 te construeren, zou u deze moeten construeren met behulp van de intrinsieke functie #date(2018, 12, 31).

Gestructureerde waarden

Lijsten

Een lijst is een geordende reeks waarden.

U kunt een lijst definiëren met behulp van accolades. {1, 2, 3} is een lijst met de getallen 1, 2 en 3. Omdat de volgorde belangrijk is, is dit niet dezelfde lijst als {3, 2, 1}.

{“Hallo”, “Wereld”} is een lijst die de tekst “Hallo” en “Wereld” bevat.

Lijsten van lijsten zijn ook mogelijk, dus {{1, 2}, {3, 4, 5}} is een lijst van twee lijsten. De eerste lijst bevat de getallen 1 en 2 en de tweede lijst bevat de getallen 3, 4 en 5.

Je kunt opeenvolgende lijsten maken met het formaat {x..y}. {2..5} zal de lijst {2, 3, 4, 5} opleveren. Dit werkt ook voor tekstkarakters. {“a”.. “d”} produceert de lijst {“a”, “b”, “c”, “d”}.

U kunt ook een lijst zonder items maken, {} is de lege lijst.

Omdat lijsten geordend zijn, kunnen we naar items in de lijst verwijzen met een op nul gebaseerd index nummer. {1, 2, 3}{2} zal evalueren naar 3 aangezien dit het 2e item in de lijst is (gebaseerd op een index van nul).

Records

Een record is een geordende opeenvolging van Fields. Elk veld bestaat uit een veldnaam die het veld uniek identificeert en een veldwaarde die elk type waarde kan zijn.

U kunt een record definiëren met behulp van vierkante haken. is een record met twee velden. Het eerste veld in de record heeft een veldnaam van Voornaam en een waarde van “John”. Het tweede veld in het record heeft een veldnaam van Leeftijd en een waarde van 38.

Records van records zijn ook mogelijk, ] is een record met één veld met een veldnaam van Persoon en een veldwaarde die een record is.

Lege records zijn ook mogelijk, is het lege record.

U kunt naar de veldwaarde in een record verwijzen met de veldnaam. zal evalueren naar “John”.

Tabellen

Een tabel is een geordende opeenvolging van rijen waarbij elke rij een lijst is.

Tabellen kunnen alleen worden geconstrueerd met behulp van een intrinsieke functie. U kunt een tabel construeren met de functie #table() op basis van een lijst met kolomtitels en een lijst met rijen.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) maakt een tabel met 2 kolommen, 3 rijen en de kolomtitels Letters en Cijfers.

Het is mogelijk om een lege tabel te maken met behulp van lege lijsten in de intrinsieke functie #table(). #table({}, {}) zal een lege tabel produceren.

U kunt naar elke waarde in een tabel verwijzen met de op nul gebaseerde rij-index en de naam van de kolomkop. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} zal evalueren naar “C” omdat dit de 2e rij is (gebaseerd op een nul index) van de Letters kolom.

Expressies

Expressies zijn alles wat kan worden geëvalueerd naar een waarde. Dit geldt voor de waarden zelf. Bijvoorbeeld, de uitdrukking 1 is gelijk aan de waarde 1.

Hoewel u meestal denkt aan uitdrukkingen als zijnde samengesteld uit meer complexe operaties of functies.

Voorbeeld:

  • De uitdrukking 1 + 1 is gelijk aan 2.
  • De uitdrukking 3 > 2 is gelijk aan waar.
  • De uitdrukking “Hello ” & “World” evalueert naar “Hello World”.
  • De uitdrukking Text.Upper(“Hello World”) evalueert naar “HELLO WORLD”.

Operators

Naast de standaardbibliotheek heeft M-code ook een lijst met speciale functies die operators worden genoemd. Deze nemen precies twee waarden (of uitdrukkingen die evalueren naar een waarde) en geven een enkele waarde terug.

Rekenkundige

M code wordt geleverd met de basis rekenkundige operatoren die u zou verwachten en gewend bent van reguliere Excel +, -, * en /. Deze stellen u in staat om waarden respectievelijk op te tellen, af te trekken, te vermenigvuldigen en te delen.

Deze kunnen worden gebruikt met verschillende andere soorten waarden dan alleen getallen. U kunt bijvoorbeeld een duur toevoegen aan een datum.

#date(2018,12,25) + #duration(7, 0, 0, 0) zal evalueren naar 2019-01.

Vergelijking

U kunt waarden in M-code vergelijken met behulp van de vergelijkingsoperatoren <, >, <=, >=, =, <>.

  • x < y zal evalueren naar waar als x kleiner is dan y.
  • x > y zal evalueren naar waar als x groter is dan y.
  • x <= y zal evalueren naar waar als x kleiner is dan of gelijk aan y.
  • x >= y zal evalueren naar waar als x groter is dan of gelijk aan y.
  • x = y zal evalueren naar waar als x gelijk is aan y.
  • x <> y zal evalueren naar waar als x niet gelijk is aan y.

Deze kunnen gebruikt worden met verschillende types van waarden. U kunt bijvoorbeeld twee lijsten vergelijken met de operator gelijk.

{1,2,3,4} = {1,2,3} zal niet waar zijn omdat de lijsten niet gelijk zijn.

Aaneenschakeling en samenvoeging

U kunt tekst aaneenschakelen en lijsten, records en tabellen samenvoegen met behulp van de operator ampersand &.

Voorbeeld:

"Hello " & "World" zal evalueren naar “Hello World”.

{1,2,3} & {3,4,5} zal evalueren naar {1,2,3,3,4,5}.

Logisch

U kunt bewerkingen uitvoeren op Booleaanse waarden (of uitdrukkingen die evalueren naar Booleaanse waarden) met de operatoren not, and en or.

Code becommentariëren

Zoals u van elke programmeertaal zou verwachten, is het mogelijk om commentaar aan uw code toe te voegen.

Er zijn twee soorten commentaar mogelijk in M-code. Enkele regel commentaar en multi-regel commentaar.

Enkele regel commentaar 

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

Een enkele regel commentaar kan worden gemaakt door het commentaar vooraf te laten gaan door twee schuine strepen //. Alles op dezelfde regel ervoor zal worden geïnterpreteerd als M-code, alles erna zal worden geïnterpreteerd als commentaar.

Meervoudige regel commentaar 

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

Een meervoudige regel commentaar kan worden gemaakt door het commentaar te plaatsen tussen /* en */ tekens. Alles buiten deze tekens zal worden geïnterpreteerd als M-code. Alles tussen deze tekens wordt geïnterpreteerd als commentaar.

Let Statement

Met het let statement kan een reeks waarden worden geëvalueerd en toegewezen aan variabelennamen die vervolgens worden gebruikt in een volgende expressie die volgt op het in statement.

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

Deze expressie bestaat uit drie expressies die worden geëvalueerd na het let statement. Elke uitdrukking wordt gescheiden door een komma, behalve de laatste voor de in-uitdrukking. In dit voorbeeld zal de gehele let en in uitdrukking evalueren naar 3.

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

U zou kunnen denken dat de uitdrukkingen binnen een let uitdrukking moeten voorkomen in de volgorde waarin ze geëvalueerd moeten worden. Dit is niet het geval! De bovenstaande code is volkomen geldig en zal ook evalueren naar 3. De M-code-evaluator berekent automatisch de volgorde van de berekeningen die nodig zijn op basis van expressieafhankelijkheden.

Het is natuurlijk gemakkelijker voor iemand om de M-code te lezen als deze in volgorde van evaluatie is geschreven, maar er is ook een ander voordeel. Expressies verschijnen als afzonderlijke stappen in het venster Toegepaste stappen. Wanneer ze niet in volgorde worden geschreven, verschijnen de expressies als één gecombineerde stap.

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

U kunt ook expressies evalueren binnen het in deel van een let… in… expressie.

Variabele Namen 

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

U kunt vrijwel elke naam aan uw expressies toekennen met behulp van de #”” tekens. U kunt zelfs spaties en andere speciale tekens gebruiken. Het gebruik van gereserveerde trefwoorden is de enige uitzondering.

De namen van de variabelen zijn wat zal verschijnen in de Toegepaste stappen van de query editor, dus de mogelijkheid om spaties te gebruiken is een geweldige functie.

Each Statements

De each expressie is een steno voor het declareren van functies die een enkele parameter met de naam _ (underscore) nemen.

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

In dit voorbeeld maken we een nieuwe kolom die de kolom Getallen met 2 vermenigvuldigt voor elke rij.

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

We kunnen dezelfde query maken met de underscore syntaxis die semantisch gelijkwaardig is aan de each statement. Beide query’s zullen hetzelfde werken.

If Then Else Statements

M code is vrij karig in vergelijking met andere talen als het gaat om logische expressies. Er zijn geen select case of loop statements beschikbaar. Er is alleen een if… then… else… expressie beschikbaar.

if then else

De syntaxis is rechttoe rechtaan en lijkt op die van de meeste andere programmeertalen. Het kan allemaal op één regel staan, of het kan op afzonderlijke regels worden gepresenteerd om het lezen te vergemakkelijken.

Try Otherwise Statements

Er kunnen zich fouten voordoen wanneer wordt geprobeerd bewerkingen uit te voeren die bepaalde soorten gegevens vereisen. Bijvoorbeeld het vermenigvuldigen van een getal met een tekstwaarde zal een fout opleveren.

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

Errors kunnen worden voorkomen door de try… otherwise… expressie te gebruiken.

Dit voorkomt fouten in de resultaten van uw query en stelt u in staat fouten te vervangen door een willekeurige waarde of expressie.

Functies

Een functie is een toewijzing van een set parameterwaarden naar een waarde. Naast de functies in de standaardbibliotheek, kunt u met M-code uw eigen functies maken.

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

Deze query definieert een functie die twee getallen vermenigvuldigt. Vervolgens roept de query de functie met de waarden 2 en 3 aan en evalueert deze, wat resulteert in 6.

Functies met optionele parameters

Er zijn twee soorten functieparameters, een vereiste parameter en een optionele parameter.

Verplichte parameters moeten altijd worden opgegeven wanneer een functie wordt aangeroepen.

Optionele parameters hoeven niet te worden opgegeven wanneer een functie wordt aangeroepen. Als de optionele parameter ontbreekt, dan zal de waarde die aan de functie wordt doorgegeven null zijn.

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

Deze functie heeft een optioneel tweede argument. Dan roept de query de functie aan en evalueert deze met alleen de vereiste parameter met een waarde van 2, die evalueert naar 2. Merk op dat de functie rekening moet houden met het feit dat y null is, anders zou een ontbrekend optioneel argument kunnen resulteren in een functie die evalueert naar een fout.

Recursieve functies

Het is ook mogelijk om een functie te schrijven die naar zichzelf verwijst door gebruik te maken van de @ scoping operator.

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

De Fibonaccireeks is een voorbeeld van een functie die recursief is gedefinieerd. Het volgende getal in de reeks is gedefinieerd als de som van de twee vorige getallen. Dus om het n-de getal te krijgen, moet je het (n-1)e en (n-2)e getal weten.

Deze functie vindt het n-de Fibonacci getal door de (n-1)e en (n-2)e Fibonacci getallen op te tellen.

De query evalueert naar 13, omdat 13 het 7de Fibonacci getal is.

Hier zijn nog enkele nuttige voorbeelden waarbij u recursieve functies kunt gebruiken om Excel’s TRIM-functie te repliceren om overtollige spaties tussen woorden te verwijderen of om waarden in bulk te vinden en te vervangen op basis van een lijst.

Query Functies

In de bovenstaande voorbeelden is een functie binnen een query gedefinieerd en vervolgens is de functie binnen de query aangeroepen en geëvalueerd.

Het is ook mogelijk om een query te maken die een functie is (een query functie) en kan worden aangeroepen en geëvalueerd vanuit andere query’s.

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

Dit is de algemene opmaak die nodig is om een query functie te maken. Let op, u hebt een let… in… verklaring nodig binnen de let… in… verklaring van de queryfunctie om meerdere stappen te kunnen uitvoeren.

Conclusies

Power query is een geweldige functie in Excel die u kan helpen bij het automatiseren en vereenvoudigen van uw gegevensimport en -transformatie.

U kunt veel doen met behulp van alleen de grafische aanwijs- en klikinterface zonder ooit de M-code aan te raken. Maar als uw eisen geavanceerder worden, kan er een moment komen waarop u de M-code die u hebt gemaakt moet bewerken of uw eigen code vanaf nul moet schrijven.

Elke nieuwe programmeertaal is onleesbaar als u niet eerst de basis kent. Deze gids zal u hopelijk op weg helpen met M-code, zodat u kunt beginnen met het maken van meer geavanceerde query’s.

By: adminPosted on

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.