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:
- kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
- 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.
- selecteer alle cellen in de kolom OpdrGever op het tabblad Data.
- kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
- selecteer bij Toestaan de optie Lijst en vul bij Bron in: =Namen
NB denk aan het =-teken! - 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:
- selecteer een van de cellen in de tabel tblData
- 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
- Power Query heeft al een koppeling gemaakt naar de bron en de kolomtypes gewijzigd
- wijzig de naam in q_tblData om het verschil met de tabel extra duidelijk te maken
- 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 - we voegen nog een Aangepaste kolom toe met de naam Tijd en als formule =[EindTijd]-[BeginTijd]
- 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 - de Begin– en Eindtijd hebben we niet meer nodig, dus die kunnen verwijderd worden
- 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):
- ga naar een nieuw tabblad
- kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
- verplaats vanuit de tabel q_tblData het veld OpdrGever naar de Rijen, RegioNaam naar de Kolommen en de Tijd naar het Waarden-gebied
- 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:
