Bottom line: Leer hoe deze geweldige functie van Excel en Power BI genaamd Power Query u zal helpen het proces van het importeren, transformeren en opschonen van uw gegevens te automatiseren, zodat u TONEN tijd bespaart met uw werk.
Skill level: Beginner
Video Tutorial
Download de voorbeeldbestanden
De CSV-bestanden die ik in de video gebruik, kunnen hieronder worden gedownload. U moet de bestanden uitpakken uit het zip-bestand.
CSV Order Files For Power Query Overview.zip (337.0 KB)
Inleiding tot Power Query
In deze tutorial geef ik een inleidende uitleg over Power Query. U leert waarom dit mijn nieuwe favoriete Excel-tool is voor het werken met gegevens, en hoe het u kan helpen processen te automatiseren en tijd te besparen!
De Power Query Gegevensmachine
Ik keek naar een tv-programma over hoe dingen worden gemaakt, en ze legden uit hoe een deponeermachine in een banketbakkerij werkte.
De basisprincipes van een depotmachine zijn:
- Je voegt er ingrediënten aan toe.
- Verander wat instellingen.
- En hij tovert gebakjes (koekjes, donuts, koekjes) tevoorschijn die klaar zijn om te worden gebakken.
Als de wijzerplaten eenmaal zijn ingesteld, kan het proces keer op keer worden herhaald om elke keer weer perfect gebak te maken. Krijg je honger…? 🙂
Power Query werkt op een zeer vergelijkbare manier!
- Je voegt je gegevensbronnen toe (Excel-tabellen, CSV-bestanden, databasetabellen, webpagina’s, enz.)
- Druk op knoppen in het venster Power Query Editor om uw gegevens te transformeren.
- Uitvoer van die gegevens naar uw werkblad of gegevensmodel (PowerPivot) dat klaar is voor draaitabellen of rapportage.
Power Query is als een machine, want zodra u uw query hebt ingesteld, kan het proces worden herhaald met een klik op een knop (vernieuwen) telkens wanneer uw gegevens veranderen.
Als u macro’s hebt gebruikt om uw gegevens te transformeren, kunt u dit zien als een veel eenvoudiger alternatief voor VBA dat GEEN codering vereist.
Common Data Tasks Made Easy
Werkt u met gegevens die zijn geëxporteerd uit een registratiesysteem? Dit kan een grootboek, boekhouding, ERP, CRM, Salesforce.com of een ander rapportagesysteem zijn dat gegevens bevat.
Als dat het geval is, besteedt u waarschijnlijk veel tijd aan het transformeren of opnieuw vormgeven van uw gegevens om aanvullende rapporten, draaitabellen of grafieken te maken.
Deze gegevenstransformaties kunnen taken omvatten als:
- Verwijder kolommen, rijen, spaties
- Verander gegevenstypen – tekst, getallen, datums
- Kolommen splitsen of samenvoegen
- Sorteren & filterkolommen
- Berekende kolommen toevoegen
- Geaggregeerde of samengevatte gegevens
- Vind & vervang tekst
- Onttrek gegevens om te gebruiken voor draaitabellen
Klinkt een van deze taken bekend? Zo ja, dan klinken ze waarschijnlijk ook saai, repetitief en tijdrovend. 🙂 Geloof me, ik heb het grootste deel van mijn carrière besteed aan het uitvoeren van deze taken en geprobeerd snellere manieren te bedenken om ze gedaan te krijgen.
Gelukkig heeft Power Query knoppen die al deze taken automatiseren!
Overzicht van het Power Query-lint
Beginnend in Excel 2016 voor Windows, is Power Query volledig geïntegreerd in Excel. Het staat nu op het tabblad Gegevens van het lint in de groep Get & Transform.
In Excel 2010 en 2013 voor Windows is Power Query een gratis invoegtoepassing. Na installatie wordt het tabblad Power Query zichtbaar in het Excel Lint.
U gebruikt de knoppen op het tabblad Data of Power Query om uw brongegevens op te halen. Ook hier kunnen uw gegevens zijn opgeslagen in Excel-bestanden, csv-bestanden, Access, SQL-serverdatabase, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites en meer.
Als u eenmaal hebt aangegeven waar uw gegevens vandaan komen, gebruikt u het venster Power Query Editor om transformaties in de gegevens aan te brengen.
Met de knoppen in het Power Query Editor-venster kunt u uw gegevens transformeren.
denk aan enkele van de taken die u herhaaldelijk uitvoert terwijl u door de knoppen in de bovenstaande afbeelding bladert. Elke keer dat u op een knop drukt, worden uw acties (stappen) vastgelegd, en u kunt de stappen snel opnieuw toepassen wanneer u nieuwe gegevens ontvangt door de query te verversen.
Na het voltooien van uw stappen kunt u de gegevens uitvoeren naar een tabel in uw Excel-werkmap door op de knop & Sluiten te klikken.
U kunt ook bestaande query’s wijzigen en uw uitvoertabellen verversen met de gewijzigde of bijgewerkte gegevens.
Voorbeelden van datatransformatie
Hier volgen enkele voorbeelden van wat Power Query met uw gegevens kan doen.
Unpivot Data for Pivot Tables
Mijn favoriete functie van Power Query is de mogelijkheid om gegevens te Unpivot-en. Dit is een techniek die wordt gebruikt om uw gegevens klaar te maken voor de bron van een draaitabel.
De gegevens kunnen er aanvankelijk ongeveer zo uitzien als hieronder.
En u wilt dat het eindresultaat er als volgt uitziet.
Power Query kan dit met een paar klikken doen, en uw gegevens voorbereiden voor gebruik in een draaitabel.
Hier vindt u een artikel en een video over hoe u uw gegevens precies kunt uitsplitsen met Power Query.
Kijk ook eens naar mijn artikel over hoe u uw brongegevens structureert voor een draaitabel als u niet weet waarom uw gegevens er zo uit moeten zien voor een draaitabel.
Tabellen toevoegen (combineren) met Power Query
Met de functie Toevoegen van Power Query kunt u meerdere tabellen combineren (verticaal stapelen) om één grote tabel te maken. Je kunt dit doen met meerdere tabellen in één bestand, of je kunt gegevens ophalen uit een heleboel verschillende bestanden/bronnen.
Laten we zeggen dat je een map hebt met CSV- of Excel-bestanden met rapportgegevens voor elke maand. Gooi al die bestanden in de Power Query-machine, en er komt een mooie tabel uit die je vervolgens kunt gebruiken om draaitabellen en grafieken te maken.
Als de gegevens in die rapporten ook nog moeten worden getransformeerd (rijen verwijderen, kolommen splitsen, unpivot, enz.), dan kan Power Query dat in hetzelfde proces afhandelen.
Als het eenmaal is ingesteld, hoef je alleen nog maar elke maand op de knop verversen te drukken als er een nieuw bestand aan de map is toegevoegd en de rijen worden aan je uitvoertabel toegevoegd.
Hoe geweldig is dat! 🙂
Tabellen samenvoegen – een VLOOKUP-alternatief
Power Query heeft de mogelijkheid om tabellen samen te voegen of samen te voegen. Dit kan worden gebruikt als alternatief voor VLOOKUP of INDEX/MATCH formules.
L stel dat u een gegevenstabel met verkooprecords hebt en dat u een VLOOKUP gebruikt om informatie over het product te verkrijgen op basis van de naam van het verkochte product. De informatie over de productgroep bevindt zich in een andere tabel op een ander blad of in een andere werkmap.
Het gebruik van VLOOKUP-formules is geweldig, maar het betekent vaak dat u duizenden formules aan uw werkmap moet toevoegen. Dat vergroot de bestandsgrootte en de rekentijd.
Power Query maakt het heel snel en gemakkelijk om twee tabellen samen te voegen met slechts een paar klikken. Het maakt in principe gebruik van SQL joins, dus je kunt zelfs meer geavanceerde samenvoegingen doen, zoals innerlijke, uiterlijke, linker, rechter, volledige, en anti joins.
Create Custom Functions
ower Query is zo ontworpen dat je NIET hoeft te weten hoe te coderen om het te gebruiken. Het is heel eenvoudig te gebruiken omdat u gewoon op knoppen kunt klikken en filters kunt toepassen zoals u dat normaal in Excel zou doen.
Power Query kan echter worden geprogrammeerd om aangepaste functies te maken.
Het is gebaseerd op de M-taal, en de meeste functies lijken erg op het schrijven van een formule in Excel. Dit maakt het ook gebruiksvriendelijker en gemakkelijker om de code te leren.
Deze nieuwe taal en reeks functies betekent dat er veel te leren valt, maar ik beschouw dat als het leuke en uitdagende deel. Bovendien zullen werkgevers van de toekomst zeker op zoek zijn naar werknemers met Power Query-vaardigheden.
Power Query registreert uw stappen & Automatiseert processen
Power Query maakt al deze taken niet alleen eenvoudiger, maar registreert ook uw stappen zodat u ze NIET steeds opnieuw hoeft te doen. Dit bespaart u veel tijd als u elke dag, week of maand dezelfde gegevens voorbereidt.
Het kan ook vrij goed omgaan met fouten. Als de structuur van uw brongegevens verandert, vertelt Power Query u bij welke stap het fout ging en kunt u het herstellen. Dit maakt onderhoud eenvoudig en u hoeft uw proces niet helemaal opnieuw uit te voeren als er iets verandert.
U kunt Power Query gebruiken om uw gegevens klaar te maken voor gebruik in draaitabellen, grafieken en dashboardrapporten. Dit is een cruciale stap in het proces van het samenvatten en analyseren van gegevens.
De Power Query Machine & Power BI
Wel, het kan niet echt koekjes maken, maar Power Query is een behoorlijk geweldige tool! Het bespaart u een hoop tijd bij het transformeren van uw gegevens.
Power Query is slechts een onderdeel van de suite van Power BI (Business Intelligence) producten van Microsoft.
Als we teruggaan naar de analogie van het bakken van koekjes in een fabriek, kunt u Power Query zien als de eerste stap in de assemblagelijn. Zodra de koekjes zijn gevormd, moeten we ze bakken (draaitabellen, PowerPivot) en vervolgens verpakken voor presentatie (Power View, Power Map, grafieken, dashboards, enz.)
Je kunt Excel zien als het fabrieksgebouw waarin al deze tools zijn ondergebracht. Het spannende is dat er een zeer mooie toekomst is voor Excel! Al deze technologische ontwikkelingen zullen ons helpen onze gegevens op nieuwe manieren te interpreteren, tijd te besparen en indruk te maken op onze bazen. 🙂
Hoe krijg ik Power Query?
Het andere leuke is dat Power Query nu is ingebouwd in Excel, te beginnen met Excel 2016 voor Windows. Als u op Excel 2010 of 2013 dan Power Query is een gratis add-in.
Ik heb een speciale pagina die u zal helpen bepalen of u de juiste versie van Excel om Power Query te krijgen. Het biedt ook volledige installatie-instructies en de download link.
Volledige handleiding voor het installeren van Power Query
Om u een idee te geven van het belang van deze tool, Power Query is volledig geïntegreerd in Excel in Excel 2016 voor Windows, en staat op het tabblad Gegevens van het lint.
Het is ook bekend als Get & Transform, hoewel de term Power Query het meest gebruikelijk is.
Aanvullende bronnen
Dit artikel heeft een overzicht gegeven van de basisprincipes van Power Query dat u moet helpen enkele van de belangrijkste functies te begrijpen. Power Query heeft een ton van functies en er is zeker een hoop te leren.
Ik zal delen meer how-to artikelen en video’s in de komende weken. Hier zijn een paar bronnen die u op weg helpen.
Hoe u uw gegevens kunt uitsplitsen met Power Query + video-handleiding
Gratis trainingswebinar over de Power Tools
Op dit moment geef ik een gratis trainingswebinar over alle Power Tools in Excel. Dit omvat Power Query, Power Pivot, Power BI, draaitabellen, macro’s & VBA, en nog veel meer.
Het heet The Modern Excel Blueprint. Tijdens het webinar leg ik uit wat deze tools zijn en hoe ze in je workflow kunnen passen.
U leert ook hoe u de Excel-held van uw organisatie kunt worden, die go-to meid of kerel op wie iedereen vertrouwt voor Excel-hulp en leuke projecten.
De webinar wordt op meerdere dagen en tijden gehouden. Klik op de link hieronder om u te registreren en uw plaats te reserveren.
Klik hier om u te registreren voor het gratis webinar