In het vierde artikel in deze reeks gaat cm: aan de slag met enkele simpele transformaties. Ze hebben niet veel om het lijf, maar demonstreren dat wanneer u meerdere van dit soort taken moet uitvoeren, dat veel sneller gaat via de Power Query-editor dan los in Excel zelf. Ook zorgt dit ervoor dat u data alvast kunt klaarmaken voor acties waar we in de volgende artikelen op terugkomen.

Het doel van Power Query is om van een grote datatabel te gaan naar een tabel die heel snel antwoord geeft op een vraag over de data. In Excel is het mogelijk om met bijvoorbeeld filters, draaigrafieken en dashboards zulke antwoorden te verkrijgen, maar een query zorgt ervoor dat specifieke antwoorden worden gegeven, de brondata daarvan altijd up-to-date is en met de tools in Power Query genereert u deze vele malen sneller dan het geval zou zijn in de klassieke Excel. In het vorige deel zijn we al voorzichtig begonnen met enkele transformaties. In dit deel bekijken enkele simpele ingebouwde automatiseringen die u veel tijd kunnen besparen.

Voor deze tutorial gaan we aan de slag met de editor in Power Query (zie dit artikel voor meer informatie over de editor). Hierin gaat u naar het tabblad Transformeren (Transform) in het lint bovenin om de transformaties uit onderstaande voorbeelden toe te passen.

Data overzichtelijk maken

We hebben hier een fictieve tabel met adresgegevens, zoals ze helaas vaak worden aangeleverd. Deze spreadsheet is erg onhandig om te gebruiken voor datatransformaties omdat gegevens niet uniform zijn ingevoerd en de indeling zich niet leent voor snelle zoekvragen. Maar hier is Power Query juist voor, om gegevens eenvoudig klaar te zetten voor analyse. Met enkele simpele stappen maken we een overzichtelijke zoektabel van deze gegevens:

Eerste opschoonactie

Als we deze informatie laten binnenlopen in de Power Query-editor (selectie maken en dan Gegevens > Gegevens ophalen en transformeren > Van tabel/bereik (Data > Transform > From Table/Range)) zien we al een hoop dingen die niet lekker binnenlopen. Alle lege rijen krijgen het label null:

De kolomkoppen zijn eenvoudig aan te passen met de knop De eerste rij als veldnamen gebruiken. Alle null-velden weghalen en informatie doorvoeren is een heel simpele klus in Power Query. Rijen 3, 6, 9, 12 en 15 zijn de witregels die geen informatie bevatten. Deze zijn meteen weg te gooien door bovenin te kiezen voor Rijen verwijderen > Lege rijen verwijderen (Delete Rows > Delete Empty Rows).

Waarden vervangen

Kolom E is een datumkolom, maar is in de spreadsheet niet gedefinieerd en de worden verschillende datumnotaties gebruikt. Bij het importeren van gegevens in de Power Query-editor, herkent de software dat het hier om data gaat en wordt automatisch een datumtype aan deze kolom toegevoegd. Dat is handig, want niet alleen kunnen we de notatie meteen gelijktrekken, ook wordt het hierdoor heel makkelijk om te filteren. Ook hier gaan we binnenkort uitgebreider op in.

Kolom D, het klantnummer, heeft ‘Willekeurig type’ toegewezen te kregen. Ook dat geeft ons op den duur datamanipulatie-uitdagingen die we nu even makkelijk de pas kunnen afsnijden, dus het is makkelijker om hier meteen een getal van te maken. Kies voor Start > Gegevenstype (Home > Data Type) en kies uit het menu uit voor Geheel getal (Whole number).

Gegevens splitsen

Het is heel eenvoudig om informatie op te delen van de gegevens die in één veld worden weergegeven. Denk aan voor- en achternaam of datum/tijd. In dit geval willen we uit de tabel de datum behouden, maar het tijdstip kan weg, daar de timestamp in dit voorbeeld altijd middernacht is. Selecteer de kolom Date en kies uit de balk bovenin Kolom splitsen (Split Column). Hier ontvouwen zich een aantal opties. In de meeste gevallen kiest u voor het splitsen op een scheidingsteken (delimiter), zoals u dat vaak ziet in een .csv-bestand.

In dit geval is de datumnotatie (DD-MM-JJJJ) gescheiden van de tijd (00:00:00) door een spatie. Daar zit een optie voor in het uitvouwmenu. Selecteer de spatie. Vervolgens kunt u kiezen tussen diverse positie-opties, bijvoorbeeld om te splitsen op alle exemplaren (wat bijvoorbeeld handig is als u van een timestamp als 13:00:42 de uren, minuten en seconden wilt lostrekken naar hun eigen kolommen). We kiezen hier ook voor de optie Het meest rechtse scheidingsteken (Right-most delimiter), aangezien de laatste spatie zich voor de timestamp bevindt. Het kan zijn dat er voor de invoer spaties zijn gezet, waardoor u een of meerdere kolom(men) zou creëren met null-waarden. Dat willen we niet dus ‘meest rechts’ is in dit geval de veiligste optie.

Dit zorgt ervoor dat Power Query de informatie weergeeft als Datum actief.1 met de datum en Datum actief.2 met de tijd. Datum actief.2 kunnen we verwijderen (klik op de kolomknop met de rechtermuisknop en kies Verwijderen (Delete)) en Datum actief.1 hernoemen naar Datum actief (klik op de kolomknop met de rechtermuisknop en kies Naam wijzigen(Rename)).

Adres, postcode en woonplaats splitsen

In deze tabel staan alle adresgegevens in één kolom. Misschien wilt u later een zoekvraag creëren op stad en dan is dit geen handige verwerking. Ook hier gebruiken we de functie hierboven besproken, maar kiezen we voor het scheidingsteken komma om een kolom adres, kolom met postcode en kolom met plaats te maken. Selecteer de kolom, kies voor Start > Kolom splitsen > Op scheidingsteken (Home > Split Column > By Delimiter) en kies nu voor de optie Elk exemplaar van het scheidingsteken (Each occurrence of the delimiter . Dat levert Adres.1, Adres.2 en Adres.3 op. Hernoem deze en uw data ziet er als volgt uit:

Tekstvelden gelijktrekken

De nieuwe kolom ‘Vestigingsplaats’ is natuurlijk ook niet netjes met al die verschillende notatiewijzigingen. Tekstvelden zijn eenvoudig uniform te maken en bijvoorbeeld volledig in onderkast weer te geven. In dit geval willen we netjes dat elke stad begint met een hoofdletter, gevolgd door kleine letters. Dat doet door de kolom te selecteren en te kiezen voor Transformeren > Indeling > Elk Woord Met Een Hoofdletter (Transform > Format > Capitalize Each Word). Dat maakt de kolom uniform:

Hierna: Filteren

In deel 5 van ‘Power Query voor beginners’ staan we stil bij filteren van informatie om zoekvragen te verfijnen. Hiervoor hebben we nu alvast enkele acties uitgevoerd die na deel 5 een stuk logischer zullen zijn. Ook kijkt het volgende artikel naar het venster Toegepaste stappen (Applied Steps) zoals we na deze sessie het volgende overzicht hebben van bovenstaande acties:

Volg CMWeb op LinkedIn!