Power Query di Excel: Impor, Bersihkan, Gabungkan, dan Segarkan Data

By Mirko Peters · 2026-11-12

Power Query di Excel mengimpor dan membersihkan data tanpa rumus. Pelajari di mana menemukannya, From Table/Range, Applied Steps, merge vs append, pemuatan, dan penyegaran.

Automatically translated with AI from the original post. Read the English original

Power Query di Excel adalah alat pada tab Data, dalam grup Get & Transform Data, yang terhubung ke data, membersihkan dan menata ulang data tersebut, lalu memuat hasilnya ke lembar kerja. Langkah-langkah yang Anda klik dicatat dan dijalankan ulang pada setiap penyegaran, sehingga pembersihan bulanan cukup dilakukan dengan satu klik. Artikel ini ditujukan bagi pengguna Excel yang setiap minggu menyalin, menempel, dan memperbaiki ekspor data yang sama dan ingin berhenti melakukannya. Anda akan menemukan cara memulai, cara membersihkan tabel, cara melakukan append dan merge, cara memuat dan menyegarkan, serta batasan fitur ini di Mac dan web.

Poin Penting

• Power Query di Excel terdapat pada tab Data dalam grup Get & Transform Data. Fitur ini terhubung ke data, membentuknya, dan memuatnya ke lembar kerja atau Data Model.
• Setiap perubahan yang Anda buat dicatat sebagai langkah di Applied Steps. Sumber asli tidak berubah, dan langkah-langkah tersebut dijalankan ulang pada setiap penyegaran.
• Append menumpuk baris dari tabel dengan kolom yang sama. Merge menggabungkan dua tabel berdasarkan kolom kunci yang cocok, dan kolom yang dicocokkan harus memiliki tipe data yang sama.
• Gunakan Data, Refresh All untuk menjalankan ulang kueri. Di Connection Properties, Anda dapat menyegarkan pada interval tertentu atau saat file dibuka.
• Excel untuk Mac dan Excel untuk web mendukung sebagian fitur Power Query, dan halaman Microsoft berbeda dalam hal apa yang dapat dibuat di masing-masing, jadi periksa build Anda sendiri.

Microsoft menjelaskan empat tahap: menghubungkan ke data, mentransformasikannya, menggabungkannya dengan sumber lain, dan memuatnya. Sumber asli tetap tidak berubah sepanjang proses. Poin terakhir inilah yang membuat eksperimen menjadi aman. Anda tidak akan merusak ekspor yang menjadi titik awal Anda.

Apa itu Power Query dan di mana menemukannya

Power Query adalah mesin persiapan data dengan editor grafis. Menurut halaman Microsoft tentang apa itu Power Query, fitur ini melakukan pekerjaan extract, transform, dan load, tidak memiliki penyimpanan sendiri, dan menyerahkan tujuan akhir kepada produk host. Excel adalah salah satu host. Power BI Desktop adalah host lainnya, dan keduanya menggunakan editor inti yang sama. Konektor dan tujuan dapat berbeda antar host, jadi jangan berasumsi bahwa kueri yang Anda lihat di satu produk akan sama persis di produk lainnya.

Lokasinya bergantung pada versi Excel, menurut ikhtisar Power Query di Excel dari Microsoft:

• Excel 2016, 2019, dan Microsoft 365 untuk Windows. Sudah terpasang. Buka Data lalu lihat grup Get & Transform Data.
• Excel 2010 dan 2013 untuk Windows. Power Query merupakan add-in gratis dengan tab ribbon sendiri. Microsoft menyatakan fitur ini sudah tidak direkomendasikan lagi, meskipun mungkin masih berfungsi.
• Excel untuk Mac. Didukung untuk penyegaran dari beberapa sumber. Fitur ini tidak tersedia di Excel 2016 atau 2019 untuk Mac.
• Excel untuk web. Didukung sebagian. Selengkapnya di bawah.

