Tagarchief: Power Query

Power Query en het Web 2



In het vorige artikel had ik aangegeven dat ik zou beschrijven hoe je gegevens die over meerdere internet-pagina’s zijn verspreid toch met Power Query in één keer kunt overhalen naar Excel.

Belofte maakt schuld ….

Basis

Allereerst een disclaimer: je kunt (natuurlijk) alleen maar gegevens van meerdere pagina’s combineren, wanneer die pagina’s qua structuur op elkaar lijken.

We nemen als voorbeeld de pagina Tips & trucs van G-Info.

Daar staan alle artikelen die in de loop van de jaren zijn gepubliceerd; tenminste het eerste gedeelte van die artikelen.
Om het overzichtelijk te houden vind je daar de artikelen in blokken van 5.
Kies je oudere artikelen dan zie je in de adresbalk van je browser: www.ginfo.nl/tips-trucs/page/2/, waarbij het paginanummer gaat oplopen.
NB gelukkig kunnen we ook op dezelfde manier de eerste pagina ophalen: www.ginfo.nl/tips-trucs/page/1/

We gaan de gegevens van de eerste pagina binnenhalen in Excel:

  1. Analyseer de internetpagina via de optie Inspecteren zoals in het vorige artikel uitgelegd.
  2. Kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web en geef als URL in: ginfo.nl/tips-trucs/
  3. Klik in de Navigator op Document en kies Bewerken.
  4. Klik 2x op Table en dan op de Table achter Body.
  5. Dan 4x op Table achter de (eerste) DIV

  6. Nu we op het niveau van de artikelen zijn gekomen kiezen we Uitvouwen achter Children.
    LET OP zorg dat je bij het uitvouwen alleen de kolommen Children en Text meeneemt
  7. Kies nog 3x Uitvouwen met als resultaat:
  8. Selecteer de kolommen Text.4 en Text.3 en kies op de menutab Transformeren de optie Kolommen samenvoegen. Geef de nieuwe kolom de naam Artikel.
  9. Verwijder alle rijen, die we niet nodig hebben door er rechts op te klikken:
  10. Verwijder alle overbodige kolommen, geef de query de naam q_Tips1 en kies in de menutab Startpagina de optie Sluiten en laden.

Dat is al gelukt (zie het tabblad Tips1 van het voorbeeldbestand): we hebben de belangrijkste gegevens van de internetpagina in Excel.
Maar het zou mooier zijn als de kop van het artikel en de inhoud naast elkaar zou staan.

Van rijen naar kolommen

De methode die we hiervoor gaan gebruiken is een alternatief voor die uit het vorige artikel:

  1. Dubbelklik op de query q_Tips1.
  2. Kies in de menutab Kolom toevoegen de optie Indexkolom en direct daarna binnen Standaard de optie Delen (geheel getal) .
    Vul in het tussenscherm de waarde 2 in en geef de nieuwe kolom de naam Rij.
  3. Met de kolom Index geselecteerd, kies opnieuw Standaard, maar dan de optie Modulo; zorg dat door 2 gedeeld wordt.
    Geef deze kolom de naam Kolom.
    NB Met Modulo wordt de rest berekend van een deling.
  4. Verwijder de kolom Index.
  5. Selecteer de kolom Kolom, kies in de menutab Transformeren de optie Draaikolom en vul het vervolgscherm als volgt in:
  6. Verwijder de kolom met de naam Rij en wijzig de 2 overblijvende kolomnamen in Naam en Inhoud. Kies de optie Sluiten en laden (zie het tabblad Tips2 van het voorbeeldbestand).

NB pas wel even de cel-eigenschappen van de tabel aan: kies Terugloop bij de Tekstopties en de optie Boven bij Verticaal.

Query als functie gebruiken

