1  Importeren van data

Doelstellingen

Bij de analyse van data probeer je antwoorden te vinden op vragen door statistische methodes toe te passen op waarnemingen. Deze waarnemingen - verzameld uit rapportages, enquêtes en experimenten - vormen de ruggegraat van een statistisch onderzoek en worden data genoemd. In de praktijk komen veel gegevens uit externe bronnen: txt/csv, web, excel bestanden, databases, … Om deze gegevens in Excel te analyseren, moeten ze eerst geïmporteerd worden en soms worden getransformeerd en opgeschoond. Dat gebeurt met Power Query.

In Excel is Power Query beschikbaar onder de groep Gegevens ophalen en transformeren in het lint Gegevens. Hiermee kun je verbindingen maken met meerdere gegevensbronnen en daarna die gegevens structureren (bijvoorbeeld een kolom verwijderen, een gegevenstype wijzigen) of de gegevens omzetten (transformeren) naar wat anders. Uiteindelijk komen de gegevens dan in een Excel werkblad beschikbaar. Alle stappen die je met de Power Query Editor uitvoert worden in een script vastgelegd.

Deze aanbevolen werkwijze heeft de volgende voordelen:

• De oorspronkelijke gegevens blijven onveranderd.
• Omdat de uitgevoerde acties in een script zijn vastgelegd heb je tevens een vorm van documentatie van wat je met de gegevens gedaan hebt.
• In het script kun je eenvoudig een stap wijzigen, ongedaan maken of een nieuwe stap toevoegen.
• Wanneer er wijzigingen of aanvullingen in de brondata komen hoef je de query alleen maar opnieuw uit te voeren waarna de gewijzigde gegevens in het werkblad verschijnen.

1.1 Gegevens ophalen en transformeren

Om Gegevens ophalen en transformeren in Excel te gebruiken, maak je eigenlijk een query in de werkmap. Met een query kun je een verbinding maken met een breed scala aan beschikbare gegevensbronnen, een voorbeeld van de gegevens bekijken en de gegevens transformeren.

De eerste stap is het maken van een verbinding met een gegevensbron. Wanneer de verbinding is gemaakt, krijg je een voorbeeld van de gegevens te zien. Daarna kun je stappen toevoegen om de gegevens te structureren en te transformeren. Hiervoor wordt een Query Editor gestart om een aantal instructies te maken die de acties uitvoeren. De laatste stap is het laden van de gegevens als een tabel in een werkblad.

• Je hebt ook de mogelijkheid om de gegevens te laden in het ingebouwde gegevensmodel.
• Je kunt de query later ook wijzigen en/of de gegevens vernieuwen.

Voor het maken van een query gebruik je de tab Gegevens in het lint, waarna je een van de mogelijke acties in de Gegevens ophalen en transformeren groep kiest.

Figuur 1.1: Lint gegevens.

Er zijn knoppen voor het ophalen van gegevens uit tekst/csv, web en een Excel tabel/bereik. Deze gegevensbronnen en nog veel meer zijn beschikbaar via de knop Gegevens ophalen.

De groep Query’s en verbindingen heeft een knop voor het vernieuwen van query’s en een knop om bestaande query’s en verbindingen te bekijken en te beheren.

1.2 Importeren van een tekst/csv bestand

Veel gegevens in externe bronnen zijn beschikbaar als tekst (bestandstype .txt) of via komma (of karakter) gescheiden waarden (bestandstype .csv).

Taak 1.1 Hulpbestand: SalesJan2009.csv

1. Open in Excel een nieuwe lege werkmap.

2. Kies tab Gegevens > Uit tekst/CSV (groep Gegevens ophalen en transformeren).

3. Blader naar het bestand SalesJan2009.csv en klik Importeren. Een voorbeeld van de gegevens wordt getoond.

Figuur 1.2: Voorbeeld van SalesJan2009 gegevens.

4. Klik op Gegevens transformeren. De Query Editor wordt gestart in een nieuw venster. In de rechterzijbalk Queryinstellingen zie je onder Toegepaste Stappen de stappen die reeds zijn uitgevoerd.

5. Selecteer de kolommen Account_Aangemaakt en Laatste_Login door op de kolomkop te klikken met ingedrukte CTRL toets. Kies dan Kolommen verwijderen (groep Kolommen beheren) > Kolommen verwijderen. De kolommen worden verwijderd en de stap is toegevoegd aan Toegepaste Stappen.

Als alternatief kun je de rechtermuisknop gebruiken en dan de gewenste actie uit het snelmenu kiezen.

In elke kolomkop zit aan de linkerkant een knopje welke het toegepaste gegevenstype weergeeft. Wanneer je een kolom selecteert zie je het gegevenstype ook op het lint. En dit is tevens de plek waar je het gegevenstype kunt wijzigen.

Als bij het importeren het automatisch ontdekken van het gegevenstype goed gegaan is heeft kolom Prijs het gegevenstype Geheel getal (zie Figuur 1.3), kolom Transactie_Datum het gegevenstype Datum/Tijd en de andere kolommen het gegevenstype Tekst.

