Power Query är ett nytt verktyg från Microsoft för att extrahera, transformera och ladda data. Dagens artikel handlar om att bearbeta alla filer i en mapp.
Power Query är inbyggt i Excel 2016 och finns tillgängligt som gratis nedladdning i vissa versioner av Excel 2010 och Excel 2013. Verktyget är utformat för att extrahera, omvandla och ladda data till Excel från en mängd olika källor. Det bästa: Power Query kommer ihåg dina steg och spelar upp dem när du vill uppdatera data. När den här boken trycks finns Power Query-funktionerna i Excel 2016 på fliken Data i gruppen Get & Transform under Ny fråga. Det är svårt att förutsäga om Microsoft kommer att byta namn på Power Query med efteråt för att få och omvandla i Excel 2010 och Excel 2013.
Det här gratis tillägget är så fantastiskt att det kan finnas en hel bok om det. Men som ett av mina 40 bästa tipsen vill jag täcka något väldigt enkelt: att ta med en lista med filer till Excel, tillsammans med datum för filskapande och kanske storlek. Detta är användbart för att skapa en lista med budgetarbetsböcker eller en lista med foton.
I Excel 2016 väljer du Data, Ny fråga, Från fil, från mapp. I tidigare Excel-versioner använder du Power Query, From File, From Folder. Ange mappen:
När du redigerar frågan högerklickar du på de kolumner du inte vill ha och väljer Ta bort.
För att få filstorlek, klicka på den här ikonen i kolumnen Attribut:
En lista med extra attribut visas. Välj storlek.
En stor lista med Transform-alternativ är tillgängliga.
när du är klar med redigeringen av frågan klickar du på Stäng och ladda.
Data laddas till Excel som en tabell.
Senare, för att uppdatera tabellen, välj Data, Uppdatera alla. Excel kommer ihåg alla steg och uppdaterar tabellen med en aktuell lista över filer i mappen.
För en fullständig beskrivning av funktionen tidigare känd som Power Query, kolla in M is for (Data) Monkey av Ken Puls och Miguel Escobar.
M är för (DATA) Apa »
Tack till Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser och Colin Michael för att de nominerade Power Query.
Kolla på video
- Power Query-verktygen finns på fliken Data i Excel 2016
- Gratis tillägg för 2010 och 2013
- Lista alla filer från en mapp till Excel-rutnätet med Power Query
- Välj Ny fråga, Från fil, från mapp
- Inte uppenbart: expandera attributfältet för att få storlek
- Om dina data finns i CSV-filer kan du importera alla filerna samtidigt till ett enda rutnät
- Marknadsför rubrikraden
- Ta bort de återstående rubrikraderna
- Ersätt "" med null
- Fyll i för översiktsvy
- Radera den totala totalkolumnen
- Lossa data
- Formel för att konvertera månadsnamn till datum
- Komplett lista med steg - världens största ångra
- Nästa dag - uppdatera frågan för att göra alla stegen igen
Avskrift av videon
- Power Query är inbyggt i Windows-versioner av Excel 2016. Titta på fliken Data i gruppen Get & Transform. Om du har 2010 eller
- 2013 så länge du kör Windows
- och inte Mac allt som finns här i Get & Transform
- du kan ladda ner gratis från Microsoft. Sök bara efter
- Ladda ner Power Query.
- Idag är jag intresserad av att använda Power Query för att få en fillista. Jag
- vill lista alla filer i en mapp.
- Kanske måste jag se vilka filer som är
- stora filer eller jag måste sortera eller jag behöver
- du vet att få en kombination av dig
- känner till de budgetfiler som vi skickade ut
- och sedan en annan mapp vilka
- vi kom kom tillbaka.
- För att börja, gå till Data, Get & Tranform, From File, From Folder.
- Klistra in i mappvägen eller använd knappen Bläddra.
- Klicka på OK så visar de mig detta
- förhandsvisning. Välj Redigera.
- Ett par saker här ser du att vi har
- filnamnet förlängningen datumet
- åtkomst, datum ändrad, datum skapat.
- Det är verkligen inte uppenbart att den här symbolen bredvid rubriken Attribut betyder Expand. Klicka på den symbolen och det finns fler saker i
- här och om du klickar på den här symbolen så jag
- kan gå in och få saker som filstorlek
- eller om det är skrivskyddat och liknande
- att så i det här fallet vill jag bara filen
- storlek. Välj filstorlek. Klicka på ok. De ger dig ett nytt fält med namnet Attribut.Storlek.
- Jag kan se hur många byte som finns
- varje fil.
- Jag kanske inte behöver allt här kanske
- Jag behöver inte skapa det datum så jag kan
- högerklicka och säg att jag vill
- ta bort den kolumnen. Detta
- binär Jag behöver inte det tar bort
- den kolumnen. Klicka på Stäng och ladda in i menyfliksområdet.
- Om några sekunder får du en sorterbar bild av
- allt i den mappen om mappen
- förändringar kan jag komma in här och jag kan
- uppdatera frågan så går den tillbaka
- och dra ut datan i rätt, det här är
- för mig är detta ett problem som vi brukade göra
- har hela tiden vi skulle skicka ut 200
- budgetfiler
- och du får tillbaka någon, inte alla
- tillbaka måste du kunna jämföra det
- nu kan jag i princip göra en vlookup
- mellan mappar.
- Det är bara fantastiskt hur
- coolt det är men låt oss gå bortom
- vad jag har i boken och visar hur
- det är bara toppen av isberget.
- Jag ska skapa en ny fråga. Data, ny fråga, från fil, från mapp.
- Jag kopierar den mappvägen här.
- klicka på redigera.
- Från och med oktober 2016 fungerar detta trick bara med CSV
- filer, men 2017 uppdaterades den för att fungera med Excel-ark med en ark. Jag
- har en mapp en hel massa filer och
- Jag vill skapa ett excel-rutnät med alla
- av data från alla dessa filer.
- Det är inte intuitivt alls. Titta bredvid rubriken för kolumnen Binär. Det finns en ikon med två pilar som pekar nedåt på en horisontell linje.
- Klicka på det.
- BAM! det drog bara in varje skiva från
- varje enskild fil i den mappen!
- Är det inte?
- så fantastiskt menar jag att det var ett VBA-makro
- innan och det tar månader att lära sig VBA
- makron kan du lära dig kraftfråga på tio
- minuter.
- Vi måste välja den här kolumnen och
- gå för att ersätta värden säg att vi är
- ska
- ersätt ingenting med ordet null click
- Okej
- Det ger oss nollor istället för tomma celler.
- Dessa nollor tillåter oss att använda detta fantastiska
- med namnet Fill Down. Titta på det
- kolumn när jag väljer Fill Down. BAM det
- har precis dragit in all denna översiktsvy
- och sänkte värdet.
- Jag behöver inte kolumnen Grand Total.
- Högerklicka och ta bort.
- Nu vid den här tiden säger du åh ja vi kunde
- dra in det här och det skulle vara fantastiskt. Men om
- vi ville skapa en pivottabell från
- dessa data har en upprepande grupp igång
- över Jan Feb Mar är inte ett bra format
- för en pivottabell.
- Just nu har vi 47
- rader Jag måste ha 47 gånger tolv rader
- och att göra detta i en vanlig Excel-fil
- det är fruktansvärt med en multipel
- Konsolideringsområde som jag lärde mig av
- Mike Alexander på Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Denna bok kommer att lära ut
- du allt om kraftfrågan
- gränssnitt det är en fantastisk bok bäst
- bok om kraftfråga allt jag lärde mig
- Jag lärde mig från den här boken. Jag fick ett flyg från
- Orlando till Dallas - Jag läste hela boken
- och min kunskap om kraftfråga bara
- steg om två timmar kan du vara upp till
- snabba och ersätt saker som du skulle
- har varit van vid att ha gjort med VBA.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2037.xlsx
