Het koppelen van gegevensbronnen is zo gebeurd. In dit artikel brengen we data samen met de editor en kijkt cm: hoe de gekoppelde gegevens worden weergegeven in de Power Query-tool. Ook gaan we alvast aan de slag met enkele simpele transformaties.

Nu we de editor een beetje hebben verkend, is het tijd om aan de slag te gaan. Om met gegevens te kunnen werken, moet u als eerste stap Power Query vertellen waar hij moet kijken. Dat kan al gaan om een bereik in een spreadsheet, maar de kracht van dit Excel-element zit in het gebruik van andere bronnen, bijvoorbeeld uit een Access-database, Sharepoint of Exchange. U kunt ook niet-Microsoft bronnen raadplegen, zoals een Oracle- of IBM-database, en zelfs vanuit PDF’s en websites. Voor deze tutorial gaan we aan de slag met een tabel van Wikipedia.

Zoals we in deel 1 hebben gezien, bewerkt Power Query deze bron niet, het Excel-element creëert een zoekvraag op de brongegevens. Het is dus heel makkelijk om te experimenteren met een werkmap, door bijvoorbeeld data te verfijnen, zonder dat daarbij iets verandert aan de map zelf. Na afronding in de editor opent u de nieuwe door Power Query-aangemaakte tabel bijvoorbeeld op een nieuw blad in de werkmap en deze zoekvraag (query) blijft gekoppeld aan de bron. Dat betekent dat wanneer u gegevens aanpast in de bron daarna de query kunt verversen om de eventuele wijzigingen direct binnen te halen.

Lees in deze reeks ook:
1. Wat is Power Query?
2. Een blik op de editor

Bestanden in Power Query

Om te beginnen, maken we een zoekvraag aan binnen Power Query op de voorbeeldtabel Financial Sample. Met de geopende spreadsheet gaat u naar het tabblad Gegevens (Data) en kiest u voor Gegevens ophalen en transformeren > Uit andere bronnen > Van tabel/bereik (Get & Transform Data > From Other Sources > From Table/Range). Dit opent de gegevens in de editor. U kunt ook in een leeg blad gegevens importeren vanuit een opgeslagen Excel-bestand via Gegevens ophalen > Uit bestand > Uit Excel-werkmap (Get Data > From File > From Excel Workbook). Dit opent de editor en daarin kunt u gegevens verfijnen.

Het basisprincipe is hetzelfde voor tekstbestanden, bijvoorbeeld een tabel in een Word-document. Of een CSV-bestand, een door komma’s gescheiden tekstbestand met data, vanuit een gedownload archief. Of een database-bestand van bijvoorbeeld Microsoft Access, Exchange, externe database, of wat dies meer zij. Deze vindt u allemaal onder het tabblad Gegevens (Data) en de knop Gegevens ophalen en transformeren (Get Data). We staan hier in dit stadium niet al te veel bij stil, want dit wijst zich vanzelf en voor geavanceerdere koppelingen zouden we wel heel erg de diepte ingaan voor een kennismaking met Power Query. Voor nu is belangrijk: dit is de plek waar u data koppelt.

Webpagina’s in Power Query

Om te laten zien hoe krachtig de feature is om te koppelen aan databases die hun bronbestanden bijwerken, gebruiken we een heel simpel voorbeeld: de webpagina. Dit is een databron waar iedere gebruiker bij kan en het niet nodig is om bijvoorbeeld een ODBC-koppeling te maken of in te loggen op een server. Voor dit voorbeeld gebruiken we een tabel van Wikipedia. In Power Query kunnen we deze eenvoudig transformeren. Daarnaast legt u verbinding met de tabel. Wanneer deze wordt bijgewerkt op Wikipedia, wordt deze update met Vernieuwen in Excel ook meegenomen.

In dit voorbeeld kijken we naar een tabel met daarop alle Nederlandse gemeenten. Deze is te vinden op: https://nl.wikipedia.org/wiki/Lijst_van_Nederlandse_gemeenten. Kopieer dit url naar het klembord (Ctrl + C) en ga naar Gegevens > Gegevens ophalen > Uit andere bronnen > Van het web (Data > Get Data > Other Sources > From The Web). Vul in het dialoogvenster dat omhoog komt de url in (Ctrl + V om hem te plakken van het klembord).

We kiezen hier voor de standaardinstellingen. Dat komt neer op toegang waarbij u niet hoeft in te loggen, zoals in tabellen van bijvoorbeeld CBS of in dit geval Wikipedia. Bepaalde online bronnen zullen vragen om inloggegevens en die kunt u hier instellen in het linkerveld, zodat u kunt koppelen aan een omgeving waar dat voor is vereist. Voor nu gaat u door met de standaardinstelling. Kies op Verbinden (Connect) om connectie te maken. (Bovenste optie: Anoniem.)