Figuur 1.3: Gegevenstype.

6. Controleer dat kolom Transactie_Datum het juiste gegevenstype heeft. Zo niet, wijzig deze dan via Gegevenstype (groep Transformeren) > Datum/Tijd. Wanneer er een dialoogscherm komt met de vraag of de bestaande conversie vervangen moet worden, kies dan voor Vervangen van de huidige conversie.

7. In kolom Naam beginnen de meeste namen met een hoofdletter, maar niet allemaal. Dat dient gecorrigeerd te worden. Selecteer kolom Naam, rechtermuisklik en kies Transformeren > Elk Woord Met Een Hoofdletter.

8. Kies Sluiten en laden(groep Sluiten) > Sluiten en laden. De gegevens worden in een Excel tabel in een nieuw werkblad opgeslagen. Je kunt nu met de gegevens werken en de Excel map voor later gebruik opslaan.

Het is altijd mogelijk om de query achteraf te wijzigen. Selecteer hiervoor een cel in de tabel en kies tab Query > Bewerken (groep Bewerken). De Query Editor wordt weer opgestart. Van hieruit kun je tevens de gegevens vernieuwen, alle stappen worden dan herhaald.

1.3 Importeren van een webtabel

In webpagina’s tref je vaak tabellen. Bekijk maar eens de pagina Europees kampioenschap voetbal mannen. Power Query in Excel kan deze tabellen herkennen en importeren. In deze taak ga je uit de tabel Erelijst de kolommen Jaar en Winnaar importeren voor de jaren waarvoor ook een winnaar bekend is.

Taak 1.2 Import tabel Wikipedia

1. Open een nieuwe lege werkmap.

2. Kies tab Gegevens > Van het web (groep Gegevens ophalen en transformeren). Er wordt een dialoogvenster weergegeven waarin de URL van de webpagina moet worden ingevoerd.

Figuur 1.4: Dialoogvenster voor invoer URL.

3. Voer voor de URL in
   https://nl.wikipedia.org/wiki/Europees_kampioenschap_voetbal_mannen
   en klik OK.

Er wordt een verbinding met de webpagina gemaakt. Wanneer de verbinding gerealiseerd is wordt een Navigator venster getoond waarin een lijst met alle tabellen in de webpagina te zien is. Door op een tabel te klikken verschijnt aan de rechterkant van het venster een voorbeeld van de gegevens.

Figuur 1.5: Venster Navigator voor UEFA pagina op Wikipedia.

4. Selecteer tabel Erelijst[bewerken] en klik dan op de knop Gegevens transformeren. De Query Editor wordt gestart in een nieuw venster. Aan de rechterkant onder Queryinstellingen en onder Toegepaste Stappen zie je de stappen die reeds zijn toegepast.

Vervolgens kun je de gegevens naar behoefte structureren, wijzigen en opschonen.

5. Selecteer de kolommen Jaar en Winnaar door op deze kolomkoppen te klikken met ingedrukte CTRL toets. Kies dan Kolommen verwijderen (groep Kolommen beheren) > Andere kolommen verwijderen.

6. Selecteer kolom Jaar en kies Waarden vervangen (groep Transformeren). Het dialoogvenster Waarden vervangen wordt weergegeven.

Figuur 1.6: Dialoogvenster Waarden vervangen.

7. Type Details in het tekstvak Te zoeken waarde, laat het tekstvak Vervangen door leeg. Klik daarna op OK.

8. Selecteer kolom Winnaar en klik op de filter keuzepijl in de kolomkop. Deselecteer de optie leeg en klik OK.

9. Kies Sluiten en laden(groep Sluiten) > Sluiten en laden. De gegevens worden in een Excel tabel in een nieuw werkblad opgeslagen. Je kunt nu met de gegevens werken en de Excel map voor later gebruik opslaan.

Figuur 1.7: Tabel met winnaars van het EK voetbal voor mannen.

1.4 Importeren uit een Access database

In een database zit de data in tabellen die meestal aan elkaar gekoppeld zijn. Via queries in de database kun je ook nog eens bepaalde gegevens selecteren en eventueel berekeningen op gegevens uitvoeren. In deze oefening wordt gewerkt met de eenvoudige database olympischesporten.accdb.

Deze database bevat de Engelstalige en Nederlandstalige namen van de olympische sporten en disciplines voor de twee versies van de spelen (zomer, winter), alsmede de afkorting van de sportbond, het beginjaar en het eventuele eindjaar van de disciplines.

De door het IOC gebruikte definitie van sport is een verzameling van disciplines die door één sportbond worden vertegenwoordigd. Zo vallen onder de sport “Volleybal” de disciplines “Zaalvolleybal” en “Beachvolleybal”, vertegenwoordigd door de sportbond FIVB (Fédération Internationale de Volleyball).

1.4.1 Uit een query

Taak 1.3 Hulpbestand: olympischesporten.accdb

1. Open in Excel een nieuwe lege werkmap.

2. Kies tab Gegevens > Gegevens ophalen (groep Gegevens ophalen en transformeren) > Uit database > Uit Microsoft Access-database.

