Tegenwoordig heeft de moderne administrateur-controller te maken met diverse en veelal grote bestanden die men wenst te analyseren. Eer het zover is dienen de bestanden te onderworpen worden aan allerlei bewerkingen. In de praktijk maakt men gebruik van diverse zoekfuncties zoals VERT.ZOEKEN om de bestanden met elkaar te verbinden. In dit artikel laat ik u zien hoe u dat eenvoudiger en sneller voor elkaar kunt krijgen met Power Query (Ophalen en Transformeren). U kunt Power Query gratis downloaden als add-in programma voor Excel 2010 en Excel 2013. Vanaf Excel 2016 en ook in Office365 is dit feature reeds in het Lint ingebouwd onder Gegevens <> Ophalen en Transformeren.
Te bewerken databestanden
In dit voorbeeld gaan we werken met het wereldwijde energieverbruik van de Verenigde Naties. Daartoe laad ik van hun website twee CSV-bestanden:
- Totaalverbruik per land
- Privégebruik per land
De indeling van deze Comma Separated Value bestanden ziet er als volgt uit:
Bestanden inladen en bewerken met Power Query
In Excel 2016 kiest u uit het Lint: Gegevens <> Ophalen en Transformeren <> Nieuwe Query <> Uit bestand <> Uit CSV-bestand.
We selecteren het gewenste bestand (UnData_TotalConsumption), waarna het Query-Editor venster wordt geopend.
Links ziet u het bestand in onbewerkte toestand en rechts in het paneel voor de Query-instellingen worden de stappen bijgehouden als we het bestand transformeren.
Uit het lint kiezen we: Start Transformeren Kolom splitsen Op scheidingsteken Komma OK.
Daarna kiest u uit het Lint: Transformeren <> Tabel <> De eerste rij als veldnamen gebruiken
We dienen in de kolom Quantity het decimaalscheidingsteken van punt te veranderen in komma. Selecteer de kolom Quantity. Klik met de rechtermuis en uit het verkort menu selecteert u: Waarden vervangen.
Bij Te zoeken waarde vult u een punt in en bij Vervangen door geeft u een komma in. Daarna klikt u op OK. De gegevens in Quantity zijn nog als tekst weergegeven. Ze moeten nog als waarden worden weergegeven. Kies uit het Lint: Start <> Transformeren <> Gegevenstype: decimaal getal.
We willen het Jaar ook als getallen weergeven. Selecteer de kolom Jaar en uit het Lint kiest u: Start <> Transformeren <> Gegevenstype: geheel getal.
Tenslotte willen we de kolom Quantity Footnotes verwijderen. Markeer deze kolom en uit het Lint selecteert u: Start <> Kolommen beheren <> Kolommen verwijderen.
We gaan een nieuwe kolom toevoegen. Kies uit het lint: Kolom toevoegen <> Aangepaste kolom toevoegen. Vul het dialoogvenster als volgt in:
Klik op OK.
De Query-Editor ziet er nu als volgt uit:
We gaan nu het tweede bestand inladen. Dat kan direct geschieden vanuit Power Query. Uit het Lint kiest u: Start <> Nieuwe Query <> Nieuwe bron <> Bestand <> CSV UnData_PrivateConsumption <> Importeren.
In het linker gedeelte van de Query_Editor –venster ziet u twee Query’s.
Door het aanklikken van de gewenste Query kunt u tussen de twee Query’s wisselen. In het tweede bestand dienen we dezelfde stappen te herhalen als in de voorgaande Query. Aangezien de lay-out van het tweede bestand identiek is aan het eerste bestand, kunnen we tijd sparen door de Query-code te kopiëren. Deze Query-code heet M-code.
Dit gaat als volgt:
- Ga naar het eerste Query bestand (UnData_TotalConsumption)
- Kies uit het Lint: Start Query Geavanceerde Editor
- Markeer de M-code en druk op Control C
- Sluit het venster
- Ga naar de tweede Query bestand (UnData_PrivateConsumption)
- Kies uit het Lint: Start Query Geavanceerde Editor
- Markeer de aanwezige M-code
- En vervang deze door te drukken op Control V
- In de M-code vervangt u het woord Total door Private
- Klik daarna op Gereed.
U zult zien dat de tweede Query nu dienovereenkomstig is aangepast.
Query’s samenvoegen
Nu gaan we de twee query’s samenvoegen. Uit het Lint kiest u Start <> Combineren <> Query’s samenvoegen. Het volgende dialoogvenster verschijnt:
Kies het tweede bestand uit met behulp van de keuzelijst. Nu dient u de twee query’s met elkaar te verbinden door de overeenkomende kolommen in beide query’s te markeren. Daarbij dient u de Control-toets ingedrukt te houden om de kolommen Country or Area en Year te kunnen markeren.
Klik dan op OK.
U kunt de bewerkte gegevens overbrengen naar Excel. Uit het lint kiest u: Start <> Sluiten en Laden. Het blad ziet er als volgt uit:
De gegevens zijn automatisch geconverteerd naar een Excel tabel.
Wanneer de brondata gewijzigd zijn kunt u de tabel in Excel direct verversen door uit het lint te kiezen: Gegevens <> Verbindingen <> Alles vernieuwen.
Slotwoord
Met Power Query (Ophalen en Transformeren) kunt u de meest gangbare bewerkingen interactief regelen. Via de Query Editor worden de stappen in het geheugen opgeslagen. Zodra de brondata gewijzigd zijn, hoeft u alleen de Excel tabel te verversen. Zonder dit feature zou u de bewerkingsstappen via een VBA-macro en/of SQL- statements moeten regelen. De stappen in Power Query worden in de achtergrond opgeslagen in een SQL-achtige editor genaamd M-code. Met meer kennis over de M-code kunt u ingewikkelde bewerkingsstappen programmeren die niet interactief geregeld kunnen worden.
