Tagarchief: Power Query

Data verrijken

Excel, , , , ,
Voorbeeldbestand: DataVerrijkenDownload

Iedere dag krijgen of vinden we wel gegevens, waarvan je denkt: interessant, maar als ik die met een andere set combineer (verrijken van data) kan ik er nog veel meer informatie uit halen.

In dit artikel geef ik uitleg over verschillende methoden zoals bestaande tabellen uitbreiden met extra kolommen, gebruik maken van het Excel-gegevensmodel en het gebruik van Power Query.

Brongegevens

We gaan als voorbeeld een Excel-tabel gebruiken met daarin het aantal verkochte producten, uitgesplitst naar datum, klant- en productcode.
In het tabblad Verkoop van het Voorbeeldbestand vindt u 1000 records in de Excel-tabel tblVerkoop.

Welke klanten komen hierin voor? Welke producten zijn verkocht? Over welke periode gaat dit bestand?
Meer vragen dan antwoorden.

Verkoopoverzicht

Om snel inzicht te krijgen in bovenstaande vragen maken we op basis van het bestand een draaitabel:

  1. Plaats de cursor ergens in de tabel tblVerkoop.
  2. Kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel en klik op OK.
  3. Sleep de Datum naar de Rijen, Aantal naar het Waarden-gebied en Prod naar Kolommen.

We zien dus direct (tabblad OvzVerkoop) dat het 6 producten betreft en dat het over de periode 2018-2020 gaat.

Klik je op de + voor een jaar, dan zie je welke kwartalen en maanden er in het overzicht voorkomen.

NB1 vanaf versie 2016 zal Excel datums, die in de kolommen of rijen worden geplaatst, direct groeperen naar Jaren, Kwartalen en Maanden.
Gebeurt dit niet automatisch, klik dan met de rechtermuisknop op een datum en kies Groeperen; selecteer daar dan de gewenste opties.

NB2 wil je geen groepering naar kwartaal? Klik rechts op een van de tijdaanduidingen, kies Groeperen en deselecteer de optie Kwartalen.

Maar hoe zit het met de klanten?

  1. Verwijder Prod uit de Kolommen.
  2. Verplaats Jaren van Rijen naar Kolommen.
  3. Sleep Klanten naar Rijen.

Zie het tabblad OvzVerkoop.

NB Excel plaatst de klanten bij het aanmaken van de draaitabel netjes in alfabetische volgorde, maar door de methode van codering komt K10 direct na K1. Gelukkig is dit snel verholpen: selecteer de cel met K10, ‘pak’ met de muis de rand en sleep de code naar beneden.

‘Toevallig’ weet ik dat de klanten over 2 regio’s zijn verdeeld: K2, K5, K7 en K8 horen bij de regio Zuid, de anderen bij Noord.
Die moeten we nu dus nog groeperen (zie Groeperen in een draaitabel): selecteer de draaitabel-rij met K2 (het muis-symbool is dan een pijltje naar rechts), houd Ctrl ingedrukt en selecteer op dezelfde manier K5, K7 en K8. Klik rechts en kies Groeperen. Nog even de namen van de groepen aanpassen (gewoon overschrijven) en de groep Noord naar boven verslepen: klaar!

Op deze manier hebben we handmatig de eerste verrijkingen aangebracht.

Verrijking door toevoegen kolommen

Maar we weten nog meer over onze klanten en de producten:

Tabblad Klant
Tabblad Product

Hiermee kunnen we onze dataset uitbreiden. Dezelfde gegevens van het tabblad Verkoop zijn allereerst gekopieerd naar VerkBerek van het Voorbeeldbestand.

