Hoe Power Query in Excel te gebruiken

Microsoft Excel biedt verschillende hulpmiddelen en functies voor het manipuleren van gegevens, en Power Query is een van de beste. Met deze tool voor bedrijfsanalyse kunt u gegevens uit verschillende bronnen importeren en indien nodig eenvoudig transformeren en manipuleren in Excel. Kortom, het elimineert repetitieve taken en kan helpen de inspanning te verminderen en tijd te besparen.

Een groot voordeel van Power Query is dat je geen codeerexpertise of kennis nodig hebt om het te gebruiken. Laten we eens kijken hoe u het kunt gebruiken om gegevens in Microsoft Excel te manipuleren.

Toegang tot Power Query

Power Query is beschikbaar in alle versies van Microsoft Excel, te beginnen met Excel 2010. Vanaf Excel 2016 is het rechtstreeks in de applicatie ingebakken.

In Excel 2016 en later

• Start een nieuw Excel-werkblad en klik op het tabblad ‘Gegevens’ in de menubalk.

• Vanuit de opties op het tabblad ‘Gegevens’ klikt u op de optie ‘Gegevens ophalen’ linksboven onder de menubalk.

• Deze bevat alle Power Query-tools en opties voor het importeren en transformeren van gegevens.

In Excel 2013 en 2010

Voor Excel-versies 2013 en 2010 is Power Query beschikbaar als gratis add-on die u kunt downloaden van de Microsoft-website.

• Ga naar de Power Query downloadpagina en klik op de knop ‘Downloaden’ om het downloaden van de tool te starten.

• Wanneer u op de knop ‘Downloaden’ klikt, ziet u een aantal opties waaruit u de juiste kunt selecteren, afhankelijk van uw systeem.

• Nadat u de juiste optie heeft geselecteerd, klikt u op de knop ‘Downloaden’ om de tool te downloaden.

Met behulp van de Power Query-tool

Als er een Excel-werkblad is geopend, heeft u toegang tot de Power Query-tool via het tabblad ‘Gegevens’ en vervolgens via de optie ‘Gegevens ophalen’.

Gegevens importeren

• Wanneer u op de optie ‘Get Data’ klikt, worden de verschillende bronnen weergegeven waaruit u gegevens kunt importeren. Deze omvatten Excel-werkmappen, tekst- of CSV-bestanden, XML- en JSON-bestanden . Daarnaast kunt u gegevens importeren uit onder meer online databases zoals SQL Server en Microsoft Access. Andere bronnen waaruit u gegevens kunt importeren zijn Microsoft Azure en online diensten, zoals Salesforce en Facebook.
• Om gegevens te importeren, klikt u op een van de opties, zoals ‘Uit bestand’, ‘Uit database’, ‘Van Azure’, ‘Van online services’ en ‘Van andere bronnen’.

• Wanneer u gegevens importeert, toont Excel u een pop-up met een voorbeeld van de gegevens die worden geladen. Klik onderaan op de knop ‘Laden’ om het importeren van de gegevens te voltooien.

• Nu ziet u de gegevens in uw Excel-werkblad en kunt u er verschillende transformaties op toepassen.

Onderdelen van de Power Query-editor

• U hebt de Power Query Editor nodig om geïmporteerde gegevens indien nodig te transformeren. Klik op de ‘Power Query Editor starten’ nadat u op de knop ‘Gegevens ophalen’ hebt geklikt.

• Hiermee wordt de ‘Power Query Editor’ gestart, die uit zes hoofdcomponenten bestaat. Bovenaan vindt u het ‘Query Editor Ribbon’, waarin verschillende opdrachten onder verschillende tabbladen staan.

• Onder het ‘Query Editor-lint’ aan de linkerkant bevindt zich de ‘Querylijst’, waarin alle query’s in de werkmap worden weergegeven. Er zal ook een sectie ‘Gegevensvoorbeeld’ in het midden zijn, waarin alle transformaties worden weergegeven die op de gegevens zijn toegepast.

