---

---

Deze ‘nieuwe’ naam geeft ook goed aan, waar Power Query vooral voor bedoeld is: ondersteuning bij het binnenhalen van externe bestanden, inclusief het gestandaardiseerd aanpassen en verrijken van de gegevens. Met gestandaardiseerd bedoel ik, dat alle handelingen die verricht moeten worden om externe gegevens in Excel in te lezen, vastliggen in een geautomatiseerde procedure. En deze procedure wordt dan iedere keer (dag, maand, kwartaal) op precies dezelfde manier doorlopen zodat fouten, die bij handmatige acties nog wel eens willen voorkomen, worden vermeden.
Niet alleen leuk voor u, maar ook voor uw controller en interne en externe accountant!

In dit artikel komen de eerste grondbeginselen en de basis-werking van Power Query aan bod.

Extern bestand inlezen

In het Voorbeeldbestand (een zip-bestand; pak dit uit en plaats de 3 bestanden ergens op uw PC) zitten ook twee tekstbestanden (Serie1.txt en Serie2.txt). We gaan het eerste bestand met Power Query/Ophalen en transformeren (hierna altijd met PQ aangeduid) in Excel inlezen:

4. Excel interpreteert zo goed mogelijk de gegevens; hij ‘ziet’ welk scheidingsteken er is gebruikt etc.
De gegevens zien er goed uit, inclusief een kopregel, dus we kunnen deze gaan laden in Excel: kies het ‘vinkje’ naast Laden en kies de optie Laden naar …
5. zorg dat de button bij Tabel is geselecteerd en kies Laden

Het resultaat van de query komt automatisch in een Excel-tabel (in dit geval met de naam Serie1). Rechts in het scherm ziet u dat de query 15 rijen heeft binnengehaald (zie het tabblad Tekst in de werkmap PowerQuery.xlsx in het Voorbeeldbestand).

NB het overzicht is alleen maar zichtbaar als in de menutab Gegevens in het blok Ophalen en transformeren de optie Query’s weergeven is aangeklikt.

Maar…
het zou mooier zijn, als de namen in Soort met een hoofdletter zouden beginnen en het resultaat op datum zou zijn gesorteerd. Dit kan natuurlijk makkelijk in Excel, maar we gaan er voor zorgen dat dit direct iedere keer bij het inlezen al gebeurt.

Extern bestand inlezen en transformeren

Op dezelfde manier als hiervoor gaan we Serie1.txt opnieuw inlezen maar vanaf stap 4 wordt het anders:

4. kies de optie Gegevens transformeren

5. in de Power Query-editor is te zien welke stappen in de procedure Excel al heeft uitgevoerd: een Bron gekozen, iets met de kopregel en een Type gewijzigd (voor details: klik op een van de stappen en kijk in de formulebalk van de editor).
NB Power Query gebruikt een eigen programmeertaal, M. In dit artikel gaan we daar verder niet op in; wel belangrijk om te weten is, dat deze programmeertaal hoofdlettergevoelig is.
We gaan een stap toevoegen, namelijk het sorteren op datum: klik op het vinkje naast Datum en kies de optie Oplopend sorteren.
Er komt dan een procedure-stap bij: Rijen gesorteerd.

‘Normaal’ zou je kiezen voor een Aangepaste kolom toevoegen, maar de programmeurs van Microsoft hebben een prachtige optie Kolom toevoegen vanuit voorbeelden bedacht: klik op de tabel-button links van de kolomnamen.
In de eerste regel staat (na de sortering) bij Soort de tekst een; in het invulveld onder de nieuwe kolomkop Kolom1 tikken we Een en drukken op Enter.
PQ snapt ons: alle andere soorten zijn nu ook omgezet! Zie ook bovenaan waar een nieuwe transformatie-regel is vermeld: Text.Proper([Soort]). Dus klik op OK.
7. wijzig de kolomkop in Srt door dubbelklikken
8. plaats de nieuwe kolom links van Bedrag (met de muis slepen)
9. verwijder de kolom Soort (rechts klikken op de kolomkop)
10. klik op het vinkje bij Sluiten en laden en kies de optie Sluiten en laden naar. Zorg dat de Tabel-button aan staat en dat de tabel op het bestaande werkblad naast het vorige resultaat komt te staan; klik dan op de button Laden.

NB1 wanneer het bron-bestand Serie1.txt opnieuw wordt aangeleverd (met nieuwe gegevens), dan is het voldoende om deze resultaat-tabel te vernieuwen (ergens in de tabel rechts klikken).

NB2 wanneer je in het Voorbeeldbestand Vernieuwen kiest, zul je een foutmelding krijgen: de directory-structuur op mijn PC ziet er natuurlijk anders uit dan bij u. Hoe je dit oplost, leg ik hieronder uit.

Bestaand bestand transformeren

Maar ik zie nu, dat de bedragen verkeerd worden aangeleverd: het decimale teken is weggelaten.

We gaan daarom het vorige resultaat aanpassen:

1. klik ergens in de resultaat-tabel (die heeft van Excel de naam Serie1_2 gekregen)
2. kies dan in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel
3. kies, net als hiervoor, de optie Kolom toevoegen vanuit voorbeelden
4. in de eerste regel staat het bedrag 146; dit wordt dan 1,46 in de nieuwe kolom (en druk op Enter)
5. Excel begrijpt ons nog niet; in de tweede regel tikken we 1,79 (en Enter)
6. de transformatieregel wordt [Bedrag]/100. Precies wat we wilden! Klik op OK.
7. kolomkop wijzigen in Bedr, de kolom Bedrag verwijderen en dan Sluiten en laden (zorg dat het resultaat naast de andere 2 komt te staan).

