Katakanlah kita memiliki file Excel dengan beberapa tabel pintar:
Jika Anda memuat tabel ini ke Power Query dengan cara standar menggunakan perintah Data – Dapatkan data – Dari file – Dari buku (Data — Dapatkan Data — Dari file — Dari Buku Kerja), maka kita mendapatkan sesuatu seperti ini:
Gambar tersebut, menurut saya, sudah tidak asing lagi bagi banyak pengguna Power Query. Tabel bersarang serupa dapat dilihat setelah menggabungkan kueri (ala VLOOKUP), pengelompokan (perintah Kelompok oleh tab Transformasi), mengimpor semua file dari folder tertentu, dll.
Langkah logis berikutnya dalam situasi ini biasanya untuk memperluas semua tabel bersarang sekaligus – menggunakan tombol dengan panah ganda di header kolom Data:
Akibatnya, kami mendapatkan perakitan semua baris dari semua tabel menjadi satu kesatuan. Semuanya bagus, sederhana dan jelas.
Sekarang bayangkan bahwa kolom baru (Diskon) telah ditambahkan di tabel sumber dan/atau salah satu yang sudah ada (Kota) telah dihapus:
Kemudian permintaan kami setelah pembaruan akan mengembalikan gambar yang tidak begitu indah – diskon tidak muncul, dan kolom kota menjadi kosong, tetapi tidak hilang:
Dan mudah untuk mengetahui alasannya – di bilah rumus Anda dapat dengan jelas melihat bahwa nama kolom yang diperluas dikodekan dalam argumen fungsi Tabel.Perluas TabelColumn sebagai daftar dalam kurung kurawal.
Mengatasi masalah ini mudah. Pertama, mari kita dapatkan nama kolom dari header tabel mana pun (misalnya, yang pertama) menggunakan fungsi Tabel.Nama Kolom. Ini akan terlihat seperti:
Sini:
- #”Kolom lain dihapus” – nama langkah sebelumnya, dari mana kami mengambil data
- 0 {} – nomor tabel dari mana kita mengekstrak header (menghitung dari nol, yaitu 0 adalah tabel pertama)
- [Data] – nama kolom pada langkah sebelumnya, tempat tabel yang diperluas berada
Tetap menggantikan konstruksi yang diperoleh di bilah rumus ke dalam fungsi Tabel.Perluas TabelColumn pada langkah memperluas tabel alih-alih daftar hard-coded. Semuanya akan terlihat seperti ini pada akhirnya:
Itu saja. Dan tidak akan ada lagi masalah dengan memperluas tabel bersarang saat data sumber berubah.
- Membuat tabel multiformat dari satu lembar di Power Query
- Buat tabel dengan header berbeda dari beberapa file Excel
- Mengumpulkan data dari semua lembar buku menjadi satu tabel