Power Query este un instrument nou de la Microsoft pentru a extrage, transforma și încărca date. Articolul de astăzi este despre procesarea tuturor fișierelor dintr-un folder.
Power Query este integrat în Excel 2016 și este disponibil ca descărcare gratuită în anumite versiuni de Excel 2010 și Excel 2013. Instrumentul este conceput pentru a extrage, transforma și încărca date în Excel dintr-o varietate de surse. Cea mai bună parte: Power Query vă amintește pașii și îi va reda atunci când doriți să reîmprospătați datele. Pe măsură ce apare această carte, caracteristicile Power Query din Excel 2016 se află în fila Date, în grupul Obțineți și transformați, sub Interogare nouă. Este greu de prezis dacă Microsoft va redenumi retroactiv Power Query pentru a obține și transforma în Excel 2010 și Excel 2013.
Acest supliment gratuit este atât de uimitor, încât ar putea exista o carte întreagă despre el. Dar ca unul dintre primele mele 40 de sfaturi, vreau să acoper ceva foarte simplu: aducerea unei liste de fișiere în Excel, împreună cu data creării fișierului și poate cu dimensiunea. Acest lucru este util pentru crearea unei liste de registre de lucru bugetare sau a unei liste de fotografii.
În Excel 2016, selectați Date, Interogare nouă, Din fișier, Din folder. În versiunile anterioare de Excel, utilizați Power Query, From File, From Folder. Specificați folderul:
În timp ce editați interogarea, faceți clic dreapta pe orice coloane pe care nu le doriți și alegeți Eliminare.
Pentru a obține dimensiunea fișierului, faceți clic pe această pictogramă din coloana Atribute:
Apare o listă de atribute suplimentare. Alegeți Dimensiune.
Este disponibilă o listă largă de opțiuni de transformare.
când ați terminat de editat interogarea, faceți clic pe Închidere și încărcare.
Datele se încarcă în Excel ca tabel.
Mai târziu, pentru a actualiza tabelul, selectați Date, Reîmprospătați toate. Excel își amintește toți pașii și actualizează tabelul cu o listă curentă de fișiere din folder.
Pentru o descriere completă a caracteristicii cunoscute anterior sub numele de Power Query, consultați M este pentru (Data) Monkey de Ken Puls și Miguel Escobar.
M este pentru (DATA) MAIMĂ »
Mulțumim lui Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser și Colin Michael pentru nominalizarea Power Query.
Urmăriți videoclipul
- Instrumentele Power Query se află în fila Date din Excel 2016
- Supliment gratuit pentru 2010 și 2013
- Listează toate fișierele dintr-un folder în grila Excel utilizând Power Query
- Alegeți Interogare nouă, Din fișier, Din folder
- Nu este evident: extindeți câmpul atribut pentru a obține dimensiunea
- Dacă datele dvs. se află în fișiere CSV, puteți importa toate fișierele simultan într-o singură grilă
- Promovați rândul de titlu
- Ștergeți rândurile de antet rămase
- Înlocuiți „” cu nul
- Completați pentru vizualizarea conturului
- Ștergeți coloana total mare
- Dezimpivotați datele
- Formula pentru a converti numele lunilor în date
- Lista completă a pașilor - cea mai mare anulare a lumii
- A doua zi - reîmprospătați interogarea pentru a reface toți pașii
Transcrierea videoclipului
- Power Query este încorporat în versiunile Windows ale Excel 2016. Căutați în fila Date din grupul Obțineți și transformați. Dacă aveți 2010 sau
- 2013 atâta timp cât rulați Windows
- și nu Mac tot ce este aici în Get & Transform
- puteți descărca gratuit de la Microsoft. Doar căutați
- Descărcați Power Query.
- Astăzi, sunt interesat să folosesc Power Query pentru a obține o listă de fișiere. Eu
- doriți să listați toate fișierele dintr-un folder.
- Poate că trebuie să văd care sunt fișierele
- fișiere mari sau trebuie să sortez sau am nevoie
- știi să obții o combinație a ta
- cunoașteți fișierele de buget pe care le-am trimis
- și apoi un alt folder care
- ne-am întors.
- Pentru a începe, accesați Date, Get & Tranform, From File, From Folder.
- Lipiți în calea folderului sau utilizați butonul Răsfoire.
- Faceți clic pe OK și îmi arată acest lucru
- previzualizare. Alegeți Editați.
- Vedeți că avem câteva lucruri aici
- numele fișierului extensia data
- accesat, data modificării, data creării.
- Nu este evident că acest simbol de lângă titlul Atribute înseamnă Extindere. Faceți clic pe simbolul respectiv și există mai multe lucruri
- aici și dacă faceți clic pe acest simbol, atunci eu
- poate intra și obține lucruri precum dimensiunea fișierului
- sau dacă este doar în citire și lucruri de genul
- că așa că în acest caz vreau doar fișier
- mărimea. Alegeți Dimensiune fișier. Faceți clic pe OK. Vă oferă un câmp nou cu un nume de atribute.
- Pot vedea câți octeți sunt
- fiecare fișier.
- Poate că nu am nevoie de tot aici, poate
- Nu am nevoie de data creată, așa că pot
- faceți clic dreapta și spuneți că vreau
- eliminați coloana respectivă. Acest
- binar nu am nevoie care să fie eliminat
- acea coloană. Din panglică, faceți clic pe Închidere și încărcare.
- În câteva secunde, veți avea o vizualizare sortabilă a
- totul din acel folder dacă folderul
- schimbări pot veni aici și pot
- reîmprospătați interogarea și va reveni
- scoateți și trageți acele date în dreapta, acesta este
- pentru mine aceasta este o problemă pe care o obișnuiam
- am tot timpul am trimite 200
- fișiere bugetare
- iar tu iei pe cineva înapoi, nu pe toți
- înapoi trebuie să poți compara astfel
- acum pot în esență să fac un vlookup
- între dosare.
- Este uimitor cum
- mișto, dar uite să mergem dincolo
- 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.
- Această carte va învăța
- tu totul despre interogarea de putere
- interfață este o carte uimitoare cea mai bună
- carte despre putere interogă tot ce am învățat
- Am învățat din această carte. Am urcat pe un zbor de la
- Orlando la Dallas - am citit întreaga carte
- iar cunoștințele mele despre putere interogă doar
- a crescut în două ore pe care le poți face
- accelerați și înlocuiți lucrurile pe care le-ați face
- am avut un lucru obișnuit cu VBA.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2037.xlsx
