Baca dalam bahasa Inggris

Bagikan melalui


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.

Contoh tabel awal.

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)

Tempat menemukan tombol Kelompokkan menurut

Anda dapat menemukan tombol Kelompokkan menurut di tiga tempat:

  • Pada tab Beranda , di grup Transformasi .

    Cuplikan layar pita Power Query dengan opsi Kelompokkan menurut yang ditekankan di tab Beranda.

  • Pada tab Transformasi , dalam grup Tabel .

    Cuplikan layar pita Power Query dengan opsi Kelompokkan menurut yang ditekankan di tab Transformasi.

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

    Cuplikan layar tabel dengan opsi Kelompokkan menurut yang ditekankan di 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 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

Cuplikan layar dialog Kelompokkan menurut dengan kolom agregat yang diisi.

Operasi ini memberi Anda tabel berikut.

Cuplikan layar tabel output sampel dengan kolom Unit Negara, Saluran Penjualan, dan Total.

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

Cuplikan layar tabel output sampel 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.

    Cuplikan layar dialog Kelompokkan menurut dengan kolom nonaggregate.

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.

Cuplikan layar 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.

Cuplikan layar pita Power Query dengan opsi Kolom kustom ditekankan di tab Tambahkan kolom.

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

Cuplikan layar dialog Kolom kustom dengan rumus untuk Table.Max dimasukkan.

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 .

Cuplikan layar hasil rumus kolom kustom dengan Table.Max.

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

Cuplikan layar operasi perluas untuk nilai rekaman pada kolom produk berkinerja teratas.

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

Cuplikan layar 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.

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

Cuplikan layar tabel memperlihatkan entri untuk Orang sebagai Miguel dan Mike, dan Frekuensi masing-masing sebagai 3 dan 2.

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.

Cuplikan layar kotak centang Pengelompokan Fuzzy ditekankan dalam kotak dialog Kelompokkan menurut.

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.

Cuplikan layar tabel akhir sampel pengelompokan Fuzzy, tidak ada tabel transformasi.

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

Cuplikan layar dialog Kelompokkan menurut dengan opsi grup Fuzzy ditekankan.

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.

Cuplikan layar 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 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.

Cuplikan layar menu drop-down tabel transformasi sampel pengelompokan fuzzy.

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

Cuplikan layar 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.

Untuk informasi selengkapnya tentang cara kerja tabel transformasi, buka Presepsi tabel Transformasi.

Lihat juga

Menambahkan kolom kustom
Menghapus duplikat