Tutorial ini membahas cara membuat list folder ke Excel atau daftar nama file tanpa menggunakan kode VBA, tetapi cukup dengan menggunakan fitur Power Query saja.
Daftar semua nama file dalam folder boleh berupa file apa saja. Baik itu file Excel, Word, PDF, file gambar, atau file lainnya.
Baca di sini: 2 Cara Menggabungkan File Excel Menjadi Satu
Misalnya, Anda diberi tugas untuk copy nama file ke Excel, yang berada di satu atau beberapa folder.
Untuk itu, Anda copy nama file dan paste ke lembar kerja Excel.
Ulangi langkah di atas hingga semua rekap file selesai.
Kedengaran cukup mudah dan bukan pekerjaan yang berat.
Namun bagaimana bila file-nya berjumlah ratusan atau ribuan.
Sebelum Anda memperbarui resume dan mengirimkannya ke perusahaan lain untuk pindah kerja, baca dulu tutorial berikut.
Karena tugas ini bisa diselesaikan dengan cepat oleh Power Query dalam waktu beberapa menit saja.
Keuntungan Menggunakan Power Query
Ada cara lain untuk menampilkan nama file di Excel, yaitu menggunakan rumus dan kode VBA.
Namun Power Query memiliki beberapa kelebihannya:
- Bisa membuat list file dari sub folder yang berada di dalam folder utama.
- Selain daftar nama, kita juga bisa memasukkan kolom-kolom yang berisi metadata yang lain. Contohnya, tanggal pembuatan dan jenis file. Jadi bila berupa file gambar, maka ada data jenis formatnya: JPG, PNG, atau GiF.
- Juga ada data lokasi file dan ukuran file.
- Memilih kolom dan baris yang ingin disimpan.
- Dan masih banyak lagi.
Power Query Excel Ada Dimana?
Berikut hal yang perlu diketahui tentang Power Query untuk masing-masing versi Excel.
Excel 2016, 2019, dan Versi di Atasnya
Fitur Power Query ada di tab Data, grup Get & Transform.
Excel 2010 dan 2013
Pengguna Excel 2010 dan 2013 harus install Add-in Power Query. Caranya baca di bawah ini.
Add-in bisa digunakan di sistem operasi: Windows 10, Windows Server 2008 R2, Windows Server 2012, Windows 7, Windows 8.
Langkah-langkah:
- Dapatkan Add-in di: Microsoft Power Query for Excel. Jangan lupa untuk memilih 32-bit (x86) atau 64-bit (x64), sesuai versi Excel Anda.
- Install Add-in tersebut.
- Buka aplikasi Excel. Pada menu di Ribbon, akan muncul tab POWER QUERY.
Bila tab POWER QUERY tidak muncul, lakukan hal berikut untuk menampilkannya:
- Di tab File, klik Options, dan pilih tab Add-ins.
- Scroll ke bawah ke bagian Manage. Pilih COM Add-ins dan klik tombol Go.
- Pada daftar, pilih Microsoft Power Query for Excel dan klik OK.
- Tutup aplikasi Excel dan kemudian buka kembali.
Excel 2007
Power Query hanya tersedia untuk Excel 2010 dan versi di atasnya. Jadi tidak ada di Excel 2007.
Cara Menggunakan Power Query untuk Membuat Daftar File
Tergantung versi Excel yang digunakan, tutorial berikut mungkin memiliki sedikit perbedaan. Namun pada prinsipnya, lebih kurang sama.
Langkah 1: untuk pengguna:
- Excel 2016, 2019, dan versi di atasnya: pada tab Data, grup Get & Transform, klik New Query. Arahkan kursor ke opsi From File dan klik From Folder.
- Excel 2010 dan 2013: di tab Power Query, klik From File dan pilih From Folder.
Langkah 2: di kotak Folder path, gunakan tombol Browse untuk mencari lokasi folder yang berisi file dan kemudian klik OK.
Akan muncul daftar nama file beserta metadata lainnya, seperti: Extension (tipe file), tanggal dibuat, tanggal modifikasi, serta alamat folder (folder path).
Langkah 3: jika tidak ada perubahan, lanjutkan dengan menekan tombol Load. Maka pekerjaan Anda pun selesai. Jangan lupa untuk menyimpan file Excel tersebut.
Langkah 4: apabila masih ingin menghapus kolom atau menambah data yang lain, maka tekan tombol Edit. Ikuti petunjuk di bawah untuk cara mengeditnya.
Catatan: edit juga bisa kita lakukan setelah melakukan “Load” data.
Caranya:
- Arahkan kursor pada panel Worbook Queries yang berada di sebelah kanan layar. Akan muncul satu layar baru dan pilih tombol Edit untuk membuka layar Power Query Editor.
- Atau di Table Tools, tab Query, klik Edit Query.
Selanjutnya ikuti cara berikut untuk mengedit data pada daftar nama file.
Mengedit Daftar Data Power Query
Pada Power Query Editor kita bisa menghapus kolom dan baris pada daftar dengan menggunakan opsi di Manage Columns dan Reduce Rows.
Berikut penjelasan cara menggunakannya.
Menghapus Kolom Daftar File
Ada 2 cara yang bisa kita gunakan.
Cara 1:
- Di bagian Manage Columns, klik Choose Columns dan pilih Choose Columns.
- Centang kotak kolom yang ingin disimpan dan hilangkan centang pada kotak kolom yang ingin dihapus. Misalnya yang dicentang hanya kotak Name dan Folder Path.
- Klik OK. Sekarang hanya tersisa 2 kolom saja.
- Klik Close & Load atau tombol X untuk kembali ke lembar kerja Excel. Selesai.
- Bila Anda berubah pikiran dan ingin memasukkan kolom yang lain juga, maka ulangi langkah-langkah di atas.
Cara 2:
- Lakukan salah satu dari hal berikut:
- Pilih kolom yang ingin dihapus, kemudian di bagian Manage Columns, klik Remove Columns.
- Atau pilih kolom yang ingin digunakan. Kemudian klik Remove Columns dan pilih Remove Other Columns untuk menghapus kolom yang ingin dibuang.
- Klik Close & Load.
Menghapus Baris Daftar File
Cara 1:
- Di bagian Reduce Rows, klik Keep Rows.
- Pada opsi yang muncul, pilih:
- Keep Top Rows: untuk menyimpan baris mulai dari posisi teratas. Anda harus memasukkan jumlah baris yang ingin disimpan. Misalnya untuk menyimpan 20 baris data, maka ketik angka 20 pada kotak dan klik OK.
- Keep Bottom Rows: untuk menyimpan baris mulai dari posisi bawah.
- Keep Range of Rows: untuk menyimpan kumpulan baris. Misalnya kita ingin menyimpan mulai dari baris 5 sebanyak 10 baris ke bawahnya (yaitu baris 5 sampai dengan baris 14). Untuk itu, di kotak First row masukkan angka 5 dan di kotak Number of rows masukkan angka 10.
- Akhiri dengan mengklik Close & Load.
Cara 2:
- Di bagian Reduce Rows, klik Remove Rows.
- Pada opsi yang muncul, pilih:
- Remove Top Rows: untuk menghapus baris mulai dari posisi teratas. Masukkan jumlah baris yang ingin dihapus dan tekan OK.
- Remove Bottom Rows: untuk menghapus baris mulai dari posisi bawah.
- Remove Alternate Rows: untuk menghapus baris yang berada di tengah.
- Klik Close & Load untuk kembali ke lembar kerja Excel.
Tips: seandainya ada kekeliruan saat melakukan langkah-langkah di atas, kita bisa mengedit atau membatalkan perubahan yang kita buat.
Caranya:
- Di kotak Applied Steps yang berada di sebelah kanan layar Power Query Editor, klik tombol X untuk menghapus hal yang telah kita lakukan sebelumnya.
- Atau klik icon yang berbentuk roda bergerigi untuk mengedit angka atau kotak yang dicentang sebelumnya.
Penutup
Bila sudah tahu cara memakai Power Query, maka cukup mudah membuat list semua nama file dalam sebuah folder dan menaruhnya di lembar kerja Excel.
List tersebut akan ditampilkan dalam format Table yang berisi tombol Filter. Kita bisa ubah tampilan warnanya dengan Table Styles.