Mengelompokkan atau meringkas baris

  • Artikel

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 akan menggunakan tabel sampel berikut.

Contoh tabel awal.

Tempat menemukan tombol Kelompokkan menurut

Anda dapat menemukan tombol Kelompokkan menurut di tiga tempat:

  • Pada tab Beranda , di grup Transformasi .

    Kelompokkan menurut pada tab Beranda.

  • Pada tab Transformasi , dalam grup Tabel .

    Kelompokkan menurut pada tab Transformasi.

  • Pada menu pintasan saat Anda mengklik kanan untuk memilih kolom.

    Kelompokkan menurut pada menu pintasan.

Menggunakan fungsi agregat untuk mengelompokkan menurut satu atau beberapa kolom

Dalam contoh ini, tujuan Anda adalah meringkas total unit yang dijual di tingkat negara dan saluran penjualan. Anda akan menggunakan kolom Negara dan Saluran Penjualan untuk melakukan operasi grup menurut.

  1. Pilih Kelompokkan menurut pada tab Beranda .
  2. Pilih opsi Tingkat Lanjut, sehingga Anda bisa memilih beberapa kolom untuk dikelompokkan.
  3. Pilih kolom Negara.
  4. Pilih Tambahkan pengelompokan.
  5. Pilih kolom Saluran Penjualan.
  6. Di Nama kolom baru, masukkan Total unit, di Operasi, pilih Jumlah, dan di Kolom, pilih Unit.
  7. Pilih OK

Kelompokkan menurut kotak dialog dengan kolom agregat.

Operasi ini memberi Anda tabel berikut.

Contoh tabel output dengan kolom Negara, Saluran Penjualan, dan Total unit.

Operasi tersedia

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.

Melakukan operasi untuk mengelompokkan menurut satu atau beberapa kolom

Mulai dari sampel asli, dalam contoh ini Anda akan 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.

Contoh tabel output dengan operasi.

  1. Gunakan kolom berikut sebagai Kelompokkan menurut kolom:

    • Negara
    • Saluran Penjualan

  2. Buat dua kolom baru dengan mengikuti langkah-langkah berikut:

    1. Agregat kolom Unit dengan menggunakan operasi Jumlah . Beri nama kolom ini Total unit.
    2. Tambahkan kolom Produk baru dengan menggunakan operasi Semua baris.

    Kotak dialog Kelompokkan menurut dengan kolom non-agregat.

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 bisa memilih spasi kosong di dalam sel untuk melihat pratinjau konten tabel di bagian bawah kotak dialog.

Panel pratinjau detail tabel.

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.

Mengekstrak informasi 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.

Tambahkan kolom kustom.

Beri nama kolom baru Anda Produk berkinerja teratas. Masukkan rumus Table.Max([Products], "Units" ) di bawah Rumus kolom kustom.

Rumus kolom kustom dengan Table.Max.

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 .

Hasil rumus kolom kustom dengan Table.Max.

Dengan kolom produk pemain teratas baru ini yang berisi nilai [Rekaman], Anda dapat memilihMemperluas. ikon perluas, pilih bidang Produk dan Unit, lalu pilih OK.

Perluas operasi untuk nilai rekaman pada kolom produk berkinerja teratas.

Setelah menghapus kolom Produk Anda dan mengatur jenis data untuk kedua kolom yang baru diperluas, hasil Anda akan menyerupai gambar berikut.

Tabel akhir dengan semua transformasi.

Pengelompokan fuzzy

Catatan

Fitur berikut ini hanya tersedia di Power Query Online.

Untuk menunjukkan cara melakukan "pengelompokan fuzzy", pertimbangkan tabel sampel yang diperlihatkan dalam gambar berikut.

Tabel dengan sembilan baris entri yang berisi berbagai ejaan dan kapitalisasi nama Miguel dan William.

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 akan dikelompokkan menurut kolom Orang .

Tabel memperlihatkan entri untuk Orang sebagai

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.

Kotak centang Pengelompokan fuzzy dalam kotak dialog Kelompokkan menurut.

Untuk setiap grup baris, Power Query akan 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.

Tabel akhir sampel pengelompokan fuzzy, tidak ada tabel transformasi.

Namun, Anda memiliki kontrol lebih atas operasi pengelompokan fuzzy dengan memperluas opsi grup Fuzzy.

Opsi grup fuzzy.

Opsi berikut tersedia untuk pengelompokan fuzzy:

  • Ambang kesamaan (opsional): Opsi ini menunjukkan bagaimana dua nilai serupa harus dikelompokkan bersama. Pengaturan minimum 0 akan menyebabkan semua nilai dikelompokkan bersama-sama. Pengaturan maksimum 1 hanya akan memungkinkan nilai yang cocok persis untuk dikelompokkan bersama-sama. Defaultnya adalah 0,8.
  • Abaikan huruf besar/kecil: Saat membandingkan string teks, huruf besar/kecil akan diabaikan. Pemadatan diaktifkan secara default.
  • Kelompokkan dengan menggabungkan bagian teks: Algoritma akan 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 akan memetakan nilai (seperti memetakan MSFT ke Microsoft) untuk mengelompokkannya bersama-sama.

Untuk contoh ini, tabel transformasi akan 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.

Tabel memperlihatkan Dari nilai mike dan William, dan Ke nilai Miguel dan Bill.

Penting

Penting bahwa tabel transformasi memiliki nama kolom dan kolom yang sama seperti yang ditunjukkan di atas (harus "Dari" dan "Ke"), jika tidak Power Query tidak akan mengenali ini.

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.

Menu drop-down tabel transformasi sampel pengelompokan fuzzy.

Setelah Anda memilih tabel transformasi, pilih OK. Hasil operasi tersebut memberi Anda informasi berikut:

Tabel akhir sampel pengelompokan fuzzy dengan tabel transformasi.

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.

Lihat juga

Menambahkan kolom kustom
Menghapus duplikat