Tagarchief: Power Query

Grenzen aan de groei – 2

Excel, , , , , , ,
Voorbeeldbestand: ClubRome_2.xlsbDownloaden

LET OP: na het downloaden de extensie wijzigen in xlsb

In het vorige artikel (Grenzen aan de groei – 1) hebben we beloofd dat we een poging zouden wagen om het model uit het rapport van de Club van Rome in Excel te implementeren, althans een vereenvoudigde versie daarvan.
In dat artikel is te lezen hoe we dat zouden willen doen en aan de hand van wat vingeroefeningen hebben we laten zien dat het (in theorie) mogelijk zou moeten zijn.

Die laatste conclusie staat nog steeds, maar helaas hebben we wel moeten constateren dat de hoeveelheid verbanden tussen de diverse variabelen en de daarbij behorende parameters zo groot is dat een totale implementatie heel erg veel tijd gaat kosten. Dus deze keer vormt het Voorbeeldbestand geen afgerond project.
In dit artikel zullen we laten zien hoe ver we gekomen zijn en welke Excel-opties daarbij zijn gebruikt.

Voor eenieder de uitdaging om de ‘handdoek in de ring’ weer op te rapen en het model verder uit te werken!

Nogmaals: het model

(zie het tabblad SystemDynamics van het Voorbeeldbestand)

Dit is een schematische weergave van het (vereenvoudigde) ‘Club van Rome’-model; voor nadere uitleg zie het vorige artikel.

Belangrijk om te weten is het volgende:

  1. de pijlen geven aan welke relaties er tussen de variabelen zijn onderkend
  2. de pijlen laten zien in ‘welke richting’ de beïnvloeding loopt
  3. we onderkennen in het model 3 soorten variabelen: de Inputs, de Kritische Factoren en de Tussen-variabelen.
    De Input-variabelen worden niet beïnvloed door de omgeving, maar kunnen wel in de loop van de jaren variëren.
    De KF’s zijn die 5 variabelen die in alle grafieken van het rapport terugkomen.
    Alle overige vallen onder de categorie Tussen-variabelen.
  4. het rapport van de Club van Rome is in 1972 gepubliceerd; waar in dit artikel naar het verleden wordt verwezen bedoelen we dan ook de periode van 1900 tot en met 1970.
  5. voor diverse variabelen is in de literatuur (zie het tabblad Docu van het Voorbeeldbestand) te achterhalen wat de waardes in het verleden zijn geweest. Dit is de eerste basis van de implementatie van het model.
  6. bij de verdere implementatie heeft iedere variabele een eigen tabblad gekregen (behalve de Inputs; zie hierna). Alle aannames voor de berekening van een variabele staan in het betreffende tabblad vermeld. Vaak is daar ook een grafiek opgenomen van die variabele om snel het resultaat van de aannames te controleren.
Tabblad T15

Implementatie

Tabblad Beschr

In dit tabblad van het Voorbeeldbestand staat een overzicht van alle gebruikte variabelen met daarbij (zover al uitgezocht) de meest relevante berekening:

De betekenis van de Code en de Naam moge duidelijk zijn; de kolom Schema bevat de tekst zoals die op het tabblad SystemDynamics wordt gebruikt. In de kolom Omschrijving staat in het kort een nadere toelichting op de variabele en de laatste kolom bevat de (belangrijkste) formule voor de berekening van die variabele.

NB de codes onder de 10 voor de Tussenvariabelen hebben een extra 0 gekregen; dit om bij standaard-sorteringen altijd direct de juiste volgorde te hebben (anders zou bijvoorbeeld T10 vóór T2 komen).

Tabblad Inputs

Dit tabblad bevat de gegevens van de 4 Input-variabelen. Deze vormen samen de Excel-tabel tblInputs.

Op dit moment zijn alleen de I3 en I4 gevuld en per kolom hebben alle jaren dezelfde waarde. De juiste interpretatie van deze variabelen vergt nog onderzoek.

