LET OP: na het downloaden de extensie wijzigen in zip
Het einde van het jaar nadert en zo ook de (voor Nederlanders zeer bekende) Top-2000, een marathon-uitzending op Radio-2 met de 2.000 beste (?) liedjes uit de pop-geschiedenis.
In een eerder artikel (Top2000 en draaitabellen) hebben we laten zien hoe je draaitabellen kunt gebruiken om meer inzicht te krijgen in de ontwikkeling binnen deze lijst vanaf 1999.
Naar aanleiding van dat artikel reageerde iemand op de site met de opmerking dat de Cats in 2020 met 5 nummers in de lijst voorkwamen. En dat dit in de overzichten niet was terug te vinden. Wat bleek: in de officiële lijst van Radio-2 over 2020 was deze groep opgenomen onder The Cats.
Tijd dus om een nieuw systeem te maken waarmee we inconsistentie in de aanlevering snel kunnen corrigeren. En een mooie aanleiding om een handig onderdeel van Power Query eens onder de loep te nemen.
Brongegevens
Op het goeie, vertrouwde Wikipedia is veel te vinden, dus ook de standen van de Top2000 in de afgelopen jaren. Zelfs de eerste 10 van 2021 zijn al ingevuld.
Op het tabblad Wiki van de file Top2000 vs2.xlsx in het Voorbeeldbestand hebben we met behulp van Power Query deze tabel overgehaald (zie de query tblWiki in Query’s en verbindingen).
NB de link naar de bron kan op 2 manieren weergegeven worden: eindigend op Lijst_van_Radio_2-Top_2000’s óf op Lijst_van_Radio_2-Top_2000%27s. Een URL mag eigenlijk geen ‘vreemde’ tekens bevatten; tegenwoordig worden deze tekens echter door de browser zelf omgezet naar de bijbehorende ASCII-code (een apostrof heeft de hexadecimale code 27; in de URL voorafgegaan door een %).
Deze Wiki-tabel is met wat Power Query-inspanning omgezet naar losse bestanden per jaar.
Deze jaar-standen zijn tot en met 2019 opgeslagen in een map Top2000 in het Voorbeeldbestand.
De laatste 2 jaren hebben we nog even apart gehouden voor demonstratiedoeleinden.
De map, de 2 jaarbestanden en de voorbeeldfile zijn allemaal terug te vinden in het Voorbeeldbestand.
Alle jaar-bestanden hebben dezelfde lay-out: ze bevatten één tabblad (met de naam Data) en hebben allemaal 4 kolommen met dezelfde kopnamen:
NB het Wiki-bestand blijkt wel consistent te zijn in de naamgeving van de artiesten (en de titels)!
Bron-bestanden koppelen
Ja, ik weet het: dat klinkt niet logisch! Eerst losse bestanden maken en dan weer aan elkaar koppelen?
Maar we bereiken daarmee wel dat we flexibeler zijn in het maken van overzichten.
Daarnaast geeft het inzicht in een methode die veel vaker gebruikt kan worden. Wat dat laatste betreft: in menig rapportageproces gaat iedere maand (te) veel tijd zitten in het ophalen van nieuwe maandstanden, het toevoegen aan de bestaande gegevens en daarna zorgen dat alle overzichten weer up-to-date zijn. En dat kan dus met ‘één druk op de knop’! Tenminste als je rapportage-systeem goed is ingericht.
Daar gaat ie:
- pak het Voorbeeldbestand uit
- start met een nieuwe werkmap
- kies in de menutab Gegevens in het blok Gegevens ophalen en transformeren de optie Gegevens ophalen
- kies Uit bestand en dan Uit map
- selecteer de map Top2000 uit het hiervoor uitgepakte Voorbeeldbestand
- kies bij de button Combineren
de optie Combineren en laden naar … - in het vervolgscherm kiezen we het tabblad Data en OK
- Excel gaat nu even ‘denken’, waarna we de vraag krijgen wat we met de gecombineerde bestanden willen doen. Voorlopig nog niks, dus we kiezen voor Alleen verbinding maken. Wel zorgen dat de gegevens aan het gegevensmodel worden toegevoegd:
- Om de klus te klaren heeft Excel diverse query’s gegeneerd. Voor ons is alleen de Top2000-query interessant.
NB we kiezen er voor om alleen een verbinding te maken. Waarom zou je de gegevens nog een keer in dit bestand opnemen? De basisgegevens zijn altijd te raadplegen in de map Top2000.
Met deze techniek wordt het bestand veel minder groot, dat is altijd handig.
Maar het vervelende is nu, dat de Top2000-query altijd naar de net gekozen map zoekt. Wanneer u bijvoorbeeld de file Top2000 vs2.xlsx uit het Voorbeeldbestand opent en u vernieuwt de Top2000-query dan krijgt u ongetwijfeld een foutmelding.
Dat kunnen we makkelijk oplossen:
- geef een cel een naam (bijvoorbeeld MapNaam; zie het tabblad Data)
- vul deze cel met het pad naar en de naam van de gewenste map.
In de Verkenner van Windows haalt u die gemakkelijk op door rechts te klikken in de kop en kies dan Adres kopiëren:
- dubbelklik op de query Top2000
- kies bij TOEGEPASTE STAPPEN de eerste stap: Bron
- wijzig de formulebalk nu in
= Folder.Files(Excel.CurrentWorkbook(){[Name=”MapNaam”]}[Content]{0}[Column1]) - nu we toch bezig zijn, voegen we aan de query nog een paar lay-out-stappen toe:
- kies de optie Sluiten en laden
Overzichten
Maar, nu hebben we wel een koppeling maar nog geen gegevens, laat staan informatie. Dat lossen we snel op:
- selecteer een lege cel in de werkmap met de Top2000-query
- kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
- in het vervolgscherm ziet u dat het systeem als bron het gegevensmodel wil gebruiken. Daar zitten onze brongegevens, dus dat is ok.
- de locatie voor het draaitabelrapport kan een nieuw werkblad zijn of beginnen in een cel van een bestaand werkblad.
- kies de button OK
- plaats de 4 velden zoals hiernaast in het blok Rijen
- plaats het veld Uitz_Jr in het blok Filters of maak een Slicer die op dat veld gebaseerd is
- het resultaat staat op het tabblad OvzPerJaar van het voorbeeldbestand
NB1 we gebruiken de draaitabel dus alleen om gegevens snel te rangschikken; we passen geen berekeningen toe.
NB2 gebruik in de draaitabellen die op een gegevensmodel zijn gebaseerd alleen tabellen met daarbij een oranje database-teken (of is het een prullenbak?).
De map Top2000 bevat op dit moment alleen de jaren 1999 tot en met 2019. In die jaren hebben maar vier verschillende nummers op plaats 1 gestaan. Dat geldt ook voor plaats 2; bij plaats 3 zien we al vijf verschillende nummers.
Door in de slicer een specifiek uitzend-jaar te kiezen, krijg je het betreffende jaaroverzicht:
In het tabblad OvzPerArtiest staat een andere draaitabel:
Nog een ander overzicht in het tabblad ArtiestenOvz van het Voorbeeldbestand:
Kijk voor een mooi, meer grafisch overzicht op de site van de Dutch Data Dude: 
Consistentie
Zoals beloofd zou ons systeem de inconsistentie in naamgeving moeten oplossen. ‘Helaas’ zijn de makers van de Wiki-pagina zo ijverig geweest om de inconsistentie daar al te verwijderen.
Maar voor het geval dat er in de toekomstige aanleveringen wel weer andere namen opduiken laten we hier zien hoe je dat makkelijk kunt oplossen.
Het tabblad Data van het Voorbeeldbestand bevat 2 Excel-tabellen waarin de noodzakelijke correcties kunnen worden opgenomen.
Deze zijn op de standaardmanier met Power Query aan het gegevensmodel toegevoegd (de query’s hebben de namen q_tblCorArtiest en q_tblCorTitel).
NB de correcties die hier staan zijn natuurlijk niet nodig, maar worden gebruikt als voorbeeld.
Nu gaan we de Top2000-query aanpassen, zodat het resultaat weer consistent is:
- dubbelklik op de Top2000-query (of klik rechts en kies Bewerken)
- kies in het blok Combineren van de menutab Start de optie Query’s combineren
- vul het scherm in, zoals hiernaast; de groene kolommen geven de velden aan met mogelijk overeenkomende waarden.
- zorg bij Type join dat Left outer is geselecteerd. We willen natuurlijk wel alle records uit de bron zien!
- klik op OK
- herhaal de stappen 2 t/m 5 ook voor de correctie van de titels.
- vouw de 2 kolommen met tabellen uit door op de button met de 2 pijltjes te klikken
Zorg dat alleen de kolom Moet zijn wordt meegenomen. - kies in de menutab Kolom toevoegen in het blok Algemeen de optie Voorwaardelijke kolom
- vul het scherm in zoals hieronder
NB bij Uitvoer en Anders eerst de juiste soort selecteren via de vink-button - voeg nog een Voorwaardelijke kolom toe om ook de gecorrigeerde titels mee te nemen
- nu kunnen er nog wat overbodige kolommen verwijderd worden, andere kolommen krijgen een nieuwe naam en misschien een andere plaats. Kies dan weer Sluiten en laden.
Alle stappen kunnen gevolgd worden in het blok aan de rechterkant van de editor.
Klik op een stap en je ziet in de formulebalk wat er precies gebeurt.
Sommige stappen zijn van een ’tandwieltje’ voorzien; als je daar op klikt krijg je een meer overzichtelijk tussenscherm te zien met de uit te voeren handelingen.
In de laatste stap is bij de Eigenschappen van die stap (rechts klikken) een Beschrijving ingevuld.
Ga je met de muis over deze stap dan wordt de beschrijving direct zichtbaar
Een prima methode dus om de werking van de query te documenteren.
Nieuwe of gewijzigde gegevens
Na al dat werk kunnen we nu achterover gaan leunen:
- kopieer de files 2020.xlsx en 2021.xlsx uit het voorbeeldbestand naar de Top2000-map
- zorg dat in de file Top2000 vs2.xlsx in het tabblad Data de juiste mapnaam staat aangegeven
- klik rechts op één van de draaitabellen en kies Vernieuwen
- alles is bijgewerkt!
Komt Radio-2 met de volledige lijst van 2021: controleer de namen van de kolomkoppen, zorg dat het tabblad de naam Data heeft, bewaar de file in de Top2000-map onder de naam 2021.xlsx (overschrijven dus) en vernieuw een draaitabel.
Dezelfde procedure kun je hanteren als in één van de basis-bestanden een fout zou staan.
Veel plezier met de Radio-2/Top-2000!
Helemaal top. Super