Di Windows, Microsoft juga mencantumkan .NET Framework 4.7.2 atau yang lebih baru, serta WebView2 Runtime untuk konektor web, sebagai persyaratan. Jika perintahnya tidak tersedia di PC kerja, tanyakan kepada tim IT apakah build-nya sudah terbaru sebelum Anda menganggap fiturnya dihapus.

Cara menggunakan Power Query di Excel: kueri pertama Anda

Cara termudah untuk memulai adalah dengan data yang sudah ada di buku kerja Anda.

1. Klik sel mana pun di tabel Excel Anda. Jika data Anda berupa rentang biasa, tekan Ctrl+T terlebih dahulu untuk mengubahnya menjadi tabel.
2. Buka Data > Get & Transform Data > From Table/Range.
3. Power Query Editor akan terbuka dengan pratinjau data Anda.
4. Lakukan perubahan yang diperlukan (lihat bagian berikutnya).
5. Pilih Home > Close & Load.

Halaman Microsoft tentang impor dari tabel Excel menyebutkan bahwa hasilnya secara default masuk ke lembar kerja baru dan lembar kerja asli tidak diubah. Gunakan Load To jika Anda menginginkan tujuan yang berbeda. Ganti nama lembar kerja baru agar tidak tertukar dengan sumbernya.

Untuk terhubung ke sumber lain, gunakan Data > Get Data dan pilih sumber: berkas, basis data, layanan daring, atau web. Setelah Anda memilihnya, Anda akan mendapatkan pratinjau serta pilihan antara Load (langsung ke Excel) dan Transform Data (membuka editor terlebih dahulu). Saran saya, pilih Transform Data hampir setiap kali, bahkan ketika Anda merasa datanya sudah bersih. Editor adalah tempat Anda mengatur tipe kolom, dan tipe yang salah menjadi penyebab sebagian besar kesalahan refresh di kemudian hari.

Jika Anda ingin memulai dari awal, halaman yang sama mencantumkan Data > Get Data > From Other Sources > Blank Query, atau Launch Power Query Editor di bawah Get Data.

Editor dengan bahasa sederhana

• Pratinjau data menampilkan tabel Anda dan diperbarui setelah setiap perubahan.
• Panel Query Settings mencantumkan APPLIED STEPS. Setiap tindakan yang Anda lakukan menambahkan satu langkah, misalnya Source atau Changed Type. Anda dapat memilih langkah untuk melihat data pada titik tersebut, mengubah langkah, atau menghapusnya.
• Panel Queries mencantumkan semua kueri dalam buku kerja. Bukalah dengan panah di samping pratinjau.
• Advanced Editor memungkinkan Anda melihat dan mengedit skrip dasar dalam bahasa Power Query M. Microsoft mencatat bahwa setiap langkah yang Anda buat di antarmuka ditulis dalam M. Anda tidak perlu menyentuhnya untuk mendapatkan manfaat dari alat ini.

Membersihkan dan menata ulang data dengan langkah-langkah

Pekerjaan pembersihan yang umum di editor: memfilter baris, menghapus kolom, mengubah tipe data, memisahkan kolom, dan mengganti nilai. Editor mencatat masing-masing sebagai satu langkah. Perintahnya terdapat di ribbon editor dan di menu yang muncul saat Anda mengklik kanan header kolom. Perintah yang terlihat bergantung pada tipe kolom, jadi periksa menu header kolom sebelum Anda mencari di ribbon.

Rutinitas yang efektif untuk sebagian besar ekspor data:

1. Atur tipe datanya terlebih dahulu. Periksa ikon tipe di samping setiap header kolom. Tanggal, angka, dan teks berperilaku berbeda pada langkah berikutnya serta saat penggabungan.
2. Hapus yang tidak Anda perlukan. Lebih sedikit kolom berarti hasil yang lebih kecil, lebih cepat, dan lebih sedikit kejutan ketika sumber berubah.
3. Filter baris. Gunakan panah di header kolom untuk menghapus sel kosong atau nilai yang tidak Anda inginkan.
4. Perbaiki teks. Ganti salah ketik dan kode lama, rapikan spasi, atau pisahkan kolom yang memuat dua informasi.
5. Ganti nama kueri di panel Query Settings agar mudah ditemukan nanti.

Karena Applied Steps berupa daftar, Anda dapat memperbaiki kesalahan sebelumnya tanpa mengulang dari awal. Jika sebuah langkah gagal setelah sumbernya berubah, periksa langkah pertama yang menampilkan kesalahan. Sebagian besar waktu, langkah itu menunjuk ke kolom yang nama atau tipenya berubah.

Satu catatan tentang kolom yang diganti namanya: halaman refresh Microsoft menyebutkan bahwa kolom sumber yang baru hanya muncul di Power Query setelah Anda meninjau langkah Source, dan bilah kuning yang menyatakan pratinjau mungkin sudah usang berarti Anda perlu menyegarkan cache lokal. Jika kolom baru tidak muncul, periksa dua hal tersebut terlebih dahulu.

Menggabungkan data: append dan merge

Kedua perintah ini memiliki fungsi yang berbeda, dan orang sering keliru menggunakannya.

Append menumpuk baris. Gunakan ini saat Anda punya tabel dengan jenis yang sama dalam jumlah banyak, misalnya satu ekspor per bulan. Microsoft menggambarkan append sebagai penumpukan baris dari satu kueri ke kueri lain. Ada dua jenis: inline append dibangun di atas kueri yang sudah ada, sedangkan intermediate append membuat kueri baru setiap kali dijalankan.

Merge menggabungkan dua tabel secara berdampingan berdasarkan nilai yang cocok, mirip pencarian (lookup). Halaman Microsoft tentang menggabungkan kueri memberikan langkah-langkahnya:

1. Pastikan Anda memiliki minimal dua kueri dengan kolom untuk dicocokkan. Kolom yang dicocokkan harus memiliki tipe data yang sama, misalnya Text atau Number.
2. Di editor, buka Home > Merge Queries. Hasilnya menjadi langkah baru dalam kueri saat ini. Jika Anda ingin kueri terpisah, buka panah di samping Merge Queries lalu pilih Merge Queries as New.
3. Pilih tabel utama beserta kolom kuncinya, lalu tabel terkait beserta kolom kuncinya. Untuk beberapa kolom kunci, pilih kolom-kolom tersebut dengan urutan yang sama di kedua tabel.
4. Periksa jumlah kecocokan yang ditampilkan di dialog. Jika lebih rendah dari perkiraan Anda, kemungkinan kolom kunci berbeda dalam ejaan, spasi, atau tipenya.
5. Pilih Join Kind (lihat di bawah) lalu pilih OK.
6. Kolom baru akan muncul. Pilih ikon Expand di samping judul kolom tersebut, lalu centang kolom terkait yang ingin Anda tambahkan.

Jenis join menurut Microsoft:

• Left outer mempertahankan semua baris tabel utama beserta kecocokan dari tabel terkait.
• Right outer mempertahankan semua baris tabel terkait beserta kecocokan dari tabel utama.
• Full outer mempertahankan semua baris dari kedua tabel.
• Inner hanya mempertahankan baris yang cocok di kedua tabel.
• Left anti mempertahankan baris utama yang tidak memiliki kecocokan. Right anti mempertahankan baris terkait yang tidak memiliki kecocokan.
• Cross memasangkan setiap baris tabel utama dengan setiap baris tabel terkait.