De KF-tabbladen

Zoals gezegd hebben alle KF’s (en ook de Tussen-variabelen) een eigen tabblad. Deze bladen hebben allemaal dezelfde structuur: de eerste kolom bevat de jaren en de tweede kolom de daarbij behorende waarde. Samen vormen deze 2 kolommen een Excel-tabel met een overeenkomende naam (in bovenstaand voorbeeld tblKF1).

NB de waarde-kolom heeft een voorwaardelijke opmaak: wanneer een cel een formule bevat dan wordt de inhoud in het groen, vet en cursief weergegeven.

In de volgende kolommen staat altijd de code en naam van de variabele, eventueel gebruikte bronnen en de diverse aannames.
De rest van het tabblad wordt gebruikt om zo nodig extra berekeningen uit te voeren, de aannames toe te lichten etcetera.

NB bij KF5 wordt een zogenaamde Eigen functie gebruikt (zie voor een toelichting het vorige artikel, Vingeroefening 2).

De T-tabbladen

Zoals aangegeven hebben deze tabbladen dezelfde structuur als de KF’s. In het voorbeeld hierboven is te zien dat voor T12 een formule wordt gebruikt, waarbij de gevoeligheid (GF) voor de invloed van een variabele kan worden ingesteld. Daarnaast kan aangegeven worden of de beïnvloeding een vertraging V kent en of er over een periode P gemiddeld moet worden (zie het artikel Grenzen aan de groei – 1).

LET OP in dat artikel hebben we, bij het gebruik van een periode, een rekenkundig gemiddelde gebruikt. Dat is, theoretisch gezien, niet juist. Hoewel dat in dit model, bij niet te grote ontwikkelingen in de tijd, niet echt relevant is, hebben we toch de betere methode gehanteerd waarbij het gemiddelde wordt bepaald met behulp van de Pde-machts wortel (ofwel tot de macht 1/P).

Resultaten

In principe zijn de resultaten van het model bekend als alle tabbladen zijn gevuld met waardes en formules. Door ’te spelen’ met de aannames, en vooral met de eventuele GF-, V– en P-waardes kan het model gefinetuned worden.

We zijn gestart met de onderkant van het model, het gedeelte rond de Industriële productie. De bovenkant is op de uitkomsten daarvan gebaseerd. Het implementeren van deze onderkant heeft heel wat hoofdbrekens gekost (wat is de betekenis van de variabelen, hoe zijn deze variabelen van elkaar afhankelijk, hoe kunnen we de afhankelijkheid modelleren, welke GF-, V– en P-waardes geven een zo getrouw mogelijk beeld van de werkelijkheid etcetera).

Door tijdgebrek moet G-Info het verder vullen van het model dan ook aan anderen overlaten.

Wel zullen we hieronder nog laten zien op welke manier de resultaten in Excel gemakkelijk kunnen worden weergegeven.

Tabblad Variabelen

Op dit tabblad in het Voorbeeldbestand staan de 25 variabelen van het model nogmaals in een overzicht. Maar ditmaal met aanvullende gegevens, waarmee we de lay-out van de output kunnen sturen:

  1. in de eerste kolom staat aangegeven welk soort variabele het betreft: Input, KF of Tussen
  2. de tweede kolom geeft aan of de betreffende variabele in de standaard-grafieken van het rapport van de Club van Rome is opgenomen
  3. in dat rapport wordt een variabele altijd op dezelfde (onzichtbare) schaal weergegeven. Dat kunnen we nabootsen door aan te geven welke waarde van de variabele overeenkomt met de onderkant van het grafiekgebied (de kolom GrafMin) en welke waarde met de bovenkant (GrafMax)
  4. in de laatste kolom staat een formule waarmee het label bij de betreffende lijn in de grafiek wordt bepaald. Standaard is het een combinatie van de 3e en 4e kolom, gescheiden door een ‘harde return’:

Dit overzicht is een Excel-tabel met de naam tblVar.

