Power Query is ingebouwd in Windows-versies van Office 365, Excel 2016, Excel 2019 en is beschikbaar als gratis download in Windows-versies van Excel 2010 en Excel 2013. De tool is ontworpen om gegevens te extraheren, transformeren en laden in Excel vanuit een verschillende bronnen. Het beste deel: Power Query onthoudt uw stappen en speelt ze af wanneer u de gegevens wilt vernieuwen. Dit betekent dat u gegevens op dag 1 in 80% van de normale tijd kunt opschonen, en dat u gegevens op dag 2 tot en met 400 kunt opschonen door simpelweg op Vernieuwen te klikken.
Ik zeg dit over veel nieuwe Excel-functies, maar dit is echt de beste functie om Excel in 20 jaar te bereiken.
Ik vertel een verhaal in mijn live seminars over hoe Power Query werd uitgevonden als een steunpilaar voor klanten van SQL Server Analysis Services die gedwongen werden Excel te gebruiken om toegang te krijgen tot Power Pivot. Maar Power Query werd steeds beter en iedereen die Excel gebruikt, zou de tijd moeten nemen om Power Query te leren.
Krijg Power Query
Mogelijk hebt u al Power Query. Het bevindt zich in de groep Ophalen en transformeren op het tabblad Gegevens.
Maar als u zich in Excel 2010 of Excel 2013 bevindt, gaat u naar internet en zoekt u naar Power Query downloaden. Uw Power Query-opdrachten verschijnen op een speciaal Power Query-tabblad in het lint.
Gegevens opschonen voor de eerste keer in Power Query
Om u een voorbeeld te geven van de geweldige eigenschappen van Power Query, stel dat u het onderstaande bestand elke dag krijgt. Kolom A is niet ingevuld. Kwartalen gaan over de pagina in plaats van naar beneden.
Sla om te beginnen die werkmap op uw harde schijf op. Zet het op een voorspelbare plaats met een naam die u elke dag voor dat bestand zult gebruiken.
Selecteer in Excel Gegevens ophalen, Uit bestand, Uit werkmap.
Blader naar de werkmap. Klik in het voorbeeldvenster op Blad1. In plaats van op Laden te klikken, klikt u op Bewerken. U ziet de werkmap nu in een iets ander raster: het Power Query-raster.
Nu moet u alle lege cellen in kolom A corrigeren.Als u dit zou doen in de Excel-gebruikersinterface, is de logge commandoreeks Home, Zoeken & selecteren, Ga naar speciaal, Spaties, Is gelijk aan, Pijl-omhoog, Ctrl + Enter .
Selecteer in Power Query Transform, Fill, Down.
Alle null-waarden worden vervangen door de waarde van bovenaf. Met Power Query zijn er drie klikken nodig in plaats van zeven.
Volgend probleem: de wijken gaan over in plaats van naar beneden. In Excel kunt u dit oplossen met een draaitabel met een meervoudig consolidatiebereik. Dit vereist 12 stappen en 23+ klikken.
Selecteer in Power Query de twee kolommen die geen kwartalen zijn. Open de vervolgkeuzelijst Kolommen ongedaan maken op het tabblad Transformeren en kies Andere kolommen ongedaan maken, zoals hieronder wordt weergegeven.
Klik met de rechtermuisknop op de nieuw gemaakte Attribuutkolom en hernoem deze Kwartaal in plaats van Attribuut. Meer dan twintig klikken in Excel worden vijf klikken in Power Query.
Om eerlijk te zijn, niet elke reinigingsstap is korter in Power Query dan in Excel. Als u een kolom verwijdert, betekent dit nog steeds dat u met de rechtermuisknop op een kolom klikt en Kolom verwijderen kiest. Maar om eerlijk te zijn gaat het verhaal hier niet over de tijdwinst op dag 1.
Maar wacht even: Power Query onthoudt al uw stappen
Kijk aan de rechterkant van het Power Query-venster. Er is een lijst met de naam Applied Steps. Het is een directe audittrail van al uw stappen. Klik op een tandwielpictogram om uw keuzes in die stap te wijzigen en de wijzigingen door de toekomstige stappen te laten lopen. Klik op een stap om te zien hoe de gegevens er vóór die stap uitzagen.
Als u klaar bent met het opschonen van de gegevens, klikt u op Sluiten en laden, zoals hieronder wordt weergegeven.
Tip
Als uw gegevens meer dan 1.048.576 rijen bevatten, kunt u de vervolgkeuzelijst Sluiten en laden gebruiken om de gegevens rechtstreeks naar het Power Pivot-gegevensmodel te laden, dat plaats biedt aan 995 miljoen rijen als u voldoende geheugen op de machine hebt geïnstalleerd.
Binnen enkele seconden verschijnen uw getransformeerde gegevens in Excel. Geweldig.
De uitbetaling: schone data morgen met één klik
Maar nogmaals, het Power Query-verhaal gaat niet over de tijdwinst op dag 1. Wanneer u de gegevens selecteert die door Power Query worden geretourneerd, verschijnt een paneel Query's en verbindingen aan de rechterkant van Excel, met daarin een knop Vernieuwen. (We hebben hier een knop Bewerken nodig, maar omdat er geen is, moet u met de rechtermuisknop op de oorspronkelijke query klikken om de oorspronkelijke query te bekijken of wijzigingen aan te brengen).
Het is leuk om gegevens op dag 1 op te schonen. Ik vind het heerlijk om iets nieuws te doen. Maar als mijn manager het resulterende rapport ziet en zegt: “Prachtig. Kun je dit elke dag doen? " Ik groei al snel een hekel aan de verveling van het elke dag opschonen van dezelfde dataset.
Dus om dag 400 van het opschonen van de gegevens te demonstreren, heb ik het originele bestand volledig gewijzigd. Nieuwe producten, nieuwe klanten, kleinere aantallen, meer rijen, zoals hieronder weergegeven. Ik bewaar deze nieuwe versie van het bestand in hetzelfde pad en met dezelfde bestandsnaam als het originele bestand.
Als ik de querywerkmap open en op Vernieuwen klik, rapporteert Power Query binnen een paar seconden 92 rijen in plaats van 68 rijen.
Het opschonen van de gegevens op Dag 2, Dag 3, Dag, 4,… Dag 400,… Dag Infinity kost nu twee klikken.
Dit ene voorbeeld bekrast alleen het oppervlak van Power Query. Als je twee uur met het boek bezig bent, M is voor (Data) Monkey van Ken Puls en Miguel Escobar, leer je over andere functies, zoals deze:
- Combineer alle Excel- of CSV-bestanden uit een map in één Excel-raster
- Het omzetten van een cel met Appel; Banaan; Kers; Dille; Aubergine naar vijf rijen in Excel
- Een VERT.ZOEKEN uitvoeren naar een opzoekwerkmap terwijl u gegevens naar Power Query overbrengt
- Van een enkele query een functie maken die op elke rij in Excel kan worden toegepast
Voor een volledige beschrijving van Power Query, bekijk M Is for (Data) Monkey van Ken Puls en Miguel Escobar. Eind 2019 zal de hernoemde tweede editie, Master Your Data, beschikbaar zijn.
Met dank aan Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser en Colin Michael voor het nomineren van Power Query.