Posisi tabel itu penting, karena left dan right bergantung pada tabel mana yang Anda pilih lebih dulu. Kotak centang Use fuzzy matching to perform the merge mencocokkan nilai yang mendekati, dan menurut Microsoft fitur ini hanya berfungsi pada kolom teks. Jika Anda terbiasa menggunakan lookup sebelumnya, merge dengan left outer join adalah padanan yang paling dekat, dan left anti join adalah cara cepat untuk melihat baris mana yang tidak memiliki kecocokan, misalnya pesanan tanpa catatan pelanggan.

Microsoft juga menyebutkan tingkat privasi. Power Query mengklasifikasikan setiap sumber sebagai Public, Organizational, atau Private, dan menggunakannya untuk mencegah penggabungan data yang tidak disengaja yang dapat membuka informasi pribadi ke sumber lain. Jika merge meminta Anda mengatur tingkat privasi, baca pesannya sebelum Anda mengklik lanjut.

Memuat kueri dan menyegarkannya

Tutup editor dengan Home > Close & Load. Halaman Microsoft tentang membuat, memuat, dan mengedit kueri menjelaskan tujuan-tujuan berikut:

• Close & Load mengirim hasilnya ke worksheet.
• Close & Load To memungkinkan Anda memilih tujuan lain, misalnya Data Model. Di dialog Import Data, centang Add this data to the Data Model jika Anda berencana membuat PivotTable dari beberapa tabel.
• Jika Load To dinonaktifkan, gunakan Close & Load, lalu buka Data > Queries & Connections, klik kanan kueri di tab Queries, dan pilih Load To.

Untuk mengubah pengaturan default, buka File > Options and settings > Query Options > Data Load. Anda dapat menggunakan pengaturan pemuatan standar atau menetapkan default kustom untuk memuat ke worksheet atau ke Data Model.

Untuk mengedit kueri nanti, pilih sel di dalam datanya lalu pilih Query > Edit, atau klik kanan kueri di Queries & Connections dan pilih Edit.

Refresh

Refresh menjalankan ulang setiap langkah terhadap sumber yang aktif saat ini. Cara-caranya, dari halaman refresh Microsoft:

• Data > Refresh All memperbarui semua isi buku kerja (Ctrl+Alt+F5). Panah di sebelahnya menawarkan Refresh untuk item yang dipilih (Alt+F5). Esc menghentikan penyegaran.
• Untuk penyegaran otomatis, buka Data > Queries & Connections, buka tab Connections, klik kanan sebuah koneksi, lalu pilih Properties. Pada tab Usage, Anda dapat mengatur Refresh every (dalam menit) dan Refresh data when opening the file.
• Enable background refresh pada tab yang sama memungkinkan Anda tetap bekerja selama kueri berjalan. Opsi ini tidak tersedia untuk kueri OLAP atau koneksi yang menjadi sumber Data Model.

Kata sandi yang disimpan tidak dienkripsi, dan Microsoft menyarankan agar Anda tidak menyimpannya. Hapus centang Save password pada tab Definition, dan Excel akan meminta kata sandi sekali per sesi.

Jika Anda mengirim buku kerja kepada orang lain, mereka mungkin memerlukan akses ke sumber datanya. Kueri yang membaca file di drive Anda atau situs yang tidak dapat mereka buka akan gagal di komputer mereka. Letakkan sumber data di lokasi bersama, atau kirim hasil yang telah dimuat sebagai nilai.

Power Query di Mac dan di Excel for the web

Kedua platform mendukung sebagian fitur ini, tetapi halaman dokumentasi Microsoft tidak sepenuhnya sejalan.

Excel for the web. Halaman Microsoft menyebutkan Anda dapat mengimpor dari sumber termasuk buku kerja Excel, Text/CSV, XML, JSON, SQL Server, daftar SharePoint Online, dan OData, serta tabel kosong atau kueri kosong. Anda dapat membuka Power Query Editor (Data > Get Data > Launch Power Query Editor), memuat dengan Close & Load, dan menyegarkan dengan Data > Refresh All. Melihat dan menyegarkan kueri tersedia untuk semua pelanggan Microsoft 365, sedangkan fungsi yang lebih lengkap memerlukan paket bisnis atau paket enterprise (E3 ke atas). Beberapa fitur memerlukan cookie pihak ketiga yang diaktifkan di browser.

Excel for Mac. Salah satu halaman Microsoft menyebutkan Anda memulai dengan Data > Get Data > Get Data (Power Query), bahwa pembentukan data memerlukan Microsoft 365 dan versi 16.69 atau yang lebih baru, serta bahwa Transform Data membuka editor. Pada halaman yang sama, bagian terpisah menyatakan bahwa penulisan kueri di editor tidak tersedia di Excel for Mac, dan menjelaskan penulisan kueri dengan VBA di Windows serta impor modul di Mac. Saya tidak dapat mendamaikan kedua pernyataan tersebut, jadi uji di Mac Anda sendiri sebelum membangun proses di atasnya. Impor SQL Server disebutkan hanya tersedia di Insiders Beta.

Ketika Power Query tidak cukup

Saya akan terus terang: Power Query dirancang untuk menyiapkan data, bukan untuk menyimpannya. Microsoft menyatakan bahwa produk host menentukan ke mana hasil dikirim, yang di Excel berarti lembar kerja atau Data Model. Lembar kerja memiliki batas jumlah baris tetap, sehingga hasil kueri yang sangat besar tidak akan muat di lembar kerja. Muat ke Data Model sebagai gantinya, atau pindahkan pekerjaan ke database atau pipeline khusus.

Batasan lain yang perlu diketahui:

• Kueri membaca sumber saat dijalankan. Kueri tidak mengalirkan perubahan langsung ke dalam buku kerja.
• Kolaborasi cukup merepotkan. Dua orang yang mengedit kueri yang sama di buku kerja bersama adalah ide yang buruk. Sepakati satu pemilik dan beri nama kueri dengan jelas.
• Jika Anda menangani data yang diatur regulasi, ingatlah bahwa buku kerja sangat mudah disalin. Patuhi aturan organisasi Anda tentang tempat file semacam itu boleh disimpan.

Kesalahan umum

• Melewatkan tipe data, lalu bertanya-tanya mengapa penggabungan tidak menemukan kecocokan.
• Menggabungkan berdasarkan kolom dengan tipe berbeda atau yang memiliki spasi di akhir.
• Menggunakan merge padahal yang dimaksud append, atau sebaliknya.
• Memuat semua data ke lembar kerja padahal kueri khusus koneksi atau Data Model lebih sesuai.
• Menyimpan kata sandi sumber di dalam koneksi.
• Lupa menyegarkan sebelum mengirim laporan.
• Memindahkan file sumber lalu tidak memahami mengapa kueri gagal.

Topik terkait untuk dijelajahi

• Hapus duplikat di editor. Power Query dapat menghapus duplikat pada tabel sebagai sebuah langkah yang dapat diulang, berbeda dengan perintah sekali pakai di ribbon.
• Unpivot kolom. Ubah kolom bulan menjadi baris sebelum Anda membuat PivotTable. Microsoft menyebut Power Query tepat untuk keperluan ini.
• Parameter. Microsoft memiliki halaman tentang kueri parameter untuk menggunakan ulang kueri dengan input yang berbeda.
• Bahasa M. Pelajari cara membaca skrip di Advanced Editor, dimulai dari halaman Power Query M.
• Power BI. Pelajari cara kerja editor yang sama di Power BI Desktop saat data Anda sudah melampaui kemampuan Excel.

Langkah berikutnya

Pilih satu ekspor yang Anda bersihkan secara manual setiap minggu. Ubah menjadi tabel, jalankan melalui From Table/Range, atur tipe kolom, lalu muat ke lembar kerja baru. Minggu depan, tempelkan ekspor baru ke dalam tabel dan tekan Refresh All. Itulah Power Query di Excel yang bekerja sebagaimana mestinya.