Het principe ETL

ETL betekent Extract – Transform – Load: verzamel de data en zet ze in een gestructureerde format die in de BI-tool geladen kan worden.

Soms is het bronbestand al geldig gestructureerd, meestal moet je de data nog transformeren (veldnamen aanpassen, kolommen/rijen wissen, gegevenstype aanpassen, ...) en dat laatste doe je best ofwel in het bronbestand ofwel in Power Query vooraleer de tabel(len) in de Desktop geladen worden.

De basisgegevens die je wil analyseren moeten in een duidelijke tabelstructuur staan om bruikbaar te zijn voor Power BI.
Dat betekent:

Bovenaan staan de kolomtitels die in dit verband de veldnamen heten. Die namen komen in de rapporten terug als mogelijke waarden en labels.
De tabel bevat geen lege regels, niet onder de kolomtitels, niet ergens tussen de rijen waarden. Een lege cel kan wel, maar vermijd je beter indien mogelijk.
Vermijd teveel opmaak en zorg voor een basis tabelstructuur met rijen en kolommen, geen gesplitste of samengevoegde cellen.
In één kolom of veld staan altijd gegevens van hetzelfde type, bv. allemaal datums of allemaal getallen of allemaal namen.
- Zorg er bij tekst voor dat ze allemaal ofwel met een hoofdletter ofwel met een kleine letter beginnen, want dat beïnvloedt de sorteervolgorde.
Op één rij staan alle gegevens van één persoon of item; zo'n rij heet een record.
Staan de tabellen in een Excelwerkmap, zet ze dan elk op een eigen werkblad. Dat is overzichtelijker voor de gebruiker én voor Power BI.

DOE Normaliseer tabel Graan.xlsx in Excel.

We illustreren het probleem van een geldige tabelstructuur in Excel met Graan.xlsx. Download dit bestand en open in Excel.
Begin met de huidige tabel aan te vullen.

Verkopen: als het product "graan" is, vermenigvuldig je met de prijs van graan, anders zuivel. In E9: =ALS(C9="graan";D$3;D$4)*D9 en kopieer naar beneden.
Premie: bij een verkoop van meer dan 5 000 eenheden graan of zuivel krijgen de vertegenwoordigers 25 eurocent per stuk meerverkoop (d.w.z. boven de 5 000).
- In F9 komt: =ALS(D9>5000;(D9-5000)*0,25;0) en kopieer naar beneden.
Uitbetaald loon = nettoloon + premie.
Maak een zoektabel van de namen en nettolonen: selecteer F3:G5, klik in het naamvak linksboven (waar F3 staat), typ als naam lonen en druk Enter.
In G9 komt als formule: =VERT.ZOEKEN(B9;lonen;2;ONWAAR)+F9 en kopieer naar beneden.

Je krijgt verscheidene keren #N/B. In G10 bv. is dat omdat er geen naam staat in B10.
Conclusie: lege cellen kunnen voor fouten zorgen.
Oplossing: vermijd lege cellen. In casu: kopieer telkens de namen naar beneden.

Bij Peters blijft #N/B staan. Reden: de naam in de zoektabel bevat twee e's.
Conclusie: verschillende schrijfwijzen van benamingen zorgen voor fouten.
Oplossing: contacteer Pe(e)ters en controleer de correcte schrijfwijze. De correcte vorm is met 1 e.

Nu is alles in orde. Of niet? Janssens krijgt bv. in maart 3x een maandloon uitbetaald! Reden: de gegevens van Janssens in maart staan over 3 regels verspreid.
Conclusie: De gegevens van één item moeten op één regel staan.
Oplossing: maak een tabel waarbij per maand de gegevens van elke vertegenwoordiger op één regel samen staan.

Mogelijke uitwerking (gekopieerd waar mogelijk, dan opmaak gewist, veldnamen met terugloop):

Zo zie je dadelijk wie wat wanneer verkocht, gewoon door de tabelstructuur. Nog even de formules aanvullen (in het voorbeeld op rij 26):

Verkopen graan, in G26, wordt =C26*E26. Kopieer naar beneden en naar rechts.
Premie graan, in I26, wordt =ALS(C26>5000; (C26-5000)*0,25; 0). Kopieer naar beneden en naar rechts.
Uitbetaald loon, in L26, wordt =I26+J26+K26 en kopieer naar beneden. Nu krijgt iedere vertegenwoordiger het correcte loon uitbetaald.