Power Query en Power Pivot zijn twee aparte hulpmiddelen binnen Excel. Power Query haalt gegevens binnen en maakt ze schoon. Power Pivot houdt meerdere tabellen samen, zodat je ze als één geheel kunt samenvatten. Weten welke je moet gebruiken, scheelt uren herhaald handwerk.
Power Query: één keer opschonen, altijd vernieuwen
Je begint op het tabblad Gegevens met Gegevens ophalen. Power Query kan een CSV- of Excel-bestand lezen, een hele map met bestanden, een database of een webpagina. De gegevens openen in de Power Query-editor, waar je lege rijen verwijdert, kolommen splitst, typen wijzigt, filtert en nog veel meer.
Elke handeling wordt opgeslagen als een toegepaste stap in een lijst rechts. Klik op een stap om de gegevens te zien zoals ze op dat punt waren, of verwijder een stap die je niet meer wilt. Als je Sluiten en laden kiest, komt het resultaat in een werkbladtabel of in het Gegevensmodel.
Zet volgende maand de nieuwe export op dezelfde plek en druk op Alles vernieuwen. Dezelfde stappen worden opnieuw uitgevoerd. Je oorspronkelijke bestand wordt nooit gewijzigd: Power Query leest er alleen uit.
Taken die het goed doet
- Toevoegen: stapel twaalf maandbestanden met dezelfde kolommen tot één tabel.
- Samenvoegen: koppel twee tabellen aan een gedeelde kolom, een beetje als opzoeken, maar met alle overeenkomsten in één keer erbij.
- Ontdraaien: zet een brede tabel met één kolom per maand om in een overzichtelijke lijst met de kolommen Maand en Waarde. Die is veel makkelijker samen te vatten.
- Tekst opschonen: spaties verwijderen, hoofdletters corrigeren, "Smith, Ann" splitsen in twee kolommen.
Als je dezelfde opschoning meer dan twee keer met de hand doet, is het waarschijnlijk een Power Query-taak.
Power Pivot: meerdere tabellen, één samenvatting
Stel je een tabel Orders voor met een kolom CustomerID en een aparte tabel Customers met de regio van elke klant. Zonder Power Pivot zou je de regio met een opzoekfunctie naar Orders kopiëren. Met Power Pivot voeg je beide tabellen toe aan het Gegevensmodel en leg je een relatie tussen de twee kolommen CustomerID. Een draaitabel kan de bestellingen dan direct per regio groeperen.
Het Gegevensmodel slaat data ook in gecomprimeerde vorm op, zodat het veel meer rijen aankan dan de limiet van 1,048,576 rijen van een werkblad. Je kunt je eigen maatstaven schrijven in een formuletaal die DAX heet, bijvoorbeeld een totaal dat geannuleerde bestellingen negeert.
Kiezen, en een paar waarschuwingen
- Rommelige of wisselende invoer: Power Query.
- Meerdere verwante tabellen, zeer grote data, herbruikbare maatstaven: Power Pivot (het Gegevensmodel).
- Kleine, nette, eenmalige data: gewone formules of een gewone draaitabel zijn genoeg.
- Welke functies beschikbaar zijn, hangt af van de Excel-editie en het platform. Het Power Pivot-venster in het bijzonder zit niet in elke editie, en Excel voor Mac heeft minder opties dan Excel voor Windows.
De twee werken goed samen: maak schoon met Power Query, laad naar het Gegevensmodel, en leg daarna de relaties en vat samen.