De query, q_Tips2, die we hiervoor hebben gemaakt, willen we nu voor verschillende internetpagina’s van G-Info gebruiken. Hij moet zich dus als een functie gedragen, waar we door middel van een parameter kunnen aangeven, welke pagina moet worden opgehaald:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Selecteer aan de linkerkant de query q_Tips2 en kies de optie Geavanceerde editor in de menutab Startpagina.
  1. Kopieer alle stappen van deze query; kies Annuleren.
  2. Klik met de rechter muisknop in de linkerkolom en voeg een Lege query toe:
  3. Ga weer naar de Geavanceerde editor en plak de gekopieerde stappen uit punt 2.
  4. Voeg vooraan een regel toe:
    let AllePaginas=(PagNr) =>
  5. Wijzig de Bron-stap in:
    Bron = Web.Page(Web.Contents(“ginfo.nl/tips-trucs/page/”&Number.ToText(PagNr))),
  6. Voeg nog een laatste regel toe:
    in AllePaginas
  7. Klik op Gereed. Test de functie door als parameter een 1, 2 etc in te tikken en dan Aanroepen te kiezen.
  8. Geef de functie de naam fAllePags en kies de optie Sluiten en laden.

Functie gebruiken

De functie die we net gemaakt hebben gaan we gebruiken om met behulp van Power Query alle tips van G-Info tegelijkertijd op te halen:

  1. Allereerst moeten we aangeven welke paginanummers we willen importeren. We leggen dat in een Excel-tabel vast (met de naam tblPagNrs; zie het tabblad AlleTips van het voorbeeldbestand)
  1. Klik ergens in die tabel en kies in de menutab Gegevens in het blok Gegevens ophalen en transformeren de optie Van tabel/bereik.
  2. Kies in de menutab Startpagina de optie Kolom splitsen/Op scheidingsteken en vul het scherm als volgt in:

    NB we splitsen in 50 kolommen zodat bij uitbreiding van het aantal tips-pagina’s de routine goed blijft werken.
  3. Kies in de menutab Transformeren de optie Transponeren.
  4. Filter nu de lege rijen uit (rechtsklikken op een lege cel).
  5. Wijzig de kolomnaam in Pagina.
  6. Kies in de menutab Kolom toevoegen de optie Aangepaste functie aanroepen. Kies in het vervolgscherm bij Functiequery onze hiervoor gecreëerde functie; de rest van de opties zijn oké:

    LET OP de routine gaat nu de gegevens van alle pagina’s ophalen; dat kost even wat tijd!
  7. Vouw de kolom fAllePags uit.
  8. Verwijder de kolom Pagina.
  9. Geef de query de naam q_AlleTips en kies Sluiten en laden naar.

Helaas, we hebben bij het maken van de tips-query iets over het hoofd gezien: als er reacties zijn op een artikel dan ‘raakt het systeem in de war’ (zie het tabblad AlleTips van het voorbeeldbestand).

Ook staat er ergens nog zoiets als Een reactie tot dusver.
Bij het filteren van regels moeten we ook de reacties weglaten:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Klik rechts op de functie fAllePags en kies Dupliceren; wijzig de naam van de nieuwe functie in fAllePags2.
  3. Kies de optie Geavanceerde editor.
  4. Voeg als volgt na gefilterd4 2 stappen toe en wijzig de stap daarna:
    #”Rijen gefilterd4″ = Table.SelectRows(#”Rijen gefilterd3″, each [Name] <> “nav”),
    #”Rijen gefilterd5″ = Table.SelectRows(#”Rijen gefilterd4″, each not Text.Contains([Artikel], ” reactie tot dusver”)),
    #”Rijen gefilterd6″ = Table.SelectRows(#”Rijen gefilterd5″, each not Text.Contains([Artikel], ” reacties bekijken”)),

    #”Andere kolommen verwijderd” = Table.SelectColumns(#”Rijen gefilterd6“,{“Artikel”}),
  5. Klik op Gereed.
  6. Test de nieuwe functie door deze aan te roepen met als parameter bijvoorbeeld 3.
  7. Kies de optie Sluiten en laden.

