De juiste koers met PowerQuery
Wie weet nog de naam van de Sloveense munteenheid voordat de euro werd ingevoerd? Hoeveel lires of Belgische francs er in een gulden gingen? En een week later? De euro heeft internationaal zakendoen stukken eenvoudiger gemaakt. Maar voor het zakendoen met landen die niet de euro gebruiken, blijven wisselkoersen belangrijke informatie. Natuurlijk, die zijn op te vragen bij je bank, en tegenwoordig voor iedereen online, real time beschikbaar.
Gebruik PowerQuery om met één klik de meest recente koersen in je Excel-administratie te verwerken.
PowerQuery kan informatie van internet ophalen, en de link naar de betreffende website vasthouden. Verandert er iets op de website, dan kun je je Excel-bestand direct bijwerken. Probeer onderstaand voorbeeld maar eens uit!
De wisselkoersen voor dit voorbeeld haal ik van de website www.x-rates.com. Deze site bestaat uit meerdere pagina’s, dus ik navigeer naar de website om PowerQuery straks de juiste URL te kunnen geven:
De benodigde wisselkoersen staan in een tabel op de pagina Rates Table. Aan de linkerkant heb ik de omrekeneenheid aangepast van dollar naar euro.
Op de gekozen pagina staan twee tabellen: een top 10 en de complete lijst, alfabetisch gesorteerd. Die complete lijst wil je in Excel gebruiken. Daartoe klik je in de adresbalk van je browser, selecteer je de URL en kopieer je deze naar het klembord:
https://www.x-rates.com/table/?from=EUR&amount=1
Je kunt nu je browser sluiten, die heb je voor dit voorbeeld niet meer nodig.
Ga naar Excel. Je start PowerQuery vanaf het tabblad Gegevens, in het blok Gegevens ophalen en transformeren.
In Office 365 heb je een knop: Van het web. Heb je een andere Excel-versie en zie je de knop niet, klik even op de verschillende mogelijkheden tot je een vergelijkbare keuze hebt.
PowerQuery start met het vragen naar de URL waarop de benodigde info staat:
Plak de gekopieerde URL in de URL-regel, en klik op OK.
In het Navigator-venster zie je links de tabel(len) die PowerQuery op de pagina heeft gevonden. Selecteer een tabel om het voorbeeld te bekijken. Als je twijfelt, kun je in de webweergave de gekozen tabel zien in HTML-opmaak.
De benodigde tabel kun je nu rechtstreeks in Excel plakken met de knop Laden. Wil je nog iets wijzigen aan de gegevens, een kolom splitsen, toevoegen of wat dan ook, klik dan op Gegevens transformeren. PowerQuery opent dan de tabel in het PowerQuery-venster. Hieronder zie je een weergave van dit venster:
Er is veel vertrouwds aan het PowerQuery-venster. Er is een lint met meerdere tabbladen en een formuleregel. Rechts naast de kolomkoppen vind je de knoppen om het sorteer-/filtermenu te openen, zoals je dat ook hebt bij een tabel in Excel. Links voor de kolomkopnaam zie je het symbool dat aangeeft hoe PowerQuery de gegevens heeft geïnterpreteerd. Als tekst, als getal of als datum. Klik op zo’n symbool om het menu te openen waarmee je de instelling kunt wijzigen.
Helemaal rechts in het PowerQuery-venster vind je de queryinstellingen:
Onder de EIGENSCHAPPEN kun je een passende naam voor je query opgeven. Onder de TOEGEPASTE STAPPEN vind je een lijst met alle stappen die door PowerQuery en door de gebruiker zijn gezet. Door op het kruisje voorafgaand aan de stap te klikken, maak je deze stap ongedaan. PowerQuery heeft geen ‘pijltje terug’-toets en Ctrl-Z werkt niet. Ten onrechte uitgevoerde stappen kun je alleen ongedaan maken door ze uit deze lijst te verwijderen. (In een volgende aflevering van dit spannende feuilleton kom ik hier uitgebreider op terug.)
In dit voorbeeld met de wisselkoersen hoeft er aan de gegevens niets te worden aangepast. Wij gaan er een Excel-tabel van maken. Klik hiertoe op de knop Sluiten en laden, vooraan in het lint.
De gegevens worden nu in Excel geplakt. Het PowerQuery-venster sluit, en er verschijnt rechts in je werkbladvenster een paneel met de gemaakte query.
Hoover met je muis over de naam van de query om een voorbeeld te zien en enkele bewerkingsknoppen. Dubbelklik op de naam van je query om het PowerQuery-venster te heropenen. Het menu dat verschijnt als je met je rechtermuisknop klikt op de naam van je query bevat een aantal nuttige opties.
Het grote gemak zit hem er echter in dat je de koersen in je tabel kunt laten bijwerken met de laatste gegevens van de website, enkel door te klikken op de knop (Alles) Vernieuwen. Deze knop vind je op meerdere plaatsen op het lint:
De query heeft de link naar de website bewaard, haalt zelfstandig de nieuwe koersinformatie op en werkt je tabel bij.
Een volgende keer meer over het aanpassen van brongegevens in PowerQuery. Wij laten PowerQuery gegevens ophalen uit een map, passen die gegevens aan om ze goed te kunnen gebruiken in Excel en maken er een draaitabel van. Worden er nieuwe gegevens toegevoegd aan de map, dan voegt PowerQuery die toe aan je draaitabel. Wederom met één druk op de knop Vernieuwen.