Kolom unpivot
Di Power Query, Anda bisa mengubah kolom menjadi pasangan atribut-nilai, di mana kolom menjadi baris.
Diagram memperlihatkan tabel kiri dengan kolom dan baris kosong, dan nilai Atribut A1, A2, dan A3 sebagai header kolom. Dalam tabel ini, kolom A1 berisi nilai V1, V4, dan V7. Kolom A2 berisi nilai V2, V5, dan V8. Kolom A3 berisi nilai V3, V6, dan V9. Dengan kolom yang tidak dipivot, tabel kanan diagram berisi kolom dan baris kosong, kolom Atribut dengan sembilan baris dengan A1, A2, dan A3 diulang tiga kali, dan kolom Nilai dengan nilai V1 hingga V9.
Misalnya, mengingat tabel seperti berikut ini, di mana baris negara dan kolom tanggal membuat matriks nilai, sulit untuk menganalisis data dengan cara yang dapat diskalakan.
Cuplikan layar tabel yang berisi kolom Negara yang diatur dalam tipe data Teks, dan tiga kolom dengan tanggal 1 Juni 2023, 1 Juli 2023, dan 1 Agustus 2023 ditetapkan sebagai tipe Data bilangan bujur. Kolom Negara berisi AS di baris 1, Kanada di baris 2, dan Panama di baris 3.
Sebagai gantinya, Anda dapat mengubah tabel menjadi tabel dengan kolom yang tidak dipivot, seperti yang ditunjukkan pada gambar berikut. Dalam tabel yang diubah, lebih mudah untuk menggunakan tanggal sebagai atribut untuk memfilter.
Cuplikan layar tabel yang berisi kolom Negara yang ditetapkan sebagai tipe data Teks, kolom Atribut diatur sebagai jenis data Teks, dan kolom Nilai yang ditetapkan sebagai tipe data Bilangan Bujur. Kolom Negara berisi AS dalam tiga baris pertama, Kanada di tiga baris berikutnya, dan Panama dalam tiga baris terakhir. Kolom Atribut berisi tanggal 1 Juni 2023 dalam baris pertama, keempat, dan ketujuh, tanggal 1 Juli 2023 pada baris kedua, kelima, dan kedelapan, dan tanggal 1 Agustus 2023 di baris ketiga, keenam, dan kesembilan.
Kunci dalam transformasi ini adalah Anda memiliki sekumpulan tanggal dalam tabel yang semuanya harus menjadi bagian dari satu kolom. Nilai masing-masing untuk setiap tanggal dan negara harus berada di kolom yang berbeda, secara efektif membuat pasangan atribut-nilai.
Power Query selalu membuat pasangan atribut-nilai dengan menggunakan dua kolom:
- Atribut: Nama judul kolom yang tidak dipivot.
- Nilai: Nilai yang berada di bawah masing-masing judul kolom yang tidak dipivot.
Ada beberapa tempat di antarmuka pengguna tempat Anda dapat menemukan kolom Unpivot. Anda bisa mengklik kanan kolom yang ingin Anda pivot, atau Anda bisa memilih perintah dari tab Transformasi di pita.
Ada tiga cara untuk membuka pivot kolom dari tabel:
- Kolom unpivot
- Batalkan pivot kolom lainnya
- Batalkan pivot hanya kolom yang dipilih
Untuk skenario yang dijelaskan sebelumnya, Anda harus terlebih dahulu memilih kolom yang ingin Anda batalkan pivotnya. Anda dapat memilih Ctrl sebanyak yang Anda pilih kolom sebanyak yang Anda butuhkan. Untuk skenario ini, Anda ingin memilih semua kolom kecuali kolom bernama Negara. Setelah memilih kolom, klik kanan salah satu kolom yang dipilih, lalu pilih Batalkan pivot kolom.
Cuplikan layar tabel dengan kolom 1 Juni 2023, 1 Juli 2023, dan 1 Agustus 2023 dipilih, dan perintah Kolom unpivot dipilih di menu pintasan."
Hasil dari operasi tersebut menghasilkan hasil yang ditunjukkan pada gambar berikut.
Setelah membuat kueri Anda dari langkah-langkah sebelumnya, bayangkan tabel awal Anda diperbarui agar terlihat seperti cuplikan layar berikut.
Cuplikan layar tabel dengan negara asli yang sama, 1 Juni 2023, 1 Juli 2023, dan kolom tanggal Agustus 2023, dengan penambahan kolom tanggal 1 September 2023. Kolom Negara masih berisi nilai AS, Kanada, dan Panama, tetapi juga memiliki Inggris ditambahkan ke baris keempat dan Meksiko ditambahkan ke baris kelima.
Perhatikan bahwa Anda menambahkan kolom baru untuk tanggal 1 September 2023 (1/9/2023), dan dua baris baru untuk negara/wilayah Inggris dan Meksiko.
Jika Anda me-refresh kueri, perhatikan bahwa operasi dilakukan pada kolom yang diperbarui, tetapi tidak memengaruhi kolom yang awalnya tidak dipilih (Negara, dalam contoh ini). Ini berarti bahwa kolom baru yang Anda tambahkan ke tabel sumber juga tidak dipivot.
Gambar berikut ini memperlihatkan seperti apa kueri Anda setelah refresh dengan tabel sumber baru yang diperbarui.
Cuplikan layar tabel dengan kolom Negara, Atribut, dan Nilai. Empat baris pertama kolom Negara berisi AS, empat baris kedua berisi Kanada, empat baris ketiga berisi Panama, empat baris keempat berisi Inggris, dan empat baris kelima berisi Meksiko. Kolom Atribut berisi tanggal 1 Juni 2023, 1 Juli 2023, dan Agustus 2023 dalam empat baris pertama, yang diulang untuk setiap negara.
Anda juga dapat memilih kolom yang tidak ingin Anda pivot dan membatalkan pivot kolom lainnya dalam tabel. Operasi ini adalah tempat Unpivot kolom lain dimainkan.
Hasil dari operasi tersebut menghasilkan hasil yang sama persis dengan yang Anda dapatkan dari kolom Unpivot.
Cuplikan layar tabel yang berisi kolom Negara yang ditetapkan sebagai tipe data Teks, kolom Atribut diatur sebagai jenis data Teks, dan kolom Nilai yang ditetapkan sebagai tipe data Bilangan Bujur. Kolom Negara berisi AS dalam tiga baris pertama, Kanada di tiga baris berikutnya, dan Panama dalam tiga baris terakhir. Kolom Atribut berisi tanggal 1 Juni 2023 dalam baris pertama, keempat, dan ketujuh, tanggal 1 Juli 2023 pada baris kedua, kelima, dan kedelapan, dan tanggal 1 Agustus 2023 di baris ketiga, keenam, dan kesembilan.
Catatan
Transformasi ini sangat penting untuk kueri yang memiliki jumlah kolom yang tidak diketahui. Operasi akan membatalkan pivot semua kolom dari tabel Anda kecuali yang telah Anda pilih. Ini adalah solusi ideal jika sumber data skenario Anda mendapatkan kolom tanggal baru dalam refresh, karena itu akan diambil dan tidak dipivot.
Mirip dengan operasi kolom Unpivot, jika kueri Anda di-refresh dan lebih banyak data diambil dari sumber data, semua kolom tidak dipivot kecuali yang sebelumnya dipilih.
Untuk mengilustrasikan proses ini, katakanlah Anda memiliki tabel baru seperti yang ada di gambar berikut.
Cuplikan layar tabel dengan kolom Negara, 1 Juni 2023, 1 Juli 2023, 1 Agustus 2023, dan 1 September 2023, dengan semua kolom diatur ke jenis data Teks. Kolom Negara berisi, dari atas ke bawah, AS, Kanada, Panama, Inggris, dan Meksiko.
Anda dapat memilih kolom Negara , lalu memilih Batalkan pivot kolom lain, yang menghasilkan hasil berikut.
Cuplikan layar tabel dengan kolom Negara, Atribut, dan Nilai. Kolom Negara dan Atribut diatur ke jenis data Teks. Kolom Nilai diatur ke tipe data Seluruh nilai. Empat baris pertama kolom Negara berisi AS, empat baris kedua berisi Kanada, empat baris ketiga berisi Panama, empat baris keempat berisi Inggris, dan empat baris kelima berisi Meksiko. Kolom Atribut berisi 1 Juni 2023, 1 Juli 2023, 1 Agustus 2023, dan 1 September 2023 dalam empat baris pertama, yang diulang untuk setiap negara.
Tujuan dari opsi terakhir ini adalah untuk hanya membuka pivot kolom tertentu dari tabel Anda. Opsi ini penting untuk skenario di mana Anda berurusan dengan jumlah kolom yang tidak diketahui dari sumber data Anda dan Anda hanya ingin membatalkan pivot kolom yang dipilih.
Untuk melakukan operasi ini, pilih kolom yang akan dilepas pivotnya, yang dalam contoh ini adalah semua kolom kecuali kolom Negara . Kemudian klik kanan salah satu kolom yang Anda pilih, lalu pilih Batalkan pivot hanya kolom yang dipilih.
Perhatikan bagaimana operasi ini menghasilkan output yang sama dengan contoh sebelumnya.
Cuplikan layar tabel yang berisi kolom Negara yang ditetapkan sebagai tipe data Teks, kolom Atribut diatur sebagai jenis data Teks, dan kolom Nilai yang ditetapkan sebagai tipe data Bilangan Bujur. Kolom Negara berisi AS dalam tiga baris pertama, Kanada di tiga baris berikutnya, dan Panama dalam tiga baris terakhir. Kolom Atribut berisi tanggal 1 Juni 2023 dalam baris pertama, keempat, dan ketujuh, tanggal 1 Juli 2023 pada baris kedua, kelima, dan kedelapan, dan tanggal 1 Agustus 2023 di baris ketiga, keenam, dan kesembilan.
Setelah melakukan refresh, jika tabel sumber kami berubah memiliki kolom 9/1/2020 baru dan baris baru untuk Inggris dan Meksiko, output kueri berbeda dari contoh sebelumnya. Katakanlah tabel sumber kami, setelah refresh, berubah ke tabel dalam gambar berikut.
Output kueri kami terlihat seperti gambar berikut.
Sepertinya ini karena operasi unpivot hanya diterapkan pada kolom 6/1/2020, 7/1/2020, dan 8/1/2020 , sehingga kolom dengan header 9/1/2020 tetap tidak berubah.