Power Query: the 2021 Definitive Beginners’ Guide

Hi, welcome to the beginners’ guide to Power Query by UpSlide.
heeft u zich ooit afgevraagd wat Power Query is? Hoe het werkt? Zo ja, dan bent u op de juiste plaats!
Pauline, onze Power Query expert van Finance 3.1, gaf ons al haar beste tips en advies om dit artikel te schrijven.
hier leert u de basis van de software en kunt u zelfs een beetje oefenen! Ben je klaar?

als u nu geen tijd hebt om het te lezen, download dan de PDF-versie en geniet er later van!

Wat is Power Query? Wanneer moet ik het gebruiken?

Power Query is een Excel-invoegtoepassing waarmee gebruikers gegevens kunnen importeren, opschonen en consolideren. U kunt Power Query gebruiken wanneer:

• grote hoeveelheden gegevens verwerken (waarbij Excel beperkt is tot een miljoen rijen)
• Gegevens importeren uit verschillende bronnen (bijvoorbeeld Excel, CSV, SharePoint, SQL en Salesforce)
• meerdere tabellen, bestanden of zelfs mappen bewerken, opschonen en combineren!

Power Query is een gebruiksvriendelijke en krachtige tool, zelfs voor beginners, omdat je geen coderingservaring nodig hebt om aan de slag te gaan. Bovendien verandert het de oorspronkelijke gegevensbron niet. Het resultaat is als een momentopname van opnieuw vormgegeven gegevens die naar Excel worden gebracht. Wat is daar zo geweldig aan? Nou, omdat de brongegevens niet direct worden geïmporteerd, maakt het uw Excel-bestand niet zwaarder (dat wil zeggen het vertraagt uw computer niet!).

om samen te vatten, Power Query is een data-analyse tool die ETL (Extract-Transform-Load) processen zowel eenvoudiger als effectiever maakt.

Wat is het verschil tussen Power Query en Power Pivot?

Power Pivot wordt vaak geassocieerd met Power Query omdat het een Microsoft Office-invoegtoepassing is die gegevensanalyse vergemakkelijkt. Interessant, echter, deze twee tools kunnen samenwerken met elkaar! Power Pivot is gewoon de volgende stap van data transformatie: het stelt gebruikers in staat om gegevensmodellen te maken van tabellen die eerder zijn gereinigd door Power Query.

terwijl soortgelijke, Power Pivot meer wordt gebruikt voor gegevensanalyse, tijdgerelateerde berekeningen en het genereren van koppelingen. Eenvoudig gezegd:

• Power Query kunt u automatisch consolideren en opnieuw verwerken van bestanden (dat wil zeggen de standaardisatie van rapportageprocessen of consolidatie van meerdere bestanden met hetzelfde formaat).
• met Power Pivot kunt u databases van enkele miljoenen regels analyseren door geaggregeerde informatie te extraheren volgens de regels die door uw bedrijf zijn ingesteld.

Wat is het verschil tussen Power Query en Power BI?

de onderliggende Query-technologie van Power Query is ook opgenomen in Power BI.

met de interface van de Power BI-desktopsoftware kunt u profiteren van de drie technologieën (Power Query, Power Pivot en Power View). Met Power BI kunt u alle stappen van de data-analyseketen uitvoeren, van het extraheren van gegevens tot het maken van visuals op een interactief dashboard dat u kunt delen en analyseren.

> Lees ons artikel over het exporteren van Power BI naar Excel

Waarom is Power Query een geweldig alternatief voor Excel VBA?

Power Query kan eenvoudig VBA (Visual Basic for Applications) vervangen omdat het u in staat stelt om:

• verwerk uw tabellen gewoon door te klikken op knoppen. Geen programmeervaardigheden nodig!
• Visualiseer uw bewerkingen stap voor stap zonder een enkele macro uit te voeren.
• Bewerk eenvoudig de volgorde van de stappen van de verschillende stappen door te slepen en neer te zetten! U kunt ook onnodige stappen toevoegen en verwijderen met een simpele klik.

(zie Deel 2-Paragraaf3: hoe aan de slag met Power Query?)

Power Query kan VBA echter niet vervangen als het gaat om interactie met Excel ‘ s interface, het automatiseren van processen, het genereren van nieuwe bestanden, het verzenden van e-mails, enz.

is Microsoft Power Query vrij? Is er een Power Query voor Mac?

Ja. Power Query is een gratis uitbreiding van Excel. Dat is een ander belangrijk voordeel van de invoegtoepassing.
(lees de volgende vraag om uit te vinden hoe u het op uw Excel-bureaublad kunt krijgen)

Power Query voor Mac? Ja, groot nieuws: Query editor is nu compatibel met Excel voor Mac! Maar de integratie is vrij beperkt op dit moment, omdat u alleen het bestandspad kunt wijzigen en bestaande query ‘ s kunt vernieuwen.

deel 2: aan de slag met Power Query!

hoe Stroomquery downloaden?

Power Query is beschikbaar voor Excel sinds 2010.

hier leest u hoe u Power Query toevoegt volgens uw Excel-versie*:

• Goed Nieuws, U hoeft geen Stroomquery te downloaden voor Excel 2019, 2016 of in Office 365. U kunt het vinden in het “Data” lint onder de knop “Get & Transform”.
• voor Excel 2013 en 2010 moet u de Power Query-invoegtoepassing downloaden. Het zal verschijnen in een nieuw speciaal tabblad met de naam “Power Query” (in Excel 2010 moet u een Professional Plus met Software Assurance-licentie hebben).

*hier is hoe u uw Excel-versie controleert:

Ga naar Bestand > Account > over Excel. Dan verschijnt er een venster. Lees de eerste twee getallen tussen haakjes. In het onderstaande voorbeeld is de Excel-versie 2016.

voor meer informatie is hier nog een gedetailleerd artikel over het installeren en activeren van Power Query in Excel.

Hoe activeer ik Power Query in Excel?

afhankelijk van uw Excel-versie (zie bovenstaande vraag: Waar is Power Query in Excel?) u hoeft alleen maar te klikken op de knop met de naam “Get data” in de “Get & Transform” sectie in de “Data” tab voor Excel 2016 en verder. Voor Excel-versies vóór 2013 inbegrepen, gaat u gewoon naar het Power Query ribbon.

houd er rekening mee dat wanneer u de Power Query-interface invoert, u niet langer in staat zult zijn om met Excel te communiceren. Als u terug moet gaan naar Excel, moet u de Power Query-editor sluiten.

Hoe werkt Power Query?

in Excel hebt u alleen toegang tot de volgende functionaliteiten wanneer u klikt op “Queries & verbindingen”:

Data ribbon: om te zoeken naar gegevensbronnen (“Power Query” lint voor Excel 2010 en 2013).

Queries & deelvenster Verbindingen: om snel alle verbindingen (queries) te bekijken die u hebt gemaakt.

zodra u Power Query hebt gestart, landt u op de Power Query-Editor die bestaat uit 4 verschillende elementen:

Hoofdlint: Hiermee kunt u query ‘ s vanaf nul maken dankzij de knoppen (als u bekend bent met de taal M, kunt u ook toegang krijgen tot de geavanceerde Editor om ze handmatig aan te passen).

Queries: om alle verbindingen met gegevensbronnen En-streams bij te houden.

voorbeeld van de gegevenstabel: om uw gegevensresultaat na een specifieke stap te visualiseren.

Query-Instellingen (Toegepaste stappen): om door alle stappen te navigeren die u hebt gemaakt. Power Query registreert automatisch alle gemaakte transformaties.

• in sectie 4 leert u hoe Power Query in principe werkt door een gegevenstabel te transformeren.
• in Paragraaf 5 wordt het nog interessanter omdat u zult ontdekken hoe u gegevens uit verschillende bronnen kunt verwerken en queries kunt samenvoegen.

hoe aan de slag met Power Query?

stelt u zich de volgende situatie voor:

u bent de financieel controleur van een technologiebedrijf dat computers en smartphones verkoopt. Momenteel bent u actief in 3 landen: Frankrijk, Groot-Brittannië en China. Elke maand stelt u een geconsolideerd rapport op voor uw manager, met KPI ‘ s zoals totale omzet, omzet per land of omzet per product. Om dit te doen, stuurt elke country manager – Dennis, Douglas en Karen – je een dossier.

probleem: elk bestand geeft de verkopen weer in de eigen denominatie van het land. Het is te tijdrovend om ze om te zetten in EURO ‘ s en ze vervolgens te consolideren in het eindverslag.

wat als u het proces kunt automatiseren en uw rapport in een paar minuten kunt berekenen door alleen Power Query te gebruiken?

zou dat niet geweldig zijn? Nou, met Power Query, je kunt! Laten we beginnen met het voorbereiden van je eerste tabel 🙂

eerst heb je gegevens nodig. Download ons Power Query voorbeeld en sla het op uw lokale server.

volg dan deze eenvoudige stappen:

• pak het bestand uit en sla het op uw lokale server.
• open nu een nieuw werkblad in Excel en ga naar de Data tab > haal gegevens > uit bestand > uit de werkmap, zoals hieronder getoond.

(als u Excel-versie 2013 of 2010 hebt, gaat u gewoon naar het tabblad Power Query)

• Ga naar het pad waar u de uitgepakte bestanden die u zojuist hebt gedownload hebt opgeslagen en selecteer het Excel-bestand met de naam “Exchange Rates”.

• klik op data transformeren.

het is u wellicht ook opgevallen dat enkele toegepaste stappen al zijn verschenen in het deelvenster Query-Instellingen. Deze worden automatisch gedaan.

laten we nu een beetje oefenen, te beginnen met het opschonen van de gegevenstabel:

• verwijder de bovenste 3 rijen, ga naar rijen > verwijder de bovenste rijen en typ “3”. U ziet direct de transformatie op de data table preview evenals in de toegepaste stappen (Query-Instellingen).

• Verwijder lege rijen: verwijder rijen > Verwijder lege rijen (zoals hieronder getoond).

• promoot je eerste rij als koptekst: klik op”Use First Row as Headers” (eerste rij als koptekst gebruiken). Zoals je misschien hebt gemerkt, Power Query automatisch een stap toegevoegd om het type van sommige gegevens te wijzigen (de waarden in de eerste kolom zijn nu geformatteerd als datums).

• lege kolommen verwijderen: Klik op “Kolommen kiezen” en schakel kolommen 7, 8, 9 en 10 uit.

je doet het goed! Nog drie stappen en je bent helemaal klaar!

• de kolommen losmaken: Selecteer de kolom ” wisselkoers “en ga in het tabblad transformeren naar kolommen > andere kolommen uitpakken (U kunt ook de vijf andere kolommen selecteren en”kolommen uitpakken” selecteren).

• hernoem de kolommen: klik op de headercellen, voer “Date” in de eerste in, “Currency” in de tweede en “Rate” in de derde.

• pas een filter toe: klik net als in Excel op het vervolgkeuzemenu van de kolom “datum” en selecteer “Dec-18”.

• klik op het tabblad Home op Sluiten en laden om de finaletabel in een nieuw blad te laden.

goed gedaan! Je hebt je eerste gegevenstabel gevormd.

laten we verder gaan met Power Query

stel je de volgende situatie voor:

dankzij uw Power Query-vaardigheden bent u nu SENIOR financieel controleur van het technologiebedrijf. Nu moet je de gegevens van December 2018 consolideren die de landmanagers je hebben gestuurd.

probleem: elk bestand geeft de verkopen weer in de eigen denominatie van het land. Het is tijdrovend om ze om te zetten in EURO ‘ s, en nog meer om ze vervolgens te consolideren in het eindverslag.

wat als u het proces kon automatiseren en uw rapport kon berekenen in een paar minuten alleen met behulp van Power Query?

nu zullen we zien hoe we verschillende gegevensbronnen kunnen consolideren en samenvoegen met de tabel die we in de vorige sectie hebben opgesteld.

laten we eerst de hele map importeren, inclusief de drie verschillende bronbestanden:

• als u nog steeds in Power Query bent, ga dan naar nieuwe bron > bestand > map.

(Als u terug bent in Excel, gaat u naar gegevens > uit de map ophalen)

• Selecteer de map “Sales_201812” die u eerder hebt gedownload en opgeslagen. Klik daarna op OK.

er moet een nieuw venster verschijnen.

dan moet u de queries samenvoegen:

• Selecteer “combineer & bewerken” in het vervolgkeuzemenu (onderaan het nieuwe venster).

• Selecteer de voorbeeldtabel “Dec-18” (houd er rekening mee dat alle bladen dezelfde naam moeten hebben).

• u kunt in de eerste kolom controleren of alle bronnen zijn opgenomen (klik op drop down menu) en klik vervolgens op OK.

u bevindt zich nu in de Power Query-editor.

• Ga naar tabblad transformeren > waarden vervangen.

• vervang nu ” € “door ” EUR”.

• Ga naar Home tab > Queries samenvoegen.

er verschijnt een nieuw venster:

1. selecteer de kolom “valuta”.
2. Selecteer het bestand “wisselkoersen” in het vervolgkeuzemenu.
3. selecteer de kolom “valuta”.
4. klik op OK.

• nu moet u de wisselkoersgegevens weergeven die u zojuist hebt opgehaald:
  1. Selecteer de kolom “wisselkoers”.
  2. klik op de knop Uitvouwen.
  3. Controleer alleen “Rate”.
  4. vinkje “origineel gebruiken …” uit.
  5. klik op OK.

Gefeliciteerd! Je hebt net een andere gegevensbron geladen, zoekopdrachten samengevoegd en de overeenkomstige wisselkoers opgehaald. Geen VLOOKUP of INDEX + MATCH meer!

om te eindigen maken we een kolom” eenheidsprijs EUR “om alle prijzen om te zetten in EURO’ s:

• Ga naar tabblad Kolom toevoegen > aangepaste kolom.

• er verschijnt een nieuw venster. Laten we de formule van de prijs in EURO ‘ s schrijven:
  1. noem het “eenheidsprijs EUR”.
  2. Dubbelklik op” eenheidsprijs lokale valuta ” (of selecteer het en klik vervolgens op Invoegen).
  3. Type”/”.
  4. Dubbelklik op”Rate”.
  5. klik op OK.

• zodra de kolom verschijnt, klik op het type symbool (net voor de naam) en selecteer “decimaal nummer”.

we moeten hetzelfde doen om de kolom “inkomsten” te genereren:

• Ga naar tabblad Kolom toevoegen > aangepaste kolom.
• er verschijnt een nieuw venster. Laten we de Inkomstenformule typen:
  1. noem het “Inkomsten”.
  2. Dubbelklik op “eenheidsprijs EUR” (of selecteer het en klik vervolgens op Invoegen).
  3. Type”*”.
  4. Dubbelklik op”hoeveelheid”.
  5. klik op OK.

• nogmaals, Wijzig het kolomtype in “decimaal getal”.

om te eindigen, laten we het exporteren naar een nieuwe gegevenstabel in Excel!

• Ga naar Home tab > sluiten & Laden > sluiten & Laden.

nu kunt u volledig profiteren van Excel-functies om de gegevens te analyseren, een draaitabel te maken, enz.

Wow! Als je het hebt gehaald tot het einde, gefeliciteerd! Je weet nu hoe Power Query werkt.

nu is het jouw beurt!

Ik wil het aan u overdragen: heeft u alle bovenstaande vragen over Power Query begrepen?
hebt u onze tutorial bestanden gedownload en de bovenstaande richtlijnen gevolgd om het zelf te proberen?
laat het ons weten door hieronder een korte reactie achter te laten!

voor meer informatie over Power Query: