Power Query novi je Microsoftov alat za izdvajanje, pretvaranje i učitavanje podataka. Današnji članak govori o obradi svih datoteka u mapi.
Power Query ugrađen je u Excel 2016 i dostupan je kao besplatno preuzimanje u određenim verzijama programa Excel 2010 i Excel 2013. Alat je dizajniran za izdvajanje, pretvaranje i učitavanje podataka u Excel iz različitih izvora. Najbolji dio: Power Query pamti vaše korake i reproducirat će ih kad želite osvježiti podatke. Kako se ova knjiga tiska, značajke Power Queryja u programu Excel 2016 nalaze se na kartici Podaci, u grupi Dohvati i transformiraj, pod Novi upit. Teško je predvidjeti hoće li Microsoft retroaktivno preimenovati Power Query u Get & Transform u programima Excel 2010 i Excel 2013.
Ovaj besplatni dodatak toliko je nevjerojatan da bi o njemu mogla postojati cijela knjiga. Ali kao jedan od mojih 40 najboljih savjeta, želim pokriti nešto vrlo jednostavno: unošenje popisa datoteka u Excel, zajedno s datumom izrade datoteke i možda veličinom. Ovo je korisno za stvaranje popisa proračunskih radnih knjiga ili popisa fotografija.
U programu Excel 2016 odabirete Podaci, Novi upit, Iz datoteke, Iz mape. U ranijim verzijama programa Excel koristite Power Query, From File, From Folder. Navedite mapu:
Tijekom uređivanja upita desnom tipkom miša kliknite bilo koji stupac koji ne želite i odaberite Ukloni.
Da biste dobili Veličina datoteke, kliknite ovu ikonu u stupcu Atributi:
Pojavljuje se popis dodatnih atributa. Odaberite Veličina.
Dostupan je veliki popis mogućnosti Transformacije.
kad ste gotovi s uređivanjem upita, kliknite Zatvori i učitaj.
Podaci se učitavaju u Excel kao tablica.
Kasnije, da biste ažurirali tablicu, odaberite Podaci, Osvježi sve. Excel pamti sve korake i ažurira tablicu trenutnim popisom datoteka u mapi.
Za cjelovit opis značajke koja se prije zvala Power Query, pogledajte M je za (Data) Monkey Kena Pulsa i Miguela Escobara.
M je za (DATA) MAJMUN »
Zahvaljujući Miguelu Escobaru, Robu Garciji, Mikeu Girvinu, Rayu Hauseru i Colinu Michaelu na nominaciji za Power Query.
Gledaj video
- Alati Power Query nalaze se na kartici Podaci u programu Excel 2016
- Besplatni dodatak za 2010. i 2013. godinu
- Popis svih datoteka iz mape u Excel mrežu pomoću Power Queryja
- Odaberite Novi upit, Iz datoteke, Iz mape
- Nije očito: proširite polje atributa da biste dobili veličinu
- Ako su vaši podaci u CSV datotekama, možete sve datoteke odjednom uvesti u jednu mrežu
- Promovirajte red naslova
- Izbrišite preostale retke zaglavlja
- Zamijenite "" s nulom
- Ispunite okvirni prikaz
- Izbrišite stupac ukupnog zbroja
- Uklonite podatke
- Formula za pretvaranje imena mjeseci u datume
- Potpuni popis koraka - najveće poništavanje na svijetu
- Sljedeći dan - osvježite upit da biste ponovno izvršili sve korake
Prijepis videozapisa
- Power Query ugrađen je u Windows verzije programa Excel 2016. Pogledajte karticu Podaci u grupi Dohvati i transformiraj. Ako imate 2010 ili
- 2013. sve dok imate Windows
- a ne Mac sve što je ovdje u Get & Transform
- možete besplatno preuzeti s Microsofta. Samo potražite
- Preuzmite Power Query.
- Danas me zanima upotreba Power Queryja za dobivanje popisa datoteka. Ja
- želite navesti sve datoteke u mapi.
- Možda moram vidjeti koje su datoteke
- velike datoteke ili ih moram razvrstati ili trebam
- znaš dobiti kombinaciju tebe
- znati proračunske datoteke koje smo poslali
- a zatim druga mapa koje
- vratili smo se vratili.
- Za početak idite na Podaci, Dohvati i preuzmi, Iz datoteke, Iz mape.
- Zalijepite u putanju mape ili upotrijebite gumb Pregledaj.
- Pritisnite U redu i oni će mi to pokazati
- pregled. Odaberite Uredi.
- Evo nekoliko stvari koje vidite ovdje imamo
- datoteci ime ekstenzije datum
- pristupljeno, datum izmjene, datum izrade.
- Zaista nije očito da ovaj simbol pored naslova Atributi znači Proširi. Pritisnite taj simbol i u njemu ima još stvari
- ovdje i ako kliknete ovaj simbol onda ja
- mogu ući i dobiti stvari poput veličine datoteke
- ili ako je samo za čitanje i slično
- tako da u ovom slučaju samo želim datoteku
- veličina. Odaberite Veličina datoteke. Kliknite ok. Daju vam novo polje s imenom Attributes.Size.
- Vidim koliko je bajtova
- svaka datoteka.
- Možda mi ovdje ne treba sve
- Ne treba mi datum kreiranja da bih mogao
- desni klik i reci da želim
- ukloni taj stupac. Ovaj
- binarni ne trebam to će ukloniti
- ta kolona. Na vrpci kliknite Zatvori i učitaj.
- Za nekoliko sekundi imat ćete razvrstavajući pogled na
- sve u toj mapi ako je mapa
- promjene mogu doći ovdje i mogu
- osvježite upit i vratit će se natrag
- izvucite te podatke točno, ovo je
- za mene je ovo problem koji smo nekada imali
- imamo svo vrijeme koje bismo poslali 200
- proračunske datoteke
- i vratite nekome ne sve
- natrag morate biti u mogućnosti to usporediti
- sada u osnovi mogu napraviti vlookup
- između mapa.
- Nevjerojatno je kako
- cool je, ali pogledajte idemo dalje
- što imam u knjizi i pokazati vam kako
- to je samo vrh ledenog brijega.
- Stvorit ću još jedan upit. Podaci, novi upit, iz datoteke, iz mape.
- Ovdje ću kopirati taj put do mape.
- kliknite uredi.
- Od listopada 2016. ovaj trik funkcionira samo s CSV-om
- datoteke, ali je 2017. ažuriran za rad s Excel-ovim datotekama s jednim listom. Ja
- ima mapu cijelu gomilu datoteka i
- Želim stvoriti jednu excel mrežu sa svima
- podataka iz svih ovih datoteka.
- To uopće nije intuitivno. Pogledajte pored naslova za binarni stupac. Postoji ikona s dvije strelice usmjerene prema dolje na vodoravnu crtu.
- Kliknite to.
- BAM! upravo je povukao svaki pojedini zapis od
- svaka pojedinačna datoteka u toj mapi!
- Nije
- to nevjerojatno mislim da je to bio VBA makro
- prije i trebaju mjeseci za učenje VBA
- makronaredbe možete naučiti upit snage za deset
- minuta.
- Moramo odabrati ovaj stupac i
- 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.
- Ova knjiga će podučavati
- sve o upitu napajanja
- sučelje je nevjerojatna knjiga najbolja
- knjiga o ispitivanju napajanja sve što sam naučio
- Naučio sam iz ove knjige. Došla sam na let iz
- Orlando do Dallasa - pročitao sam cijelu knjigu
- i moje znanje o pitanju napajanja samo
- vinuli za dva sata možete biti do
- ubrzajte i zamijenite stvari koje biste
- su imali običaj raditi s VBA-om.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2037.xlsx
