Transformaties in Power Query

Een gegevenslijst moet correct gestructureerd zijn om bruikbaar te zijn voor verdere analyse. In de Power BI Desktop kan je data importeren van vele bestandsformaten, zolang er maar ergens een tabelstructuur in zit.

Wel moet je de structuur dus in orde brengen en bovendien de "ruis" (titels, tekst buiten de gegevenstabel, afbeeldingen, ...) verwijderen vooraleer je een datastructuur in de Desktop kan inladen. Daarvoor dient Power Query.

DOE Download de multitabellenversie van case Outdoor, transformeer ze in de Query en importeer ze in Power BI Desktop.

Om het gebruik van Power Query te demonstreren, gebruiken we een nieuwe versie van case Outdoor.

Download Outdoor2.xlsx en sla op in een makkelijk te onthouden map.
Als je wil, kan je het bestand even bekijken in Excel, maar sluit Excel daarna terug.
Start Power BI Desktop en begin een Leeg Rapport.
Klik op de groene knop, Gegevens Uit Excel Importeren, navigeer naar de map waar je het bestand opsloeg en dubbelklik erop.
In de Navigator zet je vinkjes aan de 7 tabellen: Categorie, Factuurinfo (= factuurhoofding, met algemene info), Klanten, Leveranciers, Personeel, Producten, Verkopen (= producten per factuur).
Klik onderaan op Gegevens Transformeren en je komt in een apart venster terecht, de Power Query Editor.

Zolang je in de Power Query Editor werkt, zal je 'query's' aanpassen en dat worden je 'tabellen' na het opladen in de Desktop.

Veldnamen en gegevenstypes controleren

Wat je in elke query zeker moet controleren zijn de veldnamen en de gegevenstypes.

Klik op '123' langs een veldnaam en de lijst met mogelijke datatypes klapt open:

datatype	betekenis
decimaal getal	decimaal getal dat bestaat uit maximum 15 cijfers (= van geheel getal van 15 cijfers tot getal met 14 decimalen)
vast decimaal getal	getal met 4 decimalen en maximum 15 cijfers links van het decimale teken
geheel getal	geheel getal bestaande uit maximum 19 cijfers
percentage	decimale waarde die getoond wordt als percentage (bv. 0,25 = 25%)
datum/tijd	serieel getal dat een datum en tijd tussen het jaar 1900 en 9999 kan bevatten. Het gehele deel is de datum, het decimale deel de tijd
datum	enkel datum, zonder tijd
tijd	enkel tijd, geen datum
datum/tijd/tijdzone	datum met expliciete toevoeging van tijdzone
duur	een tijdsduur, opgeslagen als een decimaal getal
tekst	verzameling Unicode karakters, d.w.z. alle mogelijke letters, cijfers en symbolen die in een lettertype voorkomen
waar/onwaar	de waarde is ofwel waar, true (= 0) ofwel onwaar, false (= -1)
binair	om een niet-tekstbestand (bv foto) in de database op te nemen. Beter niet gebruiken, kan fouten geven
op basis van landinstellingen-id	om problemen met het laden van een datum of decimaal getal op te lossen. Bv. in België is het decimale teken een komma, internationaal een punt.

Bekijk query Verkopen: de veldnamen zijn factuur-id, product, hoeveelheid en links ervan staat het datatype 123, geheel getal.
Verkopen bevat erg veel rijen. Wat als er nu eens een lege tussenzit? Klik op het driehoekje aan knop Rijen Verwijderen en kies optie Lege Rijen Verwijderen, dat kan nooit kwaad.
Klik op query Categorie: in plaats van de eigenlijke veldnamen staat er 'Column1' en 'Column2' en de veldnamen staan eronder. Klik op de knop De Eerste Rij Als Veldnamen Gebruiken om dit te corrigeren.
Bekijk dan query Personeel: boven de 1e kolom staat als type 'ABC123' d.w.z. de kolom bevat waarden van verschillende datatypes. Probleem: in Excel stond titel "Personeel" boven de tabel. Ook hier staan de eigenlijke veldnamen op de rij eronder, dus: De Eerste Rij Als Veldnamen Gebruiken.
Controleer de gegevenstypes: hier heb je behalve datatype Tekst en Geheel Getal ook 2x Datum en een kolom Waar/Onwaar.
Ook bij Factuurinfo staat er een datum tussen. Bij Producten staan prijzen, dat zijn decimale getallen.

