Power Query adalah alat baru dari Microsoft untuk mengekstrak, mengubah, dan memuat data. Artikel hari ini adalah tentang memproses semua file dalam satu folder.
Power Query ada di dalam Excel 2016 dan tersedia sebagai unduhan gratis di versi Excel 2010 dan Excel 2013 tertentu. Alat ini dirancang untuk mengekstrak, mengubah, dan memuat data ke dalam Excel dari berbagai sumber. Bagian terbaiknya: Power Query mengingat langkah-langkah Anda dan akan memutarnya kembali saat Anda ingin merefresh data. Saat buku ini dicetak, fitur Power Query di Excel 2016 berada di tab Data, dalam grup Dapatkan & Transformasi, di bawah Kueri Baru. Sulit untuk memprediksi apakah Microsoft akan mengganti nama Power Query secara retroaktif menjadi Dapatkan & Transformasi di Excel 2010 dan Excel 2013.
Add-in gratis ini sangat menakjubkan, mungkin ada satu buku lengkap tentangnya. Tetapi sebagai salah satu dari 40 tip teratas saya, saya ingin membahas sesuatu yang sangat sederhana: membawa daftar file ke Excel, bersama dengan tanggal pembuatan file dan mungkin ukurannya. Ini berguna untuk membuat daftar buku kerja anggaran atau daftar foto.
Di Excel 2016, Anda memilih Data, Kueri Baru, Dari File, Dari Folder. Di versi Excel yang lebih lama, gunakan Power Query, Dari File, Dari Folder. Tentukan foldernya:
Saat mengedit kueri, klik kanan kolom mana pun yang tidak Anda inginkan dan pilih Hapus.
Untuk mendapatkan Ukuran File, klik ikon ini di kolom Atribut:
Daftar atribut tambahan muncul. Pilih Ukuran.
Tersedia daftar besar opsi Transformasi.
Setelah Anda selesai mengedit kueri, klik Tutup & Muat.
Data dimuat ke Excel sebagai tabel.
Nanti, untuk memperbarui tabel, pilih Data, Segarkan Semua. Excel mengingat semua langkah dan memperbarui tabel dengan daftar file saat ini di folder.
Untuk penjelasan lengkap tentang fitur yang sebelumnya dikenal sebagai Power Query, lihat M adalah untuk (Data) Monkey oleh Ken Puls dan Miguel Escobar.
M untuk (DATA) MONKEY »
Terima kasih kepada Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser dan Colin Michael yang telah menominasikan Power Query.
Menonton video
- Alat Power Query berada di tab Data di Excel 2016
- Add-in gratis untuk tahun 2010 dan 2013
- Buat daftar semua file dari folder ke dalam kisi Excel menggunakan Power Query
- Pilih Kueri Baru, Dari File, Dari Folder
- Tidak jelas: luaskan bidang atribut untuk mendapatkan ukuran
- Jika data Anda dalam file CSV, Anda dapat mengimpor semua file sekaligus ke dalam satu kotak
- Promosikan baris judul
- Hapus baris header yang tersisa
- Ganti "" dengan nol
- Isi untuk outline view
- Hapus kolom total keseluruhan
- Pisahkan data
- Rumus untuk mengubah nama bulan menjadi tanggal
- Daftar lengkap langkah - Undo terbesar di dunia
- Hari berikutnya - segarkan kueri untuk melakukan kembali semua langkah
Transkrip Video
- Power Query adalah bawaan untuk versi Windows dari Excel 2016. Lihat pada tab Data dalam grup Dapatkan & Transformasi. Jika Anda memiliki 2010 atau
- 2013 selama Anda menjalankan Windows
- dan bukan Mac semua yang ada di Get & Transform
- Anda dapat mengunduh secara gratis dari Microsoft. Cari saja
- Unduh Power Query.
- Hari ini, saya tertarik menggunakan Power Query untuk mendapatkan daftar file. saya
- ingin membuat daftar semua file di folder.
- Mungkin saya perlu melihat file mana yang merupakan
- file besar atau saya perlu mengurutkan atau saya butuhkan
- Anda tahu untuk mendapatkan kombinasi Anda
- mengetahui file anggaran yang kami kirimkan
- dan kemudian folder berbeda yang mana
- kami kembali.
- Untuk memulai, masuk ke Data, Dapatkan & Transformasi, Dari File, Dari Folder.
- Tempel di jalur folder atau gunakan tombol Telusuri.
- Klik OK dan mereka menunjukkan ini kepada saya
- pratinjau. Pilih Edit.
- Beberapa hal di sini Anda lihat kami miliki
- nama file ekstensi tanggal
- diakses, tanggal diubah, tanggal dibuat.
- Benar-benar tidak jelas bahwa simbol di sebelah heading Attributes berarti Perluas. Klik simbol itu dan ada lebih banyak barang masuk
- di sini dan jika Anda mengklik simbol ini maka saya
- bisa masuk dan mendapatkan hal-hal seperti ukuran file
- atau jika itu hanya-baca dan hal-hal seperti
- jadi dalam hal ini saya hanya ingin file
- ukuran. Pilih Ukuran File. Klik ok. Mereka memberi Anda bidang baru dengan nama Attributes.Size.
- Saya bisa melihat berapa byte yang masuk
- setiap file.
- Mungkin saya tidak membutuhkan semuanya di sini, mungkin
- Saya tidak perlu tanggal dibuat agar saya bisa
- klik kanan dan katakan bahwa saya ingin
- hapus kolom itu. Ini
- biner saya tidak perlu itu akan dihapus
- kolom itu. Dari Ribbon, klik Close & Load.
- Dalam beberapa detik, Anda akan mendapatkan tampilan yang dapat diurutkan
- semua yang ada di folder itu jika folder tersebut
- perubahan saya bisa datang ke sini dan saya bisa
- segarkan kueri dan itu akan kembali
- keluar dan tarik data itu dengan benar
- bagi saya ini adalah masalah yang dulu kami alami
- miliki semua waktu kami akan mengirimkan 200
- file anggaran
- dan Anda mendapatkan seseorang kembali, tidak semuanya
- kembali Anda harus bisa membandingkannya
- sekarang saya pada dasarnya dapat melakukan vlookup
- antar folder.
- Sungguh menakjubkan bagaimana caranya
- keren tapi lihat mari kita melampaui
- 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 mengajarkan
- segalanya tentang power query
- antarmuka itu adalah buku luar biasa yang terbaik
- buku tentang power query semua yang saya pelajari
- Saya belajar dari buku ini. Saya naik pesawat dari
- Orlando ke Dallas - Saya membaca seluruh buku
- dan pengetahuan saya tentang power query saja
- melonjak dalam dua jam Anda bisa sampai
- mempercepat dan mengganti hal-hal yang Anda inginkan
- dulu pernah dilakukan dengan VBA.
Unduh berkas
Unduh file contoh di sini: Podcast2037.xlsx
