PowerQuery, PowerPivot, PowerBI: is het wat voor mij?

Excel is als analysepakket onovertroffen. Je hebt een kleine 500 functies ter beschikking, je kunt draaitabellen maken van je gegevens en de grafiekmodule is uitgebreid. In een handomdraai maak je een zeer verhelderend dashboard.

So far so good. Maar hoe krijg je je gegevens in Excel? Veel softwarepakketten (CRM, boekhouden, meetprogramma’s) kennen wel een exportmodule, maar lang niet altijd in Excel-formaat. Het importeren van data in Excel is lang een struikelblok geweest. Goed, een tekst- of csv (comma separated values)-bestand was met behulp van de optie Tekst-naar-kolommen nog wel te gebruiken, maar data afkomstig van servers, internet of in een afwijkend bestandsformaat, daar had Excel moeite mee.

Dat heeft Microsoft zich aangetrokken, en dus is er al enige versies een hulpmiddel beschikbaar: PowerQuery. In Excel 2010 en 2013 als los te installeren add in, vanaf 2016 een geïntegreerd onderdeel van Excel, te vinden op het tabblad Gegevens.

Met PowerQuery kun je dus gegevens importeren in Excel. Van een pdf-bestand. Een afbeelding, zoals een foto gemaakt met je telefoon van een presentielijst, of een tabel uit de krant. Online-gegevens van het CBS. Van je bank. Van SharePoint. Van Azure. Allemaal geen probleem. En PowerQuery kan nog veel meer. Je kunt het ook gebruiken om je gegevens te optimaliseren voordat je er in Excel mee aan de slag gaat. Kolommen splitsen? Datum omzetten van Engels/Amerikaanse naar West-Europese notatie? Lege rijen verwijderen? Geen probleem. Rijen toevoegen met een berekening, een vergelijking, een link naar gegevens uit een andere tabel/query? Geen probleem. Moet je elke keer veel aanpassingen maken aan je gegevens voordat je ze kunt gebruiken in Excel? PowerQuery onthoudt al je stappen, en met één klik kun je ze een volgende keer opnieuw laten uitvoeren, als een soort macro. Maar dan zonder programmeren. PQ is geweld.

Excel is niet ontworpen als database. De ruimte in een bestand (1.048.576 rijen x 16.384 kolommen x 255 werkbladen) lijkt heel behoorlijk, maar iedereen met een tabel van meer dan tienduizend rijen waarmee je wat berekeningen uitvoert, zal de ervaring kennen dat het bestand heel groot wordt, en Excel traag.
Hier komt het tweede hulpmiddel om de hoek kijken: het datamodel. Dit Excel-onderdeel slaat gegevens veel compacter op, zoals in een echte database. Hierdoor kun je in Excel analyses uitvoeren op tabellen met miljoenen rijen gegevens, zonder dat je bestand groot en traag wordt. Je benadert het datamodel via PowerPivot. Het datamodel en PowerPivot zijn ook geïntegreerd in Excel. Met behulp van PowerPivot kun je tabellen koppelen, zoals in een database met sleutelvelden, en je kunt veel efficiëntere berekeningen maken (metingen en KPI’s). Werk je op een financiële afdeling? Ben je data-analist? Dan kun je bijna niet meer om PowerQuery en PowerPivot heen.

Een paar jaar gelden bracht Microsoft een nieuwe tool uit: PowerBI (BI = Business Intelligence). Een concurrent voor onder andere QlikView, Tableau, IBM’s Watson en Google Charts. Microsoft maakt er handig gebruik van dat veel gebruikers ervaring hebben met het Office-pakket. Er is veel herkenning. De desktopapplicatie is ook nog eens gratis te gebruiken. Maar wat kun je er mee?  Dashboards maken!

PowerBi bestaat uit drie onderdelen: PowerQuery, het datamodel en een grafische module. De grafieken die je daarmee kunt maken, zijn iets gelikter dan de grafieken die je maakt in Excel. Wil je je dashboard delen met anderen, dan kan dat op elk apparaat met internetverbinding en een browser. Daartoe dien je je dashboard eerst te uploaden naar PowerBI.com, de website waar al je dashboards worden bewaard. Dát kost wel geld.

Er is dus veel overlap met Excel, want ook daarin vind je de modules PowerQuery en het datamodel. Uitwisseling van gegevens is dan ook eenvoudig. Volg deze serie om te leren werken met PowerQuery en PowerPivot. In de komende weken laten wij je zien hoe je begint, en hoe je verder gaat. Een 1- of 2-daagse training volgen is een snellere manier om te leren werken met deze belangrijke Excel-onderdelen!