NB Door de paginanummers op het tabblad AlleTips2 van het voorbeeldbestand te wijzigen en de tabel daaronder te Vernieuwen kun je het overzicht aanpassen aan je wensen.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Power Query en het Web



Dit artikel gaat over het gebruik van Power Query als hulpmiddel om gegevens van het WEB te halen.

Hebben we eenmalig een overzicht nodig dan volstaat meestal een eenvoudige kopieer-plak-actie.

Maar wijzigen de gegevens op een internet-pagina periodiek dan moeten we die actie iedere keer opnieuw uitvoeren, inclusief eventuele extra berekeningen etc.

In de afgelopen jaren heeft G-Info al diverse methoden besproken (zie bijvoorbeeld Excel en het World-Wide-Web en MS-Query: een alternatief voor Vert.Zoeken), maar de opties die daar besproken zijn, zijn door Microsoft bij het opleveren van nieuwe versies steeds meer gesloopt.
Maar niet getreurd: we hebben daar Power Query voor teruggekregen; vanaf versie 2016 Gegevens ophalen en transformeren genoemd.

Hieronder enkele ‘simpele’ voorbeelden, maar ook een uitleg hoe je iedere willekeurige internet-pagina kunt uitlezen.
Volgende keer zullen we kijken hoe je gegevens die over verschillende pagina’s verspreid staan, kunt overhalen naar Excel.

Inwoneraantal per land

Op Wikipedia (nl.wikipedia.org/wiki/Lijst_van_landen_naar_inwonertal) is een mooi overzicht te vinden van het aantal inwoners per land. Dit wordt natuurlijk niet dagelijks bijgewerkt; op dit moment staan hier gegevens van 2020.

Maar hiermee kunnen we wel makkelijk de kracht van Power Query laten zien.

Hoe haal je deze gegevens binnen in Excel?

  1. open een nieuw werkblad
  2. kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
  3. kopieer de Wikipedia-URL uit de adresbalk van je internet-browser naar het tussenscherm in Excel
  1. in de Navigator zie je in dit geval dat we kunnen kiezen uit het hele document (de internet-pagina) of een tabel.
    Klik één keer op de naam Table 0 en je zult zien dat dit precies de gegevens zijn die we willen ophalen.
  2. kies rechtsonder het ‘vinkje’ naast Laden
  1. Kies daar Laden naar.
    In het vervolgscherm geven we aan dat we als resultaat een Tabel willen.
    Geef aan of deze op een bestaand werkblad moet komen of op een nieuwe.

Daar zijn de gegevens zoals we die op de internet-pagina hebben gezien (zie het tabblad Inwoners in het Voorbeeldbestand. Helaas wel zonder de mooie vlaggetjes!).
Zijn de gegevens op internet gewijzigd: klik rechts op de tabel en kies Vernieuwen.

