5 tindakan sehari-hari yang dilakukan Power Query lebih baik daripada alat Excel biasa

Alat Excel standar biasanya bisa menyelesaikan pekerjaan, tetapi mereka sering kekurangan ketahanan dan fleksibilitas yang dibutuhkan untuk tugas data dan transformasi yang kompleks. Power Query tidak hanya melakukan tugas-tugas ini lebih cepat—ia melakukannya dengan lebih baik. Berikut lima tindakan di mana Anda sebaiknya meninggalkan alat pita Excel untuk Editor Power Query.

Sebelum Anda mulai: Memuat data Anda

Pilih sel mana saja di dalam dataset Anda, lalu di tab Data, klik "Dari Tabel/Range."

Setelah Anda selesai menerapkan langkah-langkah Power Query, klik "Tutup & Muat" di tab Beranda untuk mengembalikan data ke lembar kerja baru.

Gunakan kolom kondisional (alih-alih pernyataan IF bersarang)

Logika Excel standar sering mengandalkan rangkaian teks panjang dan manual yang sulit dibuat dan bahkan lebih sulit diperbaiki.

Skenario

Anda perlu mengkategorikan penjualan menjadi "kecil," "sedang," dan "besar" berdasarkan nilainya.

Biasanya, Anda akan menambahkan kolom baru dan menggunakan rumus IF bersarang:

=IF([@Sales]>1000,"Large",IF([@Sales]>500,"Medium","Small"))

Namun, satu koma yang salah tempat bisa merusak seluruh string, dan memeriksa logika di bilah formula yang panjang adalah sesuatu yang menyebalkan.

Cara menggunakan kolom bersyarat

Setelah memuat data Anda ke dalam Power Query Editor, pada tab Tambahkan Kolom, klik "Kolom Bersyarat."

Di kotak dialog, beri nama kolom baru Anda, dan atur logika Anda menggunakan menu drop-down. Dalam kasus ini, aturan pertama adalah mengembalikan kata "Besar" jika nilai di kolom Penjualan lebih besar dari 1.000.

Kemudian, klik "Tambah Klausul" dan ulangi proses untuk kondisi kisaran menengah.

Terakhir, masukkan nilai Else sebagai Kecil dan klik "OK."

Kolom kondisi baru kemudian muncul, dan Anda siap untuk menutup dan memuat tabel baru ke dalam lembar kerja Anda.

Mengapa kolom kondisional lebih baik daripada pernyataan IF bersarang

Metode ini membuat langkah terapan visual yang dapat diaudit oleh siapa saja hanya dengan melihat sekilas. Anda tidak perlu menulis kode apa pun, dan jika ambang batas Anda berubah, Anda cukup mengklik ikon roda gigi di panel Langkah Terapan untuk memperbarui aturan tanpa menyentuh satu tanda kurung pun.

Gabungkan kueri (alih-alih menggunakan pencarian)

Menghubungkan dua dataset yang berbeda adalah tugas inti di Excel, tetapi mengandalkan rumus berbasis sel sering kali menyebabkan referensi yang rusak dan kinerja yang lambat.

Skenario

Anda memiliki tabel Penjualan dan daftar Master Produk yang berada di lembar kerja terpisah, dan Anda perlu menarik harga satuan dari daftar Master ke dalam tabel Penjualan Anda.

Meskipun XLOOKUP bisa melakukan ini, itu menjadi masalah jika file sumber dipindahkan, header kolom dihapus, atau dataset tumbuh secara signifikan.

Cara menggabungkan kueri

Langkah pertama adalah menyimpan tabel pertama di memori Power Query. Untuk melakukan ini, muat tabel ke dalam Power Query Editor, dan di bawah panah Tutup & Muat, klik "Tutup & Muat Ke."

Kemudian, dalam dialog, pilih "Hanya Buat Koneksi," dan klik "OK."

