Power Query er et nyt værktøj fra Microsoft til at udpakke, transformere og indlæse data. Dagens artikel handler om behandling af alle filer i en mappe.
Power Query er indbygget i Excel 2016 og er tilgængelig som en gratis download i visse versioner af Excel 2010 og Excel 2013. Værktøjet er designet til at udtrække, omdanne og indlæse data til Excel fra en række forskellige kilder. Den bedste del: Power Query husker dine trin og afspiller dem, når du vil opdatere dataene. Efterhånden som denne bog trykkes, er Power Query-funktionerne i Excel 2016 under fanen Data i gruppen Get & Transform under Ny forespørgsel. Det er svært at forudsige, om Microsoft med tilbagevirkende kraft omdøber Power Query til Get & Transform i Excel 2010 og Excel 2013.
Denne gratis tilføjelse er så fantastisk, at der kunne være en hel bog om det. Men som et af mine top 40 tip vil jeg dække noget meget simpelt: at bringe en liste med filer til Excel sammen med dato for oprettelse af fil og måske størrelse. Dette er nyttigt til oprettelse af en liste over budgetarbejdsbøger eller en liste over fotos.
I Excel 2016 skal du vælge Data, Ny forespørgsel, Fra fil, Fra mappe. I tidligere Excel-versioner skal du bruge Power Query, From File, From Folder. Angiv mappen:
Under redigering af forespørgslen skal du højreklikke på de kolonner, du ikke ønsker, og vælge Fjern.
For at få filstørrelse skal du klikke på dette ikon i kolonnen Attributter:
En liste over ekstra attributter vises. Vælg størrelse.
En stor liste med Transform-indstillinger er tilgængelige.
når du er færdig med at redigere forespørgslen, skal du klikke på Luk og indlæs.
Dataene indlæses til Excel som en tabel.
Senere, for at opdatere tabellen, skal du vælge Data, Opdater alle. Excel husker alle trin og opdaterer tabellen med en aktuel liste over filer i mappen.
For en komplet beskrivelse af funktionen tidligere kendt som Power Query, se M er for (Data) Monkey af Ken Puls og Miguel Escobar.
M er til (DATA) ABE »
Tak til Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser og Colin Michael for at have nomineret Power Query.
Se video
- Power Query-værktøjerne findes på fanen Data i Excel 2016
- Gratis tilføjelse i 2010 og 2013
- Liste over alle filer fra en mappe i Excel-gitteret ved hjælp af Power Query
- Vælg Ny forespørgsel, Fra fil, fra mappe
- Ikke indlysende: udvid attributfeltet for at få størrelse
- Hvis dine data er i CSV-filer, kan du importere alle filerne på én gang i et enkelt gitter
- Fremme overskriftsrækken
- Slet de resterende overskriftsrækker
- Erstat "" med null
- Udfyld for oversigtsoversigten
- Slet kolonnen i alt
- Frigør dataene
- Formel til at konvertere månedsnavne til datoer
- Komplet liste over trin - verdens største fortrydelse
- Næste dag - opdater forespørgslen for at udføre alle trin igen
Udskrift af videoen
- Power Query er indbygget i Windows-versioner af Excel 2016. Se på fanen Data i gruppen Get & Transform. Hvis du har 2010 eller
- 2013, så længe du kører Windows
- og ikke Mac alt, hvad der er her i Get & Transform
- du kan downloade gratis fra Microsoft. Bare søg efter
- Download Power Query.
- I dag er jeg interesseret i at bruge Power Query til at få en filliste. jeg
- ønsker at liste alle filerne i en mappe.
- Måske skal jeg se, hvilke filer der er
- store filer, eller jeg skal sortere, eller jeg har brug for
- du ved at få en kombination af dig
- kender de budgetfiler, vi har sendt ud
- og derefter en anden mappe hvilke
- vi kom kom tilbage.
- For at starte skal du gå til Data, Get & Tranform, From File, From Folder.
- Indsæt i mappestien, eller brug knappen Gennemse.
- Klik på OK, og de viser mig dette
- forhåndsvisning. Vælg Rediger.
- Et par ting her ser du, at vi har
- filnavnet udvidelsen datoen
- adgang, dato ændret, dato oprettet.
- Det er virkelig ikke indlysende, at dette symbol ved siden af Attributter-overskriften betyder Udvid. Klik på det symbol, og der er flere ting i
- her, og hvis du klikker på dette symbol, så vil jeg
- kan gå ind og få ting som filstørrelse
- eller hvis det er skrivebeskyttet og lignende
- at så i dette tilfælde vil jeg bare have en fil
- størrelse. Vælg filstørrelse. Klik på ok. De giver dig et nyt felt med navnet Attributter.Størrelse.
- Jeg kan se, hvor mange byte der er
- hver fil.
- Måske har jeg måske ikke brug for alt her
- Jeg har ikke brug for den oprettede dato, så jeg kan
- højreklik og sig, at jeg vil
- fjern den kolonne. Dette
- binær Jeg har ikke brug for det fjerner
- den kolonne. Klik på Luk & indlæs i båndet.
- Om få sekunder får du en sorterbar visning af
- alt i den mappe, hvis mappen
- ændringer, jeg kan komme ind her, og jeg kan
- opdater forespørgslen, og den vil gå tilbage
- ud og træk disse data til højre, dette er
- for mig er dette et problem, vi plejede
- har hele tiden vi ville sende 200 ud
- budgetfiler
- og du får nogen tilbage, ikke alle dem
- tilbage skal du kunne sammenligne det
- nu kan jeg i det væsentlige lave en vlookup
- mellem mapper.
- Det er bare forbløffende hvordan
- sejt det er, men se, lad os gå videre
- hvad jeg har i bogen og viser dig hvordan
- det er bare toppen af isbjerget.
- Jeg opretter endnu en forespørgsel. Data, ny forespørgsel, fra fil, fra mappe.
- Jeg kopierer den mappesti her.
- klik på rediger.
- Fra oktober 2016 fungerer dette trick kun med CSV
- filer, men i 2017 blev den opdateret til at arbejde med Excel-ark med enkelt ark. jeg
- har en mappe en hel masse filer og
- Jeg vil oprette et excel-gitter med alle
- af dataene fra alle disse filer.
- Det er overhovedet ikke intuitivt. Se ud for overskriften til kolonnen Binær. Der er et ikon med to pile, der peger nedad på en vandret linje.
- Klik på det.
- BAM! det trak bare hver eneste rekord ind fra
- hver eneste fil i den mappe!
- Er det ikke
- det fantastiske, jeg mener, det var en VBA-makro
- før, og det tager måneder at lære VBA
- makroer, du kan lære strømforespørgsel om ti
- minutter.
- Vi er nødt til at vælge denne kolonne og
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at 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.
- Denne bog vil lære
- alt om strømforespørgslen
- interface er det en fantastisk bog bedst
- bog om magtforespørgsel alt, hvad jeg lærte
- Jeg lærte af denne bog. Jeg kom på en flyvning fra
- Orlando til Dallas - Jeg læste hele bogen
- og min viden om magtforespørgsel bare
- steget om to timer, du kan være op til
- hastighed og udskift ting, som du ville
- har haft en vant til at have gjort med VBA.
Download fil
Download eksempelfilen her: Podcast2037.xlsx
