Nieuwe mogelijkheden in Excel (inclusief 6 Power Pivot tips)

Twee generaties geleden had nog niemand een mobiele telefoon en kon Excel nog prima mee. In deze tijd, waar op jouw mobiele telefoon Excel staat, is mijn favoriete rekenprogramma ingehaald door databases. Excel kan namelijk niet zo goed uit de voeten met grote hoeveelheid gegevens. In de praktijk doen 100.000 rijen met een aantal berekeningen de wachttijd irritant oplopen.

De Power gereedschappen in Excel

Maar veranderingen zijn ingezet. Microsoft heeft vanaf Excel 2010 de Power gereedschappen toegevoegd. Die kunnen letterlijk miljoenen rijen uit databases verwerken. 

In afbeelding 1 zie je welke stappen bestaan in jouw Excel- of Power model. Vanuit dit stappenplan neem ik je mee naar twee van de nieuwe gereedschappen. En gebruik je toevallig de nieuwe Power BI in Excel invoegtoepassing, dan publiceer en deel je de dashboards van jouw Excel model direct op het web die jij of je manager weer bekijkt op de iPhone. 

Power Query

Power Query behoort tot het tweede pijltje. Sinds Excel 2016 is het standaard in iedere versie van Excel aanwezig in het tabblad Gegevens, onder de sectie ‘Ophalen en transformeren’. In alle andere versies is het een invoegtoepassing, die je gratis download bij www.microsoft.com/BI.

ETL staat voor Extract, Transform, Load en eigenlijk zegt deze omschrijving alles. Met Power Query haal je gegevens op uit bronnen, ruim je puin en laad je de opgeschoonde gegevens in een Excel- of een Power Pivot tabel. Van alle Power gereedschappen is dit je beste vriend en het meest eenvoudig in gebruik. Het begint met het ophalen van gegevens, waarvoor je een heleboel mogelijkheden hebt.

In Afbeelding 2 zie je een paar van de invoer methoden. In het Navigator venster kies je vaak niet voor Laden, maar voor Bewerken. Het Power Query venster opent en je kiest uit het lint hoe je de geïmporteerde gegevens aanpast. 

Logisch denken, de juiste volgorde van stappen en weten wat voor mogelijkheden je in het lint hebt is de kunst van Power Query. Ben je klaar, dan kies je voor Laden en sluiten in het tabblad Start om het resultaat in Excel binnen te halen.

In afbeelding 3 zie je een voorbeeld van een tabel, die als een ware puinhoop begint en het uiteindelijke, opgeschoonde resultaat in Excel. Alle handelingen worden opgeslagen in een script. De knop ‘Alles vernieuwen’ zorgt ervoor dat het script opnieuw wordt uitgevoerd.

Power Query tips

1. In het venster Laden en sluiten bestaat de mogelijkheid om de tabel toe te voegen aan het gegevensmodel. Dit zorgt er voor dat de gegevens worden geladen in Power Pivot. Heb je een tabel met meerdere miljoenen, dan is dit de enige optie die werkt.

2. Mijn absolute favoriet? Importeren via de mogelijkheid ‘Van map’. Meerdere Excel-, tekst- of XML bestanden met uniforme structuren plaats je in een map en vervolgens maakt Power Query daar een in een handeling een tabel van. 

3. Vergeet de knop Alles vernieuwen niet. Een simpel VBA scriptje zorgt ervoor dat die stappen automatisch worden uitgevoerd als je het model opent: de ultieme vorm van niets doen in de productie omgeving. 

Power Pivot

Power pivot was de ‘new kid on the block’. Jammer genoeg is het niet altijd in iedere versie van Excel aanwezig. In Excel 2010 is het een gratis invoegtoepassing, die op (bijna) alle versies van Excel 2010 werkt. Vanaf Excel 2013 is Power Pivot een onderdeel van de versie Professional Plus. 

De mogelijkheden van Power pivot zijn:

– Importeren van databases en tabellen. Bij voorkeur gebruik je hiervoor Power Query.
– Relaties leggen tussen tabellen. Door relaties te leggen ben je in staat om over verschillende tabellen berekeningen te maken. In afbeelding 4 zie je het relatie venster.
– Berekenen in Power Pivot doe je door middel van DAX functies. 
– Uitvoer doe je door middel van een draaitabel Plus. 

Power Pivot is het derde en vierde pijltje van links van het schema in figuur 1. Het gaat bij Power Pivot om berekeningen en de presentatie van de resultaten in Excel dashboards. Power pivot heeft een eigen tabblad en je komt altijd via de knop beheren in het Power Pivot venster (afbeelding 5).

Power pivot gebruikt DAX functies om berekeningen uit te voeren. Deze lijken soms op Excel functies, maar zijn dat niet! Zo zorgt de DAX TOTALYTD in een berekening voor een year-to-date overzicht. Dit soort functies vind je niet in Excel. Er zijn twee soorten DAX berekeningen beschikbaar: 

– Of je voegt een berekende kolom toe. Hiermee maak je een berekening op rij niveau. Wordt de tabel ververst en wordt daarmee de tabel groter of kleiner, het resultaat in de kolom wordt daarop uiteraard aangepast. 

– Of je maakt een aggregaat berekening, genaamd meting. Ververs je het model, dan geldt hetzelfde als voor de berekende kolom.

Afbeelding 6 laat je een voorbeeld zien van een berekende kolom en een meting.

Draaitabellen uit een Power Pivot model zijn veel krachtiger dan zijn Excel broers. Belangrijkste verschillen zijn de mogelijkheid om alle tabellen van het Power Pivot model te gebruiken (een Excel draaitabel gebruikt maar een tabel of bereik) en de Power Pivot draaitabel berekent iedere DAX uit in de draaitabel. Dat geeft oneindige veel meer mogelijkheden dan de 11 samenvattingsfuncties van de gewone Excel draaitabel.

De kracht van een draaitabel komt goed tot zijn recht als je slicers en tijdslijnen gebruikt. Daarmee maak je een echt dashboard, waarbij de gebruiker gecontroleerde overzichten tot zijn beschikking krijgt. Afbeelding 7 laat een voorbeeld zien van een Power Pivot dashboard.

Power Pivot tips:

1. Databases of tabellen kan je direct importeren in Power Pivot. Het is echter beter om de import te laten lopen door Power Query. Je gebruikt dan een plaats waar je alle invoerbestanden beheerd.

2. Relaties werken met sleutels in sleutel kolommen. Voor een relatie zijn twee verschillende soorten tabellen nodig: de feiten tabel (die de gegevens bevat) en de dimensie tabel (die de connectie informatie en de omschrijvingen bevat). De ware kunst van Power Pivot is het vinden of maken van de juiste sleutels.

3. De basis van Excel is berekeningen, de basis van Power Pivot is filteren! Dat vergt een ander denkpatroon.

4. Power Pivot heeft een ongekende rekenkracht. Modellen waarin bijvoorbeeld vijf tabellen met ieder meer dan 2,5 miljoen rijen werken zonder problemen.

5. Power Pivot is een meester in het comprimeren van gegevens. Daardoor is het in staat om een kopie van de geïmporteerde databases op te slaan in jouw Excel-model. Dit leidt soms tot echt grote Excel bestanden, maar ook tot veiligheidsrisico’s. Omdat je (een deel van) de bedrijfsgegevens uit de beveiligde server omgeving kopieert naar jouw Excel-model kan je onbedoeld delen met anderen personen.

6. Power Pivot omvat een andere manier van werken dan Excel. Ga zeker het product uitproberen, maar wil je verder, denk dan aan het volgen van een training.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen. Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.