• Met de ‘Formulebalk’ kunt u de M-code van de transformatiestap bewerken. Alle transformaties worden vastgelegd en verschijnen als stappen in het gebied ‘Toegepaste stappen’.

• In het gedeelte ‘Eigenschappen’ kunt u zoekopdrachten voorzien van namen.

Transformaties toepassen

U kunt verschillende transformaties toepassen op de gegevens die zijn geïmporteerd in de Power Query Editor. Deze omvatten tekstformaties, bijsnijden, transponeren en meer.

Teksttransformaties

Tekst kan worden omgezet in hoofdletters of kleine letters nadat u deze in de Editor hebt geïmporteerd.

• Ga in de Power Query Editor bovenaan naar het tabblad ‘Transformeren’ en je ziet verschillende opties, zoals ‘Transponeren’, ‘Waarden vervangen’, etc.

• De optie ‘Opmaak’ is in het midden aanwezig, naast de optie ‘Kolom splitsen’. Klik erop om de beschikbare opmaakopties te bekijken.

• Klik op een optie, zoals ‘kleine letters’ of ‘HOOFDLETTERS’, om de tekst in de geselecteerde kolom om te zetten in kleine letters of hoofdletters. Op dezelfde manier zal het klikken op andere opties de tekst dienovereenkomstig transformeren.

• Met de optie ‘Opmaak’ kunt u ook alle witruimte verwijderen door de optie ‘Bijsnijden’ te gebruiken. Wanneer u op de knop ‘Bijsnijden’ klikt, worden alle extra witruimten uit de tekst verwijderd.

Kolommen splitsen

Naast het transformeren van de tekst, maakt de Power Query Editor het mogelijk om kolommen op verschillende manieren te splitsen.

• Nadat u de gegevens in de Power Query Editor heeft geïmporteerd, klikt u op de kolomkop om de hele kolom te selecteren.

• Klik vervolgens op de knop ‘Kolom splitsen’ links van de knop ‘Opmaak’. Dit geeft je een lijst met opties waarmee je de geselecteerde kolom op verschillende manieren kunt splitsen.

• Om de kolom te splitsen op basis van een scheidingsteken, klikt u op de betreffende optie. Hierdoor wordt de pop-up voor het splitsen per scheidingsteken weergegeven, waar u het scheidingsteken kunt selecteren, zoals komma, dubbele punt, gelijkteken, enz.

• Klik op de knop ‘OK’ om de kolom naar wens te splitsen en u zult zien dat de kolom is gesplitst.

Gegevens transponeren

Met de optie ‘Transponeren’ kunnen gebruikers gegevens van rijen naar kolommen schakelen of omgekeerd. Om dit te doen, importeert u eerst de gegevens in de Power Query Editor, zoals eerder uitgelegd.

• Na het laden van de gegevens gaat u bovenaan naar het tabblad ‘Transformeren’, waar u de optie ‘Transponeren’ vindt.

• Klik op de optie ‘Transponeren’ om de rijen naar kolommen te converteren.

Query’s combineren

Met Power Query kunt u eenvoudig meerdere datasets combineren met de opties ‘Samenvoegen’ en ‘Toevoegen’.

Met behulp van de samenvoegoptie

Met de samenvoegbewerking kunt u een nieuwe query maken door bestaande query’s te combineren.

• Importeer eerst de gegevens in het Excel-werkblad vanuit een bestand, database of andere bronnen. In dit geval hoeft u de gegevens niet in de Power Query Editor te laden, maar moet u meerdere gegevenssets importeren.

• Onder de opties voor het importeren van gegevens ziet u nog een optie, ‘Query’s combineren’. Wijs uw cursor naar deze optie en er zijn twee opties beschikbaar: Toevoegen en Samenvoegen.

• Als u op de knop ‘Samenvoegen’ klikt, krijgt u een nieuwe pop-up waarin u de datasets kunt selecteren die moeten worden samengevoegd.

