Power Query gebruiken in Excel: de complete gids

Power Query is een krachtige tool voor bedrijfsinformatie in Excel waarmee u gegevens kunt importeren, opschonen en transformeren volgens uw behoeften. Als je ooit uren hebt besteed aan het opschonen van gegevens (we hebben) of moeite hebt gehad om gegevens uit verschillende bronnen samen te voegen (we hebben ook), zal deze functie je leven gemakkelijker maken.

De beste manier om te leren hoe u Power Query in Excel kunt gebruiken, is door enkele algemene taken uit te voeren, zoals het importeren van gegevens en het gebruik van de Power Query-editor om de resulterende tabellen te transformeren en de gegevens te combineren.

Dit artikel helpt u op weg door duidelijke Power Query-voorbeelden met stapsgewijze handleidingen weer te geven . Door Power Query onder de knie te krijgen, kunt u uw werk naar een hoger niveau tillen.

Ben je klaar? Laten we dit doen.

Inhoudsopgave

• Hoe te weten of u toegang hebt tot Power Query
• Gegevens importeren met Power Query
• De Power Query-editor gebruiken
  • Gegevensvoorbeeld
  • Eigenschappen
• Werken met meerdere gegevensbronnen
• Gegevens toevoegen met Power Query
• Gegevens samenvoegen met Power Query
• 5 manieren om gegevens te transformeren met Power Query
  • 1. Gegevens uitbreiden en condenseren
  • 2. Gegevens sorteren
  • 3. Gegevens formatteren
  • 4. Kolommen toevoegen en verwijderen
  • 5. Gegevenstypen wijzigen
• Werken met formules en M-taal
  • Formule balk
  • Schrijven en wijzigen van M-code
• Wat is M-taalsyntaxis?
• Gegevensverbindingen vernieuwen
• Hoe Power Query te integreren met het gegevensmodel
• Hoe te automatiseren met Power Query en VBA
• Geavanceerde Power Query-functies
• Samenwerken en delen
• Laten we dit afronden

Hoe te weten of u toegang hebt tot Power Query

Voordat u Power Query gaat gebruiken, moet u er zeker van zijn dat u een versie van Excel hebt die deze functie heeft.

Hier volgt een kort overzicht van Power Query per versie:

• Excel 2010 en Excel 2013: beschikbaar als invoegtoepassing die moet worden gedownload en geïnstalleerd.

• Excel 2016: geïntegreerd in Excel als “Get & Transform”.

• Excel 2019: verbeterde prestaties en functionaliteit.

• Excel 2021 en Excel voor Microsoft 365: integratie met gegevensbronnen in de cloud.

• Excel voor Mac: alleen beschikbaar vanaf Microsoft 365 versie 16.69 en hoger.

Om te controleren of de tool is geïntegreerd in uw Excel-werkmap (2016 of later), gaat u naar het tabblad Gegevens en zoekt u naar het gedeelte "Gegevens ophalen en transformeren" in deze afbeelding:

Als u Excel 2010 of 2013 op Windows hebt, kunt u de Power Query-invoegtoepassing downloaden , installeren en inschakelen.

Gegevens importeren met Power Query

Met de Power Query-tool kunt u een breed scala aan gegevensbronnen importeren en er verbinding mee maken:

• Bestanden, inclusief CSV-, XML- en JSON-indelingen

• Web-URL's of API-eindpunten

• Databases, waaronder Access, SQL Server, Oracle en de meeste moderne databases

• Azure-services, SharePoint-bestanden en OData-feeds

De eenvoudigste gegevensbron om mee te beginnen is een eenvoudig CSV-bestand met meerdere gegevensregels. Maak een tekstbestand en voeg deze vier voor- en achternamen toe, gescheiden door een komma:

• Joe, bloggen

• Anna, Ryan

• James, Steward

• Maria, Bruin

Sla het bestand op met de extensie .CSV. Je bestand ziet er als volgt uit:

Volg deze stappen om de gegevens met Power Query te laden:

1. Ga naar het tabblad Gegevens.

2. Klik op de knop "Bestanden ophalen" op het tabblad Gegevens ophalen en transformeren.

3. Selecteer uw voorbeeldbestand.

Het kan zijn dat u een paar seconden moet wachten voordat de tool de gegevens naar het voorbeeldvenster heeft gehaald. Power Query gebruikt behoorlijk wat machineresources, zelfs voor een kleine import, dus wees geduldig mensen.

Tip: sluit andere applicaties af om de prestaties te versnellen.

Power Query geeft het venster "Tekst/CSV importeren" weer met een voorbeeld van uw gegevens.

Als de gegevens er correct uitzien, kunt u doorgaan met het laden of transformeren van de gegevens:

• de knop Laden laadt gegevens rechtstreeks in Excel.

• met de knop Gegevens transformeren kunt u de gegevens wijzigen voordat u deze laadt.

Is het je opgevallen dat het voorbeeldbestand geen header had? Als het rechtstreeks in Excel wordt geladen, zijn de kopteksten "Kolom 1" en "Kolom 2".

In plaats daarvan kunt u de gegevens naar de Power Query-editor halen en enkele wijzigingen aanbrengen. Klik hiervoor op de knop "Gegevens transformeren".

De Power Query-editor wordt nu geopend, wat ons naar de volgende sectie brengt.

De Power Query-editor gebruiken

In deze sectie onderzoeken we enkele essentiële aspecten van het werken met de Power Query-editor.

Gegevensvoorbeeld

In het gebied Gegevensvoorbeeld kunt u uw geïmporteerde gegevens bekijken en bewerken. U kunt rijen filteren, kolomgegevenstypen wijzigen en de vorm van de gegevens aanpassen aan uw behoeften.

Wanneer u klaar bent, laadt Excel de getransformeerde gegevens in een werkblad.

Met behulp van het voorbeeldbestand kunt u de kolomnamen rechtstreeks in dit deelvenster bewerken.

Om dit te doen:

1. Klik met de rechtermuisknop op de kolomkop.

2. Klik op "Hernoemen" in het vervolgkeuzemenu.

3. Typ de voorkeursnamen in de koptekstvakken.

Terwijl u wijzigingen aanbrengt, wordt het gedeelte "Toegepaste stappen" aan de rechterkant van het voorbeeldvenster bijgewerkt om elke transformatie weer te geven, zodat u uw gegevensverwerkingsstappen kunt volgen en indien nodig kunt wijzigen.

Eigenschappen

Het instellen van eigenschappen voor uw zoekopdrachten is essentieel voor het organiseren en volgen van uw werk.

Het deelvenster Query-instellingen bevindt zich aan de rechterkant van het Power Query-venster.

1. Klik op de koppeling "Alle eigenschappen".

2. Bewerk de naam van de query zodat deze beschrijvend is.

3. Voeg een beschrijving toe om meer informatie te geven.

Het is gemakkelijk om te vergeten waarvoor de query's van vorige week waren ingesteld. In dit geval is de naam van de zoekopdracht gewijzigd en is er een beschrijving toegevoegd die het doel van de zoekopdracht weergeeft.

Als u meer wilt weten over de beschikbare opties, bekijk dan dit overzicht van de Power Query-editor .

Werken met meerdere gegevensbronnen

De Power Query-editor biedt twee primaire methoden voor het combineren van gegevens uit meerdere bronnen: toevoegen en samenvoegen.

Appending voegt tabellen verticaal toe en stapelt ze op elkaar. Door samen te voegen kunt u tabellen samenvoegen op basis van gemeenschappelijke kolommen.

Om te zien hoe dit werkt, maakt u een tweede klein CSV-bestand aan met dezelfde structuur als het eerste. Zet deze keer twee namen (eerste en laatste) in het bestand:

• Joe, bloggen

• Laura, Riet

Merk op dat een van deze namen wordt herhaald uit het vorige bestand.

Volg nu deze stappen om de gegevens naar uw Microsoft Excel-werkmap te halen.

1. Sluit het venster Power Query-editor en sla uw wijzigingen op.

2. Voeg een nieuw werkblad toe.

3. Volg de stappen in het vorige gedeelte om het tweede bestand te importeren.

4. Klik op de knop Transformeren.

5. Hernoem de kolomkoppen als "Voornaam" en "Achternaam".

Het rechterdeelvenster in het Excel-werkblad toont de huidige query's en verbindingen. U zou nu twee query's in het deelvenster moeten zien.

Gegevens toevoegen met Power Query

In deze sectie voegt u het tweede CSV-voorbeeldbestand toe aan het eerste.

Volg deze stappen om gegevens toe te voegen:

1. Klik met de rechtermuisknop op de eerste tabel (Klantnamen) in het deelvenster Query's en verbindingen.

2. Kies "Bewerken" om de tabel te openen in de Power Query-editor.

3. Klik op Query's toevoegen in de sectie Combineren.

4. Kies de tweede tabel uit de vervolgkeuzelijst met tabellen die u wilt toevoegen.

Het resultaat zijn twee extra rijen in de eerste tabel.

Merk op dat één rij wordt herhaald - dit is hoe de append-bewerking werkt. Het voegt de gegevens van de ene tabel toe aan de andere, ongeacht of de gegevens al bestaan.

U kunt de dubbele gegevens in de Power Query-editor met deze stappen verwijderen:

1. Klik op de kolomkop 'Voornaam'.

2. Houd de "Ctrl" -toets ingedrukt (of "Cmd" -toets op Mac).

3. Klik op beide kolomkoppen.

4. Klik op "Rijen verwijderen" op het tabblad Start van het Power Query Editor-lint.

5. Selecteer "Duplicaten verwijderen" in het vervolgkeuzemenu.

Gegevens samenvoegen met Power Query

Wanneer u gegevens samenvoegt, moet u de overeenkomende kolommen tussen de query's kiezen.

Stel dat u twee door komma's gescheiden bestanden heeft waarvan de ene de telefoonnummers van klanten bevat en de andere adressen. De twee bestanden zien er als volgt uit:

Volg de instructies in de vorige secties om elk bestand in zijn eigen Power Query te laden. Dan kun je oefenen met het samenvoegen van de tabellen.

In het geval van onze voorbeeldgegevens moeten beide kolommen (Voornaam en Achternaam) worden gekozen als overeenkomende kolommen. Houd de Ctrl -toets ingedrukt om meerdere kolommen te kiezen.

Volg deze stappen:

1. Klik met de rechtermuisknop op de eerste tabel in het deelvenster Query's en verbindingen.

2. Kies "Bewerken" om de tabel te openen in de Power Query-editor.

3. Selecteer "Query's samenvoegen" in de groep "Combineren".

4. Kies de tweede tabel in de vervolgkeuzelijst met tabellen.

5. Klik op de eerste kolom in de eerste tabel.

6. Houd de Ctrl-toets ingedrukt en klik op de tweede kolom in de eerste tabel.

7. Houd de Ctrl-toets ingedrukt en klik op beide kolommen in de tweede tabel.

8. Behoud de standaard join-operator als "Left Outer".

9. Klik op "OK" om de wijzigingen toe te passen.

De join-operator "Left Outer" zorgt ervoor dat dubbele gegevens worden geëlimineerd.

In de Power Query-editor ziet u een nieuwe kolom met de naam 'Tabel' of 'Geneste tabel' die de samengevoegde gegevens van de tweede query bevat.

1. Klik op het uitvouwpictogram (twee pijlen) in de kop van deze kolom.

2. Verwijder het vinkje bij "originele kolom als voorvoegsel gebruiken" (dit voorkomt lange kolomnamen).

3. Klik OK".

4. Klik op "Sluiten en laden".

De samengevoegde tabel had vier kolommen, waarbij de telefoon- en adresgegevens waren samengevoegd. Misschien heb je nog wat op te ruimen.

U kunt bijvoorbeeld de naam van de tabel (Merge1) en de adreskolom in de onderstaande afbeelding bewerken.

5 manieren om gegevens te transformeren met Power Query

Het transformeren van gegevens in uw Excel-bestand is gemakkelijk gemaakt met Power Query, zodat u uw gegevens effectief kunt opschonen, ordenen en manipuleren.

Enkele van de meest gebruikelijke manieren om gegevens te transformeren zijn:

• Uitbreiden en condenseren van gegevens

• Gegevens sorteren

• Data aan het formatteren

• Kolommen toevoegen en verwijderen

• Gegevenstypen wijzigen

1. Gegevens uitbreiden en condenseren

Het uitbreiden en condenseren van data is belangrijk om uw datasets beter beheersbaar te maken. Met Power Query kunt u tabellen uitvouwen of samenvouwen voor een meer georganiseerde weergave.

In het vorige gedeelte is uitgelegd hoe u kunt uitbreiden door gegevens uit gerelateerde tabellen op te nemen.

U kunt het ook samenvatten om alleen samengevatte gegevens op te nemen op basis van een aggregatie naar keuze, zoals som, aantal of percentage.

2. Gegevens sorteren

Om gegevens te sorteren, kunt u de volgorde snel wijzigen door oplopende of aflopende waarden.

Klik op het pijltje naar beneden naast een kolomnaam en kies “Sorteer oplopend” of “Sorteer aflopend”.

3. Gegevens formatteren

Het tabblad Transformeren in de Power Query-editor biedt veel opties.

Met het vervolgkeuzemenu Formaat kunt u bijvoorbeeld alle gegevens in een kolom of de hele tabel wijzigen in hoofdletters of kleine letters.

4. Kolommen toevoegen en verwijderen

Power Query vereenvoudigt het proces van het toevoegen, verwijderen en wijzigen van kolommen.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

U moet bijvoorbeeld decimale punten naar percentages converteren of op tekst gebaseerde percentages naar numerieke percentages converteren voor eenvoudigere berekening en vergelijking.

Werken met formules en M-taal

Wanneer u Power Query in Excel gebruikt, zult u de M-taal tegenkomen, de kern van het maken van geavanceerde formules, het manipuleren van gegevens en het produceren van krachtige query's. In dit gedeelte leer je over:

• de formulebalk

• M-code

• de essentiële elementen van de M Language-syntaxis

Formule balk

De formulebalk in de Power Query-editor van Excel is het gebied waar u uw formules en M-code invoert.

Om het te gebruiken, gaat u naar de Power Query-editor door een query te selecteren in het deelvenster Query's en verbindingen. De formulebalk bevindt zich boven het gegevensvoorbeeldvenster.

Schrijven en wijzigen van M-code

Om veelzijdige en krachtige query's in Excel te maken, moet u de Power Query-taal, ook wel M-code genoemd, schrijven en wijzigen. Hier zijn enkele tips om aan de slag te gaan:

• Gebruik het deelvenster Toegepaste stappen om stapsgewijze code te schrijven, zodat u de logica van uw query gemakkelijk kunt volgen en wijzigen.

• Combineer M-code met een Excel-tabel, draaitabellen en externe gegevensbronnen, zoals Azure en tekstbestanden, om dynamische en geautomatiseerde rapporten te maken.

• Pas de Query-opties aan om uw voorkeur voor het laden van gegevens te verfijnen en de prestaties te verbeteren.

Naarmate u vertrouwd raakt met M-code, zult u merken dat u veel processen kunt automatiseren en optimaliseren, waardoor uw gegevensanalyse in Excel efficiënter en krachtiger wordt.

Volg deze stappen om de code achter een query te bekijken:

1. Open de query in de Power Query-editor.

2. Klik op "Geavanceerde editor" op het tabblad Start.

Dit is de code achter ons voorbeeld van een samengevoegde tabel (de uwe kan er anders uitzien):

Wat is M-taalsyntaxis?

Het begrijpen van de M Language-syntaxis is cruciaal voor het maken van effectieve Power Query-formules. Houd rekening met de volgende punten:

• M Taal is hoofdlettergevoelig, dus wees voorzichtig met hoofdletters.

• Gebruik vierkante haken [] voor verwijzingen naar kolommen, records en lijsten.

• Om naar een vorige stap in uw query te verwijzen, gebruikt u de naam van de variabele (bijv. #"Stapnaam").

• Voer een queryformulestap uit met de instructie "in".

Naarmate u meer ervaring opdoet met Power Query-taalstroom , ontdekt u nieuwe manieren om uw gegevens te transformeren en te analyseren, waardoor uw Excel-werkmappen een nog waardevoller bezit worden.

Bekijk de video hieronder, het is tijd om de schrobborstel tevoorschijn te halen en je rommelige gegevens op te ruimen.

Gegevensverbindingen vernieuwen

Een van de belangrijkste functies van Power Query is de mogelijkheid om uw Power Query-verbinding eenvoudig te vernieuwen.

Wanneer uw gegevensbronnen zijn bijgewerkt en u de nieuwste informatie in uw werkmap nodig hebt, kunt u de gegevensverbindingen vernieuwen in het deelvenster Query's en verbindingen.

Als u het paneel aan de rechterkant van uw werkblad niet ziet, kunt u het openen door naar de gegevenstabel te gaan en op "Queries & verbindingen" te klikken.

Wanneer het deelvenster Query's en verbindingen is geopend, worden alle query's weergegeven. Volg deze stappen:

1. Klik met de rechtermuisknop op de query die u wilt vernieuwen.

2. Selecteer Vernieuwen in het contextmenu.

Hiermee wordt uw werkmap bijgewerkt met de nieuwste gegevens uit uw verbonden gegevensbronnen.

Hoe Power Query te integreren met het gegevensmodel

Het gegevensmodel van Excel is een krachtige functie waarmee u business intelligence-tools, zoals draaitabellen, kunt maken op basis van gegevensrelaties.

Het gegevensmodel is in feite een verborgen database die de geïmporteerde gegevens en relaties tussen tabellen bevat.

Het maakt deel uit van de Power Pivot-functie die is ingebouwd in de huidige versies van Excel. Mogelijk moet u Power Pivot als een invoegtoepassing installeren om er toegang toe te krijgen in eerdere versies).

U kunt uw Power Query-gegevensverbindingen met het gegevensmodel als volgt integreren:

1. Laad uw gegevens met behulp van Power Query zoals beschreven in de vorige secties.

2. In plaats van het rechtstreeks in de werkmap te laden, kiest u Laden naar in het menu Laden.

3. Schakel het selectievakje Deze gegevens toevoegen aan het gegevensmodel in het dialoogvenster Gegevens importeren in.

Hoe te automatiseren met Power Query en VBA

Met VBA-integratie (Visual Basic for Applications) kunt u aangepaste gegevensverbindingen maken en stappen voor gegevenstransformatie automatiseren in Power Query.

U kunt uw bestaande VBA-vaardigheden gebruiken om geavanceerde gegevensmodellen te bouwen door gegevens van platforms zoals Microsoft Access, SharePoint en Analysis Services op te nemen.

Hoewel Power Query geen systeemeigen ondersteuning voor VBA heeft, kunt u VBA-code gebruiken om Power Query-query's indirect te openen, te vernieuwen en te manipuleren.

Hierdoor kunt u krachtige, geautomatiseerde dataworkflows creëren en uw data-analyseproces stroomlijnen.

Hier volgt een voorbeeldcode waarmee een krachtige query in de huidige werkmap wordt vernieuwd.

:

Dim wb als werkboek, pq als object

Stel wb in = ThisWorkbook

Stel pq = wb.Queries ("Klantnamen") in

 pq.Vernieuwen

Hier is de code in een Excel VBA-macro:

Geavanceerde Power Query-functies

Excel's Power Query biedt een overvloed aan geavanceerde functies om uw gegevensanalyseproces te vereenvoudigen en te verbeteren. Door te leren hoe u deze functies effectief kunt gebruiken, kunt u uw vaardigheden op het gebied van gegevensanalyse naar een geheel nieuw niveau tillen.

De ingebouwde tools voor gegevensanalyse omvatten:

• Voorwaardelijke kolommen voor het toepassen van complexe logica op uw gegevenstransformaties.

• Groeperings- en aggregatiefuncties om uw gegevens op een zinvolle manier samen te vatten.

• Pivot- en unpivot-bewerkingen om uw gegevens opnieuw vorm te geven voor eenvoudigere analyse.

U vindt veel van deze functies op het tabblad Transformeren in de Power Query-editor.

Bovendien kunt u Power Query koppelen aan Business Intelligence- tools zoals LuckyTemplates voor meer geavanceerde opties voor gegevensanalyse en visualisatie .

Samenwerken en delen

Het delen van uw dataverbindingen, vragen en transformaties met uw teamleden is essentieel voor effectieve samenwerking.

U kunt een werkmap met query's delen door deze op te slaan op een netwerkstation, SharePoint of een andere gedeelde opslaglocatie.

U kunt de M-code ook exporteren achter een enkele query met deze stappen:

1. Bewerk de query in de Power Query-editor.

2. Klik op "Geavanceerde editor" op het tabblad Start.

3. Kopieer de volledige M-code in het venster Geavanceerde editor.

4. Open een teksteditor (bijvoorbeeld Kladblok) en plak de gekopieerde M-code in de teksteditor.

5. Sla het tekstbestand op en stuur het naar uw collega.

Laten we dit afronden

Met een scala aan functies en de gebruiksvriendelijke interface is Power Query een essentiële tool voor Excel-gebruikers die effectief willen werken met externe gegevens.

Deze waardevolle functie helpt het proces van het maken van rapporten, het opschonen van gegevens en het uitvoeren van complexe analyses op uw gegevenstabellen te stroomlijnen. Gebruik makend van

Power Query bespaart u uiteindelijk tijd en verhoogt de efficiëntie bij het werken met Excel-bestanden.