Power Query er et nytt verktøy fra Microsoft for å pakke ut, transformere og laste inn data. Dagens artikkel handler om behandling av alle filer i en mappe.
Power Query er innebygd i Excel 2016 og er tilgjengelig som gratis nedlasting i visse versjoner av Excel 2010 og Excel 2013. Verktøyet er designet for å trekke ut, transformere og laste data til Excel fra en rekke kilder. Den beste delen: Power Query husker trinnene dine og vil spille dem av når du vil oppdatere dataene. Når denne boken kommer til å trykke, er Power Query-funksjonene i Excel 2016 i Data-fanen, i Get & Transform-gruppen, under New Query. Det er vanskelig å forutsi om Microsoft vil endre navn på Power Query med tilbakevirkende kraft til Get & Transform i Excel 2010 og Excel 2013.
Dette gratis tillegget er så fantastisk at det kan være en hel bok om det. Men som et av de 40 beste tipsene mine, vil jeg dekke noe veldig enkelt: bringe en liste over filer til Excel, sammen med dato for opprettelse av fil og kanskje størrelse. Dette er nyttig for å lage en liste over budsjettarbeidsbøker eller en liste over bilder.
I Excel 2016 velger du Data, Ny spørring, Fra fil, Fra mappe. I tidligere Excel-versjoner, bruk Power Query, From File, From Folder. Spesifiser mappen:
Mens du redigerer spørringen, høyreklikker du kolonnene du ikke vil ha, og velger Fjern.
For å få filstørrelse, klikk på dette ikonet i attributtekolonnen:
En liste over ekstra attributter vises. Velg størrelse.
En stor liste med Transform-alternativer er tilgjengelig.
når du er ferdig med å redigere spørringen, klikker du Lukk og last.
Dataene lastes til Excel som en tabell.
Senere, for å oppdatere tabellen, velger du Data, oppdater alle. Excel husker alle trinnene og oppdaterer tabellen med en gjeldende liste over filer i mappen.
For en fullstendig beskrivelse av funksjonen tidligere kjent som Power Query, sjekk ut M er for (Data) Monkey av Ken Puls og Miguel Escobar.
M er for (DATA) AAP »
Takk til Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser og Colin Michael for å nominere Power Query.
Se på video
- Power Query-verktøyene er i Data-fanen i Excel 2016
- Gratis tillegg for 2010 og 2013
- Liste opp alle filene fra en mappe i Excel-rutenettet ved hjelp av Power Query
- Velg Ny spørring, Fra fil, Fra mappe
- Ikke opplagt: utvid attributtfeltet for å få størrelse
- Hvis dataene dine er i CSV-filer, kan du importere alle filene samtidig i et enkelt rutenett
- Fremme overskriftsraden
- Slett de gjenværende toppradene
- Erstatt "" med null
- Fyll ut for oversikten
- Slett den totale totalkolonnen
- Unpivot dataene
- Formel for å konvertere månedsnavn til datoer
- Komplett liste over trinn - verdens største angre
- Neste dag - oppdater spørringen for å gjøre alle trinnene på nytt
Transkripsjon av videoen
- Power Query er innebygd i Windows-versjoner av Excel 2016. Se på Data-fanen i Get & Transform-gruppen. Hvis du har 2010 eller
- 2013 så lenge du kjører Windows
- og ikke Mac alt som er her i Get & Transform
- du kan laste ned gratis fra Microsoft. Bare søk etter
- Last ned Power Query.
- I dag er jeg interessert i å bruke Power Query for å få en filliste. Jeg
- ønsker å liste opp alle filene i en mappe.
- Kanskje jeg trenger å se hvilke filer som er
- store filer eller jeg trenger å sortere eller jeg trenger
- du vet å få en kombinasjon av deg
- kjenner til budsjettfilene vi sendte ut
- og deretter en annen mappe hvilke
- vi kom kom tilbake.
- For å starte, gå til Data, Get & Tranform, From File, From Folder.
- Lim inn i mappebanen eller bruk Bla gjennom-knappen.
- Klikk OK, og de viser meg dette
- forhåndsvisning. Velg Rediger.
- Et par ting her ser du at vi har
- filnavnet utvidelsen datoen
- åpnet, dato endret, dato opprettet.
- Det er egentlig ikke åpenbart at dette symbolet ved siden av Attributter-overskriften betyr Utvid. Klikk på det symbolet, så er det flere ting i
- her og hvis du klikker på dette symbolet så vil jeg
- kan gå inn og få ting som filstørrelse
- eller hvis det er skrivebeskyttet og lignende
- at så i dette tilfellet vil jeg bare ha fil
- størrelse. Velg Filstørrelse. Klikk ok. De gir deg et nytt felt med navnet Attributes.Size.
- Jeg kan se hvor mange byte det er i
- hver fil.
- Kanskje jeg ikke trenger alt her kanskje
- Jeg trenger ikke datoen som er opprettet slik at jeg kan
- høyreklikk og si at jeg vil
- fjern den kolonnen. Dette
- binær Jeg trenger ikke det vil fjerne
- den kolonnen. Klikk på Lukk og last fra båndet.
- Om noen sekunder vil du ha en sorterbar oversikt over
- alt i den mappen hvis mappen
- endringer jeg kan komme inn her og jeg kan
- oppdater spørringen, og den vil gå tilbake
- ut og trekk dataene i riktig dette er
- for meg er dette et problem vi pleide å gjøre
- har hele tiden vi vil sende ut 200
- budsjettfiler
- og du får noen tilbake, ikke alle
- tilbake må du kunne sammenligne det
- nå kan jeg egentlig gjøre en vlookup
- mellom mappene.
- Det er bare utrolig hvordan
- kult det er, men se, la oss gå utover
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- 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 boka vil lære
- deg alt om strømforespørselen
- grensesnitt det er en fantastisk bok best
- bok om strømforespørsel om alt jeg lærte
- Jeg lærte av denne boka. Jeg kom på en flytur fra
- Orlando til Dallas - Jeg leste hele boka
- og min kunnskap om kraftforespørsel bare
- steg om to timer kan du være opp til
- hastighet og bytt ut ting som du ville
- har vært vant med å ha gjort med VBA.
Last ned fil
Last ned eksempelfilen her: Podcast2037.xlsx