Aan de Excel-tabel (met de naam tblVerkBerek) zijn nieuwe kolommen toegevoegd:

  1. Regio, in cel F3 staat de formule:
    =INDEX(tblKlant[Regio];VERGELIJKEN([@Klant];tblKlant[Klant];0))
    NB1 hier is een alternatief voor verticaal zoeken gebruikt. De avz-truc komt goed van pas! Zie zoeken-index-en-vergelijken.
    NB2 alle berekeningen worden binnen een Excel-tabel uitgevoerd, waardoor we gestructureerde verwijzingen kunnen gebruiken. Zie voor de voordelen van tabellen: kunst-en-excel.
    NB3 na het invoeren van de formule in F3 wordt deze automatisch naar beneden doorgevoerd, zodat alle cellen in die kolom een consistente formule hebben.
  2. In cel G3 halen we op een vergelijkbare manier de naam van het product op:
    =INDEX(tblProd[ProdNaam];VERGELIJKEN([@Prod];tblProd[Product];0))
  3. De totale kosten, die met een verkoop zijn gemoeid worden in cel H3 bepaald:
    =[@Aantal]* INDEX(tblProd[KostPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  4. In cel I3 bepalen we de omzet:
    =[@Aantal]* INDEX(tblProd[VerkPrijs]; VERGELIJKEN([@Prod];tblProd[Product];0))
  5. Daarmee kunnen we in J3 de bruto-winst berekenen:
    =[@Omzet]-[@Kosten]
  6. Maar (sommige) klanten krijgen korting; in cel K3:
    =INDEX(tblKlant[Korting];VERGELIJKEN([@Klant];tblKlant[Klant];0))
  7. Waarmee we ook een netto-winst kunnen bepalen in cel L3:
    =[@Omzet]*(1-[@Korting])-[@Kosten]

Op basis van deze nieuwe dataset kunnen we diverse analyses uitvoeren, uiteraard met behulp van een draaitabel. Bijvoorbeeld (zie tabblad OvzVerkBerek van het Voorbeeldbestand):

Verrijken m.b.v. gegevensmodel

Een nadeel van de vorige methode is, dat het bestand meteen een stuk groter wordt wanneer er kolommen aan een tabel worden toegevoegd. Dat valt nog wel mee als het over 1.000 records gaat, maar als het er een miljoen zijn en als het meer kolommen betreft….

De koppelingen, die we hiervoor met Index/Vergelijken hebben gemaakt, kunnen sinds versie 2013 ook intern in Excel met behulp van een gegevensmodel worden vastgelegd zonder dat dit extra ruimte in beslag neemt. Wel moeten de basisgegevens in Excel-tabellen vastliggen. In het Voorbeeldbestand bevatten de tabbladen Verkoop, Klant en Product onze basisgegevens.

Nu gaan we het gegevensmodel vullen door de relaties tussen deze tabellen vast te leggen:

  1. Kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Relaties (de button met 3 tabellen en lijntjes daar tussen).

  1. U legt een relatie vast door op de knop Nieuw te klikken:

    De eerste tabel moet de gegevens bevatten, die uitgebreid gaan worden; in dit geval dus tblVerkoop.
    We gaan via de klant-code gegevens opzoeken, dus geven als kolom Klant op.
    Daaronder moeten we aangeven in welke tabel we gaan zoeken, in het huidige voorbeeld tblKlant.
    Ook in deze tabel bevat de kolom Klant de overeenkomende gegevens.
    Klik op OK.
    NB1 in de laatste stap staat het woord primair; hiermee wordt bedoeld, dat de kolom unieke gegevens moet bevatten. Anders kan er geen relatie aangemaakt worden.
    NB2 heb je meer dan 1 kolom nodig om een relatie te leggen (bijvoorbeeld Naam en Afd) dan zul je een kunstgreep moeten uithalen: in beide tabellen moet je de 2 kolommen aan elkaar koppelen in een nieuwe kolom, bijvoorbeeld Naam&Afd.
  2. Maak op dezelfde manier ook een relatie tussen tblVerkoop en tblProd; de kolomnamen die daarbij gebruikt worden zijn respectievelijk Prod en Product.
    De namen hoeven dus niet hetzelfde te zijn.
  3. Sluit het scherm Relaties beheren.

Nu het gegevensmodel is gevuld kunnen we op basis daarvan overzichten maken met behulp van draaitabellen (in het tabblad OvzVerkModel1 van het Voorbeeldbestand staat een voorbeeld):

  1. Plaats de cursor in een lege cel. Hier zal het overzicht komen, dus meestal doe je dit op een nieuw tabblad.
  1. Start via Toevoegen/Draaitabel een nieuwe draaitabel
  2. Hiernaast is te zien, dat Excel nu automatisch weet, dat we ons gegevensmodel als bron willen gebruiken. Klik OK.

  1. Het overzicht van draaitabel-velden ziet er iets anders uit dan bij een ‘normale’ draaitabel.
    Alle tabellen uit het gegevensmodel zijn beschikbaar met alle bijbehorende kolommen.
    Hiernaast zijn alle 4 gebieden gevuld met gegevens uit 3 verschillende tabellen.
    NB1 bevat de werkmap nog meer Excel-tabellen (net als het Voorbeeldbestand), dan zijn die ook in dit overzicht zichtbaar.
    NB2 heb je velden naar de gebieden versleept en klik je bovenaan op Actief, dan zie je alleen de tabellen die gebruikt zijn in dit overzicht.

Helaas: op deze manier kun je alleen velden uit de basis-tabel (in het voorbeeld tblVerkoop) in het waarden-gebied plaatsen. Sleep je bijvoorbeeld Korting in het waarden-gebied dan krijg je de volgende melding:

Dit scherm krijg je ook als je niet alle tabellen via relaties met elkaar hebt verbonden, maar wel kolommen uit die tabellen gebruikt.

Wat nu wel kan: in de draaitabel kunnen unieke waarden geteld worden. Laten we even via een voorbeeld kijken (tabblad OvzVerkModel2 van het Voorbeeldbestand):

  1. Maak een draaitabel aan zoals hiervoor beschreven.
  2. Plaats Regio en Klant in de Rijen.
  3. Sleep Aantal naar het Waarden-gebied.
  4. Sleep daarna 2x de Datum naar het Waarden-gebied.
  5. Klik op het vinkje achter Datum2 en kies Waardeveldinstellingen.
  6. Helemaal onderaan krijg je nu de mogelijkheid om te kiezen voor Uniek aantal.

Telling van Datum geeft het aantal datums weer per klant, dus eigenlijk het aantal keer dat een klant voorkomt (als de datum is gevuld); het totaal is dan ook precies 1.000.

Unieke telling van Datum2 laat zien hoe vaak een unieke datum voorkomt bij een klant.

Verrijken m.b.v. Power Query

En dan nu een oplossing die als een soort combinatie van de vorige 2 gezien kan worden: Power Query. Binnen dit Excel-onderdeel leggen we de verbanden tussen de tabellen vast én we maken daar allerlei berekeningen die we nodig hebben:

  1. Selecteer een cel in de tabel tblKlant.
  2. Kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  1. In principe zijn alle kolommen in orde. Aangezien Korting een percentage is, kunnen we de lay-out nog aanpassen:
    * klik op de 1.2 naast Korting
    * wijzig de instelling naar Percentage.
  1. Kies dan het vinkje naast Sluiten en laden.
  2. Klik op Sluiten en laden naar.
  3. Zorg dat de optie Alleen verbinding maken is geselecteerd en klik op de button Laden.
  4. Voer de vorige stappen ook uit voor de tabel tblProduct. Zorg dat KostPrijs en VerkPrijs de instelling Decimaal getal krijgen.
  1. Als laatste doet u hetzelfde met de tabel tblVerkoop, maar nog NIET sluiten. Wijzig de opmaak van de kolom Datum in alleen Datum.
  2. Voordat we Power Query afsluiten moeten we nog relaties leggen tussen de 3 tabellen.
    Kies het vinkje achter Query’s samenvoegen en kies de optie Samenvoegen als nieuw.

  1. Maak de verbindingen zoals hierboven en klik op OK. Wijzig de naam van de nieuwe query in PQtblVerkoop.
  2. Om de gegevens uit tblKlant als extra kolommen toe te voegen klikt u op het symbool rechts van tblKlant. Stel de opties in zoals hierboven en klik OK.
  3. Doe hetzelfde met tblProduct.
  4. Nu gaan we nog wat extra kolommen toevoegen: klik in de kolom Datum, klik op de menutab Kolom toevoegen en kies binnen de optie Datum voor het Jaar. Doe hetzelfde om de maand, de naam van de maand en het kwartaal toe te voegen.
  5. Kies daarna de optie Aangepaste kolom binnen Kolom toevoegen. De naam wordt Omzet en de formule =[Aantal]*[VerkPrijs].
  6. Voeg op dezelfde manier nog 3 kolommen toe:
    Kosten: =[Aantal]*[KostPrijs]
    Brutowinst: =[Omzet]-[Kosten]
    Nettowinst: =[Aantal]*([VerkPrijs]*(1-[Korting])-[Kostprijs])
  7. Zorg dat de 4 toegevoegde kolommen de instelling Decimaal getal hebben.
  1. Kies nu Sluiten en laden en zorg dat er alleen een verbinding tot stand wordt gebracht.
    LET OP zorg wel dat deze query PQtblVerkoop in de laatste stap wordt toegevoegd aan het gegevensmodel.

Nu staat alles klaar om overzichten te maken. Op het tabblad OvzVerkPQ van het Voorbeeldbestand vindt u een voorbeeld:

De gegevens van de bruto- en nettowinst zijn niet als getallen zichtbaar. Met behulp van Voorwaardelijke opmaak hebben die gegevensbalken gekregen:

  1. Selecteer een cel in een betreffende rij.
  2. Kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak.
  3. Kies dan Gegevensbalken en een kleur(overgang).
  4. Naast de cel staat nu het tekentje van een draaitabel, kies daar de onderste optie.
  5. Via Regels beheren/Regel bewerken de optie Alleen balk weergeven aanvinken.
  6. Pas de rijhoogte naar wens aan.

De Aantallen hebben op een vergelijkbare manier een Voorwaardelijke opmaak gekregen, namelijk Kleurenschalen.

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd? Door te klikken op de button

Wijzigt er iets aan de basisgegevens of hebt u nieuwe gegevens aan de tabellen toegevoegd?
Door te klikken op de button Alles vernieuwen in het blok Verbindingen van de menutab Gegevens, worden alle koppelingen, het gegevensmodel en alle draaitabellen ververst.

Unpivot

Excel, ,
Voorbeeldbestand: Unpivot.xlsxDownload

Ofwel hoe kom ik van een ‘gewoon’ Excel-overzicht naar een database-structuur.

Uit mijn artikelen mag blijken, dat ik een draaitabel-fanaat ben. De mogelijkheden en flexibiliteit van deze Excel-optie zijn grandioos. Een voorwaarde voor het gebruik daarvan is wel dat de brongegevens op een bepaalde manier klaar staan. Dit heb ik ooit de database-structuur genoemd.
Ben je verantwoordelijk voor een rapportageproces en vraag je iemand een overzicht te maken van bijvoorbeeld per dag de aantallen per regio, dan krijg je in 90% van de gevallen een overzicht zoals hierboven links wordt getoond (in de rijen de dagen en in de kolommen de regio’s), een ‘gewoon’ Excel-overzicht. Een groot voordeel hiervan dat het voor de mens makkelijk leesbaar en begrijpelijk is. Een groot nadeel dat je niet meer flexibel bent. Hoe zorg je er voor, dat totalen toch nog kloppen als je even één regio niet mee wilt laten tellen of wanneer je soms wel en soms niet de subtotalen van de maanden wilt tonen?

In dit artikel daarom nogmaals aandacht voor draaitabellen, maar zeker ook voor een methode om een ‘gewoon’ overzicht geautomatiseerd om te zetten naar een database-structuur.

Brongegevens

In het tabblad Data van het Voorbeeldbestand staat een overzicht met aantallen per werkdag van het eerste kwartaal van 2020, uitgesplitst naar regio.

In kolom B staan de werkdagen met een zodanige opmaak, dat ook de omschrijving van de dag wordt weergegeven (zie het artikel ginfo.nl/data-datums).

NB1 kolom G bevat per dag een totaal over alle regio’s. Excel toont in sommige cellen een klein waarschuwingsdriehoekje. Wanneer je met de muis daarboven ‘gaat hangen’ krijg je te zien wat de waarschuwing is.

In dit geval ‘ziet’ Excel dat naast de getallen onder de regio’s er ook nog een getal links daarvan staat; intern Excel is een datum namelijk een getal.

NB2 de werkdagen zijn als volgt gecreëerd: in cel B3 is de datum 6-1-20 ingevoerd. Daarna is de vulgreep (het kleine vierkantje rechtsonder) naar beneden doorgevoerd. Niet met de linker-muis-toets, maar met behulp van de rechter-muis-toets. Laat u die toets los, dan kunt u diverse opties kiezen; in dit geval Werkdagen doorvoeren.

Unpivot

Voordat we op basis van deze brongegevens een draaitabel (in het Engels Pivottable) kunnen gaan maken moeten we het voorbeeldbestand eerst gaan omzetten naar een database-structuur. Daar gebruiken we Power Query voor (zie ook het artikel ginfo.nl/power-query).

Het voorbeeld-overzicht lijkt op het resultaat van een draaitabel; dit overzicht moeten we dus ont-draaitabellen. Laten we toch maar de Engelse uitdrukking Unpivot gebruiken.
In het tabblad Ovz1 van het Voorbeeldbestand was een kopie van de kolommen B t/m G uit het tabblad Data opgenomen.
Hoe gaan we deze nu Unpivot-ten?

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
    In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  3. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  4. eerst een paar kleine aanpassingen: verwijder de Totaal-kolom (die maken we straks wel weer met behulp van een draaitabel) en klik links van Datum op het kalender-symbool en wijzig het type van Datum/tijd in Datum
  5. zorg dat de Datum-kolom is geselecteerd en klik dan in de menutab Transformeren in het blok Alle kolommen op het kleine driehoekje naast de optie Draaitabel opheffen
    Kies de optie Draaitabel voor andere kolommen opheffen.
  1. Dat is het al bijna! Verander de kop van
    de 2e kolom in Regio en die van de 3e in Aantal (via dubbelklikken).
  2. Kies dan in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Draaitabel

Op basis van de nieuwe brongegevens gaan we nu een draaitabel maken:

  1. klik ergens in de nieuwe tabel
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Bepaal waar de nieuwe draaitabel moet komen en klik op OK
  4. Plaats de Datum in het blok Rijen, Regio in Kolommen en Aantal in het Waarden-blok.
    Afhankelijk van de Excel-versie zal Excel de datums ook al groeperen in maanden. Gebeurt dit bij u niet automatisch dan kunt u later de datums nog (automatisch laten) groeperen.

En we hebben een overzicht, dat uitblinkt in gemak en flexibiliteit (zie het tabblad Ovz1 van het Voorbeeldbestand): gebruik de plus- en min-tekens vóór de maanden om deze uit- of in te klappen, klik op een driehoekje bij de kopjes om een bepaalde item wel of niet mee te nemen, bepaalde sortering door te voeren etc.

NB Ontvangt u gegevens over andere maanden, voeg die dan onderaan de bron-tabel toe. Vergeet niet daarna de met Power Query gegeneerde tabel te vernieuwen én ook de draaitabel te vernieuwen.

Brongegevens2

Maar dezelfde brongegevens kunnen natuurlijk ook worden aangeleverd zoals hiernaast weergegeven.

Het jaar en de maand zijn alleen gevuld wanneer er een wijziging plaats vindt.

Voordat we op bovenstaande manier kunnen gaan ‘Unpivot-ten’ moeten de ontbrekende gegevens in die kolommen worden aangevuld.
Dat kan handmatig door de waardes te kopiëren, maar dat kan wat makkelijker:

  1. selecteer in de Maand-kolom alle cellen met en zonder waardes (op het tabblad Data2 van het Voorbeeldbestand de cellen C3:C64)
  2. klik Ctrl-G (of gebruik de F5-toets, dit is de optie Ga naar), kies de button Speciaal, in het vervolgscherm de optie Lege cellen en klik dan op OK
  3. de eerste lege cel is nu geselecteerd. Type daarin het =-teken en tik op de cursor-omhoog-toets (we maken dus een verwijzing naar de bovenstaande cel, in dit geval =C3).
    LET OP druk dan NIET op Enter maar op Ctrl-Enter. Op die manier worden alle geselecteerde (dus lege) cellen met deze formule gevuld.
  4. Selecteer alle gevulde cellen in de Maand-kolom, tik Ctrl-C en klik dan met de rechter-muisknop ergens in dit gebied en kies de tweede Plak-optie (Waarden). De formules worden door harde waarden overschreven.
    NB deze laatste stap is eigenlijk niet nodig voor het verdere Unpivot-proces.
  5. Doe hetzelfde voor de Jaar-kolom.

Dit overzicht kan dan op dezelfde manier als hiervoor worden getransformeerd naar een database-structuur (zie tabblad Data2 in het Voorbeeldbestand). Met dit verschil: zorg dat in stap 5 eerst de kolommen Jaar, Maand en Dag zijn geselecteerd.

Unpivot2

Maar het bijwerken van de brongegevens kunnen we natuurlijk ook met behulp van Power Query doen (zie het tabblad Ovz2 in het Voorbeeldbestand):
NB ik kreeg een tip van Martien, dat onderstaande methode veel te omslachtig is (zie reactie hieronder); als algemene werkwijze bij het gebruik van gegevens uit andere records is het wel belangrijk om deze manier te kennen.

  1. klik op een van de cellen in het overzicht, bijvoorbeeld cel C3
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel.
  3. In oudere versies van Excel moet u iets vergelijkbaars zoeken in de menutab Power Query.
  4. klik in het vervolgscherm op OK. Het overzicht wordt automatisch omgezet naar een Excel-tabel.
  5. in deze records moeten op diverse plaatsen dus waarden uit het record daarvoor worden opgehaald; dat kan alleen als de records een index hebben:
    kies in de menutab Kolom toevoegen de optie Indexkolom
  6. nu gaan we een nieuwe jaarkolom toevoegen. Kies in de menutab Kolom toevoegen de optie Voorwaardelijke kolom.

    Vul het vervolgscherm in zoals hierboven.
  7. Het nieuwe resultaat ziet er nu als volgt uit:

    In de formulebalk moeten we “test” vervangen door een formule die verwijst naar het Jaar in het vorige record:
    #”Index toegevoegd”{[Index]-1}[Jaar]
    Ofwel: zoek in het resultaat van de vorige stap (met de naam Index toegevoegd) in het record met het volgnummer Index-1 de waarde in de kolom Jaar.
  8. deze formule moet wel in alle records worden doorgevoerd:
    selecteer de eerste cel in de nieuwe kolom en kies in de menutab Transformeren in het blok Alle kolommen de optie Doorvoeren omlaag
  9. voor de maand wordt op een vergelijkbare manier een nieuwe kolom toegevoegd. De querystap ziet er dan als volgt uit:
    = Table.AddColumn(#”Omlaag doorgevoerd”, “Aangepast.1”, each if [Maand] = null then #”Omlaag doorgevoerd”{[Index]-1}[Maand] else [Maand])
    Vergeet niet de formule in deze kolom door te voeren.
    NB het opzoeken in andere records is voor Excel een intensief proces; het verversen van de gegevens kan even duren.
  10. de oorspronkelijke Jaar– en Maand-kolom en ook de Index kunnen nu verwijderd worden. Verplaats de 2 aangepaste kolommen naar voren en geef die dan de namen Jaar en Maand.
  11. we zijn toch lekker bezig. Laten we zorgen dat we ook een echte datum in het bestand krijgen:
    Kies via het driehoekje linksboven in de tabel (dus naast de nieuwe kolom Jaar) de optie Kolom toevoegen vanuit voorbeelden.
    Vul onder Kolom1 in het vervolgscherm in: 6 jan 2020 en druk op Enter. Uit de formule, die Excel genereert, blijkt dat hij/zij niet weet wat we willen. In de tweede regel plaatsen we 7 jan 2020. Nu begrijpen we elkaar, dus klik op OK.
    De nieuwe kolom heeft de naam Samengevoegd gekregen; verander die in Datum (via dubbelklikken).
    Het type van deze kolom is Tekst (zie de aanduiding ABC naast de naam); dit veranderen we in Datum (klik op ABC).
    Verplaats deze kolom nu zodat die op de vierde plaats komt.
  12. nu nog even de unpivot-truc: selecteer de eerste 4 kolommen en kies de optie Draaitabel voor andere kolommen opheffen.
    Nog even de namen van de twee laatste kolommen wijzigen in Regio en Aantal.
  13. Als laatste: kies in de menutab Startpagina in het blok Sluiten het driehoekje bij Sluiten en laden. Kies Sluiten en laden naar ….
    In het vervolgscherm moet de optie Tabel aan staan, kies dan de plaats waar de nieuwe tabel moet komen en klik op Laden.

Voor het resultaat, een draaitabel en de Power Query’s zie het tabblad Ovz2 van het Voorbeeldbestand.

NB1 een query is altijd te bekijken door in het scherm Werkmapquery’s op de betreffende query te dubbelklikken.

NB2 het kan gebeuren dat een query niet direct in Excel geladen wordt (het ‘wieltje’ bij de querynaam blijft draaien) stop het vernieuwen en probeer het opnieuw:

Power Query

Excel, , ,
Voorbeeldbestand: PowerQuery.zipDownload

Waarschijnlijk is deze toevoeging aan Excel de grootste sprong voorwaarts sinds de introductie van Draaitabellen.
De eerste versies zijn ongeveer 10 jaar geleden door Microsoft vrijgegeven als een gratis add-in; sinds Excel-versie 2016 is het standaard beschikbaar, maar dan onder de naam Ophalen en Transformeren.

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:

  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 tekstbestand
  3. zoek het bestand Serie1.txt uit het Voorbeeldbestand op en kies Importeren

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.

6. nu moet Soort nog met een hoofdletter beginnen. Dit doen we door een nieuwe kolom toe te voegen.

‘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.

Door PQ worden enkele hulp-query’s aangemaakt. Deze worden intern door het systeem gebruikt voor het combineren van de bestanden.
Zie het tabblad TekstCombi in het Voorbeeldbestand voor het resultaat.

Wanneer er nu een derde serie gegevens wordt aangeleverd dan plaatst u dat tekstbestand in dezelfde directory en kiest u Vernieuwen van deze resultaat-tabel.

LET OP het combineren van bestanden kan natuurlijk alleen maar als ALLE bron-bestanden dezelfde structuur hebben. Het is dus zaak om intern in de organisatie goede afspraken te maken over aanlevering van bestanden.

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: