Power Query - De Basis

Voor veel Excel gebruikers klinkt Power Query als iets waarmee je alleen moet beginnen als je Excel volledig beheerst.

Niets is minder waar. De wereld is ook in dit geval eenvoudiger dan je denkt!

Power Query is een tool in Excel waarmee je op simpele wijze (een groot aantal) handelingen om gegevens te importeren én te vernieuwen automatiseert en beheert! De gegevens die je wilt importeren kunnen overal vandaan komen. Het kan een Excel bestand zijn, een .csv bestand, een tekst bestand, een .xml bestand of zelfs een .pdf bestand. Je kunt ook de nieuwe JSON bestanden en gegevens via Microsoft Azure importeren.

In essentie bestaat Power Query bestaat uit drie stappen. Je haalt gegevens op, je werkt gegevens bij en je leest gegevens in. Naast deze functionaliteit biedt Power Query nog iets dat het heel erg flexibel maakt. Alle stappen die je hebt geïmplementeerd worden in een lijst bijgehouden en kun je op elk moment aanpassen.

Het werken met Power Query lijkt misschien lastig, maar is het niet. Je moet het alleen, zoals met alles, gewoon een paar keer hebben gedaan!

Power Query is een tool dat je kunt zien als een soort editor. De Power Query editor vind je op het tabblad Gegevens van het Lint.

Kies, van de groep Gegevens ophalen en transformeren, de optie Gegevens ophalen > Power Query editor starten. Je kunt nu beginnen met het ophalen, bewerken en inlezen van gegevens!

De Power Query editor is vrij uitgebreid. Een dialoog of venster kun je het niet meer noemen, want het bevat maar liefst vijf tabbladen een hele hoop ingewikkeld lijkende opties.

Logisch opgebouwd is de Power Query editor wat mij betreft niet. Normaal gesproken begin je met iets nieuws waarvoor je dan een nieuwe bron zou moeten selecteren. Aangezien we in de westerse wereld van links naar rechts lezen, zou je verwachten dat de keuze voor het selecteren van die nieuwe bron aan de linkerkant van het tabblad Start zou staan. Dit is dus niet het geval. De keuze is er wel degelijk, maar bevindt zich helemaal aan de rechterkant, of het eind van het tabblad Start. De optie Sluiten en laden, waarmee je alle handelingen beëindigt, bevindt zich dan weer links, of helemaal aan het begin van het tabblad Start. Wat mij betreft compleet onlogisch!

Ik kan mij voorstellen dat je de Power Query editor op het eerste gezicht nogal overweldigend vindt. Laat je hier echter niet door uit het veld slaan! Probeer het vooral allemaal uit. Klik op een tabblad. Beweeg de muis over een pictogram. Bekijk de tooltip. Wie weet wat je daardoor meteen al leert!

De eerste stap bij het werken met de Power Query editor betreft het ophalen van gegevens. In termen van Power Query zul je een nieuwe bron moeten kiezen. Dit werkt als volgt:

• • Klik, op het tabblad Start, in de groep Nieuwe query op Nieuwe bron
  • Klik vervolgens bij Bestand (of Database of Andere bronnen) op het type bestand dat je wilt inlezen

In dit voorbeeld komen de gegevens uit een tekstbestand.

Nadat je hebt geklikt op Tekst/csv verschijnt de dialoog Gegevens importeren. Omdat je de keuze hebt gemaakt om een tekstbestand te importeren worden, in de map die je kiest, alleen de tekstbestanden weergegeven.

Selecteer het bestand dat je wilt gebruiken en klik op de knop Importeren. Je kunt het bestand ook importeren door er meteen op te dubbelklikken!

Het geselecteerde tekstbestand wordt nu als voorbeeld in een venster geopend. De ervaren Excel gebruiker herkent hier ongetwijfeld een andere opzet van de wizard tekst importen in.

Net als bij de wizard tekst importeren kun je ook hier aangeven welk scheidingsteken er moet worden gebruikt om de gegevens op de juiste wijze te kunnen inlezen.

Na het aanklikken van het scheidingsteken wordt het voorbeeld overeenkomstig de gemaakte keuze bijgewerkt. Figuur 6 toont aan dat de gemaakte keuze correct is aangezien alle velden netjes van elkaar gescheiden zijn.

Ook het gegevenstype van een veld (kolom) kun je, net als bij de wizard tekst importeren, bepalen. In de Power Query variant kun je dat baseren op (een deel van ) de gegevens. Dit deel kan de eerste 200 rijen, of records, betreffen, maar ook de volledige set met gegevens.

Standaard wordt de optie Gebaseerd op de eerste 200 rijen getoond, aangezien die in veruit de meeste gevallen een afspiegeling is van de volledige set. Een bijkomend voordeel van deze keuze is dat Excel minder tijd nodig heeft om de set met gegevens te analyseren.

In feite heb je een andere variant van de wizard tekst importen doorlopen. Met een verschil. In deze opzet kun je niet voor ieder veld bepalen wat het gegevenstype is. Geen paniek, want dat kun je op een later tijdstip altijd nog doen!

Indien je tevreden bent met het getoonde voorbeeld, kun je het ophalen van de gegevens in de Power Query editor bevestigen door op de knop OK te klikken.

De gevulde Power Query editor toont meteen de veelzijdigheid van dit tool. Als het de eerste keer is dat je het gebruikt, snap ik dat je je afvraagt waar je in hemelsnaam moet beginnen. Wat moet je doen? Het antwoord zal wellicht verrassend zijn. De editor sluiten!

De beste keuze om Power Query te leren is om de gegevens eerst op een werkblad in Excel in te lezen. Hiervoor heb je twee opties. Dit zijn:

• • Sluiten en laden
  • Sluiten en laden naar …

Als je kiest voor de optie Sluiten en laden, worden de gegevens in de vorm van een tabel op een werkblad ingelezen. Dit is de standaard instelling en kun je niet aanpassen.

Kies je voor de optie Sluiten en laden naar… , dan kun je aangeven in welke vorm en waar je de gegevens wilt inlezen.

Door te kiezen voor Sluiten en laden naar … wordt de dialoog Gegevens importeren getoond. De standaard keuzes, die worden gebruikt als je Sluiten en laden zou hebben gekozen, worden dan geselecteerd.

De dialoog Gegevens importeren is naar eigen inzicht aan te passen. Ik kies er bijvoorbeeld altijd voor om wat ruimte over te laten aan de linkerkant en de bovenkant van mijn werkblad. Vandaar dat ik bij Bestaand werkblad cel B10 opgeef.

Het resultaat van de keuze in Figuur 12 is het werkblad dat hieronder is afgebeeld.

Voor nu is dit genoeg. De gegevens zijn ingelezen en het is zaak om niet te veel te doen zonder dat de acties zijn vastgelegd. Met andere woorden, sla dit werkboek op en pas dingen aan die betrekking hebben op de opmaak. Met een beetje handigheid maak je een werkboek zoals afgebeeld in onderstaand voorbeeld.

Figuur 14 maakt duidelijk op welke wijze de gegevens zijn ingelezen. Als je kijkt naar het Lint zie je dat er twee extra tabbladen zichtbaar zijn. Het tabblad Tabelontwerp, dat aan geeft dat er een tabel actief is en het tabblad Query, dat aangeeft dat er een query actief is.

• • Ik sla deze stap nooit over. Natuurlijk kun je ook verder werken met de Power Query editor, maar het is naar mijn mening beter om regelmatig een werkboek op te slaan. Het is zonde als wijzigingen om wat voor redenen dan ook verloren gaan omdat je het werkboek niet tijdig hebt opgeslagen!

Nu de gegevens uit de Power Query editor zijn in gelezen kun je de gegevens (eventueel) gaan bewerken. Dit doe je natuurlijk ook in de Power Query editor. Uitgaande van het voorbeeld in Figuur 14, doe je het volgende:

• • Selecteer een cel (B11) in de tabel waardoor de tabel en de query actief worden
  • Klik, op het Lint, op het tabblad Query
  • Kies, van de groep Bewerken, de optie Bewerken

Na deze handelingen verschijnt de Power Query editor, waarin het tabblad Start actief is.

Een van de fraaiste toepassingen die de Power Query editor biedt is de mogelijkheid om de aanpassingen die aan de query zijn gedaan te wijzigen of terug te draaien. Dit vind je terug in het venster Queryinstellingen onder de kop TOEGEPASTE STAPPEN. In dat venster zijn alle  aanpassingen die je hebt uitgevoerd in chronologische volgorde opgenomen. In Figuur 15 zie je dit venster aan de rechterkant van de Power Query editor.

• • Het venster Queryinstellingen is standaard zichtbaar, maar kun je verbergen door op het tabblad Weergeven in de groep Indeling op het pictogram Queryinstellingen te klikken.

Om een aanpassing te wijzigen of te verwijderen moet je die selecteren door er op te klikken.

Als de aanpassing gewijzigd kan worden staat er een sterretje achter de toegepaste stap. Daar kun je op klikken zodat de dialoog verschijnt die die toegepaste stap betreft. In het getoonde voorbeeld in Figuur 15 kun je de eerste en de tweede stap dus aanpassen.

Als de aanpassing verwijderd kan worden wordt een kruisje voor de toegepaste stap zichtbaar als je die stap selecteert of er met je muis overheen beweegt. In het getoonde voorbeeld in Figuur 15 kun je de derde stap dus verwijderen.

In onderstaand voorbeeld kan het bronbestand niet worden ingelezen omdat de locatie waar dat bestand zou moeten staan niet kan worden gevonden. In dat geval krijg je een duidelijke, maar helaas wel Engelstalige, mededeling.

Om het probleem van een ongeldige gegevensbron te corrigeren heb je twee mogelijkheden:

• • Klik, in het gele vlak, op de knop Instellingen bewerken
  • Klik, bij TOEGESPASTE STAPPEN, op het sterretje naast Bron

In beide gevallen verschijnt de volgende dialoog.

Figuur 17 toont het bestandspad of de bestandsnaam die niet juist is. Klik op Bladeren… om het juiste pad of bestand te kiezen en er voor te zorgen dat de gegevens wel ingelezen kunnen worden. Klik daarna op OK om de gegevens daadwerkelijk in te lezen.

Nu de gegevens correct zijn ingelezen kun je je gaan verdiepen in de volgende stappen. Die hebben allemaal betrekking op de hoeveelheid en opmaak van de gegevens. Met een beetje geluk ziet de inhoud van de Power Query editor er uit zoals afgebeeld in Figuur 18.

Het feit dat alle pictogrammen een kleur hebben betekent dat je ze allemaal kunt gebruiken.

De schuifbalk (regel 16) in Figuur 18 maakt duidelijk dat er veel meer dan de zichtbare vier velden of kolommen zijn ingelezen. Heb je die allemaal nodig? Zo niet, verwijder ze meteen.

Dit werkt als volgt:

• • Klik, op het Lint, op het tabblad Start
  • Kies, van de groep Kolommen beheren, de optie Kolommen kiezen > Kolommen kiezen

De dialoog Kolommen kiezen verschijnt.

Door simpelweg een vinkje bij een veld te plaatsen (of weg te halen) geef je aan of je dat veld wilt inlezen (of niet). Ik raad je aan om dat meteen te doen. Wat je niet nodig hebt moet je ook niet inlezen, maar maakt de gegevensset wel compacter en overzichtelijker.

• • Mocht je een veld niet hebben aangevinkt, of als je niet zeker bent of je een veld nodig hebt is er geen man over boord. Je kunt in een later stadium de dialoog weer oproepen en de velden alsnog kiezen of verwijderen!

Het grote voordeel van de Power Query editor wordt duidelijk in Figuur 21. De velden die je hebt verwijderd zijn opgenomen in de lijst met toegepaste stappen onder de naam Andere kolommen verwijderd!

Een volgende stap heeft betrekking op de opmaak van de velden. In Excel termen spreek je dan over algemeen, getal, tekst, datum, tijd of percentage.

Van alle velden die Power Query heeft ingelezen, zijn er twee waarin zich een datum bevindt. Dit zijn de velden Date Modified en Date Added. Als je de veldnaam van één van die velden aanklikt zie je bij Transformeren dat het Gegevenstype Datum/tijd is.