Voordat de editor wordt geopend, komt hier de Navigator naar voren. Daarin selecteert u welke tabel u nodig heeft. Regelmatig komt het voor dat er meerdere tabellen op een pagina staan. Wanneer u op een tabel in het linkerveld klikt, krijgt u rechts alvast een voorvertoning te zien, zodat u kunt bepalen of dit de juiste tabel betreft. De voorvertoning is beperkt tot enkele tientallen regels, zodat het geen minuten duurt om al deze informatie in dit stadium in te laden. U kunt bovenaan nog op de tab Webweergave (Webview) klikken om een stuk van de tabel te zien zoals hij in de browser te zien is. Klik op Gegevens transformeren (Edit). Dat opent de editor met daarin 347 Nederlandse gemeenten en specifieke informatie per gemeente:

We gaan hier alvast een paar kleine transformaties uitvoeren. Zo zien we hier 351 rijen, terwijl er 347 gemeenten zijn. Dat komt omdat er kopinformatie is meegenomen als reguliere tabelinformatie. Rijen 1, 2, 350 en 351 kunnen worden verwijderd. Dat doet u met Rijen verwijderen > Bovenste rijen verwijderen (Remove Rows > Remove Top Rows) en bij Aantal (Number of rows) 2 in te vullen. Doe hetzelfde met Rijen verwijderen > Onderste rijen verwijderen (Remove Rows > Remove Bottom Rows). Nu hebben we 347 rijen voor 347 gemeenten.

De tweede transformatie is interessanter voor wat we uiteindelijk willen bereiken. We willen bijvoorbeeld een standaardzoekvraag maken waarbij alleen gemeentes van Noord-Brabant van 50.000 inwoners of meer worden weergegeven in de tabel binnen Excel. Dat doen we heel simpel door een filter in te stellen op de kolom Provincie. Klik op het pijltje in de kolomkop om de filteropties te openen. Deselecteer alles, vink enkel Noord-Brabant aan en klik op OK. Daarmee blijven er 56 gemeentes over.

Om te filteren op inwoners, moeten we dat veld eerst instellen op getal. Het gegevenstype van de kolom Inwoneraantal staat namelijk nu ingesteld op tekst (te herkennen aan het ABC-pictogram). Daardoor zijn er enkel tekstfilters beschikbaar, maar geen numerieke opties. Klik met de rechtermuisknop op de kolomkop en kies Type wijzigen > Geheel getal (Change Type > Whole Number). Nu kunnen we filteren met numerieke gegevens. Klik op het pijltje in de kolomkop en kies Getalfilters > Groter dan of gelijk aan (Number Filters > Greater Than or Equal To). Stel hier de drempelwaarde 50.000 in. Dat levert in totaal 13 Noord-Brabantse gemeenten op, met Oosterhout als kleinste (56.596 inwoners) en Eindhoven als grootste (238.478 inwoners).

Vervolgens verwijderen we alle kolommen die we niet meer nodig hebben en behouden alleen nog maar de naam van de gemeente en het inwonersaantal. Klik op de kolommen die weg kunnen en kies voor Kolommen verwijderen (Delete Columns) Om vervolgens de tabel van de zoekvraag (geef me gemeentes in Noord-Brabant van 50.000 inwoners of meer en hun inwonersaantallen) daadwerkelijk in Excel te krijgen, klikt u op Sluiten en laden > Sluiten en laden, of Sluiten en laden naar… als u bijvoorbeeld een specifieke werkmap in gedachten heeft (Close and Load > Close And Load, of Close And Load To… om de tabel weer te geven op een spreadsheet die u kunt sorteren en bewerken zoals u van Excel gewend bent:

De bewerkingen die u In Power Query aanbrengt, vormen de zoekvraag. Deze query plaatst u vervolgens in uw werkmap en de volgende keer dat u de informatie nodig heeft, raadpleegt u de query. Dan hoeven alle transformaties niet opnieuw te worden uitgevoerd: de verfijningen die u heeft aangebracht zijn in het resultaat aanwezig. Ook een bijgewerkte tabel, als er een update is geweest op de website, wordt verwerkt in de query. Als er bijvoorbeeld een herindeling is geweest in Noord-Brabant, wordt dit weergegeven in de tabel, maar een herverdeling in Utrecht niet. Een gemeente die wordt opgesplitst en onder de 50.000 inwoners valt, verdwijnt eveneens uit de tabel als deze informatie wordt ververst.

Tot nu toe hebben we nog weinig gedaan wat niet ook in het ouderwetse Excel kan, zij het wat directer in Power Query. Maar met deze basis is het eenvoudig om snel antwoord te geven op vragen waar u veel meer tijd kwijt bent in Excel als u met een handmatige tabel aan de slag gaat. In deel 4 kijken we naar enkele van deze tijdbesparende transformaties.

Volg CMWeb op LinkedIn!