Power Query är inbyggt i Windows-versioner av Office 365, Excel 2016, Excel 2019 och finns som gratis nedladdning i Windows-versioner av Excel 2010 och Excel 2013. Verktyget är utformat för att extrahera, omvandla och ladda data till Excel från en olika källor. Det bästa: Power Query kommer ihåg dina steg och spelar upp dem när du vill uppdatera data. Det betyder att du kan städa data på dag 1 i 80% av normal tid, och du kan städa data på dag 2 till 400 genom att helt enkelt klicka på Uppdatera.
Jag säger detta om många nya Excel-funktioner, men det här är verkligen den bästa funktionen att träffa Excel på 20 år.
Jag berättar en historia i mina live-seminarier om hur Power Query uppfanns som en krycka för SQL Server Analysis Services-kunder som tvingades använda Excel för att komma åt Power Pivot. Men Power Query fortsatte att bli bättre, och varje person som använder Excel borde ta sig tid att lära sig Power Query.
Få Power Query
Du kanske redan har Power Query. Det finns i gruppen Get & Transform på fliken Data.
Men om du är i Excel 2010 eller Excel 2013, gå till Internet och sök efter Ladda ner Power Query. Dina Power Query-kommandon kommer att visas på en särskild Power Query-flik i menyfliksområdet.
Rengör data första gången i Power Query
För att ge dig ett exempel på några av de fantastiska i Power Query, säg att du får filen som visas nedan varje dag. Kolumn A är inte ifylld. Kvartal går över istället för ner på sidan.
För att börja, spara den arbetsboken på din hårddisk. Lägg den på en förutsägbar plats med ett namn som du kommer att använda för den filen varje dag.
I Excel väljer du Hämta data, från fil, från arbetsbok.
Bläddra till arbetsboken. Klicka på Sheet1 i förhandsgranskningsfönstret. Istället för att klicka på Ladda, klicka på Redigera. Du ser nu arbetsboken i ett något annat rutnät - Power Query-rutnätet.
Nu måste du fixa alla tomma celler i kolumn A. Om du skulle göra detta i Excel-användargränssnittet är den otrevliga kommandosekvensen Hem, Sök och välj, Gå till Special, Tomt, lika, Uppåtpil, Ctrl + Enter .
I Power Query väljer du Transform, Fill, Down.
Alla nollvärden ersätts med värdet ovanifrån. Med Power Query tar det tre klick istället för sju.
Nästa problem: Kvartalen går över istället för ner. I Excel kan du fixa detta med en pivottabell för flera konsolideringsintervall. Detta kräver 12 steg och 23+ klick.
Markera de två kolumner som inte är kvartal i Power Query. Öppna rullgardinsmenyn Unpivot Columns på fliken Transform och välj Unpivot Other Column, som visas nedan.
Högerklicka på den nyligen skapade attributkolumnen och byt namn på den i stället för attribut. Tjugo plus klick i Excel blir fem klick i Power Query.
För att vara rättvis är inte alla rengöringssteg kortare i Power Query än i Excel. Att ta bort en kolumn innebär fortfarande att högerklicka på en kolumn och välja Ta bort kolumn. Men för att vara ärlig handlar historien här inte om tidsbesparingar på dag 1.
Men vänta: Power Query kommer ihåg alla dina steg
Titta på höger sida av Power Query-fönstret. Det finns en lista som heter Applied Steps. Det är en omedelbar granskning av alla dina steg. Klicka på en kugghjulsikon för att ändra dina val i det steget och få ändringarna att kaskadas genom de framtida stegen. Klicka på valfritt steg för att se hur data såg ut före det steget.
När du är klar med rengöring av data klickar du på Stäng och ladda som visas nedan.
Dricks
Om dina data är mer än 1 048 576 rader kan du använda rullgardinsmenyn Stäng och ladda för att ladda data direkt till Power Pivot-datamodellen, som rymmer 995 miljoner rader om du har tillräckligt med minne installerat på maskinen.
På några sekunder visas dina transformerade data i Excel. Grymt bra.
Utdelningen: Ren data imorgon med ett klick
Men återigen handlar Power Query-historien inte om tidsbesparingar på dag 1. När du väljer de data som returneras av Power Query visas en fråga och anslutningar-panelen till höger om Excel och på den finns en uppdateringsknapp. (Vi behöver en Redigera-knapp här, men eftersom det inte finns någon måste du högerklicka på den ursprungliga frågan för att visa eller göra ändringar i den ursprungliga frågan).
Det är kul att städa data på dag 1. Jag älskar att göra något nytt. Men när min chef ser den resulterande rapporten och säger ”Vacker. Kan du göra det varje dag? ” Jag börjar snabbt hata tråkigheten att städa samma datauppsättning varje dag.
Så för att visa dag 400 för rengöring av data har jag ändrat originalfilen fullständigt. Nya produkter, nya kunder, mindre antal, fler rader, som visas nedan. Jag sparar den här nya versionen av filen i samma sökväg och med samma filnamn som originalfilen.
Om jag öppnar sökarbetsboken och klickar på Uppdatera inom några sekunder rapporterar Power Query 92 rader istället för 68 rader.
Rengöring av data på dag 2, dag 3, dag, 4, … dag 400, … dag oändlighet tar nu två klick.
Det här exemplet repar bara ytan på Power Query. Om du spenderar två timmar med boken, M är för (Data) Monkey av Ken Puls och Miguel Escobar, kommer du att lära dig om andra funktioner, som dessa:
- Kombinera alla Excel- eller CSV-filer från en mapp till ett enda Excel-rutnät
- Konvertera en cell med Apple; Banan; Körsbär; Dill; Aubergine till fem rader i Excel
- Gör en VLOOKUP till en uppslagsarbetsbok när du tar med data till Power Query
- Gör en enda fråga till en funktion som kan tillämpas på varje rad i Excel
För en fullständig beskrivning av Power Query, kolla in M Is for (Data) Monkey av Ken Puls och Miguel Escobar. I slutet av 2019 kommer den andra titeln, Master Your Data, att finnas tillgänglig.
Tack till Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser och Colin Michael för att de nominerade Power Query.
