Power Query in Excel, de data stofzuiger

In mijn vorige artikel heb ik geschreven over het vijflagen model (afbeelding 1). In dit artikel ga ik dieper in op de tweede laag vanaf beneden. De onderste laag sla ik bewust over. Er valt veel over die laag te vertellen, maar dat is een technisch verhaal en daardoor minder geschikt. De twee laag heet Prepare of, in gewoon Nederlands, Voorbereiden. Het is zeker de moeite waard om deze laag dieper te gaan verkennen. Ook voor Excel-gebruikers zal deze laag een steeds belangrijker rol in de rapportagecyclus worden.

ETL staat voor Extract, Transform and Load (Ophalen, transformeren en laden) en is in de professionele datawereld als sinds mensenheugenis de gewoonste zaak van de wereld. In Excel 2010 is Power Query in Excel opgenomen. Bijna geruisloos mag ik wel zeggen, maar naar mijn mening had Power Query zeker 10 jaar eerder in Excel mogen verschijnen. Veel echt Excel-leed was hiermee voorkomen. 

Power Query is, zoals een deelnemer aan een congres scherp opmerkte, een data stofzuiger. Je haalt data op uit een database (wat ook een Excel tabel of model mag zijn!), je verwijdert de onjuiste (vieze) data, verrijkt naar wens met bijvoorbeeld aanvullende (berekende) kolommen en je laadt vervolgens het resultaat in bijvoorbeeld Excel of Power BI.

Microsoft heeft voor ETL bewerkingen een fors aantal producten: traditioneel Excel, het moderne Ophalen en transformeren (voorheen Power Query) in Excel, Power Query in Power BI, DataFlow in de cloud en SQL in de database omgeving (afbeelding 2). De volgorde binnen gaat van de Business naar de professionele BI-omgeving. 

Excel, binnen Microsoft kringen al eens aangeduid als de werelds meest gebruikte BI-gereedschap, heeft traditioneel een ingebouwde ETL gereedschap set. Wil je bijvoorbeeld het jaar uit de een datum kolom hebben, dan gebruik je de functie =JAAR() en vul je daarmee een nieuwe kolom in de tabel aan. (Afbeelding 3). Dat werkt goed, totdat je een grote tabel gaat gebruiken. Stel, je hebt een tabel met 500.000 rijen. Voeg je daar een kolom het jaar toe, dan krijg je direct 500.000 berekeningen voor je kiezen. Herberekening, wat standaard na iedere invoer is ingesteld, duurt dan frustrerend lang.

Zoals al gezegd leeft Power Query sinds 2010 in Excel. Het is voor mij DE (graag even aandacht voor  het vette lettertype en de hoofdletters) belangrijkste ontwikkeling binnen Excel sinds haar bestaan. Hou Power Query in de gaten. Ik voorspel dat Power Query niet alleen ontzettend belangrijk voor Excel is, maar ook een van de belangrijkste producten van Microsoft gaat worden. 

Excel is gebaseerd op cellen, het kruispunt tussen een kolom en een rij. Power Query werkt met tabellen en kolommen. Dat vereist wel een wezenlijke andere mindsetting van de gebruiker. Wil je het jaar in een kolom hebben, dan gebruik je daarvoor in Power Query een knop in het lint (afbeelding 4). Dat is veel eenvoudiger en effectiever dan de handelingen, die je in Excel moet verrichten. 

Maar er zijn nog meer voordelen. Een ander sterk punt van Power Query is dat de kolom weg wordt  “weggeschreven” als tekst. Laad de tabel in Excel en je bespaart zomaar 500.000 berekeningen. Zeker weten dat het jouw Excel model een boost geeft! En er is nog een ander voordeel. Databases bezitten de eigenschap om te groeien. Als het bijvoorbeeld gegevens uit een boekhoudpakket betreft, wordt de tabel, bij iedere boeking voorzien van nieuwe rijen, die de boekingsinformatie bevat. Zo zou het zomaar kunnen zijn dat de tabel met 500.000 rijen de volgende maand 500.500 rijen bevat. In Excel – werkend op celniveau – moet je controleren of de laatste 500 rijen ook voorzien zijn van de JAAR functie. Dat is al een risico. Power Query, werkend op tabel niveau, zal automatisch de nieuwe 500 rijen voorzien van het jaartal. Is de tabel eenmaal geladen, dan wordt ook automatisch de tabel in Excel opgerekt. Zoals de Engelsen dat zo mooi omschrijven: “my cup of tea”. In Excel geldt nu eenmaal, hoe minder handmatig werk, hoe beter.

Het grotere broertje van Power Query heet DataFlow en de preview van dit product is in November 2018 geïntroduceerd. DataFlow werkt als Power Query, maar is onafhankelijk van een programma, zoals Excel of Power BI. In afbeelding 5 zie je schematisch het verschil tussen Power Query en DataFlow. Je definieert op dezelfde manier als je doet in Power Query een kolom met jaren. Gebruik je nu deze tabel vanuit DataFlow, dan is de tabel al voorzien van de kolom en die kolom lijkt een onderdeel van de originele tabel te zijn. Overigens, ongemerkt gaan we steeds meer naar de professionele kant van dataverwerking toe.

Het laatste ETL programma is SQL (Structured Query Language), full-scale programmeertaal. SQL wordt gebruikt door de database professionals. Het grootse verschil ligt in de basis van de verschillende programma’s en daarmee de manier van werken. Excel is een functie taal, Power Query gebruikt een script en SQL is een programmeertaal. En dat laatste vraagt een forse studielast, specialistische kennis en is daarmee niet direct een optie voor de business. SQL is onder database professionals zeer populair, omdat het met veel (ook niet Microsoft) databases type werkt. 

Kijk je naar de Business kant van BI, dan is een product als Power Query het moderne gereedschap voor de Excel of Power BI gebruiker. Wat heeft dat nu voor invloed op jou?  Veel, heel veel. Kijk je naar jouw periodieke Excel rapportage modellen, die veel tijd kosten om samen te stellen, dan ga je op zoek naar berekeningen, die je in Power Query plaatst. Doe je dat systematisch dan ontdek je dat berekeningen worden vervangen door vooraf geprepareerde data. Het gevolg is dat jouw model sneller en efficiënter wordt. Waar wacht je nog op?

Henk Vlootman is founder en CEO van Quanto. Hij 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. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland

Lees ook: Hoe bouw je een data driven cultuur?