Wel jammer van de derde kolom: doordat er een jaar achter het aantal staat, ziet Excel dit aantal niet als getal.
Daar gaan we gauw iets aan doen; we gaan uit die kolom het aantal extraheren:

  1. open zo nodig het scherm met het overzicht van de query’s in deze werkmap: de optie Query’s weergeven in de menutab Gegevens
  2. dubbelklik op q_Inwoners (of klik rechts en kies de optie Bewerken)
  3. klik ergens in de kolom Inwonertal
  4. kies in de menutab Kolom toevoegen in het blok Uit tekst de optie Extraheren; kies Tekst voor scheidingsteken en vul het vervolgscherm in (tik op de (-toets, haakje openen)
  5. verander het gegevenstype van de nieuwe kolom (klik op ABC en kies de optie Geheel getal) en wijzig de naam van de kolom via dubbelklikken in AantInwoners
  6. verwijder de oude kolom Inwonertal en klik in de menutab Start op de button Sluiten en laden

Weer wat geleerd: ik wist niet dat Indonesië zo veel inwoners heeft!

NB Excel haalt alleen letters en cijfers op; je mist dus plaatjes (in dit geval de land-vlaggen) en bijvoorbeeld ook de opmerking bij het aantal van China.

Corona-cijfers

De gegevens in het vorige voorbeeld hadden we ook nog ‘handmatig’ kunnen overnemen; ze wijzigen hooguit één keer per jaar.

Een ander voorbeeld vinden we op de site news.google.com/covid19; de corona-data, die je daar kunt vinden, worden enkele keren per dag bijgewerkt. Een geautomatiseerd proces is dan een uitkomst.

Op het tabblad Corona van het Voorbeeldbestand staat het resultaat van een query:

Ook deze internet-pagina bevat een tabel, dus een koppeling via Power Query is snel gemaakt.
In de kolom Nieuwe gevallen is de tekst vervangen door de waarde 0 (nul).
Als laatste is nog een index toegevoegd; op die manier zien we dat Nederland het 21e land is, rekenend met het aantal Corona-gevallen.
De regel met Nederland wordt in Excel met behulp van Voorwaardelijke opmaak geaccentueerd.

Klik ergens rechts in de tabel en kies Vernieuwen.

Wil je weten welke stappen er zijn doorlopen om dit resultaat te bereiken: klik dubbel op de query q_Covid ( of klik rechts en kies Bewerken).

Je komt dan in de Power Query Editor. Klik op één van de stappen aan de rechterkant en boven in de formulebalk zie je wat deze stap daadwerkelijk inhoudt. De opdracht hierboven ziet er ingewikkeld uit, maar gelukkig is dit makkelijk te implementeren: klik rechts op een cel en kies de optie Waarden vervangen.

Maar zo eenvoudig als in de vorige 2 voorbeelden gaat het vaak niet; Excel kan alleen maar een tabel ophalen als in de internet-opmaak gebruik is gemaakt van de zogenaamde table-tag. Deze opmaak-techniek was ‘vroeger’ de standaard als je een overzicht op internet wilde plaatsen; met de opkomst van allerlei hulpprogramma’s om internet-pagina’s te genereren (bijvoorbeeld WordPress) worden steeds vaker alternatieve opmaak-opties gebruikt, die door Excel (nog) niet als tabel worden herkend.

Opmaak internet-pagina’s

Om de rest van het artikel beter te kunnen volgen moeten we het daarom (kort) over de opmaak van internet-pagina’s hebben.
Voor deze opmaak zijn diverse regels afgesproken zodat alle verschillende browsers weten hoe ze de informatie moeten interpreteren. Deze regels zijn vastgelegd in een taal: HTML (HyperText Markup Language). We kunnen hier geen uitgebreide uitleg daarvan geven, maar zullen de belangrijkste punten (voor ons doel) belichten.

Wanneer we rechts klikken op de Wikipedia-pagina van ons eerste voorbeeld, is één van de opties Paginabron weergeven.

Binnen HTML wordt gewerkt met zogenaamde tags; deze kun je herkennen aan de tekens < en >. Tags kun je zien als commando’s voor je internet-browser.
Als eerste krijgt de browser te zien dat het documenttype HTML is, daaronder dat binnen de html voor de site-inhoud de Nederlandse taal wordt gehanteerd. Class wordt onder andere gebruikt om elementen een bepaalde opmaak mee te geven.
Iedere internet-pagina moet een head hebben en ook een body (waar de echte inhoud van de pagina staat). Vaak wordt ook gebruik gemaakt van een footer.
De tag title wordt gebruikt om het tabblad in je browser een naam te geven.

Ieder blok van een internet-pagina wordt voorafgegaan door een tag en afgesloten met een sluit-tag (dezelfde naam maar met een /).

We kunnen deze paginastructuur nog op een andere manier bekijken. Wanneer je rechts klikt op het inwoneraantal van China, kies dan Inspecteren:

Je ziet dan ‘direct’ waar het element waar je op hebt geklikt, zich in de pagina-structuur bevindt. De head is dichtgeklapt (het pijltje wijst naar rechts en je ziet puntjes tussen de open- en sluit-tag), de body is geopend (pijltje naar beneden) dus daar zit het inwonertal in.
De body begint met twee div‘s, die direct weer worden gesloten; die zijn hier voor opmaak-doeleinden bedoeld.

NB div is de afkorting van division; hiermee kunnen makkelijk bepaalde secties van een pagina worden afgebakend.

Dan komen we in een div met als id de naam content. Daarbinnen komen we iets verderop in de div bodyContent.
Uiteindelijk zien we dan de tag table, met daarin een kop (thead) en een tbody. Binnen de tbody komt een regel-tag (tr) en daarbinnen 3 keer een td (data-cell).

De derde staat in een gekleurde balk; klik op het pijltje en je ziet de inhoud van die tabel-cel.

NB misschien heb je geprobeerd om in het eerste voorbeeld bij het extraheren van het inwoneraantal als scheidingsteken het haakje-openen vooraf te laten gaan door een spatie. Hier kun je zien waarom dat niet werkte: tussen de html-spatie (&nbsp;) en het haakje staat nog een tag.

Artikelen van G-Info

Op iedere pagina van ginfo.nl staat rechts een overzicht van alle Tips & trucs. We gaan de gegevens uit die kolom overhevelen naar Excel (zie voor het resultaat het tabblad Artikelen in het Voorbeeldbestand en voor de uiteindelijke query q_G-Info-artikelen):

  1. eerst gaan we die internet-pagina analyseren: klik in de browser rechts op de naam van het eerste artikel (in het voorbeeld Histogrammen) en kies de optie Inspecteren.
  1. het eerste wat opvalt is dat de table-tag nergens te vinden is.
    Binnen de body moeten we de eerste div hebben. Daarbinnen de eerste div na de header, daarbinnen de tweede div, en dan twee keer de eerste div en dan een blok aside.
  1. we zijn er bijna: We slaan een h3 over (daar zien we de kop van de kolom) en vinden dan binnen een ul (unordered list) diverse li-tags (list items). Dat zijn de artikelen, die we zoeken.
  2. nu kunnen we aan de slag: kies in Excel in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
  3. vul als URL in: www.ginfo.nl en klik OK
  4. zoals we al hadden verwacht kan Power Query (PQ) geen tabel vinden, alleen de document-tag. Klik daarop en kies dan Bewerken.

  5. in de editor zien we maar 1 regel, het HTML-document. Maar in de kolom Children zitten alle blokken die we bij de analyse hebben gezien.
    Wanneer je die cel activeert (klik NAAST het woord Table), dan zie je beneden dat deze cel 2 elementen bevat, een HEAD en een BODY.
  6. klik nu op het woord Table; in de editor zien we dan 2 regels:

    In de analyse hebben we gezien dat we in de body moeten zijn; klik op Table in die regel.
  7. we gaan verder inzoomen zoals we hiervoor in de analyse hebben gezien: de eerste div, de eerste div na de header, dan de 2e div, de eerste div, nog een keer de eerste div en dan de aside.
    De h3 zouden we overslaan: kies de ul-table. En daar zijn ze!
  8. klik NIET op een Table achter een LI: dan zoomen we in op één artikel en zien we de andere namen niet meer.
    We willen de gegevens van alle ‘children‘ hebben: klik op de button met de 2 pijltjes achter de kolomnaam
  9. vul het tussenscherm als volgt in:
  10. en nog een keer op dezelfde manier uitvouwen levert het volgende op:

NB heb je ergens een fout gemaakt? Klik aan de rechterkant Navigatie open (dubbelklikken of klikken op het ’tandwieltje’) en je kunt één of meerdere stappen terug gaan.

Nu we de gegevens hebben gevonden, moeten we nog wel wat opschoonacties doen (maar bedenk wel: dit hoef je binnen PQ allemaal maar één keer te doen, daarna kun je het overzicht met Vernieuwen iedere keer snel actualiseren):

  1. verwijder alle kolommen, behalve de kolom waar de teksten in staan:
  2. de lege regels verwijderen door rechts te klikken op één van de null-waarden:
  3. de naam en de datum van het artikel moeten naast elkaar komen staan, we moeten dus rijen combineren. Dat kan bijvoorbeeld op de volgende manier (in het volgende artikel komt een andere methode aan bod):
    * kies in de menutab Kolom toevoegen de optie Indexkolom (de index moet met 0 beginnen)
    * direct gevolgd door , vul in het vervolgscherm de waarde 2 in; we willen 2 regels bij elkaar nemen

    NB Microsoft gebruikt hier een vreemde terminologie; wat bedoeld wordt is, dat we na deling een geheel getal overhouden (dus eventuele decimalen weg laten)
    * nu gaan we, met de laatst toegevoegde kolom geselecteerd, de rijen groeperen: kies in de menutab Startpagina de optie Groeperen op, kies als Bewerking de optie Som en als Kolom de kolom met teksten (we gaan teksten ‘optellen’)
    * dat kan natuurlijk niet! We moeten even wat corrigeren: wijzig in de formulebalk List.Sum([Text.2]) door Text.Combine([Text.2], “||”) en druk op Enter.
    LET OP PQ is hoofdletter-gevoelig.
    NB we hebben hier || als scheidingsteken gebruikt; we hebben iets nodig waar we van weten dat dit nergens in de tekst voorkomt.
    * selecteer de kolom met teksten, kies in de menutab Startpagina de optie Kolom splitsen en voer als aangepast scheidingsteken || in.
    PQ splitst de kolom én verandert het type van de nieuwe kolom automatisch naar datum.
    * nog een paar aanpassingen: verwijder de eerste kolom en wijzig de kolomkoppen.
    * kies Sluiten en laden

Weersverwachtingen

Nog een voorbeeld, nu van de website knmi.nl/nederland-nu/weer/verwachtingen.

Een korte analyse van deze site laat zien dat ook hier geen table-tag te vinden is. Dus dat wordt weer tellen!
NB de span-tag is ook een html-hulpmiddel om de pagina in secties te verdelen.
Met wat experimenteren moet je in PQ tot een overzicht van li-tags kunnen komen.

Vouw de kolom Children uit en daarna nog een keer.

Daarmee hebben we kolom gevonden met de informatie die we zoeken. Nu moeten er nog wat schoningsacties worden uitgevoerd en de lay-out aangepast vergelijkbaar met het vorige voorbeeld, inclusief het omzetten van rijen naar kolommen (zie de query q_KNMI in het Voorbeeldbestand).

Om de dag en de datum in één kolom te krijgen hebben we de optie Kolommen samenvoegen in de menutab Transformeren gebruikt.

Power Query vraagt daarbij naar een gewenst scheidingsteken: kies Aangepast en vul dan in Character.FromNumber(10). Standaard wordt deze scheiding als tekst ingevuld, dat is niet de bedoeling. Verwijder in de formulebalk de twee ” en druk op OK.

Nog een paar kleine aanpassingen en kies dan Sluiten en laden:

En zo hebben we de meest recente weersverwachtingen in Excel. Op ieder gewenst moment kunnen we dit overzicht actualiseren.

NB1 om de lay-out in Excel mooi te krijgen hebben alle cellen van het overzicht een opmaak meegekregen (via Ctrl-1 of rechtsklikken; zie het tabblad KNMI van het Voorbeeldbestand).

NB2 zoals al eerder aangegeven kunnen we helaas op deze manier niet de plaatjes overhalen.

NB3 op de site van het KNMI staat ook de datum en tijd aangegeven waarop het overzicht is bijgewerkt. Een mooie oefening om die ook terug te vinden!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Data verrijken deel 2



In het vorige artikel hebben we gezien dat er verschillende manieren zijn om data aan te vullen met andere gegevens.
De meest flexibele methode is het gebruik van Power Query in samenhang met het Excel-gegevensmodel.

Deze keer gaan we opnieuw in op deze methode, waarbij ook aandacht voor de diverse manieren van het samenvoegen van Power Query’s en de consequenties daarvan.

Basis-gegevens

Als voorbeeld voor dit artikel gebruiken we een tijdregistratie van een medewerker van een ICT-afdeling.

Hij krijgt zijn opdrachten van diverse personen en wordt ingezet om telefonisch mensen uit diverse regio’s te ondersteunen.

Het tabblad Data van het Voorbeeldbestand bevat daartoe een Excel-tabel (met de naam tblData).
Om de invoer van de opdrachtgever te vergemakkelijken (en invoer-fouten te voorkomen) is de input van de namen van een Gegevens-validatie voorzien.
De bron voor deze validatie ligt vast op het tabblad Basis in de Excel-tabel tblTeams. Helaas is Microsoft bij het implementeren van tabellen ‘vergeten’ om de mogelijkheden daarvan ook bij gegevens-validatie toe te staan. Dat moet daarom via een omweg ingeregeld worden.

Voer de volgende stappen uit:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
  1. het vervolgscherm vullen we in zoals hiernaast weergegeven.
    De verwijzing kun je het makkelijkst maken door op de pijl-omhoog te klikken, zo nodig nog het tabblad Basis te selecteren en dan op de bovenrand van de cel Naam te klikken.
    Deze gedefinieerde naam kunnen we nu gebruiken binnen de gegevens-validatie.
  1. selecteer alle cellen in de kolom OpdrGever op het tabblad Data.
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. selecteer bij Toestaan de optie Lijst en vul bij Bron in: =Namen
    NB denk aan het =-teken!
  4. de overige standaard-instellingen laten we zo, dus klik op OK

NB in nieuwe records van de tabel zal de gegevens-validatie automatisch worden doorgevoerd.

Ook de gegevens in de kolom Regio in het tabblad Data hebben op een vergelijkbare manier een validatie gekregen. Daarbij is als naam Regios gebruikt, die verwijst naar de kolom Regio in het tabblad Basis.
Maar de ICT-afdeling moet voor sommige werkzaamheden een andere regio-code in kunnen voeren. Daarom is de optie Foutmelding weergeven in het tabblad Foutmelding van de Gegevensvalidatie uitgevinkt.

Overzicht per opdrachtgever en regio

Dit overzicht kunnen we snel met behulp van een draaitabel genereren op basis van de gegevens van het tabblad Data.

Vanwege de flexibiliteit gaan we eerst een koppeling maken in Power Query:

  1. selecteer een van de cellen in de tabel tblData
  2. kies in de menutab Gegevens in het blok Ophalen en transformeren de optie Uit tabel

NB tijden worden binnen Excel als een decimaal getal vastgelegd: 12:00 als 0,5, 6:00 als 0,25, 3:00 als 0,125, 2:24 als 0,1 etcetera

  1. Power Query heeft al een koppeling gemaakt naar de bron en de kolomtypes gewijzigd
  2. wijzig de naam in q_tblData om het verschil met de tabel extra duidelijk te maken
  3. in plaats van nummers voor de regio’s willen we een omschrijving hebben (1=Noord, 2=Oost, 3=Zuid, 4=West):
    * kies in de menutab Kolom toevoegen de optie Kolom vanuit voorbeelden
    * tik in de eerste regel Noord in, in de volgende Zuid enzovoort net zolang tot PQ ‘snapt’ wat de omschrijving moet zijn
  4. we voegen nog een Aangepaste kolom toe met de naam Tijd en als formule =[EindTijd]-[BeginTijd]
  5. wijzig het type van Tijd in Decimaal getal
    LET OP NIET wijzigen in Tijd; dan kan er in de hierop gebaseerde draaitabel niet mee gerekend worden
  6. de Begin– en Eindtijd hebben we niet meer nodig, dus die kunnen verwijderd worden
  7. kies als laatste stap in de menutab Start het ‘vinkje achter Sluiten en laden en zorg dan dat er Alleen een verbinding gemaakt wordt en dat de gegevens aan het gegevensmodel worden toegevoegd

Nu gaan we het overzicht maken (zie het tabblad Ovz1 van het Voorbeeldbestand):

  1. ga naar een nieuw tabblad
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. verplaats vanuit de tabel q_tblData het veld OpdrGever naar de Rijen, RegioNaam naar de Kolommen en de Tijd naar het Waarden-gebied
  4. de Som van Tijd wordt nu als een decimaal getal weergegeven. Klik rechts op één van de getallen en kies Getalnotatie. Kies in het vervolgscherm als categorie Tijd en het gewenste Type.

Overzicht per team en regio

Zoals te zien is in het tabblad Basis van het Voorbeeldbestand zijn de opdrachtgevers aan teams gekoppeld.

Voordat we verder gaan maken we eerst 2 verbindingen op de manier zoals hiervoor beschreven. De eerste verbinding wordt tot stand gebracht met tblTeams en krijgt de naam q_tblTeams, de andere met tblRegio met de naam q_tblRegio.

Op de manier zoals beschreven in het vorige artikel worden de 3 q_tbl-verbindingen aan elkaar gekoppeld (zie q_tblData2 in het Voorbeeldbestand). In deze query zorgen we er ook voor, dat wanneer er geen regio gevonden kan worden (als de code groter dan 4 is) er Onbekend wordt gegenereerd (via de optie Waarde vervangen).

Op basis van q_tblData2 kunnen we een draaitabel maken die de Tijden uitzet tegen Regio en Team (zie het tabblad Ovz2 van het Voorbeeldbestand).

Maar …. misschien was het u hiervoor ook al opgevallen: de draaitabel telt alles goed op behalve de totaal-tijd rechtsonder!

Gelukkig is het maar een kwestie van lay-out: ik heb het verkeerde Type in de categorie Tijd bij Getalnotatie gekozen (namelijk 13:30). Dit type begint na 24 uur weer bij 0.
Wanneer we een aangepaste getalnotatie kiezen en we zetten vierkante haken rond het uur (ik heb [u]:mm ingetikt) dan ziet het er beter uit:

NB bij de team-indeling is een test-naam blijven staan. Wanneer we die verwijderen en op de menutab Gegevens in het blok Verbindingen de optie Alles vernieuwen kiezen dan komt er in de draaitabel een naam (leeg) tevoorschijn. Uiteraard is dit in q_tblData2 op dezelfde manier als bij Regio om te zetten naar Onbekend.

Overzicht per team en regio 2

Hoe komt het nu, dat we die onbekende gevallen in ons overzicht te zien krijgen?
Er worden codes of namen gebruikt die niet in de basis-gegevens voorkomen. Gelukkig zien we dat direct omdat dan in het resultaat lege velden voorkomen. Ook gelukkig, dat Excel de koppeling standaard zodanig legt dat deze ‘vreemde’ records niet verdwijnen.
Bij het query’s samenvoegen zien we deze tussenstap:

Waar het om gaat is het Type join (verbinding). Standaard staat deze ingesteld op Left outer. Dit betekent dat alle records uit de eerste tabel worden getoond en als er een overkomst te vinden is in de tweede tabel dan worden deze records aangevuld met die gegevens.

Power query kent verschillende joins:

De join Left outer is de meest gebruikte (en ook bruikbare), maar de Inner kan ook handig zijn.
Wanneer we in ons voorbeeld records met regio-codes groter dan 4 en/of ‘vreemde’ namen niet willen meenemen dan gebruiken we dit type verbinding.

In het tabblad Ovz3 van het Voorbeeldbestand is op basis van q_tblData3 een overzicht gegenereerd. In de onderliggende verbindingen is gebruik gemaakt van inner-joins:

LET OP afwijken van de standaard-join (Left outer) moet u alleen doen als het echt nodig is. Het risico van verdwijnende records is dan altijd aanwezig; het is raadzaam om in dat geval een controle in te bouwen:


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Data verrijken



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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Unpivot



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:


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?