Power Query Excel: Bersihkan dan Gabung Data Tanpa Rumus

Setiap bulan, admin di kantor pusat menerima belasan file Excel dari cabang. Formatnya mirip tapi tidak sama persis. Ada kolom tanggal yang terbaca sebagai teks, ada baris total di bawah, ada nama produk dengan spasi berlebih. Sebelum laporan bisa dibuat, setengah hari habis untuk copy-paste dan merapikan.

Power Query di Excel dibuat untuk pekerjaan seperti ini. Anda merekam langkah membersihkan dan menggabungkan data sekali saja. Bulan depan, cukup taruh file baru di folder yang sama lalu klik Refresh. Semua langkah dijalankan ulang otomatis, tanpa rumus dan tanpa macro.

Pengertian Power Query

Power Query adalah fitur di Excel untuk mengambil data dari berbagai sumber, membersihkannya, lalu memuatnya ke lembar kerja. Di Excel versi Windows, fitur ini ada di tab Data, bagian Get & Transform Data.

Power Query tersedia di Excel untuk Microsoft 365, Excel 2024, 2021, 2019, dan 2016. Excel untuk Mac juga sudah punya Power Query, tapi beberapa sumber data dan fitur editornya masih lebih lengkap di versi Windows. Cek dulu versi Excel yang dipakai tim Anda sebelum membangun laporan bersama.

Cara kerjanya berbeda dari rumus biasa:

  • Rumus bekerja per sel dan ikut tersimpan di lembar kerja. Kalau datanya ribuan baris dengan banyak VLOOKUP, file jadi berat.
  • Power Query bekerja per tabel. Setiap langkah (hapus kolom, ubah tipe data, filter) dicatat sebagai daftar langkah yang bisa Anda lihat, ubah, atau hapus di panel Applied Steps.

Sumber data yang bisa dibaca antara lain file Excel, CSV, folder berisi banyak file, tabel di workbook yang sama, halaman web, dan database seperti SQL Server.

Mengimpor dari banyak file sekaligus

Fitur yang paling menghemat waktu adalah impor dari folder. Langkahnya:

  1. Simpan semua file export cabang di satu folder, misalnya Laporan Cabang/2026-09.
  2. Di Excel, pilih Data > Get Data > From File > From Folder.
  3. Pilih folder tersebut. Excel menampilkan daftar file yang ditemukan.
  4. Klik Combine & Transform Data supaya bisa dirapikan dulu sebelum dimuat.
  5. Pilih file contoh dan sheet atau tabel yang berisi data.

Power Query lalu menumpuk isi semua file menjadi satu tabel. Ia juga menambahkan kolom nama file sumber, yang berguna untuk mengetahui baris mana berasal dari cabang mana.

Syaratnya, semua file sebaiknya punya struktur yang sama: nama kolom, urutan, dan jumlah kolom yang konsisten. Kalau satu cabang menamai kolom “Qty” dan cabang lain “Jumlah”, Power Query akan menganggapnya kolom berbeda. Menyepakati template export dengan cabang sering kali menyelesaikan separuh masalah.

Alur Power Query: file export cabang di satu folder digabung, dibersihkan, diringkas dengan pivot, lalu menjadi laporan yang tinggal di-refresh

Langkah transformasi yang paling sering dipakai

Setelah data tergabung, Anda merapikannya di Power Query Editor. Beberapa langkah yang hampir selalu dipakai:

  • Use First Row as Headers: menjadikan baris pertama sebagai nama kolom.
  • Remove Rows: membuang baris kosong, baris judul tambahan, atau baris total di bagian bawah.
  • Change Type: memastikan kolom tanggal terbaca sebagai tanggal dan kolom angka sebagai angka. Ini sumber masalah paling umum di data export.
  • Trim dan Clean: membuang spasi berlebih dan karakter tak terlihat, supaya “Kopi Susu “ dan “Kopi Susu” dianggap sama.
  • Replace Values: menyeragamkan penulisan, misalnya “Jkt” menjadi “Jakarta”.
  • Split Column: memecah kolom, misalnya kode “JKT-001” menjadi kode cabang dan nomor.
  • Merge Queries: menggabungkan dua tabel berdasarkan kolom kunci, mirip VLOOKUP tapi lebih ringan. Contohnya menambahkan nama kategori dari tabel master produk.
  • Unpivot Columns: mengubah tabel yang melebar (satu kolom per bulan) menjadi format memanjang yang mudah dianalisis.
  • Group By: meringkas data, misalnya total penjualan per cabang per produk.

Semua langkah ini dicatat. Kalau suatu saat hasilnya aneh, Anda bisa klik langkah satu per satu dan melihat di mana datanya mulai salah. Prinsip merapikan data secara umum dibahas lebih lengkap di artikel data cleaning adalah.

Refresh: bagian yang membuat Power Query layak dipelajari

Setelah hasilnya dimuat ke lembar kerja, laporan Anda terhubung ke sumbernya. Untuk bulan berikutnya:

  1. Taruh file export baru di folder yang sama (atau ganti isinya).
  2. Buka workbook laporan.
  3. Klik Data > Refresh All.

Semua langkah dari impor, pembersihan, sampai ringkasan dijalankan ulang. Pivot table dan grafik yang dibangun dari hasil query ikut diperbarui.

Di Excel untuk Windows, lewat Query Properties, Anda juga bisa mengatur supaya data otomatis di-refresh setiap file dibuka atau setiap beberapa menit selama file terbuka. Yang perlu diingat, refresh ini tetap membutuhkan Excel yang sedang berjalan. Kalau Anda ingin laporan diperbarui dan dikirim sendiri tanpa ada yang membuka file, itu sudah masuk wilayah otomatisasi terjadwal di luar Excel.

Contoh: gabung laporan penjualan cabang

Misalnya sebuah jaringan toko bahan bangunan punya delapan cabang. Setiap awal bulan, tiap cabang mengirim file export penjualan dari sistem kasirnya.

Dengan Power Query, alurnya menjadi:

  1. Admin menyimpan delapan file ke folder bulan berjalan.
  2. Query menggabungkan semua file, membuang baris total, dan memperbaiki tipe tanggal.
  3. Kolom kode cabang diambil dari nama file, lalu digabung dengan tabel master cabang untuk mendapatkan nama wilayah.
  4. Data digabung dengan master produk untuk mendapatkan kategori.
  5. Hasilnya dimuat ke satu tabel, lalu pivot table menampilkan omzet per wilayah, per kategori, dan per bulan.

Bulan berikutnya, admin cukup menyimpan file baru dan klik Refresh All. Pekerjaan setengah hari berubah menjadi beberapa menit, selama format file dari cabang tidak berubah. Contoh proses serupa di bisnis lain bisa Anda lihat di bagian contoh penerapan.

Kapan Power Query mulai tidak cukup

Power Query sangat membantu, tapi ada batasnya:

  • Data sudah sangat besar. Ratusan ribu baris masih bisa, tapi refresh mulai lambat dan file membengkak.
  • Banyak orang butuh angka yang sama. Kalau tiap orang punya salinan workbook sendiri, angkanya bisa berbeda-beda.
  • Butuh jadwal otomatis penuh. Refresh tetap bergantung pada Excel yang terbuka di komputer seseorang.
  • Sumber datanya aplikasi online. Mengambil data dari API aplikasi lebih cocok memakai tool integrasi.

Kalau Anda merasa di titik ini, baca artikel dashboard Excel vs tools BI. Sebaliknya, kalau yang Anda butuhkan adalah otomatisasi langkah di dalam Excel seperti format laporan dan tombol cetak, macro Excel VBA bisa melengkapi Power Query.

Kalau tim Anda masih menghabiskan banyak jam untuk merapikan file export setiap bulan, Aqilatic bisa membantu memetakan mana yang cukup diselesaikan dengan Power Query dan mana yang perlu jalur data otomatis. Mulai saja dari audit proses gratis.

Pertanyaan yang sering muncul

Apakah Power Query perlu diinstal terpisah?

Di Excel 2016 ke atas dan Microsoft 365, Power Query sudah bawaan, ada di tab Data. Hanya Excel 2010 dan 2013 yang dulu memakai add-in terpisah, dan versi itu sudah tidak lagi didukung Microsoft.

Apakah Power Query mengubah file sumber?

Tidak. Power Query hanya membaca file sumber, lalu hasilnya dimuat ke tabel baru. File export asli tetap utuh, sehingga Anda bisa mengulang proses kapan saja.

Apa bedanya Power Query di Excel dan di Power BI?

Mesinnya sama, begitu juga bahasa di baliknya (bahasa M). Bedanya, di Power BI hasilnya dipakai untuk dashboard dan bisa di-refresh terjadwal lewat layanan online, sedangkan di Excel hasilnya dimuat ke lembar kerja.