Power Query on Microsoftin uusi työkalu tietojen purkamiseen, muuntamiseen ja lataamiseen. Tämän päivän artikkeli koskee kaikkien kansiossa olevien tiedostojen käsittelyä.
Power Query on sisäänrakennettu Excel 2016: een ja se on saatavana ilmaiseksi ladattuna tietyissä Excel 2010- ja Excel 2013 -versioissa. Työkalu on suunniteltu purkamaan, muuntamaan ja lataamaan tietoja Exceliin useista lähteistä. Parasta: Power Query muistaa vaiheesi ja toistaa ne, kun haluat päivittää tiedot. Kun tätä kirjaa painetaan, Excel 2016: n Power Query -ominaisuudet ovat Tiedot-välilehden Hae ja muunna -ryhmän Uusi kysely -kohdassa. On vaikea ennustaa, nimeääkö Microsoft taannehtivasti Power Queryin saadakseen ja muunnettaessa Excel 2010: ssä ja Excel 2013: ssa.
Tämä ilmainen lisäosa on niin hämmästyttävä, että siitä voisi olla koko kirja. Mutta yhtenä 40 suosituimmasta vinkistäni haluan käsitellä jotain hyvin yksinkertaista: tuoda luettelo tiedostoista Exceliin sekä tiedoston luomispäivämäärä ja ehkä koko. Tästä on hyötyä budjettityökirjojen tai valokuvaluettelon luomisessa.
Excel 2016: ssa valitset Tiedot, Uusi kysely, Tiedostosta, Kansiosta. Käytä aiemmissa Excel-versioissa Power Queryä, tiedostosta, kansiosta. Määritä kansio:
Muokatessasi kyselyä napsauta hiiren kakkospainikkeella sarakkeita, joita et halua, ja valitse Poista.
Saat tiedostokoon napsauttamalla tätä kuvaketta Attribuutit-sarakkeessa:
Näkyviin tulee luettelo ylimääräisistä määritteistä. Valitse Koko.
Saatavana on laaja luettelo muunnosvaihtoehdoista.
Kun olet muokannut kyselyä, napsauta Sulje ja lataa.
Tiedot latautuvat Exceliin taulukkona.
Myöhemmin voit päivittää taulukon valitsemalla Tiedot, Päivitä kaikki. Excel muistaa kaikki vaiheet ja päivittää taulukon nykyiseen luetteloon kansiossa olevista tiedostoista.
Täydellisen kuvauksen ominaisuudesta, joka tunnettiin aiemmin nimellä Power Query, tutustu M is for (Data) -apinaan Ken Puls ja Miguel Escobar.
M on (DATA) MONKEY: lle »
Kiitos Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser ja Colin Michael Power Queryn ehdokkaista.
Katso video
- Power Query -työkalut ovat Excel 2016: n Tiedot-välilehdessä
- Ilmainen lisäosa vuosina 2010 ja 2013
- Luettele kaikki kansion tiedostot Excel-ruudukkoon Power Queryn avulla
- Valitse Uusi kysely, tiedostosta, kansiosta
- Ei selvä: laajenna määritekenttää saadaksesi koon
- Jos tietosi ovat CSV-tiedostoina, voit tuoda kaikki tiedostot kerralla yhteen ruudukkoon
- Mainosta otsikkoriviä
- Poista jäljellä olevat otsikkorivit
- Korvaa "" nollalla
- Täytä ääriviivat
- Poista kokonaissarake
- Poista tietojen kääntö
- Kaava kuukausien nimien muuntamiseksi päivämääriksi
- Täydellinen luettelo vaiheista - maailman suurin Kumoa
- Seuraava päivä - päivitä kysely ja tee kaikki vaiheet uudelleen
Videon transkriptio
- Power Query on sisäänrakennettu Excel 2016: n Windows-versioihin. Katso Hae ja muunna -ryhmän Tiedot-välilehteä. Jos sinulla on 2010 tai
- 2013 niin kauan kuin sinulla on Windows
- ja ei Macia kaikkea mitä täällä Get & Transform -ohjelmassa on
- voit ladata ilmaiseksi Microsoftilta. Etsi vain
- Lataa Power Query.
- Tänään olen kiinnostunut käyttämään Power Queryä tiedostoluettelon saamiseksi. Minä
- haluat listata kaikki kansion tiedostot.
- Ehkä minun täytyy nähdä, mitkä tiedostot ovat
- suuria tiedostoja tai minun täytyy lajitella tai tarvitsen
- tiedät saada yhdistelmä sinusta
- tiedämme lähettämämme budjettitiedostot
- ja sitten eri kansio, mitkä
- tulimme takaisin.
- Aloita siirtymällä kohtaan Data, Get & Tranform, Tiedostosta, Kansiosta.
- Liitä kansiopolulle tai käytä Selaa-painiketta.
- Napsauta OK ja he osoittavat tämän
- esikatselu. Valitse Muokkaa.
- Pari asiaa täällä näet
- tiedoston nimi laajennus päivämäärä
- käytetty, muokattu päivä, luotu päivä.
- Ei todellakaan ole ilmeistä, että tämä merkki Attribuutit-otsikon vieressä tarkoittaa Laajenna. Napsauta sitä symbolia, ja siellä on lisää tavaroita
- täällä ja jos napsautat tätä symbolia, minä
- voi mennä sisään ja saada esimerkiksi tiedostokoon
- tai jos se on vain luku -muodossa ja niin edelleen
- että tässä tapauksessa haluan vain tiedoston
- koko. Valitse Tiedoston koko. Napsauta OK. Ne antavat sinulle uuden kentän, jonka nimi on Attributes.Size.
- Näen kuinka monta tavua on
- kukin tiedosto.
- Ehkä en tarvitse kaikkea täällä ehkä
- En tarvitse luotua päivämäärää voidakseni
- napsauta hiiren kakkospainikkeella ja sano, että haluan
- poista kyseinen sarake. Tämä
- binaarinen, jota en tarvitse, poistaa
- että sarake. Napsauta valintanauhassa Sulje ja lataa.
- Muutamassa sekunnissa sinulla on lajiteltava näkymä
- kaikki siinä kansiossa, jos kansio
- muutoksia voin tulla tänne ja voin
- päivitä kysely ja se palaa takaisin
- ja vedä tiedot oikeaan
- minulle tämä on ongelma, jota käytimme
- meillä on koko ajan lähetämme 200
- budjettitiedostot
- ja saat jonkun takaisin, ei kaikkia
- sinun täytyy pystyä vertaamaan niin
- nyt voin tehdä olennaisesti vlookupin
- kansioiden välillä.
- On vain hämmästyttävää miten
- siistiä se on, mutta katsotaanpa mennään pidemmälle
- 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.
- Tämä kirja opettaa
- sinulle kaikki virtakyselystä
- käyttöliittymä on hämmästyttävä kirja paras
- kirja virtakyselystä kaikesta, mitä opin
- Olen oppinut tästä kirjasta. Pääsin lennolle
- Orlando Dallasiin - Luin koko kirjan
- ja tietoni vain tehokyselystä
- noussut kahdessa tunnissa voit olla jopa
- nopeuttaa ja korvata haluamasi asiat
- on ollut tapana tehdä VBA: n kanssa.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2037.xlsx