• Als u de datasets selecteert, krijgt u een voorbeeld te zien. Linksonder kunt u selecteren hoe u de datasets wilt samenvoegen voordat u op de knop ‘OK’ klikt.

Met behulp van de optie Toevoegen

Met de optie ‘Toevoegen’ kunt u een nieuwe tabel maken door de rijen van de vorige zoekopdrachten te combineren.

• Volg dezelfde procedure als hierboven om de datasets aan het Excel-werkblad toe te voegen en ga vervolgens naar de optie ‘Toevoegen’ in de sectie ‘Query’s combineren’.

• In de pop-up die verschijnt, selecteert u de tabellen waarvoor de gegevens gecombineerd moeten worden voordat u op de knop ‘OK’ klikt. Gebruikers kunnen gegevens uit twee tabellen of uit drie of meer tabellen combineren.

• De gecombineerde gegevens verschijnen in het Power Query Editor-venster, van waaruit u deze in het werkblad kunt importeren met behulp van de knop ‘Sluiten en laden’ linksboven.

Gegevens naar het werkblad laden

Wanneer al uw bewerkingen in de Power Query Editor zijn voltooid, moet u de gegevens in uw Excel-werkblad laden.

• Er zijn verschillende manieren om de getransformeerde gegevens in uw Excel-werkblad te laden, zoals in een draaigrafiek, draaitabel, tabel of een verbinding voor de query. Klik linksboven op de optie ‘Sluiten en laden’ en u ziet twee opties: ‘Sluiten en laden’ en ‘Sluiten en laden naar’.

• Als u op de tweede optie klikt, ziet u de verschillende opties voor het laden van de gegevens in het werkblad.

• Met Excel kunt u de locatie kiezen, zoals een cel in een bestaand werkblad of een nieuw blad dat automatisch wordt gemaakt. Er is ook een optie ‘Voeg deze gegevens toe aan het gegevensmodel’.

Formules en functies gebruiken

Power Query maakt ook het gebruik van formules en functies mogelijk die vergelijkbaar zijn met Excel-werkbladen. Hiervoor moet u aangepaste kolommen toevoegen waar u formules en functies kunt toevoegen.

• Start de Power Query Editor vanaf het tabblad ‘Get Data’ en ga bovenaan naar het tabblad ‘Kolom toevoegen’.

• Aan de linkerkant ziet u uw vragen. Selecteer er een door erop te klikken en de ‘Aangepaste kolom’ wordt actief. Maak een nieuwe kolom door op de optie ‘Aangepaste kolom’ te klikken.

• Geef in het dialoogvenster voor het maken van een aangepaste kolom een ​​naam op voor de kolom.

• Voeg in het gedeelte ‘Aangepaste kolomformule’ een formule toe om de kolom te maken. Gebruik bijvoorbeeld een formule als [First Name]&""&[Last Name]. De Power Query-editor controleert of er fouten in de formule voorkomen.

• Als er geen fouten zijn, klikt u op de knop ‘OK’ en de editor maakt een kolom aan.

• Om een ​​functie te gebruiken herhaalt u de stappen totdat de pop-up ‘Aangepaste kolom’ verschijnt. Voeg in het gedeelte ‘Aangepaste kolomformule’ een functie toe, zoals Text.Upper([Full Name]), waarmee alle namen in hoofdletters worden gemaakt.

• Om het toevoegen van de kolom te voltooien, klikt u op de knop ‘OK’ om een ​​kolom te maken met de namen in hoofdletters.

Dat is alles wat u moet weten om aan de slag te gaan met Power Query. Deze tool maakt het ongelooflijk eenvoudig om gegevens indien nodig in Microsoft Excel te transformeren, zodat u met minimale inspanning kunt analyseren en conclusies kunt trekken. Het kan worden gebruikt om verschillende datasets te combineren, de opmaak ervan te wijzigen en andere acties uit te voeren. En u kunt zelfs Excel-functies en formules gebruiken met de editor, wat het nog nuttiger maakt.