3. Blader naar het bestand olympischesporten.accdb en klik Importeren. Het dialoogvenster Navigator verschijnt waarin je de gewenste items uit de database kunt selecteren.

Figuur 1.8: Dialoogvenster Navigator met als selecteerbare items 2 tabellen en 1 query.

4. Selecteer de query NL sporten en disciplines waarna in het rechterdeel van het venster een voorbeeld van de gegevens verschijnt.

5. Kies Laden > Laden. De gegevens worden in een tabel in een nieuw werkblad geladen.

1.4.2 Uit tabellen

Taak 1.4 Hulpbestand: olympischesporten.accdb

1. Kies tab Gegevens > Gegevens ophalen (groep Gegevens ophalen en transformeren) > Uit database > Uit Microsoft Access-database.

2. Blader naar het bestand olympischesporten.accdb en klik Importeren. Het dialoogvenster Navigator (Figuur 1.8) verschijnt weer.

3. Vink het selectievakje Meerdere items selecteren aan en selecteer daarna de twee tabellen Discipline en Sport.

4. Kies Laden > Laden. Je ziet nu de gegevens niet in het werkblad verschijnen, ze zitten wel in het gegevensmodel.

Wanneer je twee of meer tabellen tegelijk importeert wordt er automatisch een gegevensmodel gemaakt. Deze integreert de geïmporteerde tabellen waarbij de bestaande relaties tussen de tabellen gebruikt worden. Je kunt dit met Power Pivot bekijken en bewerken. Dit gaat als volgt.

Kies tab Power Pivot > Beheren. Er verschijnt nu een nieuw venster Power Pivot voor Excel met linksonder de tabbladen voor de geïmporteerde tabellen. Wanneer je de relatie wilt zien kies je in dit venster voor Diagramweergave (groep Weergave). Wanneer je alles bekeken hebt kun je dit venster weer sluiten.

1.4.3 Analyse

Voor het analyseren van de gegevens wordt een draaitabel gebruikt.

Taak 1.5 Analyse met draaitabel

Kies Invoegen > Draaitabel. Zorg er voor dat het gebruik van het gegevensmodel geselecteerd is (zie Figuur 1.9).

Figuur 1.9: Dialoogvenster Draaitabel maken. Selecteer het gebruik van het gegevensmodel.

Testvragen

1. Hoeveel disciplines waren er in het beginjaar van de zomerspelen?

2. Hoeveel disciplines zijn er nu voor de zomerspelen?

3. Hoeveel disciplines zijn er totaal verdwenen?

4. Welke sport heeft de meeste disciplines?

5. Hoeveel nieuwe disciplines zijn er voor de zomerspelen 2020 bijgekomen?

1.5 Opgaven

Oefening 1.1 Een csv bestand importeren

1. Open eerst het bestand verkopen.csv met een kale tekstverwerker (Kladblok, Notepad) om te onderzoeken hoe het bestand er uit ziet. Het is belangrijk om te weten is of er veldnamen in staan en welk scheidingssymbool gebruikt is. Meestal herkent Power Query dit zelf, maar het is toch wel nuttig om dit zelf eerst na te gaan.

2. Importeer de gegevens via Gegevens ophalen en transformeren.

• De veldnamen staan in de eerste rij. Om deze te veranderen in de kolomkoppen kies je Start > De eerste rij als veldnamen gebruiken (groep Transformeren) > De eerste rij als veldnamen gebruiken.

• Door de transformatie naar veldnamen wordt ook het gegevenstype aangepast. Controleer of de kolommen naar het juiste gegevenstype zijn omgezet. Zo niet, wijzig dan het gevenstype.

3. Bewaar het bestand voor later gebruik onder de naam verkopen.xlsx.

Oefening 1.2 Een Access database importeren

1. Open in Excel een nieuwe lege werkmap en importeer uit het bestand snoeporders2010.accdb de drie tabellen via Gegevens ophalen en transformeren.

2. Maak via een draaitabel een lijngrafiek van het aantal orders per maand.

3. Maak via een draaitabel een kolomdiagram van het aantal orders per provincie, gesorteerd van hoog naar laag.

Oefening 1.3 AEX koersen

Zoek op het internet naar een website die de actuele beurskoersen van de bedrijven in de AEX index als een tabel in een pagina aanbiedt. Haal vervolgens deze data via Power Query in een Excel werkblad binnen.

Oefening 1.4 Nederlandse gemeenten

Deze wikipediapagina bevat een tabel van Nederlandse gemeenten met een aantal gegevens over die gemeenten.

1. Importeer via Power Query deze tabel in Excel en neem alleen de kolommen Gemeente, Provincie, Inwoneraantal en Oppervlakte op.

2. Maak een draaitabel van het aantal gemeenten per provincie. Welke provincie telt de meeste gemeentes?

3. Maak draaigrafiek van het inwoneraantal per provincie, gesorteerd van groot naar klein.

4. Maak draaigrafiek van het Land-oppervlak per provincie, gesorteerd van groot naar klein.

5. Bedenk een manier om met een draaitabel de bevolkingsdichtheid van elke provincie te bepalen.