Het gebruik van Power Query

Basisregels

Omdat de transformaties in Power Query invloed kunnen hebben op de vernieuwing en de prestaties van het semantic model, is het zaak om een paar basisregels op te stellen voor het gebruik van Power Query.

Data dient zo ver mogelijk upstream, maar zo ver downstream als nodig, te worden getransformeerd

Power Query werkt op basis van stappen. Dit betekent dat tijdens het transformeren van een tabel elke transformatie als een aparte stap wordt gezien. Het voordeel hiervan is dat de ontwikkelaar altijd kan teruglezen welke transformaties zijn uitgevoerd en die dus ook makkelijk kan aanpassen. Het nadeel is dat een groot aantal transformaties voor performance- en vernieuwingsproblemen kunnen zorgen. Dit komt doordat Power BI bij een vernieuwing van een semantic importmodel elke stap vanaf het begin gaat uitvoeren. Veel transformaties / stappen betekent dus een langere vernieuwingstijd.

Om dit te ondervangen, is het belangrijk om de regel toe te passen: ‘Data dient zo ver mogelijk upstream, maar zo ver downstream als nodig, te worden getransformeerd’. Dit betekent dat transformaties en/of stappen die ook in de gegevensbron kunnen worden aangepast de voorkeur genieten ten opzichten van Power Query.

Behoud alleen de data die je daadwerkelijk nodig hebt

Power BI werkt op basis van rij-context. Het loopt in een kolom naar beneden ( per rij ) en herhaalt dit vervolgens voor de volgende kolom et cetera. Vanuit een prestatie-aspect betekent dit dat het beperken van het aantal kolomen prestatie winst zal opleveren. Beperk daarom het aantal kolommen tot hetgeen je echt nodig hebt.

Houd de Query Fold intact

Query folding is een functionaliteit binnen de Power Query Editor waarmee het ophalen van en het transformeren van brondata worden samengevoegd tot één query statement. Als een transformatie wordt meegenomen in query folding, betekent dit dat de native query wordt herschreven; dit is de query die wordt teruggestuurd naar het bronsysteem.

De Power Query Engine probeert query folding uit zichzelf al zoveel mogelijk toe te passen; het is echter aan de gebruiker om dit te monitoren en te ondersteunen. Een concreet voorbeeld is de volgorde van de stappen Changed Type en Filtered Rows; in de zojuist benoemde volgorde zou de filterstap niet worden meegenomen in de native query, aangezien query folding breekt door de stap Changed Type. Het omdraaien van deze stappen herstelt query folding en dit maakt de query efficiënter.

Maak gebruik van parameters om scripts dynamisch en leesbaar te houden

Een parameter is een opgeslagen waarde die losstaat van een query. Hierdoor kan hij gemakkelijk worden aangepast en/of hergebruikt in meerdere query’s. Een parameter kan een ‘single value’ zijn, een lijst of een query. Voorbeeld: Bij het gebruik van een OTAP-methode ( test, acceptatie, productie omgeving ) kan het handig zijn om snel te kunnen wisselen van omgeving. Een parameter is hier uitermate geschikt voor. In plaats van in elke query te verwijzen naar de juiste omgeving, kan er ook worden verwezen naar de alleenstaande parameter. Bij het wijzigen van omgeving hoeft dan alleen de parameterwaarde te worden aangepast.

2. Append Query's

Met een Append Query’s worden twee bestaande tabellen samengevoegd op basis van kolomnaam. In tegenstelling tot Merge Query’s waarbij twee bestaande tabellen worden samengevoegd in de breedte, wordt bij Append Query’s de tweede tabel als extra rijen toegevoegd aan de eerste tabel ( In de hoogte ). Append Query’s is bijvoorbeeld erg handig als men zijn datasources heeft opgesplits per jaar. Met Append Query’s kan deze worden samengevoegd tot één tabel.

Add Column

1. Custom column
   Soms moet er extra logica worden toegevoegd aan een tabel. Met een ‘custom column’ kan er een persoonlijke formule worden geschreven die vervolgens als kolom ( per rij niveau ) zal worden weergegeven.
    
2. Column from example
   Als niet helemaal duidelijk is hoe bovenstaande moet worden geschreven, kan gebruik worden gemaakt van ‘Column from example’. In dit geval kan een bepaalde waarde worden ingegeven waar m-query zelf een formule op loslaat.

Transform

1. Format
   M-query is gevoelig voor onzichtbare spaties en hoofdlettergevoelig. Soms is het daarom nodig om de text op te schonen. Dit kan met de optie format. Hier kan text worden getransformeerd naar alleen hoofdletters, alleen kleine letters, alles met hoofdletter, verborgen spaties verwijderen etc..
    
2. Pivot / Unpivot
   Onthoud dat Power BI het beste werkt op basis van rij-context. Het loopt in een kolom naar beneden ( per rij ) en herhaalt dit vervolgens voor de volgende kolom et cetera.
   Het kan voorkomen dat een datasource niet dit format heeft en hierdoor niet optimaal werkt voor Power BI. Met de optie ‘Unpivot’ worden kolommen getransformeerd naar rijen.
   ‘Pivot’ werkt het tegenovergestelde. Hierbij word een rij getransformeerd naar kolommen waarbij de waardes gekozen kunnen worden uit een andere kolom.
    
3. Replace values
   Data wordt niet altijd schoon aangeleverd. In sommige gevallen zullen bepaalde waardes niet mogen voorkomen, staan er lege waarden in de text, of moet er simpelweg iets anders staan. Met replace values kunnen aangeven waarden in een kolom worden aangepast. Dit kan zelfs op basis van een if statement.
    
4. Split column
   Data word vaak gecomprimeerd of samengevoegd aangeleverd. Dit betekent dat een waarden dat eigenlijk uit meerdere onderdelen bestaat vaak in één kolom staat. Met ‘Split column’ kan deze waarden worden opgesplits in meerdere kolommen.
    
5. Remove duplicates
   Met de functie ‘Remove Duplicates’ kunnen duplicaten worden verwijderd. Dit verkleint de data en geeft in theorie een prestatie winst. 

Afsluiting

Power Query zelf brengt geen vooraf gedefinieerd risico met zich mee. Het is echter van belang om na te denken over de handeling die zal worden doorgevoerd. Veel of enkele handelingen, zoals het combineren van gegevens, kunnen de ( vernieuwings) prestaties van het rapport beïnvloeden. Het wordt geadviseerd om alleen essentiële handelingen door te voeren. Als het mogelijk is om de wijziging in de gegevensbron door te voeren, wordt dat ten zeerste aanbevolen.

Met het publiceren van mijn laatste blog ‘Power Query’ is mijn serie over de introductie van Power BI afgerond. Als je mij al een tijdje volgt, heb ik je hopelijk nieuwe inzichten kunnen geven of op weg kunnen helpen met Power BI. Ik haal ontzettend veel plezier uit het schrijven van blogs voor Kasparov BI en wil dit dan ook graag voortzetten met een nieuwe serie ‘Power BI – het vervolg’.