Een tijdstip is bij dit veld niet noodzakelijk. Een datum volstaat. Je kunt dit wijzigen door op het tabblad Start in de groep Transformeren op de knop Gegevenstype te klikken en te kiezen voor Datum.

Dezelfde actie zou je kunnen toepassen op het veld Date Added. Ook daar voegt het tijdstip niet veel toe.

Door te kiezen voor datum worden alle gegevens in de velden Date Modified en Date Added omgezet naar het formaat DD/MM/YYYY.

Een andere mogelijkheid om aanpassingen aan velden te doen is door een veld te selecteren en de rechtermuisknop te gebruiken. Hierdoor wordt een snelkoppelingsmenu zichtbaar met alle mogelijke aanpassingen die je aan een veld kunt doen.

De laatste stap betreft het inlezen van de gegevens. Als alle aanpassingen in de Power Query editor zijn gedaan kun je de gegevens in tabelvorm in Excel opnemen. Dit werkt als volgt:

• • Klik, op het tabblad Start
  • Kies, van de groep Sluiten, de optie Sluiten en laden

Wanneer je tevreden bent met de opmaak van de ingelezen gegevens hoef je de Power Query editor voor dit bestand niet meer te gebruiken. Als er in de loop der tijd nieuwe gegevens aan het bronbestand worden toegevoegd kun je die met één druk op de knop in de tabel inlezen.

Dit werkt als volgt:

• • Selecteer een cel in de tabel
  • Klik op het tabblad Tabelontwerp
  • Klik, in de groep Externe tabelgegevens op Vernieuwen > Vernieuwen

Door deze handeling wordt de hele tabel bijgewerkt. Dit betekent dat nieuwe records worden toegevoegd, records die niet meer in het bronbestand staan worden verwijderd en de inhoud van cellen, indien noodzakelijk, wordt bijgewerkt.

Daar hoef je niks meer aan te doen!

Eén ding is in dit proces echter de moeite waard om te controleren. Net als in een draaitabel is er een optie om de opmaak van cellen af te laten hangen van de gegevens die je inleest. In de meeste gevallen leidt dat tot ergernis, omdat je daarmee de aangebrachte opmaak van de tabel om zeep helpt. Dit heeft vooral betrekking op de rijhoogte en kolombreedte. Iedere keer dat je de gegevens vernieuwt, worden de rijhoogte en de kolombreedte aangepast aan wat de inhoud van het bronbestand mogelijk maakt.

Dit kun je ongedaan maken op de volgende wijze:

• • Selecteer een cel in de tabel
  • Klik op het tabblad Tabelontwerp
  • Klik, in de groep Externe tabelgegevens op Eigenschappen, waardoor de dialoog Externe gegevenseigenschappen verschijnt

Om er voor te zorgen dat je de opmaak van de tabel behoudt als je nieuwe gegevens inleest, zet je een vinkje bij de optie Celopmaak behouden.

Officieel wordt Power Query gezien als een tool dat een grafische interface gebruikt voor gegevenstransformatie en gegevensvoorbereiding. In de praktijk komt dat op het volgende neer. Met Power Query kun je gegevens achtereenvolgens:

• • Ophalen
  • Bewerken
  • Inlezen

Deze drie processen kun je ook met standaard Excel functionaliteit uitvoeren. Dat werkt voor velen prima. Het grote voordeel dat Power Query heeft ten opzichte van deze traditionele werkwijze heeft betrekking op structuur, nauwkeurigheid en volledigheid.

De bewerkingen die Power Query doet hebben altijd betrekking op de hele set met gegevens. Er worden nooit records of velden vergeten of overgeslagen. Het feit dat alle handelingen te allen tijde simpel kunnen worden bewerkt maakt Power Query heel flexibel. Het feit dat alle handelingen zichtbaar in een lijst worden weergegeven maakt het ook overzichtelijk. Je kunt zien wat je gedaan hebt!

Kortom; Power Query is een tool dat iedere zichzelf respecterende Excel gebruiker moet kunnen gebruiken.

Wil je meer weten over Power Query dan is de site van Microsoft een aanrader!

Klik daarvoor op deze link.