Power Query: de onmisbare tool in Excel

Digitale vaardigheden Excel IT & Projectmanagement

Excel blijft voor eindgebruikers nog steeds de enige en de beste tool om gegevens te verwerken. Alleen kan men vaak niet altijd onmiddellijk met de aangeleverde lijsten aan de slag. 

Vaak moet er nog aan de layout gesleuteld worden, denk aan:

  • Het weghalen van overbodige spaties
  • Alles omzetten in hoofdletters of kleine letters
  • Kolommen toevoegen/verwijderen
  • Kolommen splitsen

Meestal maakt men gebruik van de standaardmogelijkheden van Excel, maar sinds Excel 2013 heeft Microsoft een reeks krachtige tools waarmee je de functionaliteit van Excel aanzienlijk kan uitbreiden. De belangrijkste tools zijn: Power Pivot, om gegevensmodellen te creëren. 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 Excel 2013, dan moet je eerst de Add-In installeren. 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.

 

Structuur in een handomdraai

Ik ga in deze post gebruik maken van een (vereenvoudigd) probleem dat ik bij een klant zag. 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 medewerker gevolgd had

structuur in een handomdraai

 

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 hij 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

data from table

 

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

power query venster

 

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

split column

 

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

split column by delimiter

 

Per persoon worden de cursussen verdeeld over verschillende kolommen.

gesplitste kolom

 

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

unpivot

 

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

 

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

trim

 

Dit geeft het volgende resultaat:

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.

split by delimiter

 

Resultaat:

resultaat

 

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

hernoemen

 

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.

inladen

 

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

pivot tablesBron: www.xylos.com

Relevante opleidingen

Excel Advanced: Lijsten en Tabellen

Bekijk deze opleiding Bekijk de opleiding "Excel Advanced: Lijsten en Tabellen"

Excel Advanced: Data Visualiseren

Bekijk deze opleiding Bekijk de opleiding "Excel Advanced: Data Visualiseren"