Bagikan melalui

Pelipatan kueri pada kueri asli

  • Artikel

Di Power Query, Anda bisa menentukan kueri asli dan menjalankannya terhadap sumber data Anda. Artikel Mengimpor data dari database menggunakan kueri database asli menjelaskan cara melakukan proses ini dengan beberapa sumber data. Namun, dengan menggunakan proses yang dijelaskan dalam artikel tersebut, kueri Anda tidak memanfaatkan pelipatan kueri apa pun dari langkah-langkah kueri berikutnya.

Artikel ini menampilkan metode alternatif untuk membuat kueri asli terhadap sumber data Anda menggunakan fungsi Value.NativeQuery dan menjaga mekanisme pelipatan kueri tetap aktif untuk langkah-langkah berikutnya dari kueri Anda.

Catatan

Kami menyarankan agar Anda membaca dokumentasi tentang pelipatan kueri dan indikator lipatan kueri untuk lebih memahami konsep yang digunakan di seluruh artikel ini.

Konektor data yang didukung

Metode yang dijelaskan di bagian berikutnya berlaku untuk konektor data berikut:

Menyambungkan ke target dari sumber data

Catatan

Untuk menampilkan proses ini, artikel ini menggunakan konektor SQL Server dan database sampel AdventureWorks2019. Pengalaman mungkin bervariasi dari konektor ke konektor, tetapi artikel ini menampilkan dasar-dasar tentang cara mengaktifkan kemampuan pelipatan kueri melalui kueri asli untuk konektor yang didukung.

Saat menyambungkan ke sumber data, penting bagi Anda untuk menyambungkan ke simpul atau tingkat tempat Anda ingin menjalankan kueri asli Anda. Untuk contoh dalam artikel ini, simpul tersebut adalah tingkat database di dalam server.

Cuplikan layar dialog pengaturan koneksi untuk koneksi ke database AdventureWorks2019 pada instans lokal SQL Server.

Setelah Anda menentukan pengaturan koneksi dan menyediakan kredensial untuk koneksi Anda, dialog navigasi untuk sumber data Anda terbuka. Dialog navigasi berisi semua objek yang tersedia yang bisa Anda sambungkan.

Dari daftar ini, Anda perlu memilih objek tempat kueri asli dijalankan (juga dikenal sebagai target). Untuk contoh ini, objek tersebut adalah tingkat database.

Di jendela navigator di Power Query, pilih dan tahan (atau klik kanan) simpul database di jendela navigator dan pilih opsi Ubah Data . Memilih opsi ini membuat kueri baru dari tampilan keseluruhan database Anda, yang merupakan target yang Anda butuhkan untuk menjalankan kueri asli Anda.

Cuplikan layar memilih data di mana pengguna mengklik kanan simpul database di navigator, dengan Transformasi Data ditekankan.

Setelah kueri Anda masuk ke editor Power Query, hanya langkah Sumber yang akan ditampilkan di panel Langkah yang diterapkan. Langkah ini berisi tabel dengan semua objek yang tersedia di database Anda, mirip dengan cara tabel ditampilkan di jendela Navigator.

Cuplikan layar kueri hanya dengan langkah sumber.

Gunakan fungsi Value.NativeQuery

Tujuan dari proses ini adalah untuk menjalankan kode SQL berikut, dan untuk menerapkan lebih banyak transformasi dengan Power Query yang dapat dilipat kembali ke sumbernya.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

Langkah pertama adalah menentukan target yang benar, yang dalam hal ini adalah database tempat kode SQL dijalankan. Setelah langkah memiliki target yang benar, Anda dapat memilih langkah tersebut—dalam hal ini, Sumber dalam Langkah yang Diterapkan—lalu pilih tombol fx di bilah rumus untuk menambahkan langkah kustom. Dalam contoh ini, ganti Source rumus dengan rumus berikut:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

Komponen terpenting dari rumus ini adalah penggunaan rekaman opsional untuk parameter keempat fungsi yang memiliki bidang rekaman EnableFolding diatur ke true.

Cuplikan layar rumus langkah kustom baru dengan penggunaan fungsi Value.NativeQuery dan kueri SQL eksplisit.

Catatan

Anda dapat membaca selengkapnya tentang fungsi Value.NativeQuery dari artikel dokumentasi resmi.

Setelah Anda memasukkan rumus, peringatan diperlihatkan yang mengharuskan Anda mengaktifkan kueri asli untuk dijalankan untuk langkah spesifik Anda. Pilih lanjutkan agar langkah ini dievaluasi.

Pernyataan SQL ini menghasilkan tabel dengan hanya tiga baris dan dua kolom.

Cuplikan layar dengan hasil kueri asli yang dievaluasi terhadap database target.

Menguji lipatan kueri

Untuk menguji pelipatan kueri kueri, Anda bisa mencoba menerapkan filter ke salah satu kolom Anda dan melihat apakah indikator pelipatan kueri di bagian langkah yang diterapkan memperlihatkan langkah sebagai dilipat. Untuk kasus ini, Anda dapat memfilter kolom DepartmentID untuk memiliki nilai yang tidak sama dengan dua.

Cuplikan layar yang menunjukkan cara memfilter kolom DepartmentID agar hanya memiliki nilai yang tidak sama dengan dua.

Setelah menambahkan filter ini, Anda bisa memeriksa apakah indikator pelipatan kueri masih memperlihatkan lipatan kueri yang terjadi pada langkah baru ini.

Langkah filter ditampilkan sebagai dilipat kembali ke sumber data di bagian langkah yang diterapkan.

Untuk memvalidasi lebih lanjut kueri apa yang sedang dikirim ke sumber data, Anda bisa memilih dan menahan (atau klik kanan) langkah Baris yang difilter dan memilih opsi Tampilkan rencana kueri untuk memeriksa rencana kueri untuk langkah tersebut.

Dalam tampilan paket kueri, Anda bisa melihat bahwa simpul dengan nama Value.NativeQuery yang memiliki hyperlink Detail Tampilan. Anda bisa memilih hyperlink ini untuk menampilkan kueri yang tepat yang dikirim ke database SQL Server.

Kueri asli dibungkus di sekitar pernyataan SELECT lain untuk membuat subkueri dari aslinya. Power Query melakukan yang terbaik untuk membuat kueri yang paling optimal mengingat transformasi yang digunakan dan kueri asli yang disediakan.

Cuplikan layar rencana kueri untuk langkah Baris yang difilter.

Tip

Untuk skenario di mana Anda mendapatkan kesalahan karena pelipatan kueri tidak dimungkinkan, kami sarankan Anda mencoba memvalidasi langkah-langkah Anda sebagai subkueri dari kueri asli asli Anda untuk memeriksa apakah mungkin ada sintaks atau konflik konteks.