De inhoud aanpassen

Controleer query Leveranciers: veldnamen en datatypes zijn in orde. Kolom Fax hebben we niet nodig. Ofwel rechtsklik je op de veldnaam en kies je Verwijderen ofwel klik je op het lint op Kolommen Kiezen en vink je kolom Fax uit. Als je achteraf de data vernieuwt, wordt in het eerste geval de kolom opnieuw verwijderd na het laden, in het tweede geval wordt ze gewoon niet meer mee geïmporteerd.
Bij Factuurinfo zijn er lege kolommen mee geïmporteerd: de inhoud is null, computertaal voor "leeg". Kolommen Kiezen en die niet meer inladen.
Dan nog query Klanten, hier zitten lege velden (null) in de kolom Voornaam. Tja, een bedrijf heeft nu eenmaal geen 'voornaam', dus kunnen we deze lege velden niet vermijden.
De gemeenten staan volledig in hoofdletters, ik wil de naam in kleine letters en beginnend met een hoofdletter. Rechtsklik op veldnaam Gemeente en kies Transformeren – Elk Woord Met Een Hoofdletter.
Wat als er nu eens spaties teveel voor of na de familienamen getypt zijn? Dat zie je niet. Uit veiligheid aanpassen maar: rechtsklik op veldnaam Naam en kies Transformeren – Omloopspaties Wissen.
Veld Land: soms staat er een punt na BE, soms niet. Aanpassen: rechtsklik op veldnaam Land en kies Waarden Vervangen. Typ een punt in het zoekvak en niets in het vervangvak. OK.
Verwijder kolom Fax via Kolommen Kiezen.

Een kolom uit een andere tabel toevoegen

In query Categorie staan enkel de categoriecode en de omschrijving. We voegen die omschrijving toe aan de productentabel, dan hebben we query Categorie niet meer apart nodig.

Activeer query Categorie. De inhoud onder veldnaam Code bestaat uit 3 letters.
Activeer query Producten. De 3 letters van de categoriecodes staan hier onder veldnaam Categorie.
Klik op het lint bij Start aan de rechterkant op het driehoekje aan Query's Samenvoegen.
- Query's Samenvoegen zal de actieve tabel, i.c. Producten, aanpassen.
- Query's Samenvoegen Als Nieuw laat de actieve tabel ongemoeid en maakt een nieuwe query met de aanpassing.
Kies Query's Samenvoegen.
De tweede tabel is Categorie, de tabel waaruit we een kolom willen overnemen.
De relatie tussen beide tabellen, de overeenkomende gegevens, staan bij Producten in kolom Categorie en bij tabel Categorie in kolom Code. Klik op die veldnamen.
Onderaan staat nu een groen vinkje dat de overeenkomst in orde is. OK.
Aan query Producten is nu een kolom Categorie.1 toegevoegd. Klik op het symbooltje rechts van de veldnaam:

Klik het vinkje aan Alle Kolommen Selecteren uit, we willen enkel kolom Omschrijving. De oorspronkelijke kolomnaam is niet nodig. OK.
Dubbelklik op veldnaam Omschrijving en vervang door Categorienaam.
De originele query Categorie moet nu niet meer ingeladen worden: rechtsklik op querynaam Categorie en klik het vinkje aan Laden Inschakelen uit. De querynaam wordt nu cursief getoond.
Alles in orde? Klik op de knop Sluiten En Toepassen.

Even geduld... Als alle meldingen verdwenen zijn, activeer je de tabelweergave: rechts kan je een tabelnaam aanklikken en dan zie je links de gegevens.
Sla op. De volgende stap is het datamodel.

Opmerking: wil je later terug van de Desktop naar de Query Editor?
Kies in rapportweergave op het lint bij Start – Gegevens Transformeren.