Query’s en verbindingen

Alle resultaten moeten nu nog geschaald worden. Dit kan uiteraard op de diverse tabbladen zelf door extra kolommen toe te voegen, maar we hebben er voor gekozen om dit met behulp van Power Query te implementeren.

NB1 Ziet u de Excel-verbindingen aan de rechterkant van het scherm niet, kies dan in de menutab Gegevens de optie Query’s en verbindingen.

Voor alle Excel-tabellen is een verbinding gemaakt. Wil je bekijken hoe die er uit ziet? Klik rechts op een verbinding en kies de optie Bewerken (of dubbel-klik op een query-naam).

NB2 de Input-query’s zijn iets ingewikkelder omdat daar één specifieke kolom uit de input-tabel moet worden opgehaald.

Bij het bewaren van de query’s is de optie Alleen verbinding maken gekozen en is de optie Toevoegen aan gegevensmodel aangevinkt.
Die laatste optie zorgt er voor dat de query’s in het gegevensmodel van deze Excel-sheet worden opgeslagen.

Het gegevensmodel bevat nog één extra query, q_GrafData. Deze combineert alle andere verbindingen tot één database. Deze database vormt de basis voor een draaitabel en een daarbij behorende grafiek.
Ook deze query is opgeslagen met de eigenschappen Alleen verbinding en Toevoegen aan gegevensmodel.

Bekijk de query door dubbelklikken op de naam.

Tabblad GrafData

Op het tabblad GrafData van het Voorbeeldbestand staat een draaitabel, gebaseerd op de query q_GrafData. Hoe genereer je zo’n draaitabel?

  1. selecteer een lege cel, waar de draaitabel moet komen
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het pop-up scherm ziet u dat Excel het gegevensmodel zal gaan gebruiken:
  4. klik op het driehoekje vóór de gewenste query uit het gegevensmodel en sleep de benodigde velden naar de juiste plaats:

In het voorbeeld hebben we ook 3 slicers toegevoegd waarmee het maken van keuzes vergemakkelijkt wordt. Hierboven hebben we met een klik op Ja in de eerste slicer alleen die variabelen geselecteerd, die ook in het rapport van de Club van Rome in de standaard-grafieken voorkomen.

LET OP als er iets aan de parameters van het model wordt gewijzigd dan worden de gegevens op het betreffende tabblad direct gewijzigd. Ook resultaten van formules op andere tabbladen kunnen daardoor wijzigen. De query’s en de draaitabel wijzigen niet automatisch mee! Alles zal vernieuwd moeten worden.
Op het tabblad GrafData wordt met één klik op de betreffende button een VBA-routine gestart die deze totale verversing van het Excel-systeem voor zijn rekening neemt. Dit kan wel enkele minuten duren!

Tabblad Grafiek

Het tabblad Grafiek van het Voorbeeldbestand bevat een draaitabelgrafiek. Dit is de grafische weergave van de gegevens van de draaitabel van het tabblad GrafData. Dus: ook keuzes gemaakt met de slicers worden in deze grafiek meegenomen.

Duidelijk is te zien dat de implementatie van het model nog lang niet klaar is. De industriële productie en de hulpbronnen geven bijvoorbeeld wel al een verwacht verloop, terwijl de blijvende toename van de bevolking of voedsel per hoofd niet reëel is.
Het model in het Voorbeeldbestand is dan ook nog maar voor een klein gedeelte geïmplementeerd. Zoals gezegd: tijdgebrek noopt ons om de rest aan andere Excel-liefhebbers over te laten.

Huizenmarkt

Excel,
Voorbeeldbestand: HuizenMarkt.xlsxDownloaden

Deze week stond er in de Volkskrant een artikel over de heftige ontwikkeling van de huizenprijzen tussen 2020 en 2021.
Er zijn al enkele gemeentes waar de gemiddelde huizenprijs boven de miljoen euro ligt!

