Power BI tutorial week 3: data analyseren en transformeren met Power Query
Companies that use data effectively are 5x more likely to make better decisions and 6x more likely to be profitable.
— McKinsey & Company
Inleiding
Vandaag de dag speelt data een cruciale rol in het behalen van bedrijfsdoelen. Zo becijferde McKinsey & Company in 2016 in het rapport “The Age of Analytics: Competing in a Data-Driven World” dat bedrijven die hun data effectief benutten vijf keer zo waarschijnlijk betere beslissingen namen en zes keer meer kans hadden op winstgevendheid. Dashboards vormen een essentiële schakel in dit proces.
Het doel van deze tutorial is om je stap voor stap door een praktijkvoorbeeld te begeleiden waarin Business Intelligence wordt ingezet. Hiervoor maken we gebruik van Power BI en een dataset van een fictief snoepgoedbedrijf (afkomstig van Kaggle). We kiezen voor Power BI vanwege de volgende redenen:
- Microsoft Power BI wordt door Gartner als leider erkend in Business Intelligence;
- Power BI Desktop is gratis beschikbaar in de Microsoft Store;*
- Heeyoo heeft uitgebreide ervaring met het bouwen van op maat gemaakte dashboards in Power BI.
*Disclaimer: Deze tutorial is specifiek ontworpen voor Power BI Desktop op Windows-systemen, aangezien Power BI Desktop momenteel alleen beschikbaar is voor Windows-gebruikers. Als je een Mac-gebruiker bent, kun je de tutorial niet rechtstreeks uitvoeren. Als je Power BI Desktop wilt gebruiken op een Mac, kun je een virtuele Windows-machine draaien via bijvoorbeeld Parallels of Boot Camp. Wel is het mogelijk Power BI Service via je webbrowser te gebruiken om rapporten en dashboards te bekijken en te delen. Houd er rekening mee dat de functionaliteit van de webversie beperkter is dan die van Power BI Desktop.
Door deze tutorial te volgen, krijg je niet alleen inzicht in de mogelijkheden van Power BI, maar ook praktische handvatten om data effectief in te zetten binnen jouw organisatie.
Nadat we vorige week alle datasets hebben ingeladen en hebben gezien hoe we de data kunnen verversen, zullen we deze week dieper ingaan op de opties die Power Query te bieden heeft. Verder zullen we de Power Query inzetten om de ‘Date’-tabel verder uit te breiden.
Verkennen statische hulpstukken Power Query
- Open het .pbix bestand van vorige week.
- Navigeer naar de Power Query door op Transform data te klikken:
Een belangrijke stap in Business Intelligence is het inspecteren, en eventueel opschonen, van de brondata. Power Query biedt hiervoor drie handige hulpmiddelen: Column quality, Column distribution, en Column profile.
- We beginnen met de ‘Sales’-tabel. Ga naar het View tabblad en vink daar Column quality aan:
Je ziet nu per kolom welk percentage van de data ‘Valid’, een ‘Error’ of ‘Empty’ is. Door met je muis over het vlak met percentages te zweven, zie je precies hoeveel rijen er onder elke categorie vallen.
Een belangrijke opmerking hierbij is dat deze controle standaard gebaseerd is op de eerste 1000 rijen. Omdat onze tabel meer rijen bevat, klikken we onderin de tabel op Column profilling based on top 1000 rows. Kies vervolgens voor Column profilling based on entire data set (zie gif). Als we nu weer met de muis boven het percentagevlak zweven, zie je dat er 10.194 valide waardes zijn:
- Het volgende hulpmiddel dat we inschakelen is Column distribution. Deze optie toont boven elke kolom (schematisch) een frequentieverdeling van het aantal waarden in de betreffende kolom:
Hierbij is het belangrijk om onderscheid te maken tussen waarden die ‘distinct’ en ‘unique’ zijn. ‘Distinct’ slaat op het aantal verschillende waarden in de kolom, terwijl ‘unique’ het aantal waarden weergeeft die slechts één keer voorkomen. Zo zien we hierboven dat voor ‘Row ID’ het aantal waarden dat ‘distinct’ is gelijk is aan het aantal unieke waarden. Met andere woorden: elke rij heeft een uniek ‘Row ID’. Voor Order ID zijn er 7.176 unieke waarden en 8.549 ‘distinct’ waarden. Hieruit kunnen we afleiden dat 1.373 OrderIDs (8.549 – 7.176) twee of meer keer voorkomen.
- Het derde, en meest geavanceerde, hulpmiddel is Column profile. Als je deze optie aanzet, verschijnen onder in de Power Query twee panelen: Column statistics en Value distribution. Deze geven meer details over de kolom die op dat moment geselecteerd is:
- Column statistics geeft je statistische info over de geselecteerde kolom, zoals het aantal waarden (count), maar ook het minimum (min) en maximum (max). Wat je precies te zien krijgt, hangt af van het datatype van de kolom. Hierboven heeft ‘Order ID’ het datatype ‘Text’, waardoor ‘Min’ en ‘Max’ hier staan voor de eerste en laatste waarde in alfabetische volgorde. Wanneer we de kolom ‘Units’ selecteren, die het datatype ‘Whole number’ heeft, zien we dat Column statistics nu meer waarden heeft, zoals ‘Average’, ‘Standard deviation’, het aantal even (‘Even’) en oneven (‘Odd’) waarden:
- Value distribution laat in één oogopslag zien hoe de waarde in een kolom verdeeld zijn. Bij kolommen met veel unieke waarden toont het paneel alleen de meest voorkomende. De rest past simpelweg niet in het overzicht. Door met je muis over een balk te bewegen zie je precies hoe vaak die waarde voorkomt:
De data-inspectiehulpstukken zijn ideaal om de data te inspecteren. De volgorde waarin we ze hebben besproken is ook de volgorde waarin je ze het best kan gebruiken. Je kunt nu zelf door de verschillende databronnen gaan en deze opties één voor één aanvinken, om zo een goed beeld te krijgen van de data.
Data transformaties met Power Query
Nu je de data hebt verkend, is het tijd om de ‘Date’-tabel uit te breiden. Zoals je later in de tutorial zult zien, is het cruciaal voor het analyseren en visualiseren van deze data om dit langs verschillende tijdsdimensies te doen. Zo kun je bijvoorbeeld trends analyseren per jaar, maand of dag.
- Navigeer naar de ‘Date’-tabel en zet eventueel de data-inspectiehulpstukken (‘Column quality’, ‘Column distribution’, ‘Column profile’) onder ‘View’ uit:
Laten we nu kijken naar de tabbladen Transform en Add column. Kortgezegd kunnen we met beide ongeveer dezelfde bewerkingen uitvoeren, maar er is een belangrijk verschil – en dat zit al in de namen. ‘Transform’ past een wijziging toe op de bestaande kolom (de oorspronkelijke data wordt dus overschreven), terwijl ‘Add column’ een nieuwe kolom toevoegt met het resultaat van de transformatie.
- Laten we samen kijken wat het verschil is. We willen de eerste datum van de maand voor elke rij zien (voor elke datum in januari 2021 is dat simpelweg ‘01/01/2021’). Via Transform (Date > Month > Start of Month) zien we dat alle data in januari 2021 nu veranderen in ‘01/01/2021’. Dat is niet dat we willen, dus verwijderen we de laatste stap weer uit het ‘Applied Steps’-paneel. Vervolgens navigeren we naar de Add Column-tab en zien we hoe dezelfde operatie (Date > Month > Start of Month) een extra kolom oplevert:
- Via het tabblad Add Column breiden we de ‘Date’-tabel verder uit met de onderstaande kolommen. Let hierbij op: na het toevoegen van een kolom is het belangrijk de ‘Date’-kolom opnieuw te selecteren voordat je een nieuwe kolom toevoegt, anders wordt de op dat moment geselecteerde kolom gebruikt als basis voor het creëren van de nieuwe kolom.
- Start of Week
- Start of Quarter
- Start of Year
- Year
- Probeer nu zelf een paar nieuwe kolommen toe te voegen om een beter gevoel te krijgen voor deze werkwijze. Na afloop kun je ze weer eenvoudig verwijderen door de betreffende ‘Applied Step’ te verwijderen. Je kunt bijvoorbeeld de naam van de maand of dag toevoegen. Omdat deze kolommen niet in het rapport gebruikt zullen worden, verwijderen we ze weer:
- Op dit punt is het een goed idee om op Close & Apply te klikken. Zo wordt de Power Query afgesloten en worden de wijzigingen in de data geladen naar de front-end. Je kunt dit terugzien door naar Table View te gaan en de ‘Date’-tabel te bekijken. Daarnaast is het slim om regelmatig je werk op te slaan, dus doen we dat nu ook (File > Save, of simpelweg Ctrl + s op een pc).
Overzicht functionaliteiten Power Query (demo)
Deze tutorial richt zich op het extraheren van waardevolle inzichten uit bedrijfsdata. Daarom gebruiken we niet alle functionaliteiten van Power Query, maar het is wel belangrijk om te begrijpen hoe krachtig deze back-end van Power BI is! In deze sectie gaan we daarom enkele basisfunctionaliteiten van Power Query doorlopen. Je kunt mee klikken, maar wees je ervan bewust dat de wijzigingen uiteindelijk niet opgeslagen zullen worden. Wil je liever gewoon alleen lezen of dit stuk overslaan? Geen probleem! Het is goed om te weten dat dit slechts het topje van de ijsberg is van wat Power Query allemaal te bieden heeft.
- Vanuit de front-end klikken we allereerst weer op ‘Transform data’ om zo weer in de Power Query terecht te komen:
- Groeperen: In de Power Query navigeren we naar de ‘Sales’-query. In deze tabel correspondeert elke rij met de verkoop van een product. De ‘Product ID’ geeft het product weer en de ‘Units’ kolom toont hoeveel eenheden er verkocht zijn. Per kolom is het mogelijk om te zien welke unieke waarden erin staan. Als we bijvoorbeeld naar de kolom Ship Mode navigeren en op het pijltje naast de kolomtitel klikken, zien we dat er vier verschillende waarden zijn. Een belangrijk punt: zoals we eerder zagen kijkt Power BI standaard alleen naar de eerste 1000 rijen. Om de hele kolom te analyseren moet je daarom eerst op Load more klikken. In dit geval blijft het aantal unieke waarden gelijk:
- Stel dat we nu willen weten wat het aantal verkochte eenheden (‘Units’) per ‘Ship mode’ is. Dit kunnen we achterhalen door de data te groeperen op ‘Ship mode’ en dan de totale eenheden te tellen met de ‘sum’ functie. Navigeer hiervoor naar Home > Transform >Group By. In de pop-up kies je de kolom waarop je wilt groeperen, voer je de naam in van de nieuwe kolom die het resultaat zal bevatten (Total Units), de ‘Operation’ die uitgevoerd moet worden (Sum) en ten slotte de kolom waarop de operatie op uitgevoerd moet worden. Het resultaat is een nieuwe kolom met de gegroepeerde data. Let op dat je deze ‘Applied step’ ook weer verwijdert om zo de oorspronkelijke dataset terug te krijgen:
- Cijfermatige bewerkingen: we blijven in de Sales-query, maar kijken nu naar cijfermatige bewerkingen. Als we de ‘Units’ kolom als voorbeeld nemen en vervolgens onder het tabblad Transform naar het paneel Number column navigeren, zien we daar een blok genaamd Standard, waaronder allerlei rekenkundige bewerkingen schuilgaan. Laten we als voorbeeld Add nemen:
- Let op dat we in het Transform-tabblad zitten en het resultaat van deze bewerking de oorspronkelijke waarden dus zal overschrijven! Hetzelfde blok met rekenkundige bewerkingen (‘Standard’) kun je ook vinden onder het ‘Add Column’-tabblad voor het geval je de uitkomst van je bewerking in een nieuwe kolom wilt opslaan. Stel dat we bij elke waarde in de kolom ‘1’ willen optellen, dan vullen we dit simpelweg in. Tot slot kunnen we zoals altijd deze actie weer ongedaan maken door de bijbehorende ‘Applied Step’ te verwijderen:
- Tekstbewerking: we navigeren nu naar de ‘Product’-query om te zien welke bewerkingen we op een tekstkolom kunnen uitvoeren. Selecteer de ‘Product Name’-kolom, ga naar het tabblad Transform, open het dropdownmenu Format binnen de groep Text Column en probeer vervolgens verschillende opties uit. Zo zien we dat we de tekst in hoofdletters (uppercase) of kleine letters (lowercase) kunnen veranderen, of we kiezen voor de optie ‘Capitalize each word’. We besluiten door elke ‘Applied Step’ weer te verwijderen:
- Dupliceren kolom: we blijven in de ‘Product’-query. Een vrij eenvoudige, maar soms handige, actie is het dupliceren van een kolom. Selecteer de kolom in kwestie, klik op de rechtermuisknop terwijl je boven de kolomtitel zweeft en selecteer vervolgens ‘Duplicate Column’:
Let op dat de gedupliceerde kolom automatisch helemaal rechts wordt toegevoegd. We kunnen deze simpelweg verslepen naar een andere plek. Vergeet niet dat ook deze actie wordt vastgelegd als een ‘Applied Step’: 
- Extractie: We kunnen deze nieuwe kolom nu gebruiken om alleen de lettercodes van het ‘Product ID’ te tonen. Dat doen we door alle tekens voor het tweede streepje (‘-‘) te extraheren. Hiervoor selecteren we onze nieuwe kolom, die we eerst ‘Product ID Letter Codes’ noemen, en vervolgens kiezen we voor: Extract > Text Before Delimiter. Omdat we het eerste scheidingsteken over willen slaan, moeten we de ‘Advanced Options’ uitvouwen en ‘Number of delimiters to skip’ op ‘1’ zetten:
- Na afloop kun je de bijbehorende ‘Applied Steps’ verwijderen. Doe dit in omgekeerde volgorde om foutmeldingen te voorkomen, aangezien een ‘Applied Step’ meestal voortbouwt op de vorige:
- Filteren: de laatste functionaliteit van de Power Query die we behandelen is filteren. Ga naar de Date-query, selecteer de kolom Year en klik op het pijltje naast de kolomtitel. Nadat je op Load more hebt geklikt, kun je bijvoorbeeld alleen het jaar ‘2024’ selecteren. Let op dat de ‘Applied Step’ Filtered Rows is toegevoegd, die je uiteraard weer kunt verwijderen:
Met deze demo sluiten we de tutorial van deze week af, waarin we de diverse functionaliteiten van Power Query hebben verkend. We hebben onder andere statistische tools gebruikt voor een verkennende data-analyse, de datumtabel uitgebreid met transformaties, en een eerste indruk gekregen van wat Power Query allemaal kan doen. Volgende week gaan we aan de slag met het modelleren van data. Een essentieel onderdeel voor het bouwen van een sterk dashboard. Tot dan!
Tijdrovende handmatige stappen in je dataprocessen? Ontdek hoe wij Power Query inzetten voor slimme automatisering. Stuur gerust een berichtje, onze Power BI Specialisten helpen je graag!