Bestaande query aanpassen

Hey, wat is er met de Datum gebeurd? Die heeft ineens ook een tijdaanduiding. Zo slim is Excel nu ook weer niet 😉

1. dubbelklik in het blok Werkmapquery’s op de laatste query (met de naam Serie1_2)
2. klik op de kolomkop Datum
3. kies dan in het blok Transformeren de optie Gegevenstype en wijzig dit in Datum
4. ga terug via Sluiten en laden

Voor analyse-doeleinden is het handig om ook een kolom Jaar en Maand te hebben. In het vierde blok op het tabblad Tekst van het Voorbeeldbestand zijn deze kolommen toegevoegd (en ook de Dag).

Zelfde query, ander bestand of andere plaats

In het tabblad Tekst2 van het Voorbeeldbestand staat het resultaat van dezelfde query als hiervoor in de vierde tabel; deze haalt de gegevens uit Serie1.txt.

Wanneer u deze query vernieuwt (via rechtsklikken op het resultaat) zult u een foutmelding krijgen.

Dat is snel opgelost:

1. dubbelklik op de betreffende query (deze heeft de vermelding De download is niet voltooid.)
2. klik op Bron in de Toegepaste stappen
3. wijzig in de formulebalk de naam achter File.Contents zodanig dat verwezen wordt naar de map op uw PC, waarin het Voorbeeldbestand is uitgepakt.
4. ga terug via Sluiten en laden

LET OP rond de map- en de bestandsnaam moeten de aanhalingstekens blijven staan.

Dezelfde handelingen moet u uitvoeren wanneer u niet de gegevens van Serie1 wilt ophalen maar die van Serie2. Wijzig in de Bron de naam van het bestand en sluit de query-editor.

NB De omvang van de resultaat-tabel verandert automatisch met de grootte van het bronbestand.

Bestanden combineren

Hiervoor hebben we gezien hoe je een tekst-bestand geautomatiseerd kunt inlezen. Ook hoe je achter elkaar verschillende bestanden kunt binnenhalen.

Maar PQ kent een veel handiger manier om gelijksoortige bestanden in te lezen en wel naar één resultaat-tabel. Laten we dit eens gaan uitproberen met de 2 tekstbestanden uit het Voorbeeldbestand.

1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
2. kies in het vervolg-menu de optie Uit bestand en daarna Uit map
3. kies via Bladeren de map met de 2 tekstbestanden en klik op OK
4. klik in het vervolgscherm (met een overzicht van alle bestanden in de betreffende map) de button Gegevens transformeren.
5. We willen alleen de tekstbestanden inlezen: kies het vinkje naast Extension en zorg dat alleen de extensie txt wordt gefilterd.
6. kies de button Bestanden combineren naast Content
7. in het vervolgscherm kiezen we OK (alle instellingen staan goed)
8. maak twee nieuwe kolommen aan (via de optie Voorbeeld; zie hiervoor): Bron wordt de Source.Name zonder extensie en Bedr wordt Bedrag/100
9. verwijder de kolommen Source.Name en Bedrag en verplaats de nieuwe kolom Bron naar voren. Maak een sortering: eerst op Bron en als tweede op de Datum. Kies daarna Sluiten en laden.

Van internet naar Excel

Soms is het handig om gegevens van een internet-pagina over te halen naar Excel (zie ook het artikel Excel en het world-wide-web).

We zullen hier laten zien hoe je de dagkoersen van de AEX kunt binnenhalen.

LET OP1 Excel kan alleen gegevens, die in een tabelvorm op internet worden gepresenteerd, inlezen.

LET OP2 In Excel zal het AEX-overzicht altijd iets achter lopen in de tijd; door de makers van de website is dit als bescherming opgenomen.

1. kies in de menutab Gegevens in het blok Ophalen en Transformeren de optie Nieuwe query
2. kies in het vervolg-menu de optie Uit andere bronnen en daarna Van het web
3. tik de gewenste URL in of kopieer die uit de adresregel van uw browser als u weet welke pagina u wilt binnenhalen; in dit geval www.beurs.nl/koersen/AEX en druk op OK.
4. in de Navigator, die dan opent, blijkt dat we Table 0 moeten hebben. Kies Gegevens transformeren.
5. nu kunt u nog kolommen verwijderen, verplaatsen en andere gewenste aanpassingen doorvoeren
6. kies Sluiten en Laden naar en plaats het overzicht op een nieuw werkblad (zie het tabblad AEX in het Voorbeeldbestand).

Het is wel handig als deze tabel zich automatisch vernieuwt:
klik in de menutab Gegevens in het blok Verbindingen op het vinkje bij Alles vernieuwen.
Kies de optie Eigenschappen van verbinding.
Zorg dat de optie Vernieuwen om de 1 minuten is aangevinkt.
Klik op OK.

De tabel kan wel wat opmaak gebruiken:

1. voeg tijdens de transformatie in PQ een kolom StDal toe; probeer het uit via enkele voorbeelden. De formulebalk moet de volgende formule bevatten:
   = Table.AddColumn(#”Type gewijzigd”, “StDal”, each if [#”+/-“] > 0 then 1 else if [#”+/-“] = 0 then 0 else -1, Int64.Type)
2. geef de nieuwe kolom in Excel een mooie Voorwaardelijke opmaak
3. zorg via opmaak dat alle getallen de juiste opmaak en het gewenste aantal decimalen krijgen.

Het resultaat staat in het tabblad AEX2 van het Voorbeeldbestand:

---