Via de link volkskrant.nl/huizenmarkt kun je de gegevens van alle gemeentes van Nederland terugvinden. Het gaat dan over de gemiddelde prijzen in 2020 en 2021, de ontwikkeling tussen die twee jaren en ook de gemeentes met de hoogste en laagste huizenprijzen.

Om het analyseren van de (ontwikkeling van de) huizenprijzen makkelijker te maken hebben we de gegevens in Excel overgenomen en verrijkt met de bijbehorende provincie. In bijgaande werkmap kun je dan ook de gegevens per provincie bekijken.

In dit artikel laten we de diverse elementen van de Excel-sheet de revue passeren.

Brongegevens

De gegevens van de Volkskrant-website zijn opgenomen op het tabblad GemHuisPrijs van het Voorbeeldbestand.

Per gemeente ziet u de gemiddelde huizenprijs in 2020 en 2021.

Het ligt voor de hand om deze gegevens in een kaartgrafiek weer te geven om snel een overzicht over heel Nederland te krijgen.

  1. selecteer de gegevens van de eerste twee kolommen
  2. kies op de menutab Invoegen in het blok Grafieken de optie Kaartgrafiek

De gemeentes met de hoogste huizenprijzen in 2021 vallen direct op.

Helaas zijn er ook ‘blinde vlekken’ te zien; Excel (of Bing?) herkent niet alle gemeentenamen zoals ze in het overzicht zijn opgenomen.

Gegevens transformeren

Zoals hiervoor aangegeven willen we de gegevens nog verrijken met de bijbehorende provincie. Ook het ‘blinde vlekken’-probleem willen we oplossen.

Sinds het beschikbaar zijn van Power Query in Excel wordt ons dit wel heel makkelijk gemaakt!

Op de website van het CBS kun je de de gemeentes per provincie vinden: www.cbs.nl/nl-nl/onze-diensten/…/gemeentelijke-indeling.

In het tabblad Gemeenten_alfabetisch_2021 van het Voorbeeldbestand zijn deze overgenomen.

De kaart-grafiek herkent niet alle namen van de gemeentes (zie ook Excel en kaarten 2).
Via een hulptabel gaan we proberen Excel ‘wat bij te leren’ (zie het tabblad GemNamen; hoe we hier mee omgaan komt later).

Van deze drie overzichten (basisgegevens, gemeentes per provincie en gemeentenamen-correctie) zijn Excel-tabellen gemaakt met respectievelijk de namen tblHuisPrijsGem, tblGemProv en tblGemNamen. Binnen Power Query zijn verbindingen gemaakt met deze drie tabellen (zie voor de techniek bijvoorbeeld het artikel Power Query).

Met de optie Query’s samenvoegen binnen Power Query zijn deze verbindingen aan elkaar gekoppeld (zie de stappen binnen de query q_Resultaat). De laatste stap is het meest interessant: de drie kolommen (2020, 2021 en een berekende kolom Wijz%) worden omgezet naar een database-structuur.
Het resultaat staat in het tabblad NwData van het Voorbeeldbestand:

NB we gaan deze nieuwe tabel gebruiken als bron voor draaitabellen. Om de bestandsgrootte te beperken hadden we er ook voor kunnen kiezen om het resultaat van de query alleen aan het gegevensmodel toe te voegen (zie het artikel Power Query) en daar de draaitabel op te baseren.

Overzichten

Het eerste overzicht (een landkaart met gemeentes) gaan we baseren op een draaitabel met als filters de Provincie en het Kenmerk (2020, 2021 of Wijz%; zie het tabblad Draai van het Voorbeeldbestand).
Aangezien Kaart-grafieken niet rechtstreeks gekoppeld kunnen worden aan een draaitabel maken we naast de draaitabel een hulptabel:

Als we dan toch bezig zijn: als het kenmerk Wijz% is, dan vermenigvuldigen we de waarde met 100, dat is beter interpreteerbaar.

NB achteraf gezien hadden we beter bij de berekening van Wijz% binnen Power Query deze vermenigvuldiging kunnen doen!

