Power Query er innebygd i Windows-versjoner av Office 365, Excel 2016, Excel 2019 og er tilgjengelig som gratis nedlasting i Windows-versjoner av Excel 2010 og Excel 2013. Verktøyet er designet for å trekke ut, transformere og laste data til Excel fra en en rekke kilder. Den beste delen: Power Query husker trinnene dine og vil spille dem av når du vil oppdatere dataene. Dette betyr at du kan rense data på dag 1 på 80% av normal tid, og du kan rense data på dag 2 til 400 ved å klikke på Oppdater.
Jeg sier dette om mange nye Excel-funksjoner, men dette er virkelig den beste funksjonen for å treffe Excel på 20 år.
Jeg forteller en historie i live-seminarene mine om hvordan Power Query ble oppfunnet som en krykke for SQL Server Analysis Services-kunder som ble tvunget til å bruke Excel for å få tilgang til Power Pivot. Men Power Query ble stadig bedre, og alle som bruker Excel, bør ta seg tid til å lære Power Query.
Få Power Query
Du har kanskje allerede Power Query. Det er i Get & Transform-gruppen i kategorien Data.
Men hvis du er i Excel 2010 eller Excel 2013, kan du gå til Internett og søke etter Last ned Power Query. Power Query-kommandoene dine vises i en dedikert Power Query-fane i båndet.
Rengjør data første gang i Power Query
For å gi deg et eksempel på noe av den fantastiske Power Query, si at du får filen vist nedenfor hver dag. Kolonne A er ikke fylt ut. Kvartaler går over i stedet for nedover på siden.
For å starte, lagre arbeidsboken på harddisken. Sett det på et forutsigbart sted med et navn du vil bruke til den filen hver dag.
I Excel velger du Få data, Fra fil, Fra arbeidsbok.
Bla til arbeidsboken. I forhåndsvisningsruten klikker du på Ark1. I stedet for å klikke Last, klikk på Rediger. Du ser nå arbeidsboken i et litt annet nett - Power Query-nettet.
Nå må du fikse alle de tomme cellene i kolonne A. Hvis du skulle gjøre dette i Excel-brukergrensesnittet, er den uhåndterlige kommandosekvensen Hjem, Finn og velg, Gå til Spesial, Tomrom, Lik, Pil opp, Ctrl + Enter .
Velg Transform, Fill, Down i Power Query.
Alle nullverdiene erstattes med verdien ovenfra. Med Power Query tar det tre klikk i stedet for syv.
Neste problem: Kvartalene går over i stedet for ned. I Excel kan du fikse dette med en pivottabell for flere konsolideringsområder. Dette krever 12 trinn og 23+ klikk.
Velg de to kolonnene som ikke er kvartaler i Power Query. Åpne rullegardinlisten Unpivot Columns i kategorien Transform og velg Unpivot Other Columns, som vist nedenfor.
Høyreklikk på den nyopprettede attributtkolonnen og gi den navnet Quarter i stedet for attributt. Tjue pluss klikk i Excel blir fem klikk i Power Query.
For å være rettferdig er ikke hvert rengjøringstrinn kortere i Power Query enn i Excel. Å fjerne en kolonne betyr fortsatt å høyreklikke på en kolonne og velge Fjern kolonne. Men for å være ærlig handler historien her ikke om tidsbesparelser på dag 1.
Men vent: Power Query husker alle trinnene dine
Se på høyre side av Power Query-vinduet. Det er en liste som heter Applied Steps. Det er en øyeblikkelig revisjonsspor av alle trinnene dine. Klikk på hvilket som helst tannhjulikon for å endre valgene dine i det trinnet og få endringene til å gå gjennom de fremtidige trinnene. Klikk på et trinn for å se hvordan dataene så ut før trinnet.
Når du er ferdig med å rengjøre dataene, klikker du på Lukk og last som vist nedenfor.
Tips
Hvis dataene dine er på mer enn 1.048.576 rader, kan du bruke rullegardinmenyen Lukk og last for å laste dataene direkte til Power Pivot Data Model, som har plass til 995 millioner rader hvis du har nok minne installert på maskinen.
I løpet av få sekunder vises de transformerte dataene dine i Excel. Rått.
Utbyttet: Rene data i morgen med ett klikk
Men igjen handler Power Query-historien ikke om tidsbesparelser på dag 1. Når du velger dataene som returneres av Power Query, vises et Queries & Connections-panel på høyre side av Excel, og det er en Oppdater-knapp. (Vi trenger en Rediger-knapp her, men fordi det ikke er en, må du høyreklikke på den opprinnelige spørringen for å se eller gjøre endringer i den opprinnelige spørringen).
Det er morsomt å rense data på dag 1. Jeg elsker å gjøre noe nytt. Men når lederen min ser den resulterende rapporten og sier “Vakker. Kan du gjøre dette hver dag? ” Jeg blir fort til å hate kjedelig å rengjøre det samme datasettet hver dag.
Så, for å demonstrere dag 400 med rengjøring av dataene, har jeg endret originalfilen fullstendig. Nye produkter, nye kunder, mindre antall, flere rader, som vist nedenfor. Jeg lagrer denne nye versjonen av filen i samme bane og med samme filnavn som originalfilen.
Hvis jeg åpner spørringsarbeidsboken og klikker på Oppdater på få sekunder, rapporterer Power Query 92 rader i stedet for 68 rader.
Rengjøring av dataene på dag 2, dag 3, dag, 4, … dag 400, … dag uendelig tar nå to klikk.
Dette ene eksemplet skraper bare overflaten til Power Query. Hvis du bruker to timer med boka M er for (Data) Monkey av Ken Puls og Miguel Escobar, vil du lære om andre funksjoner, som disse:
- Kombinere alle Excel- eller CSV-filer fra en mappe til et enkelt Excel-rutenett
- Konvertere en celle med Apple; Banan; Kirsebær; Dill; Aubergine til fem rader i Excel
- Gjør en VLOOKUP til en oppslagsarbeidsbok når du tar med data i Power Query
- Gjør et enkelt spørsmål til en funksjon som kan brukes på hver rad i Excel
For en fullstendig beskrivelse av Power Query, sjekk ut M Is for (Data) Monkey av Ken Puls og Miguel Escobar. Mot slutten av 2019 vil den andre tittelen Master's Data være tittelen tilgjengelig.
Takk til Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser og Colin Michael for å nominere Power Query.
