Data ophalen met Power BI Desktop
Gegevens importeren is de eerste stap van de data-ijsberg.
In het eenvoudige voorbeeld van het hoofdstuk ‘Eenvoudige toepassing in Power BI Desktop’ staan alle gegevens die we nodig hebben voor onze analyse in één bestand. Dat is vaak niet het geval.
Met Power BI Desktop kun je gegevens ophalen uit de zich almaar uitbreidende wereld van gegevens. En die lijst groeit nog altijd aan. Power BI verdeelt de gegevensbronnen in zeven categorieën.
Figuur 1: categorieën gegevensbronnen
1 File (Bestand): Excel-bestanden, CSV-bestanden, Tekstbestanden (TXT), JSON-bestanden (JavaScript Object Notation), mappen (met Power BI kun je volledige mappen ophalen), PDF, Sharepoint-mappen enzovoort. (Klik in het linkerdeelvenster op File om de volledige lijst te zien.)
2 Database: Access database, SQL Server database enzovoort. (Klik in het linkerdeelvenster op Database om de volledige lijst te zien.)
3 Microsoft Fabric: Power BI semantic models, Dataflows, Datamarts (preview), Warehouses, Lakehouses, KQL Database.
4 Power Platform: Power BI dataflows (Legacy), Common Data Service (Legacy), Dataverse, Dataflows.
5 Azure: een verzameling cloudservices van Microsoft.
6 Online services: Dynamics 365, Facebook, Google Analytics enzovoort. (Klik in het linkerdeelvenster op Online services om de volledige lijst te zien).
7 Other: web, Microsoft Exchange online enzovoort. (Klik in het linkerdeelvenster op Online services om de volledige lijst te zien.)
In de volgende voorbeelden maken we verbinding met een aantal veelgebruikte gegevensbronnen en laden we de gegevens in het gegevensmodel.
Notitie
Met Power BI Desktop maakt je verbinding met de brongegevens. Het bestand wordt dus eigenlijk niet echt geïmporteerd in Power BI. Wat er wel gebeurt, is dat er een nieuwe gegevensset wordt gemaakt in Power BI en dat de gegevens en het gegevensmodel (indien van toepassing) in de gegevensset worden geladen.
1 Gegevens ophalen uit bestanden
1.1 Een Excel-bestand ophalen
Situatieschets
Het bedrijf Tex-Mex bv gebruikt een Excel-werkmap om de gegevens van klanten en personeelsleden te bewaren (Database_Tex-Mex_Excel). De werkmap bevat meerdere werkbladen. We laden alle gegevens in Power BI Desktop.
1 Open Power BI Desktop. Klik in het lint op Home > Excel workbook (groep Data).
Figuur 2: verbinding maken met een Excel bestand
2 Navigeer naar het opgavebestand Database_Tex-Mex_Excel.xlsx en klik op Openen
3 Vink in het deelvenster Navigator alle tabellen aan:
Figuur 3: deelvenster Navigator
4 Klik op Load om de data naar het gegevensmodel te laden. Zodra de gegevens geladen zijn, kun je de tabel en de velden zien in het deelvenster Data.
Figuur 4: deelvenster Data
5 Klik op de weergave Table view om de gegevens te zien:
Figuur 5: weergave Table view
6 Bewaar het bestand als H3_Gegevensanalyse_Tex-Mex.pbix.
Notitie
Het is mogelijk dat je een foutmelding krijgt als je probeert om verbinding te maken met een Excel-bestand met het oude bestandsformaat (.xls-bestand). In Power BI Desktop maken zowel Access-databases als vroege versies van Excel-werkmappen (.XLS-bestanden van het type Excel 97-2003) gebruik van de Access Database Engine. Verschillende situaties kunnen ertoe leiden dat de Access Database Engine niet goed werkt. Je kunt de Access Database Engine installeren (zie FAQ, Een Access-bestand kan niet geladen worden) of het Excel-bestand converteren naar het nieuwe bestandsformaat (.xlsx).
Figuur 6: foutmelding bij het laden van een Excel-bestand met het oude bestandsformaat (.xls-bestand)
1.2
Een CSV-bestand ophalen
Notitie
Een CSV-bestand is een kommagescheiden bestand (Comma Separated Values). Dat is een bestand met een oude database-indeling. Het bestand bestaat alleen uit tekst, waardoor de gegevens gemakkelijk geïmplementeerd (lezen/schrijven) kunnen worden.
Situatieschets
Het bedrijf Tex-Mex bv heeft eigen software ontwikkeld om de klachten te registreren. Het bestand Klachten.csv is een bestand geëxporteerd uit dit systeem.
1 Klik in het lint op Home > Get Data > Text/CSV
Figuur 7: Get Data > Text/CSV
2 Navigeer naar het bestand Klachten.csv en klik op Openen
3 Je krijgt onmiddellijk de inhoud van het bestand te zien.
Figuur 8: tabel Klachten
4 Klik op Load om de data te laden naar het gegevensmodel. Zodra de gegevens geladen zijn, kun je de tabel en velden zien in het deelvenster Data.
5 Bewaar met CTRL+S.
1.3 Een volledige map met bestanden ophalen
Met Power BI kun je alle bestanden in een bepaalde map eenvoudig combineren in één tabel, zolang ze hetzelfde bestandstype en dezelfde structuur hebben.
Er zijn verschillende methoden om bestanden in een map te combineren. De meest gebruikte methode is Combine&Load (zie hieronder). Deze methode is eenvoudig toe te passen, maar werkt enkel wanneer de Excel-bestanden exact dezelfde structuur hebben. Deze methode werkt niet als bijvoorbeeld:
• het werkblad van een van de Excel-bestanden een andere naam heeft dan de naam waarnaar verwezen wordt bij het combineren van de bestanden;
• er een andere veldnaam gebruikt wordt in een van de Excel-bestanden;
• de kolommen in een andere volgorde gesorteerd zijn in een van de Excel-bestanden;
• enzovoort.
Het is dan ook aan te raden om een alternatieve methode te gebruiken om bestanden in een map te combineren. Deze methode wordt toegelicht in het hoofdstuk 'Gegevens opschonen en transformeren met Power Query Editor'.
Situatieschets
Maandelijks ontvangen we een Excel-bestand met de orders. In plaats van elk bestand afzonderlijk te laden, voegen we alle bestanden toe aan de map Orders_Excel. Met Power BI kunnen we de bestanden in één keer laden. Nieuwe bestanden worden automatisch toegevoegd zodra ze toegevoegd zijn aan de map Orders_Excel.
1 Klik in het startscherm op Get Data of in het lint op Home > Get Data > More…
2 Selecteer Folder en klik op Connect
Figuur 9: map ophalen
3 Navigeer naar de map Orders_Excel en klik op OK.
4 Er wordt een lijst weergegeven met de bestanden in de map.
Figuur 10: lijst bestanden map Orders_Excel
5 Klik op Combine > Combine & Load.
6 Standaard wordt het eerste bestand (First file) als model gebruikt. Selecteer het werkblad dat moet gecombineerd worden, het werkblad Sheet1.
Notitie
Met deze methode wordt de werkbladnaam hard gecodeerd in de Power Query-functie, waardoor je problemen krijgt als je een werkblad met een andere naam wilt combineren. Dat kan achteraf wel gewijzigd worden in de Power Query Editor (als je kennis hebt van functies in Power Query Editor). Of je kunt een alternatieve/betere methode gebruiken; deze methode wordt toegelicht in het hoofdstuk 'Gegevens opschonen en transformeren met Power Query Editor'.
Figuur 11: dialoogvenster Combine Files
7 Klik op OK.
8 De bestanden worden samengevoegd tot één tabel en geladen in het gegevensmodel. Er wordt een extra kolom toegevoegd (Source.Name) met de naam van het bronbestand per record.
Figuur 12: Source.Name
Nieuwe gegevens toevoegen kan eenvoudig door het bestand toe te voegen aan de map Orders.
9 Verplaats het opgavebestand Orders_0623.xlsx naar de map Orders.
10 Klik in het lint Home op Refresh.
Figuur 13: refresh
11 Controleer of de gegevens toegevoegd zijn. Ga naar de weergave Data. Filter de kolom Source.Name op 0623
Figuur 14: orders 0623
2 Gegevens ophalen van een webpagina
Het is mogelijk om gegevens op te halen van om het even welke webpagina. In ons voorbeeld maken we verbinding met een Wikipedia-pagina: nl.wikipedia.org/wiki/ISO_3166-1. Op die pagina staan de ISO-landcodes.
Notitie
De ISO-landcodes kun je ook vinden als Excel-bestand in de opgavebestanden (ISO-landcodes.xlsx)
We werken verder in het bestand H3_Gegevensanalyse_Tex_Mex.pbix.
1 Klik in het startscherm op Get data of in het lint op Home > Get Data > Web.
2 Kopieer en plak de URL in het dialoogvenster From Web.
Figuur 15: dialoogvenster From Web
3 Klik op OK
4 In het linkerdeelvenster van de Navigator kun je alle tabellen aanvinken die op de webpagina te vinden zijn. Vink de tabel ISO-landcodes aan.
Figuur 16: ISO-landcodes
5 Klik op Load om de data naar het gegevensmodel te laden. Zodra de gegevens geladen zijn, kun je de tabel en velden zien in het deelvenster Data
6 Bewaar met CTRL+S.
2.1 Verbinding maken met een SQL-server
Microsoft SQL-server is een relationeel databasebeheerssysteem (RDBMS), ontwikkeld door Microsoft en veel gebruikt in het bedrijfsleven. SQL (uitgesproken als ‘S-Q-L’ of ‘Sequel’) is de afkorting van Structured Query Language. Het is wereldwijd de populairste (vraag)taal om relationele databases mee aan te sturen.
Notitie
Om verbinding te maken met een SQL-server moet je toegang hebben tot een SQL-server (van het bedrijf of zelf geïnstalleerd).
1 Klik in het lint op Home > SQL-server.
Figuur 17: gegevens ophalen van de SQL Server
2 Geef in het vak Server de naam van de SQL-server op waarmee je verbinding wilt maken. Je kunt ook een databasenaam opgeven (als die bekend is).
Figuur 18: dialoogvenster SQL Server database
3 Klik op OK
4 Je krijgt een overzicht van de beschikbare tabellen in de Navigator. Vink de tabellen aan die je wilt laden in het gegevensmodel.
5 Klik op OK in het bericht om verbinding te maken via een niet-versleutelde verbinding. (Misschien is de werkwijze in jouw organisatie anders.)
Notitie
Dit voorbeeld wordt niet verder uitgewerkt omdat je toegang moet hebben tot een SQL-server (van het bedrijf of zelf geïnstalleerd).
3 Conclusie
Met Power BI kun je gegevens ophalen vanuit heel wat verschillende gegevensbronnen. De werkwijze om de data op te halen is gelijkaardig voor alle gegevensbronnen.
4 Gegevens vernieuwen
Het type gegevensbron (onlineservice, database in de cloud, bestand op een lokale computer enzovoort) bepaalt hoe de gegevens worden vernieuwd.
In sommige gevallen hoef je helemaal niets te doen. Als je bijvoorbeeld rechtstreeks verbinding maakt met de gegevensbron in plaats van de gegevens te laden in Power BI Desktop, worden de gegevens automatisch vernieuwd.
Maar in andere gevallen (bijvoorbeeld een verbinding met een werkmap op een lokale computer of met webpagina’s), moet je handmatig vernieuwen:
Klik in het lint op Home > Refresh
Figuur 19: gegevens vernieuwen
De gegevens worden opnieuw geladen.
Notitie
Je kunt ook één gegevensbron vernieuwen. Klik in het deelvenster Fields met de rechtermuisknop op de tabel en kies voor de optie Refresh