Mengelompokkan atau meringkas baris
Di Power Query, Anda bisa mengelompokkan nilai dalam berbagai baris ke dalam satu nilai dengan mengelompokkan baris sesuai dengan nilai dalam satu atau beberapa kolom. Anda dapat memilih dari dua jenis operasi pengelompokan:
Pengelompokan kolom.
Pengelompokan baris.
Untuk tutorial ini, Anda menggunakan tabel sampel berikut.
Cuplikan layar tabel dengan kolom yang memperlihatkan Tahun (2020), Negara (AS, Panama, atau Kanada), Produk (Baju atau Celana Pendek), Saluran penjualan (Online atau Penjual), dan Unit (berbagai nilai dari 55 hingga 7500)
Anda dapat menemukan tombol Kelompokkan menurut di tiga tempat:
Pada tab Beranda , di grup Transformasi .
Pada tab Transformasi , dalam grup Tabel .
Pada menu pintasan, saat Anda mengklik kanan untuk memilih kolom.
Dalam contoh ini, tujuan Anda adalah meringkas total unit yang dijual di tingkat negara dan saluran penjualan. Anda menggunakan kolom Negara dan Saluran Penjualan untuk melakukan operasi grup menurut.
- Pilih Kelompokkan menurut pada tab Beranda .
- Pilih opsi Tingkat Lanjut, sehingga Anda bisa memilih beberapa kolom untuk dikelompokkan.
- Pilih kolom Negara.
- Pilih Tambahkan pengelompokan.
- Pilih kolom Saluran Penjualan.
- Di Nama kolom baru, masukkan Total unit, di Operasi, pilih Jumlah, dan di Kolom, pilih Unit.
- Pilih OK
Operasi ini memberi Anda tabel berikut.
Dengan fitur Kelompokkan menurut , operasi yang tersedia dapat dikategorikan dengan dua cara:
- Operasi tingkat baris
- Operasi tingkat kolom
Tabel berikut ini menjelaskan masing-masing operasi ini.
Nama Operasi | Kategori | Deskripsi |
---|---|---|
Jumlah | Operasi kolom | Menjumlahkan semua nilai dari kolom |
Tengah | Operasi kolom | Menghitung nilai rata-rata dari kolom |
Median | Operasi kolom | Menghitung median dari kolom |
Min | Operasi kolom | Menghitung nilai minimum dari kolom |
Maks | Operasi kolom | Menghitung nilai maksimum dari kolom |
Persentil | Operasi kolom | Menghitung persentil, menggunakan nilai input dari 0 hingga 100, dari kolom |
Menghitung nilai yang berbeda | Operasi kolom | Menghitung jumlah nilai berbeda dari kolom |
Hitung baris | Operasi baris | Menghitung jumlah total baris dari grup tertentu |
Menghitung baris yang berbeda | Operasi baris | Menghitung jumlah baris yang berbeda dari grup tertentu |
Semua baris | Operasi baris | Menghasilkan semua baris yang dikelompokkan dalam nilai tabel tanpa agregasi |
Catatan
Hitung nilai yang berbeda dan operasi Persentil hanya tersedia di Power Query Online.
Mulai dari sampel asli, dalam contoh ini Anda membuat kolom yang berisi total unit dan dua kolom lain yang memberi Anda nama dan unit yang dijual untuk produk berkinerja tinggi, dirangkum di tingkat negara dan saluran penjualan.
Gunakan kolom berikut sebagai Kelompokkan menurut kolom:
- Negara
- Saluran Penjualan
Buat dua kolom baru dengan mengikuti langkah-langkah berikut:
- Agregat kolom Unit dengan menggunakan operasi Jumlah . Beri nama kolom ini Total unit.
- Tambahkan kolom Produk baru dengan menggunakan operasi Semua baris.
Setelah operasi selesai, perhatikan bagaimana kolom Produk memiliki nilai [Tabel] di dalam setiap sel. Setiap nilai [Tabel] berisi semua baris yang dikelompokkan menurut kolom Negara dan Saluran Penjualan dari tabel asli Anda. Anda dapat memilih spasi kosong di dalam sel untuk melihat pratinjau konten tabel di bagian bawah dialog.
Catatan
Panel pratinjau detail mungkin tidak menampilkan semua baris yang digunakan untuk operasi grup menurut. Anda dapat memilih nilai [Tabel] untuk melihat semua baris yang berkaitan dengan operasi kelompok demi grup yang sesuai.
Selanjutnya, Anda perlu mengekstrak baris yang memiliki nilai tertinggi di kolom Unit tabel di dalam kolom Produk baru, dan memanggil kolom baru tersebut Produk berkinerja teratas.
Dengan kolom Produk baru dengan nilai [Tabel], Anda membuat kolom kustom baru dengan masuk ke tab Tambahkan Kolom pada pita dan memilih Kolom kustom dari grup Umum.
Beri nama kolom baru Anda Produk berkinerja teratas. Masukkan rumus Table.Max([Products], "Units" )
di bawah Rumus kolom kustom.
Hasil rumus tersebut membuat kolom baru dengan nilai [Rekaman]. Nilai rekaman ini pada dasarnya adalah tabel hanya dengan satu baris. Rekaman ini berisi baris dengan nilai maksimum untuk kolom Unit dari setiap nilai [Tabel] di kolom Produk .
Dengan kolom produk pemain teratas baru ini yang berisi nilai [Rekaman], Anda dapat memilih ikon perluas, pilih bidang Produk dan Unit, lalu pilih OK.
Setelah Anda menghapus kolom Produk dan mengatur jenis data untuk kedua kolom yang baru diperluas, hasil Anda akan menyerupai gambar berikut.
Catatan
Fitur berikut ini hanya tersedia di Power Query Online.
Untuk menunjukkan cara melakukan "pengelompokan fuzzy", pertimbangkan tabel sampel yang diperlihatkan dalam gambar berikut.
Tujuan pengelompokan fuzzy adalah untuk melakukan operasi group-by yang menggunakan perkiraan algoritma kecocokan untuk string teks. Power Query menggunakan algoritma kesamaan Jaccard untuk mengukur kesamaan antara pasangan instans. Kemudian menerapkan pengklusteran hierarkis agglomeratif untuk mengelompokkan instans bersama-sama. Gambar berikut menunjukkan output yang Anda harapkan, di mana tabel dikelompokkan menurut kolom Orang .
Untuk melakukan pengelompokan fuzzy, Anda melakukan langkah yang sama yang dijelaskan sebelumnya dalam artikel ini. Satu-satunya perbedaan adalah kali ini, dalam kotak dialog Kelompokkan menurut , Anda memilih kotak centang Gunakan pengelompokan fuzzy.
Untuk setiap grup baris, Power Query memilih instans yang paling sering sebagai instans "kanonis". Jika beberapa instans terjadi dengan frekuensi yang sama, Power Query akan memilih instans pertama. Setelah Anda memilih OK dalam kotak dialog Kelompokkan menurut , Anda akan mendapatkan hasil yang Anda harapkan.
Namun, Anda memiliki kontrol lebih atas operasi pengelompokan fuzzy dengan memperluas opsi grup Fuzzy.
Opsi berikut tersedia untuk pengelompokan fuzzy:
- Ambang kesamaan (opsional): Opsi ini menunjukkan bagaimana dua nilai serupa harus dikelompokkan bersama. Pengaturan minimum nol (0) menyebabkan semua nilai dikelompokkan bersama-sama. Pengaturan maksimum 1 hanya memungkinkan nilai yang cocok persis untuk dikelompokkan bersama-sama. Defaultnya adalah 0,8.
- Abaikan huruf besar/kecil: Saat string teks dibandingkan, huruf besar/kecil diabaikan. Pemadatan diaktifkan secara default.
- Kelompokkan dengan menggabungkan bagian teks: Algoritma mencoba menggabungkan bagian teks (seperti menggabungkan Mikro dan lunak ke Microsoft) untuk mengelompokkan nilai.
- Tampilkan skor kesamaan: Tampilkan skor kesamaan antara nilai input dan nilai perwakilan yang dihitung setelah pengelompokan fuzzy. Memerlukan penambahan operasi seperti Semua baris untuk menampilkan informasi ini pada tingkat baris demi baris.
- Tabel transformasi (opsional): Anda dapat memilih tabel transformasi yang memetakan nilai (seperti memetakan MSFT ke Microsoft) untuk mengelompokkannya bersama-sama.
Untuk contoh ini, tabel transformasi digunakan untuk menunjukkan bagaimana nilai dapat dipetakan. Tabel transformasi memiliki dua kolom:
- Dari: String teks yang akan dicari di tabel Anda.
- Ke: String teks yang akan digunakan untuk mengganti string teks di kolom Dari .
Gambar berikut menunjukkan tabel transformasi yang digunakan dalam contoh ini.
Penting
Penting bahwa tabel transformasi memiliki nama kolom dan kolom yang sama seperti yang ditunjukkan pada gambar sebelumnya (tabel tersebut harus diberi label "Dari" dan "Ke"). Jika tidak, Power Query tidak mengenali tabel sebagai tabel transformasi.
Kembali ke kotak dialog Kelompokkan menurut , perluas opsi grup Fuzzy, ubah operasi dari Hitung baris ke Semua baris, aktifkan opsi Perlihatkan skor kesamaan , lalu pilih menu drop-down Tabel transformasi.
Setelah Anda memilih tabel transformasi, pilih OK. Hasil operasi tersebut memberi Anda informasi berikut:
Dalam contoh ini, opsi Abaikan kasus diaktifkan, sehingga nilai di kolom Dari tabel Transformasi digunakan untuk mencari string teks tanpa mempertimbangkan kasus string. Operasi transformasi ini terjadi terlebih dahulu, lalu operasi pengelompokan fuzzy dilakukan.
Skor kesamaan juga ditampilkan dalam nilai tabel di samping kolom orang, yang mencerminkan dengan tepat bagaimana nilai dikelompokkan dan skor kesamaan masing-masing. Anda dapat memperluas kolom ini jika diperlukan atau menggunakan nilai dari kolom Frekuensi baru untuk jenis transformasi lainnya.
Catatan
Saat mengelompokkan menurut beberapa kolom, tabel transformasi melakukan operasi penggantian di semua kolom jika mengganti nilai meningkatkan skor kesamaan.
Untuk informasi selengkapnya tentang cara kerja tabel transformasi, buka Presepsi tabel Transformasi.