Het gebruik van Power Query
In mijn vijfde blog over Power BI in de praktijk van Kasparov BI wil ik het hebben over data transformatie (Power Query). Opnieuw voer voor vakgenoten, voel je vrij om feedback te geven of vragen te stellen. Graag zelfs!
In het eerste artikel van deze serie, ‘een introductie van Power BI’, wordt Power Query genoemd als stap twee (2). Het doel van Power Query is de data ‘Power BI waardig’ te maken. Het wordt altijd aangeraden om data zoveel mogelijk upstream te transformeren, maar het zou heel goed kunnen dat niet elke actie volledig upstream kan worden getransformeerd. In Power Query kun je bijvoorbeeld gegevensbronnen combineren, logica toevoegen en noodzakelijke functionele wijzigingen aanbrengen.
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.
Hergebruik logica door verwijzingen
In Power Query kan worden verwezen naar een tabel door een ‘Referentie’, of de tabel en de toegepaste stappen kunnen worden gekopieerd door ‘Dupliceren’. Op basis van de benaming wordt vaak onterecht de aanname gedaan dat de ‘Referentie’ de tabel waarnaar wordt verwezen pakt als uitgangspunt, en dat deze query dus maar één keer wordt uitgevoerd. Dit is echter niet hoe Power Query werkt. De ‘Referentie’ dient om te voorkomen dat dezelfde stappen in meerdere Query’s moeten worden toegepast. Het is eigenlijk een hulpmiddel om transformaties te centraliseren om onderhoud en leesbaarheid te vergroten. Hieronder een voorbeeld om dit concept te illustreren.
- Duplicaten
Deze twee query’s zijn nagenoeg identiek, maar de ene tabel behoudt middels de laatste stap alleen rijen waarin [Product Color] gelijk is aan “Red” en de ander rijen waarin [Product Color] gelijk is aan “Black”. Als de behoefte van de eindgebruiker wijzigt en zij graag ook nog de kolom [Weight] wil toevoegen aan deze dimensietabellen, dient dit nu in twee query’s te worden aangepast. In de stap #”Removed Other Columns” wordt bepaald welke kolommen behouden worden, en ondanks dat de twee tabellen dezelfde kolommen behouden is de logica niet gecentraliseerd.
- Referenties
Beide query’s verwijzen naar dezelfde bronquery; de d_product_base query. Dit is de plek waar alle logica voorafgaand aan het filteren op [Product Color] is gecentraliseerd:
Als de behoefte van de eindgebruiker wijzigt, hoeft dit slechts op één plek aangepast te worden. Om tot deze twee tabellen te komen, wordt de d_product_base query nog steeds twee keer uitgevoerd, maar voor de leesbaarheid, het onderhoud en de consistentie is deze opzet efficiënter.
Wees je goed bewust van deze werking, aangezien dus ook die referentietabellen een impact hebben op de snelheid van de data refresh.
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.
Veelvoorkomende functies
General
- Merge Query’s
Met een Merge Query’s worden twee bestaande tabellen samengevoegd op basis van overeenkomende waarden uit een of meerdere kolommen. Dit is erg handig als twee tabellen moeten worden samengevoegd in de breedte, bijvoorbeeld om bepaalde transformaties en/of berekeningen te kunnen uitvoeren.
Verschillende samenvoegingsopties zijn:
-
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
- 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.
- 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
- 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..
- 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.
- 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.
- 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.
- 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’.
Binnenkort ook te lezen op https://kasparov-bi.nl/.
