Mengorganisir data di Excel sangat penting untuk memastikan analisis yang akurat dan alur kerja yang efisien, terutama saat menangani spreadsheet besar atau berkas dari berbagai sumber. Data yang tidak diformat memperlambat alur kerja dan meningkatkan kemungkinan kesalahan, sehingga alat pembersih data yang efektif menjadi sangat penting.
Power Query menyediakan cara fleksibel untuk memproses data yang berantakan dan mengubahnya menjadi tabel yang bersih dan mudah digunakan. Power Query dapat secara otomatis menghapus duplikat, mengoreksi kolom, dan memformat nilai tanpa perlu rumus yang rumit. Proses ini bergantung pada langkah-langkah pemrosesan yang jelas yang memungkinkan pengguna untuk dengan mudah menggunakan kembali transformasi setiap kali sumber data berubah.
Mengadopsi metode pembersihan berbasis Power Query memberikan hasil yang konsisten dan menghemat waktu secara signifikan, terutama untuk tugas yang memerlukan pembaruan data rutin. Hal ini memudahkan pembuatan laporan yang akurat dan andal sekaligus meminimalkan intervensi manual.
Bayangkan ini: laporan Excel lain masuk ke kotak masuk saya, dan sekali lagi, isinya penuh dengan indentasi, ejaan yang tidak konsisten, dan baris yang tidak berguna. Sebelumnya, saya menghabiskan waktu berjam-jam untuk memperbaikinya secara manual. Sekarang, saya menggunakan Power Query untuk menganalisis kekacauan ini, merapikan teks, mengatur strukturnya, dan menghapus semua kekacauan yang tidak perlu.
Power Query tersedia di Excel sebagai alat bawaan untuk Microsoft 365 dan dalam versi desktop mandiri yang dirilis pada tahun 2016 dan setelahnya. Power Query juga tersedia di Excel untuk web, tetapi dengan beberapa keterbatasan dibandingkan dengan versi desktop.
Temukan dan buka editor Power Query
Untuk membersihkan data yang tidak terorganisir, data tersebut harus diunggah ke editor Power Query.
Setelah memastikan data Anda berisi baris vertikal dan diformat sebagai tabel Excel, klik sel mana pun di tabel, lalu di “data“Klik pada “Dari tabel/lingkup.”
Pada tahap ini, editor Power Query terbuka dan menampilkan data Anda.
Setelah Anda menyelesaikan langkah-langkah pembersihan di bawah ini, klik “Tutup dan muat“Pada tab Beranda, pindahkan data kembali ke lembar kerja Excel biasa.”
Mendiagnosis kesalahan menggunakan analisis data
Saat pertama kali membuka set data Excel baru, langkah pertama saya selalu menemukan kesalahan dan inkonsistensi. Alat analisis data Power Query memungkinkan saya mendiagnosis integritas data secara visual dan instan.
Memperhatikan
Secara default, Power Query hanya menganalisis 1.000 baris pertama. Jika data Anda berisi lebih dari itu, klik "Analisis kolom berdasarkan 1.000 baris pertama" pada bilah hijau di bagian bawah jendela, lalu pilih "Analisis kolom berdasarkan seluruh dataset". Pastikan juga format angka yang tepat telah diatur untuk setiap kolom dengan mengklik ikon di header-nya.
Di editor Power Query, buka tab “MenunjukkanDi sana, tentukan "kualitas kolom", dan“Tata letak kolom”DanAnalisis kolom.
Kualitas kolom: Metrik validitas data
Statistik dan persentase kolom, yang terletak tepat di bawah judul kolom, memberi Anda gambaran umum tentang kualitas data setiap kolom.
Berikut arti warna-warna tersebut:
- Hijau: Semua nilai data benar.
- Abu-abu gelap atau hitam: Kolom berisi nilai kosong atau nol.
- Merah: Kolom mengandung kesalahan, seperti ketidakcocokan tipe data.
- Bagan merah dan putih: Kesalahan tertentu, yang sering kali terkait dengan gangguan koneksi data atau langkah yang diterapkan, menyebabkan masalah dalam memproses data menggunakan Power Query.
- Bagan hijau dan putih: Ada kesalahan, dan kualitas data yang tersisa saat ini tidak diketahui.
Distribusi kolom: Bagan pengulangan nilai
Diagram batang dan teks di bawah metrik kualitas kolom menunjukkan frekuensi setiap nilai dalam kolom. Ini ideal untuk mendeteksi titik data yang tidak konsisten. Misalnya, di sini, saya dapat melihat dengan jelas bahwa hanya ada satu nilai unik di kolom "".Bagian“Jadi mungkin ada kesalahan ketik di suatu tempat.”
Profil kolom: Informasi statistik dan distribusi
Mengklik pada tajuk kolom akan menampilkan panel "Profil" Informasi terperinci tentang isi kolom.
Klik kanan pada bilah distribusi horizontal untuk membuka opsi pemfilteran. Misalnya, Anda dapat memfilter data agar hanya menampilkan nilai kesalahan.
Membersihkan dan memformat nilai teks
Setelah menganalisis data, saya mengidentifikasi kolom-kolom yang memiliki masalah tekstual—biasanya spasi tak terlihat dan huruf besar/kecil yang tidak konsisten. Saya mengatasi masalah ini menggunakan alat pembersihan, pemotongan, dan koreksi huruf besar/kecil.
Pembersihan dan pemformatan data
Masalah teks yang paling umum adalah karakter yang tidak dapat dicetak dan spasi yang berlebihan, terutama saat menyalin data dari berkas PDF, situs web, atau versi Excel yang lebih lama.
Untuk membersihkan karakter yang tidak dicetak, pilih tajuk kolom teks, dan di “ل“Klik pada “Format” > “Bersihkan”.
Alat ini langsung menghilangkan hal-hal tak terlihat yang menghambat pemrosesan data, seperti tanda baca dan tanda baca.
Selanjutnya, di menu yang sama, klik “Potong” untuk menghapus semua indentasi dan sufiks yang tidak diperlukan, dan membiarkan spasi tunggal di antara kata-kata tetap seperti apa adanya.
Mencapai huruf kapital yang konsisten
Dalam contoh ini, kolom "Departemen" berisi "Meksiko", "Meksiko", dan "MEKSIKO". Karena Anda ingin menghitung data ini sebagai satu negara nanti saat memfilter, mengelompokkan, dan menggabungkan, Anda perlu memastikan kapitalisasi yang konsisten.
Klik kanan pada tajuk kolom, arahkan kursor ke “Transform”, lalu klik “Tulis setiap kata dengan huruf kapital.
Namun, hal ini juga mengakibatkan konversi Amerika Serikat ke Amerika Serikat dan Inggris Raya ke Inggris Raya, jadi Anda perlu memperbaiki masalah ini dengan mengeklik "Ganti Nilai" di tab "Konversi".
Membuat struktur menggunakan kolom bersyarat dan kustom
Terkadang, data mentah membutuhkan lebih dari sekadar pembersihan; data tersebut membutuhkan struktur baru berdasarkan nilai yang sudah ada, dan di sinilah pentingnya fitur "Tambahkan Kolom" di Power Query.
Membuat skenario kondisional IF-THEN menggunakan kolom kondisional
Saya menggunakan kolom bersyarat untuk mengklasifikasikan data numerik berdasarkan ambang batas tertentu, yang membuat data saya lebih mudah dibaca dan diringkas.
Misalkan Anda ingin membuat kolom baru bernama "Klasifikasi Penjualan" berdasarkan kolom Penjualan yang sudah ada, mengklasifikasikan penjualan sebesar $10,000 atau lebih sebagai "Tinggi" dan semua nilai lainnya sebagai "Rendah". Di editor Power Query, di bawah tab "Tambahkan Kolom", klik "Kolom Bersyarat".
Sekarang, di kotak dialog “Kolom Bersyarat”, lakukan hal berikut:
| bidang | Apa yang harus ditulis atau dipilih |
|---|---|
| Nama baru kolom tersebut | Tuliskan klasifikasi penjualan |
| Nama kolom | Pilih “Penjualan” |
| Koefisien | Pilih “lebih besar dari atau sama dengan” |
| nilai | tulis 10000 |
| Pintu keluar | Ketik Tinggi |
| Jika tidak | Tipe Rendah |
Ketika Anda mengklikbaikPower Query segera membuat kolom baru yang diurutkan.
Membuat kolom khusus menggunakan bahasa M
Untuk mengkategorikan data berdasarkan teks, saya membuat kolom kustom baru menggunakan rumus M sederhana. Hal ini memungkinkan saya untuk menstandardisasi kategori meskipun nilai teksnya sedikit berbeda.
Katakanlah Anda ingin mengkategorikan nilai-nilai di kolom "Negara" ke dalam kolom baru yang disebut "Wilayah." Secara khusus, Anda ingin mengkategorikan Meksiko, Amerika Serikat, dan Kanada ke dalam "الأمريكتينInggris dan Inggris diklasifikasikan sebagai “Eropa".
Di editor Power Query, buka tab “Tambahkan Kolom” dan klik “Kolom khusus.”
Di kotak dialog, beri nama kolom baru "Wilayah". Kemudian, untuk format kolom kustom, saya menggunakan kombinasi pemeriksaan sederhana dan pernyataan if-then-else di kolom "Negara" untuk mengelompokkannya ke dalam wilayah, memastikan untuk mengelompokkan variabel seperti AS dan Amerika Serikat bersama-sama.
jika [Negara] = "Meksiko" atau [Negara] = "AS" atau [Negara] = "Amerika" atau [Negara] = "Kanada" maka "Amerika" jika tidak, jika [Negara] = "Inggris" atau [Negara] = "Inggris" maka "Eropa" jika tidak, "Lainnya"
Klik “OK” untuk melihat kolom baru yang berhasil mengurutkan data teks yang berantakan ke dalam kelompok yang seragam untuk penyaringan dan pengelompokan di masa mendatang.
Nasihat
Kolom baru selalu ditambahkan di tepi kanan kumpulan data. Cukup klik dan seret tajuk kolom untuk memindahkannya. Pastikan juga format yang benar telah diterapkan dengan mengeklik ikon di sebelah kiri teks pada tajuk kolom.
Hilangkan kekacauan dengan menghapus baris
Langkah pembersihan utama terakhir biasanya melibatkan penghapusan semua elemen struktural yang tidak diinginkan yang sering kali mengelilingi set data yang bersih, seperti teks pengantar, baris ringkasan, dan spasi kosong. Karena filter standar tidak berfungsi pada sel di luar header, saya menggunakan alat Hapus Baris di Power Query sebagai gantinya.
Metode ini krusial karena meskipun data asli diformat sebagai spreadsheet Excel, proses ini sering kali menangkap baris-baris yang bersebelahan, terutama ketika menangani laporan besar di mana titik awal dan akhir yang tepat belum diidentifikasi secara manual. Saya menggunakan proses tiga langkah untuk mencapai hal ini: menghapus elemen atas dan bawah yang tidak diinginkan, memperbarui header, lalu menghapus baris-baris kosong.
Hapus item yang tidak diinginkan dari atas dan bawah
Pertama, pada tab “Beranda”, perluas menu tarik-turun “Hapus Baris” di bawah grup “Kurangi Baris”.
Kemudian klik pilihan berikut:
- Hapus baris teratas: Opsi ini memungkinkan Anda menghapus sejumlah baris tertentu dari bagian atas kumpulan data. Misalnya, jika terdapat teks di baris 1 dan 2 di atas judul kolom target, ketik 2 untuk menghapus kedua baris tersebut.
- Hapus baris bawah: Gunakan alat ini untuk menghapus sejumlah baris tertentu yang berisi informasi footer, seperti tanggal atau ringkasan laporan.
Setelah melakukan itu, editor Power Query akan memperlihatkan kepada Anda kumpulan data yang telah diurutkan dan bebas dari kekacauan yang tidak diinginkan.
Peningkatan judul
Setelah menghapus elemen yang tidak diinginkan, judul kolom kini berada di baris pertama, sehingga Anda dapat menjadikannya sebagai judul kolom yang dikenali dengan benar. Di tab "Beranda", klik "Gunakan baris pertama sebagai judul".
Hapus baris kosong
Terakhir, hapus baris kosong yang masih menempati ruang dan mungkin menghambat analisis selanjutnya. Pada tab Beranda, klik Hapus Baris > Hapus Baris Kosong.
Selain menganalisis kesalahan, membersihkan teks, menambahkan kolom, dan menghapus baris yang tidak diinginkan, Power Query Excel dapat membantu Anda dengan banyak tugas pengorganisasian data lainnya, seperti memisahkan sel, mengganti kesalahan, membatalkan pivot data, dan mengisi celah berdasarkan sel di atasnya. Kuncinya adalah segera memulai—setelah setengah jam bereksperimen dengan berbagai alat, Anda akan menyadari betapa bermanfaatnya fitur bawaan ini.
Membersihkan data di Excel bukan lagi tugas yang rumit dengan Power Query, yang menawarkan solusi praktis dan cepat untuk menangani spreadsheet yang berantakan. Menggunakan alat ini memastikan data yang lebih akurat dan mengurangi waktu yang terbuang untuk pemrosesan manual.
Untuk meningkatkan efisiensi kerja Anda, mulailah dengan mencoba langkah-langkah pembersihan dasar dan kemudian perluas penggunaan transformasi tingkat lanjut untuk hasil yang lebih profesional.
