Cara menyalin data dari beberapa sheet menjadi satu sheet di excel menggunakan power query

Catatan. Saat menggunakan buku kerja yang sudah selesai, Anda perlu mengubah referensi folder dalam kueri ke mana pun Anda menyimpan file sampel

Dari Editor Kueri, buka Tampilan > Editor Lanjutan dan ubah jalur folder

Jika Anda pernah menemukan situasi di mana Anda memiliki beberapa file data dengan setiap file memiliki data yang tersebar di beberapa sheet, maka Anda sebaiknya membaca

Postingan ini akan membahas cara menggunakan From Folder Power Query untuk mengimpor beberapa file dengan beberapa sheet di setiap file dan menggabungkan data ke dalam satu tabel

Contoh ini memiliki serangkaian file penjualan dalam satu folder

Setiap file berisi penjualan untuk negara tertentu dan file diberi nama sesuai dengan negara asal data penjualan (mis. e. Irlandia. xlsx, Inggris. xlsx, Luksemburg. xlsx, dan Kanada. xlsx)

Setiap file memiliki beberapa lembar dengan data berbeda dalam format yang sama. Setiap lembar berisi penjualan untuk staf penjualan tertentu dari negara tersebut dan diberi nama dengan nama staf penjualan tersebut

Seperti yang dapat Anda bayangkan, menggabungkan data secara manual akan sangat memakan waktu karena jumlah file dan sheet bertambah. Di sinilah Power Query bisa bersinar

Langkah 1. Buat Kueri Dari Folder

Buat kueri Dari Folder

1. Buka tab Power query
2. Tekan tombol Dari File
3. Pilih Dari Folder di menu tarik-turun
4. Pilih jalur folder dari file yang ingin Anda impor
5. Tekan tombol OK

Periksa data pratinjau untuk memastikannya berada di folder dan file yang benar

1. Pratinjau data impor akan muncul. Periksa ini adalah file dan folder yang benar
2. Tekan tombol Edit

Langkah 2. Hapus kolom data yang tidak diperlukan

Kueri Dari Folder akan menyertakan banyak data seperti jenis ekstensi file, tanggal modifikasi, lokasi file, dll

Ini tidak diperlukan untuk tujuan menggabungkan data. Anda dapat menghapus ini untuk menghindari kekacauan

1. Pilih dua kolom yang tidak Anda perlukan (Konten dan Nama). Tahan tombol Ctrl dan klik kiri pada judul kolom untuk memilihnya
2. Buka tab Beranda
3. Tekan tombol Hapus Kolom
4. Pilih Hapus Kolom Lain dari menu tarik-turun

Langkah 3. Pisahkan kolom nama file

Untuk memasukkan negara ke dalam data, Anda perlu mengurai teks dalam nama file

Karena konvensi penamaan file cukup sederhana (Country Name. xlsx), Anda dapat menggunakan fungsi kolom terpisah menggunakan titik sebagai pembatas

1. Sorot kolom nama file
2. Buka tab Transform
3. Tekan tombol Pisah Kolom
4. Pilih Dengan Pembatas di menu drop down
5. Pilih Kustom
6. Masukkan periode untuk pembatas
7. Pilih Di pembatas paling kanan. Ini hanya akan membagi teks nama file menggunakan periode paling kanan yang ditemukan (yaitu tepat sebelum ekstensi file xlsx)
8. Tekan tombol OK

Anda dapat menghapus kolom hasil yang berisi bagian ekstensi dari nama file terpisah

1. Pilih kolom
2. Buka tab Beranda
3. Tekan tombol Hapus Kolom
4. Pilih Hapus Kolom dari menu tarik-turun

Langkah 4. Tambahkan kolom untuk konten file

Sekarang Anda perlu menambahkan kolom untuk memasukkan konten ke dalam kueri

1. Buka tab Tambahkan Kolom
2. Tekan tombol Tambahkan Kolom Kustom
3. Beri nama kolom baru seperti GetFileData
4. Ketik rumus ini Excel.Workbook([Content],true) ke dalam area rumus
5. Tekan tombol OK

Perluas kolom baru untuk menampilkan semua item di Konten

1. Tekan ikon panah ganda kecil di sisi kanan judul kolom
2. Pilih Perluas
3. Centang Gunakan nama kolom asli sebagai awalan
4. Tekan tombol OK

Anda juga perlu memperluas kolom Data yang dihasilkan untuk menampilkan semua elemennya

1. Tekan ikon panah ganda kecil di sisi kanan judul kolom
2. Pilih Perluas
3. Centang Gunakan nama kolom asli sebagai awalan
4. Tekan tombol OK

Sekarang Anda memiliki semua kolom yang dibutuhkan ditambah beberapa tambahan

Hapus semua kolom yang tidak Anda perlukan dan atur ulang urutan kolom jika diinginkan dengan menyeret dan melepaskan kolom

Power Query akan menebak tipe data dari setiap kolom, tetapi Anda mungkin perlu memperbaikinya

1. Pilih kolom yang ingin Anda ubah tipe datanya
2. Buka tab Beranda
3. Tekan Jenis data dan pilih jenis data dari menu tarik-turun

Metode ini memungkinkan Anda mengimpor semua data dengan mudah dari banyak file dan sheet ke dalam satu tabel

Anda juga dapat menambahkan kolom data negara berdasarkan nama file dan kolom data wiraniaga berdasarkan nama sheet

Jika Anda menambahkan file ke folder atau memperbarui data dalam file, Anda dapat dengan mudah memperbarui data agregat dengan masuk ke tab Data dan menekan tombol Segarkan Semua. Ini akan menjalankan kembali kueri untuk mendapatkan data terbaru

Bagaimana cara menggabungkan data dari beberapa lembar kerja dengan Power Query?

Bagaimana cara menyalin data secara otomatis dari beberapa lembar ke satu lembar di Excel?

Bagaimana cara menggabungkan tabel atau lembar kerja Excel dengan Power Query?