Power Query adalah alat baru dari Microsoft untuk Mengekstrak, Mengubah, dan Memuatkan data. Artikel hari ini adalah mengenai memproses semua fail dalam folder.
Kueri Kuasa terbina dalam Excel 2016 dan tersedia sebagai muat turun percuma dalam versi tertentu Excel 2010 dan Excel 2013. Alat ini direka untuk mengekstrak, mengubah dan memuatkan data ke Excel dari pelbagai sumber. Bahagian terbaik: Kueri Kuasa mengingat langkah anda dan akan memainkannya semula apabila anda ingin memuat semula data. Semasa buku ini terus ditekan, ciri Power Query di Excel 2016 terdapat di tab Data, dalam kumpulan Get & Transform, di bawah New Query. Sukar untuk meramalkan sama ada Microsoft akan menamakan semula Kueri Kuasa secara retroaktif untuk Mendapatkan & Mengubah dalam Excel 2010 dan Excel 2013.
Tambahan percuma ini sangat mengagumkan, mungkin terdapat keseluruhan buku mengenainya. Tetapi sebagai salah satu daripada 40 petua teratas saya, saya ingin membahas sesuatu yang sangat mudah: membawa senarai fail ke dalam Excel, bersama dengan tarikh pembuatan fail dan mungkin ukurannya. Ini berguna untuk membuat senarai buku kerja anggaran atau senarai foto.
Di Excel 2016, anda memilih Data, Pertanyaan Baru, Dari Fail, Dari Folder. Dalam versi Excel sebelumnya, gunakan Kueri Kuasa, Dari Fail, Dari Folder. Tentukan folder:
Semasa mengedit pertanyaan, klik kanan mana-mana lajur yang tidak anda inginkan dan pilih Buang.
Untuk mendapatkan Saiz Fail, klik ikon ini di lajur Atribut:
Senarai atribut tambahan muncul. Pilih Saiz.
Terdapat banyak pilihan pilihan Transform.
apabila anda selesai mengedit pertanyaan, klik Tutup & Muatkan.
Data dimuat ke Excel sebagai jadual.
Kemudian, untuk mengemas kini jadual, pilih Data, Segarkan Semua. Excel mengingat semua langkah dan mengemas kini jadual dengan senarai fail semasa dalam folder.
Untuk penerangan lengkap mengenai ciri yang sebelumnya dikenali sebagai Power Query, periksa M adalah untuk (Data) Monkey oleh Ken Puls dan Miguel Escobar.
M adalah untuk (DATA) MONKEY »
Terima kasih kepada Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser dan Colin Michael kerana mencalonkan Power Query.
Tonton Video
- Alat Kuasa Kuasa terdapat di tab Data di Excel 2016
- Tambahan percuma untuk tahun 2010 dan 2013
- Senaraikan semua fail dari folder ke dalam grid Excel menggunakan Power Query
- Pilih Pertanyaan Baru, Dari Fail, Dari Folder
- Tidak jelas: luaskan bidang atribut untuk mendapatkan ukuran
- Sekiranya data anda ada dalam fail CSV, anda dapat mengimport semua fail sekaligus ke dalam satu grid
- Promosikan baris tajuk
- Padamkan baris tajuk yang tinggal
- Gantikan "" dengan nol
- Isi untuk paparan garis besar
- Padamkan lajur jumlah keseluruhan
- Nyahpisahkan data
- Formula untuk menukar nama bulan menjadi tarikh
- Senarai langkah lengkap - Buat asal yang terhebat di dunia
- Keesokan harinya - muat semula pertanyaan untuk melakukan semua langkah semula
Transkrip Video
- Kuasa Kuasa terbina dalam versi Windows Excel 2016. Lihat pada tab Data dalam kumpulan Dapatkan & Transform. Sekiranya anda mempunyai tahun 2010 atau
- 2013 selagi anda menjalankan Windows
- dan bukan Mac semua yang ada di Get & Transform
- anda boleh memuat turun secara percuma dari Microsoft. Cari sahaja
- Muat turun Kueri Kuasa.
- Hari ini, saya berminat menggunakan Power Query untuk mendapatkan senarai fail. Saya
- mahu menyenaraikan semua fail dalam folder.
- Mungkin saya perlu melihat fail mana
- fail besar atau saya perlu menyusun atau saya perlukan
- anda tahu untuk mendapatkan gabungan antara anda
- ketahui fail anggaran yang kami hantar
- dan kemudian folder yang berbeza
- kami datang kembali.
- Untuk memulakan, pergi ke Data, Get & Tranform, Dari File, Dari Folder.
- Tampal di laluan folder atau gunakan butang Browse.
- Klik OK dan mereka menunjukkan ini kepada saya
- pratonton. Pilih Edit.
- Beberapa perkara di sini anda dapat lihat
- fail memberi nama pelanjutan tarikh
- diakses, tarikh diubah, tarikh dibuat.
- Sangat tidak jelas bahawa simbol ini di sebelah tajuk Atribut bermaksud Perluas. Klik simbol itu dan terdapat lebih banyak barang
- di sini dan jika anda mengklik simbol ini maka saya
- boleh masuk dan mendapatkan barang seperti saiz fail
- atau jika hanya boleh baca dan perkara-perkara seperti
- jadi dalam kes ini saya hanya mahu fail
- ukuran. Pilih Saiz Fail. Klik ok. Mereka memberi anda bidang baru dengan nama Attribut.Size.
- Saya dapat melihat berapa banyak bait masuk
- setiap fail.
- Mungkin saya tidak memerlukan semuanya di sini
- Saya tidak memerlukan tarikh yang dibuat sehingga saya boleh
- klik kanan dan katakan bahawa saya mahu
- alih keluar lajur itu. Ini
- binari Saya tidak memerlukan yang akan membuang
- lajur itu. Dari Pita, klik Tutup & Muatkan.
- Dalam beberapa saat, anda akan dapat melihat
- semua yang ada di folder itu jika folder itu
- perubahan saya boleh masuk ke sini dan saya boleh
- muat semula pertanyaan dan ia akan kembali
- keluar dan tarik data yang betul ini
- bagi saya ini adalah masalah yang biasa kita lakukan
- mempunyai sepanjang masa kami akan menghantar 200
- fail belanjawan
- dan anda mendapatkan seseorang kembali bukan semuanya
- kembali anda perlu dapat membandingkannya
- sekarang saya pada dasarnya dapat melakukan tinjauan
- antara folder.
- Cukup mengagumkan bagaimana
- keren tapi nampak mari kita melangkaui
- 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.
- Buku ini akan mengajar
- anda semua tentang pertanyaan kuasa
- antara muka ia adalah buku yang luar biasa yang terbaik
- tempah pertanyaan kuasa semua yang saya pelajari
- Saya belajar dari buku ini. Saya menaiki penerbangan dari
- Orlando ke Dallas - Saya membaca keseluruhan buku
- dan pengetahuan saya mengenai pertanyaan kuasa sahaja
- melonjak dalam dua jam anda boleh mencapai
- laju dan ganti perkara yang anda mahukan
- sudah biasa dilakukan dengan VBA.
Muat turun fail
Muat turun fail contoh di sini: Podcast2037.xlsx
