Gegevens omzetten in Excel via Power Query

Voor eindgebruikers is Excel de enige en de beste tool om gegevens te verwerken. Vaak krijgt men lijsten aangeleverd die zelden een juiste layout hebben en waar nog allerlei handelingen op moeten uitgevoerd worden zoals

• Overbodige spaties verwijderen
• Alles omzetten in hoofdletters of kleine letters
• Kolommen toevoegen/verwijderen
• Kolommen splitsen
• …

Meestal maakt men gebruik van de standaardmogelijkheden van Excel, maar sinds Excel 2010 heeft Microsoft een reeds krachtige tools waarmee je de functionaliteit van Excel aanzienlijk kan uitbreiden. De belangrijkste tools hierbij zijn: Power Pivot, om gegevensmodellen te creëren die je vervolgens beter kan visualiseren met Power View (Dashboard) en Power Map (Geografische voorstellingen). Maar daarnaast is er nog een extra tool die eigenlijk heel snel kan aangeleerd worden en die enorm krachtig is: Power Query.

In deze post zijn de screenshots van Power Query voor Excel 2016, maar de functionaliteiten zijn evengoed beschikbaar in vorige versies van Excel. Heb je Excel 2016 (of Office 365), dan kan je onmiddellijk verder want standaard is Power Query geïntegreerd in Excel. Heb je nog een oude versie van Excel, dan moet je eerst de Add-In installeren: http://www.microsoft.com/en-us/download/details.aspx?id=39379 Hou er wel rekening mee dat Power Query continu door Microsoft uitgebreid wordt en dat de screenshots in deze post ondertussen dus verouderd kunnen zijn.

Ik ga in deze post gebruik maken van een (vereenvoudigd) probleem dat ik bij een klant zag (om confidentiële redenen kan ik niet met de echte gegevens werken en maak ik gebruik van een beperkte tabel).

De klant kreeg de gegevens aangeleverd zoals in onderstaande tabel. Ze bevat 2 kolommen:

• Persoon: de naam van de medewerker die één of meerdere cursussen gevolgd had
• Cursussen: een lijst van de cursussen die deze mederwerker gevolgd had.

 

Maar men was voornamelijk geïnteresseerd in:

• Hoeveel cursussen heeft elke persoon gevolgd?
• Hoe populair zijn de cursussen?
• Hoe populair is een bepaald programma? (Excel, Access, Word…)

Al deze gegevens kunnen afgeleid worden van deze tabel, maar omwille van de foutieve structuur kan je er eigenlijk niet veel mee doen. Mijn contactpersoon had ondertussen reeds gebruik gemaakt van de optie Text to Columns en was nu al enkele dagen VBA code aan het samenstellen om de structuur te wijzigen. Omdat men niet verder kon riep hij mijn hulp in. We hebben 5 minuten werk gehad, zonder 1 letter code te schrijven, dankzij Power Query.

Wat zijn de verschillende stappen die je dient uit te voeren?

 

Klik op de tabel en ga in het menu naar Data – From Table

Power Query wordt geopend en je ziet je tabel in het Power Query venster

Rechtsklik op de hoofding Cursussen en kies de optie Split Column – By Delimiter

Stel het scheidingsteken in en klik op OK. In dit geval is het een puntkomma. 

Per persoon worden de cursussen verdeeld over verschillende kolommen.

Rechtsklik op de hoofding Persoon en selecteer de optie Unpivot Other Columns

Je krijgt een mooie tabel met per lijn een persoon en 1 cursus

Verwijder kolom Attribute (Rechtsklik op de hoofding en selecteer de optie Remove)

Bij sommige waarden staat er vooraan een spatie. Verwijder deze spatie door op de hoofding Value te rechtsklikken en te kiezen voor Transform – Trim

Dit geeft het volgende resultaat:

Nu moeten we het programma nog extraheren en dat kunnen we terug op dezelfde manier doen: rechtsklik op de hoofding Value en selecteer opnieuw de optie Split Column – By Delimiter.

Aangezien het programma en het onderdeel telkens gescheiden worden door de combinatie SPATIE-KOPPELTEKEN-SPATIE selecteren we de optie Custom en geven deze combinatie in het tekstvak in.

Resultaat:

Hernoem de kolommen door op de hoofding te dubbelklikken en de gepaste naam in te geven.

Om vervolgens de gegevens te kunnen analyseren moeten we ze nog inladen in Excel. Klik in het menu Home op de knop Close & Load en je bent terug in Excel met de gegevens in de juiste layout.

De laatste stap bestaat er dan in om deze gegevens te analyseren via functies, grafieken of PivotTables.

Hoewel bovenstaande stappen heel eenvoudig lijken zit er toch een addertje onder het gras.
Stel dat we 1 record toevoegen aan de tabel; in dit geval voegen we An De Wilde toe en zij volgde 6 cursussen.

Als we de gegevens vernieuwen krijgen we dit resultaat:

De gegevens van de nieuwe persoon zijn toegevoegd aan de lijst, maar we zien slechts 5 cursussen!  Waar is de opleiding Excel – VBA naartoe?

Om dit te begrijpen moeten we kijken hoe Power Query onze gegevens gesplitst heeft. Daar zien we deze formule:

(Als je de formulebalk niet ziet klik je op View – Formula Bar)

Hier zie je duidelijk dat de gegevens opgesplitst worden in 5 cursussen. Waarom?  Omdat dat het hoogst aantal cursussen was op het moment dat we de query creëerden.

Een eenvoudige oplossing zou zijn om het aantal kolommen op een heel groot getal te zetten zodat er altijd plaats genoeg is (bvb: we stellen het in op 1000 kolommen)

Dit werkt prima, maar je zit natuurlijk wel met 1000 kolommen waarbij er slechts in enkele kolommen waarden zullen staan.  In het eindresultaat worden enkel die met de waarden overgehouden waardoor je een juist resultaat krijgt:

Maar dit is natuurlijk niet de meest efficiënte manier.  Het probleem is dat men denkt dat men in Power Query over exact dezelfde functionaliteiten als Excel beschikt en het dus ook moet oplossen met functionaliteiten die in Excel bestaan.  Maar Power Query heeft een pak extra meer mogelijkheden.

Tot nu toe hebben we de gegevens gesplitst en vervolgens via de optie Unpivot other columns onder elkaar gezet.  Maar dit kun je eigenlijk ook in 1 stap doen; bij het splitsen dienen we te melden dat we de gegevens niet in verschillende kolommen splitsen, maar naar verschillende rijen.

Het resultaat ziet er zo uit: