Optimalkan Power Query saat memperluas kolom tabel

Kesederhanaan dan kemudahan penggunaan yang memungkinkan pengguna Power BI mengumpulkan data dengan cepat dan menghasilkan laporan yang menarik dan kuat untuk membuat keputusan bisnis cerdas juga memungkinkan pengguna untuk dengan mudah menghasilkan kueri berkinerja buruk. Ini sering terjadi ketika ada dua tabel yang terkait dengan cara kunci asing berkaitan dengan tabel SQL atau daftar SharePoint. (Untuk catatan, masalah ini tidak spesifik untuk SQL atau SharePoint, dan terjadi dalam banyak skenario ekstraksi data backend, terutama di mana skema cairan dan dapat disesuaikan.) Tidak ada juga yang salah secara inheren dengan menyimpan data dalam tabel terpisah yang berbagi kunci umum—sebenarnya ini adalah prinsip dasar desain dan normalisasi database. Tapi itu menyiratkan cara yang lebih baik untuk memperluas hubungan.

Pertimbangkan contoh daftar pelanggan SharePoint berikut.

Dan daftar lokasi berikut yang dirujuknya.

Saat pertama kali menyambungkan ke daftar, lokasi muncul sebagai rekaman.

Data tingkat atas ini dikumpulkan melalui satu panggilan HTTP ke API SharePoint (mengabaikan panggilan metadata), yang dapat Anda lihat di debugger web apa pun.

Saat Anda memperluas rekaman, Anda akan melihat bidang yang digabungkan dari tabel sekunder.

Saat memperluas baris terkait dari satu tabel ke tabel lainnya, perilaku default Power BI adalah menghasilkan panggilan ke Table.ExpandTableColumn. Anda dapat melihat ini di bidang rumus yang dihasilkan. Sayangnya, metode ini menghasilkan panggilan individual ke tabel kedua untuk setiap baris dalam tabel pertama.

Ini meningkatkan jumlah panggilan HTTP satu per satu untuk setiap baris dalam daftar utama. Ini mungkin tidak tampak seperti banyak dalam contoh di atas lima atau enam baris, tetapi dalam sistem produksi di mana daftar SharePoint mencapai ratusan ribu baris, ini dapat menyebabkan penurunan pengalaman yang signifikan.

Ketika kueri mencapai hambatan ini, mitigasi terbaik adalah menghindari perilaku panggilan per baris dengan menggunakan gabungan tabel klasik. Ini memastikan bahwa hanya akan ada satu panggilan untuk mengambil tabel kedua, dan sisa ekspansi dapat terjadi dalam memori menggunakan kunci umum antara dua tabel. Perbedaan performa bisa sangat besar dalam beberapa kasus.

Pertama, mulailah dengan tabel asli, catat kolom yang ingin Anda perluas, dan pastikan Anda memiliki ID item sehingga Anda bisa mencocokkannya. Biasanya kunci asing diberi nama yang mirip dengan nama tampilan kolom dengan Id ditambahkan. Dalam contoh ini, ini adalah LocationId.

Kedua, muat tabel sekunder, pastikan untuk menyertakan Id, yang merupakan kunci asing. Klik kanan pada panel Kueri untuk membuat kueri baru.

Terakhir, gabungkan dua tabel menggunakan masing-masing nama kolom yang cocok. Anda biasanya dapat menemukan bidang ini dengan terlebih dahulu memperluas kolom, lalu mencari kolom yang cocok dalam pratinjau.

Dalam contoh ini, Anda dapat melihat bahwa LocationId di daftar utama cocok dengan Id di daftar sekunder. UI mengganti nama ini menjadi Location.Id untuk membuat nama kolom unik. Sekarang mari kita gunakan informasi ini untuk menggabungkan tabel.

Dengan mengklik kanan pada panel kueri dan memilih Kueri Baru>Gabungkan>Kueri sebagai Baru, Anda akan melihat UI yang ramah untuk membantu Anda menggabungkan kedua kueri ini.

Pilih setiap tabel dari menu drop-down untuk melihat pratinjau kueri.

Setelah Anda memilih kedua tabel, pilih kolom yang menggabungkan tabel secara logis (dalam contoh ini, itu LocationId dari tabel utama dan Id dari tabel sekunder). Dialog akan menginstruksikan berapa banyak baris yang cocok menggunakan kunci asing tersebut. Anda mungkin ingin menggunakan jenis gabungan default (kiri luar) untuk data semacam ini.

Pilih OK dan Anda akan melihat kueri baru, yang merupakan hasil gabungan. Memperluas rekaman sekarang tidak menyiratkan panggilan tambahan ke backend.

Merefresh data ini hanya akan menghasilkan dua panggilan ke SharePoint—satu untuk daftar utama, dan satu untuk daftar sekunder. Gabungan akan dilakukan dalam memori, secara signifikan mengurangi jumlah panggilan ke SharePoint.

Pendekatan ini dapat digunakan untuk dua tabel apa pun di PowerQuery yang memiliki kunci asing yang cocok.

Catatan

Daftar pengguna dan taksonomi SharePoint juga dapat diakses sebagai tabel, dan dapat digabungkan dengan cara yang dijelaskan di atas, asalkan pengguna memiliki hak istimewa yang memadai untuk mengakses daftar ini.