Pada titik ini, panel "Kueri dan Koneksi" terbuka dengan koneksi yang baru saja Anda buat.

Sekarang, ulangi proses ini untuk tabel lainnya agar kedua koneksi terlihat di panel tersebut.

Klik kanan tabel Penjualan di panel ini, lalu klik "Edit" untuk kembali ke Editor.

Sekarang, klik "Gabungkan Kueri" di tab Beranda, pilih tabel Product Master dari daftar drop-down, pilih kolom SKU yang cocok di kedua pratinjau, dan klik "OK."

Selanjutnya, klik ikon "Perluas" di header kolom baru, centang kolom yang ingin Anda pindahkan dari satu tabel ke tabel lain (dalam hal ini, "Harga"), dan klik "OK."

Saat Anda mengklik "Tutup & Muat," kueri Penjualan masih disimpan hanya sebagai koneksi di panel Kueri dan Koneksi. Jadi, klik kanan, klik "Muat Ke," pilih "Tabel," dan pilih "Lembar kerja baru."

Ketika Anda mengklik "OK", tabel yang digabungkan akan dimuat ke dalam lembar kerja baru.

Mengapa menggabungkan kueri lebih baik

Fungsi pencarian pada dasarnya adalah solusi tingkat sel, tetapi menggabungkan kueri di Power Query adalah solusi tingkat tabel. Inilah mengapa hal itu penting dalam buku kerja yang kompleks:

• Ini tidak merusak: Menggabungkan kueri membuat versi baru yang telah diubah sambil tetap menjaga tabel sumber Anda tetap utuh.
• Ini menangani beberapa hasil: Jika Anda memiliki 10 data berbeda yang ingin diambil dari daftar utama, di Power Query, Anda hanya melakukan penggabungan sekali dan cukup mencentang kotak untuk semua 10 kolom yang ingin Anda perluas.
• Berjalan lebih mulus: Power Query hanya berjalan saat Anda mengklik "Segarkan," membuatnya jauh lebih cepat untuk kumpulan data dengan ribuan baris.

Gunakan alat unpivot (alih-alih mentranspos data secara manual)

Alat Excel standar membuatnya sulit untuk mengubah data lebar menjadi format tinggi yang diperlukan untuk PivotTable dan analisis modern.

Skenario

Anda menerima laporan dengan kategori di kolom pertama, tetapi bulan-bulannya terpisah sebagai header. Untuk menggunakan data ini dalam PivotTable, Anda perlu menjadikan bulan-bulan tersebut dalam satu kolom vertikal.

Dalam Excel biasa, Anda harus menyalin, menempel, dan mentranspos setiap bulan secara manual—proses yang melelahkan yang harus Anda ulangi setiap kali data baru datang.

Cara menggunakan alat unpivot

Setelah memuat tabel lebar Anda ke dalam Power Query Editor, pilih kolom yang tidak ingin Anda balikkan (tahan Ctrl saat mengklik header jika ada lebih dari satu), klik kanan salah satu header kolom tersebut, dan klik "Unpivot Other Columns."

Power Query secara instan mengubah data, membuat kolom atribut dan kolom nilai.

Klik dua kali pada header untuk mengubah namanya menjadi sesuatu seperti Bulan dan Pendapatan, dan pastikan formatnya benar dengan mengklik ikon format angka di sebelah nama kolom.

Dataset yang diperbarui sekarang sudah siap untuk ditutup dan dimuat kembali ke dalam buku kerja Anda.

Mengapa unpivoting lebih baik

Alat unpivot bersifat dinamis dan tidak merusak data. Di Excel standar, jika Anda menambahkan kolom baru, Anda perlu menyusun ulang worksheet secara manual. Namun, Power Query secara otomatis mendeteksi setiap header bulan baru yang ditambahkan ke data sumber dan mengubahnya menjadi daftar vertikal Anda saat pembaruan berikutnya.