Op basis van deze hulptabel hebben we een kaartgrafiek gemaakt en aan de draaitabel zijn 2 slicers toegevoegd (voor de Provincie en het Kenmerk). De grafiek en de slicers zijn ‘geknipt’ en in een nieuw tabblad Dashboard geplakt.

NB1 Aangezien de grafiektitel in een kaartgrafiek niet dynamisch kan zijn (dus gekoppeld aan een cel) is de titel vervangen door een tekstblok. De inhoud daarvan is gelijk aan de cel E3 van het tabblad Draai:
=ALS(C3=”Wijz%”;”Verandering tussen 2020 en 2021″;”Gem. huisprijzen in “&C3)&
ALS(OF(C2=”(Alle)”;C2=”(Meerdere items)”);””;”
(“&C2&”)”)

NB2 de overgang in de formule hierboven van de 2e naar de 3e regel is ingevoerd door de toetscombinatie Alt-Enter, waardoor er in het tekstblok ook altijd op die plaats een regelovergang is.

NB3 voor de kaartgrafiek is een maximaal bereik van cellen geselecteerd, zodat iedere keuze van Provincie (of heel Nederland) meegenomen wordt. De formule in kolom F is daartoe wat uitgebreid anders zou de ondergrens van de legenda altijd 0 zijn; in cel F7 staat:
=ALS(C7=””;MIN($F$6:F6);C7*ALS($C$3=”Wijz%”;100;1))

LET OP Excel kan voor de provincies Drenthe en Groningen geen kaart genereren. Eén of meer plaatsen worden niet herkend?
Het vreemde is, dat het voor Drenthe wel werkt als je ook Overijssel kiest (hou Ctrl ingedrukt bij het selecteren in de slicer)! En Groningen wordt zichtbaar als je de drie andere provincies in de buurt ook kiest.

Dit is het moment om te kijken of de kaart nog ‘blinde vlekken’ heeft. Als je weet welke gemeente(s) het betreft kun je op het tabblad GemNamen proberen of een andere naam of toevoeging aan de naam werkt. In de eerste kolom komt de naam zoals die voorkomt in het eerste bronbestand, in de tweede kolom plaats je een naam waarvan je denkt/hoopt dat Excel die zal herkennen.
Vergeet niet alle verbindingen en draaitabellen te vernieuwen: klik op de button Alles vernieuwen op de menutab Gegevens:

In het tabblad Draai van het Voorbeeldbestand is nog een tweede draaitabel gecreëerd met daarin de Top-10 van de gemeentes (zie voor de werkwijze het artikel Top-5; verschillende methodes). Hier willen we een Bellengrafiek van maken net als in de Volkskrant. Ook dan moeten we met een hulptabel werken:

De draaitabel is automatisch gekoppeld aan de bestaande slicers. Wanneer we op basis van de hulptabel een Bellengrafiek maken en de assen, rasters en overige ‘ballast’ weglaten, kunnen we ook deze grafiek naar het tabblad Dashboard kopiëren.

In de cellen naast de grafiek maken we verwijzingen naar de betreffende cellen in het tabblad Draai. Wel eerst de achtergrond van het teken- en grafiekgebied transparant maken.

Nog een tekstblok met een rand er omheen, et voilà.

Ook voor de laagste prijzen (of wijzigingspercentage) maken we op dezelfde manier een grafiek op basis van een draaitabel en een hulptabel.

Het resultaat mag er zijn (zie het tabblad Dashboard van het Voorbeeldbestand).

En nog interactief ook:

Power Query en het Web 2

Excel, , , ,
Voorbeeldbestand: WebPQ2.xlsxDownload

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.

Power Query en het Web

Excel, , ,
Voorbeeldbestand: WebPQ.xlsxDownload

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!

Data verrijken deel 2

Excel, , ,
Voorbeeldbestand: DataVerrijken2.xlsxDownload

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: