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=39379Hou 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.

Wens je meer te weten over Power Query, kijk dan even naar deze opleiding bij Xylos.  Of bekijk het overzicht van alle Excel opleidingen.

 

Deel dit blogbericht

Also interesting for you

Laat een antwoord achter

Uw e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd.

Breng jouw kennis en skills naar een hoger niveau

Schrijf nu in voor onze nieuwsbrief en krijg maandelijks:

  • Exclusieve tips & tricks
  • Informatie over onze opleidingen
  • Trends in opleidingen
  • Uitnodigingen voor studiedagen en events

Meest gekozen opleidingen