Gunakan koneksi "dari folder" (alih-alih menyalin dan menempel secara manual)

Menyalin data secara manual dari beberapa file adalah salah satu penyebab paling umum terjadinya kesalahan manusia dalam spreadsheet Excel.

Skenario

Setiap hari Senin, ketika Anda menerima laporan penjualan regional terbaru, Anda membuka file tersebut, menyalin datanya, dan menempelkannya di bagian bawah lembar kerja utama. Namun, ini adalah proses yang lambat, dan mudah untuk secara tidak sengaja melewatkan satu baris atau menempelkan data dua kali.

Cara mengimpor data dari sebuah folder

Pertama, pastikan semua file sumber Anda berada dalam satu folder khusus di komputer Anda. Untuk menjaga proses tetap rapi, pastikan folder ini hanya berisi file-file yang ingin Anda gabungkan.

Kemudian, di Excel, klik Data > Dapatkan Data > Dari File > Dari Folder.

Buka folder khusus Anda, lalu klik "Buka."

Perluas menu tarik-turun "Gabungkan" dan pilih "Gabungkan & Transformasi Data."

Pilih file atau lembar contoh untuk digunakan sebagai templat dan klik "OK." Pastikan semua buku kerja menggunakan nama tab lembar dan header kolom yang sama, karena file contoh menentukan struktur untuk seluruh penggabungan.

Setelah Anda puas dengan hasilnya, klik "Tutup & Muat."

Mengapa mengimpor data dari folder lebih baik

Menggabungkan workbook Excel menggunakan Power Query artinya Anda tidak perlu lagi membuka file regional—sebagi gantinya, minggu depan, Anda bisa langsung menaruh laporan baru ke dalam folder dan klik "Refresh" di file Excel utama Anda.

Gunakan alat pemisah kolom (alih-alih metode pemisahan kolom standar)

Ada banyak cara untuk memisahkan kolom menggunakan antarmuka standar Excel, tetapi semuanya membawa masalah yang bisa menimbulkan kendala di kemudian hari.

Skenario

Anda memiliki kolom nama lengkap yang perlu dipisahkan menjadi nama depan dan nama belakang.

Di pita standar Excel, Anda mungkin menggunakan wizard Text to Columns, tetapi hasilnya bersifat statis, artinya jika Anda menambahkan nama baru atau memperbarui yang sudah ada, pemisahan tidak akan diperbarui. Sebagai alternatif, Anda bisa menggunakan fungsi TEXTSPLIT, tetapi ini akan menghasilkan kolom yang tidak konsisten jika sebuah nama mengandung inisial tengah.

Cara membagi kolom

Setelah memuat data Anda ke dalam Power Query Editor, klik kanan pada header kolom yang ingin Anda bagi, dan di menu Split Column, klik "By Delimiter."

Pilih "Spasi" sebagai pemisah Anda.

Setelah selesai, klik "OK," ubah nama kolom, lalu tutup Editor dan muat dataset yang dihasilkan ke dalam buku kerja Excel Anda.

Mengapa membagi kolom di langkah editor Power Query lebih baik

Tidak seperti alat pita, pembagian kolom di Power Query diperbarui setiap kali Anda mengklik "Segarkan," menangani baris data baru secara instan. Selain itu, ini lebih stabil secara struktural dibandingkan fungsi TEXTSPLIT, karena membuat kolom tabel nyata yang tidak bergantung pada ruang "spill" kosong. Terakhir, jika Anda perlu mengubah logika, Anda cukup mengklik ikon roda gigi di panel Langkah yang Diterapkan daripada menulis ulang rumus atau menjalankan ulang panduan.

Setelah Anda menguasai lima tindakan ini, Anda dapat lebih menyederhanakan proses Anda dengan mempelajari lebih banyak perintah Power Query penting untuk transformasi data agar dapat menangani dataset yang paling kompleks sekalipun dengan satu klik.