Power Query je novo Microsoftovo orodje za pridobivanje, preoblikovanje in nalaganje podatkov. Današnji članek govori o obdelavi vseh datotek v mapi.
Power Query je vgrajen v Excel 2016 in je na voljo kot brezplačen prenos v nekaterih različicah Excel 2010 in Excel 2013. Orodje je zasnovano za pridobivanje, pretvorbo in nalaganje podatkov v Excel iz različnih virov. Najboljši del: Power Query si zapomni vaše korake in jih bo predvajal, ko želite osvežiti podatke. Ko bo ta knjiga v tisku, so funkcije Power Query v programu Excel 2016 na zavihku Podatki v skupini Pridobivanje in preoblikovanje pod Nova poizvedba. Težko je napovedati, ali bo Microsoft za nazaj poizvedbo Power Query preoblikoval v Excel 2010 in Excel 2013.
Ta brezplačni dodatek je tako neverjeten, da bi o njem lahko obstajala cela knjiga. Kot enega izmed mojih 40 najboljših nasvetov želim zajeti nekaj zelo preprostega: v Excel vnesti seznam datotek, skupaj z datumom ustvarjanja datotek in morda velikostjo. To je uporabno za ustvarjanje seznama proračunskih delovnih zvezkov ali seznama fotografij.
V Excelu 2016 izberete Podatki, Nova poizvedba, Iz datoteke, Iz mape. V starejših različicah Excela uporabite Power Query, From File, From Folder. Določite mapo:
Med urejanjem poizvedbe z desno miškino tipko kliknite poljubne stolpce in izberite Odstrani.
Če želite velikost datoteke, kliknite to ikono v stolpcu Atributi:
Pojavi se seznam dodatnih atributov. Izberite Velikost.
Na voljo je velik seznam možnosti preoblikovanja.
ko ste končali z urejanjem poizvedbe, kliknite Zapri in naloži.
Podatki se naložijo v Excel kot tabela.
Kasneje za posodobitev tabele izberite Podatki, Osveži vse. Excel si zapomni vse korake in tabelo posodobi s trenutnim seznamom datotek v mapi.
Za popoln opis funkcije, prej znane kot Power Query, si oglejte M is for (Data) Monkey Ken Puls in Miguel Escobar.
M je za (DATA) OPICO »
Zahvaljujemo se Miguelu Escobarju, Robu Garcii, Miku Girvinu, Rayu Hauserju in Colinu Michaelu za nominacijo Power Query.
Oglejte si video
- Orodja Power Query so na zavihku Podatki v Excelu 2016
- Brezplačni dodatek za leti 2010 in 2013
- Z Power Query navedite vse datoteke iz mape v Excelovo mrežo
- Izberite Nova poizvedba, Iz datoteke, Iz mape
- Ni očitno: razširite polje atributa, da dobite velikost
- Če so vaši podatki v datotekah CSV, lahko vse datoteke hkrati uvozite v eno mrežo
- Promovirajte vrstico naslova
- Izbrišite preostale vrstice glave
- Zamenjajte "" z null
- Izpolnite za orisni pogled
- Izbrišite stolpec skupnega seštevka
- Odstranite podatke
- Formula za pretvorbo imen mesecev v datume
- Popoln seznam korakov - največja razveljavitev na svetu
- Naslednji dan - osvežite poizvedbo, da ponovite vse korake
Prepis videoposnetka
- Power Query je vgrajen v Windows različice Excela 2016. Poiščite zavihek Podatki v skupini Pridobi in preoblikuj. Če imate 2010 oz
- 2013, če imate Windows
- in ne Mac, vse, kar je tukaj v Get & Transform
- lahko brezplačno prenesete iz Microsofta. Samo poiščite
- Prenesite Power Query.
- Danes me zanima uporaba Power Query za pridobitev seznama datotek. jaz
- želite seznam vseh datotek v mapi.
- Mogoče moram videti, katere datoteke so
- velike datoteke ali jih moram razvrstiti ali jih potrebujem
- veste, da dobite kombinacijo vas
- poznamo proračunske datoteke, ki smo jih poslali
- in nato drugo mapo, katere
- smo prišli nazaj.
- Za začetek pojdite na Podatki, Pridobi in pretvori, Iz datoteke, Iz mape.
- Prilepite na pot do mape ali uporabite gumb Prebrskaj.
- Kliknite V redu in mi bodo to pokazali
- predogled. Izberite Uredi.
- Tukaj vidite nekaj stvari
- ime datoteke pripona datum
- dostop, datum spremembe, datum ustvarjanja.
- Res ni očitno, da ta simbol poleg naslova Atributi pomeni Razširi. Kliknite ta simbol in v njem je še nekaj stvari
- tukaj in če kliknete ta simbol, potem I
- lahko vstopite in dobite stvari, kot je velikost datoteke
- ali če je samo za branje in podobne stvari
- tako da v tem primeru želim samo datoteko
- velikost. Izberite Velikost datoteke. Kliknite v redu. Dajo vam novo polje z imenom Attributes.Size.
- Vidim, koliko bajtov je
- vsako datoteko.
- Mogoče tukaj ne rabim vsega
- Ne potrebujem ustvarjenega datuma, da bi lahko
- z desno miškino tipko in recite, da želim
- odstranite ta stolpec. To
- binarni ne potrebujem, da bo odstranil
- ta stolpec. Na traku kliknite Zapri in naloži.
- Čez nekaj sekund boste imeli razvrščen pogled na
- vse v tej mapi, če je mapa
- spremembe lahko vstopim sem in lahko
- osvežite poizvedbo in vrnila se bo nazaj
- izvlecite in potegnite te podatke v desno, to je
- zame je to težava, ki smo jo včasih
- imamo ves čas, da bi jih poslali 200
- proračunske datoteke
- in nekoga dobite nazaj, ne vseh
- nazaj morate biti sposobni primerjati tako
- zdaj lahko v bistvu naredim vlookup
- med mapami.
- Prav neverjetno je, kako
- kul je, ampak poglejmo, pojdimo dlje
- kar imam v knjigi in vam pokažem, kako
- to je le vrh ledene gore.
- Ustvaril bom še eno poizvedbo. Podatki, nova poizvedba, iz datoteke, iz mape.
- Pot te mape bom kopiral sem.
- kliknite uredi.
- Od oktobra 2016 ta trik deluje le s CSV
- datotek, vendar je bil leta 2017 posodobljen za delo z enodelnimi datotekami Excel. jaz
- imajo mapo cel kup datotek in
- Želim ustvariti eno excel mrežo z vsemi
- podatkov iz vseh teh datotek.
- Sploh ni intuitivno. Poiščite poleg naslova stolpca Binarno. Obstaja ikona z dvema puščicama, usmerjenima navzdol na vodoravno črto.
- Kliknite to.
- BAM! pravkar je povlekel vsak posnetek iz
- vsaka datoteka v tej mapi!
- Ni
- to neverjetno mislim, to je bil makro VBA
- prej in traja mesece, da se naučite VBA
- makrov se lahko naučite poizvedbe o moči v desetih
- minut.
- Izbrati moramo ta stolpec in
- 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.
- Ta knjiga bo učila
- vam vse o poizvedbi o napajanju
- vmesnik je neverjetna knjiga najboljši
- knjiga o moči poizvedba vse, kar sem se naučil
- Iz te knjige sem se naučil. Na let sem prišel iz
- Orlando do Dallasa - prebrala sem celo knjigo
- in samo moje znanje o moči
- skočil v dveh urah, ste lahko do
- hitrost in nadomestite stvari, ki bi jih
- so že imeli opravke z VBA.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2037.xlsx
