Membuat kueri parameter (Power Query)

Anda mungkin sudah tidak asing lagi dengan kueri parameter dengan penggunaannya dalam SQL atau Microsoft Query. Namun, parameter Power Query memiliki perbedaan utama:    

  • Parameter dapat digunakan dalam langkah kueri apa pun. Selain untuk berfungsi sebagai filter data, parameter bisa digunakan untuk menentukan hal-hal seperti jalur file atau nama server.

  • Parameter tidak meminta input. Sebagai gantinya, Anda bisa dengan cepat mengubah nilainya menggunakan Power Query. Anda bahkan bisa menyimpan dan mengambil nilai dari sel di Excel.

  • Parameter disimpan dalam kueri parameter sederhana, tetapi terpisah dari kueri data yang digunakan di kueri.  Setelah dibuat, Anda dapat menambahkan parameter ke kueri sesuai kebutuhan.

Catatan    Jika ingin cara lain untuk membuat kueri parameter, lihat Membuat kueri parameter di Microsoft Query.

Anda bisa menggunakan parameter untuk mengubah nilai dalam kueri secara otomatis dan menghindari mengedit kueri setiap kali untuk mengubah nilai tersebut. Anda cukup mengubah nilai parameter. Setelah Anda membuat parameter, parameter ini disimpan dalam kueri parameter khusus yang bisa Langsung diubah dengan mudah dari Excel.

  1. Pilih Data > Dapatkan Data > Sumber Lain > Meluncurkan Editor Power Query.

  2. Di Editor Power Query, pilih Beranda>Kelola Parameter > Parameter Baru.

  3. Dalam kotak dialog Kelola Parameter, pilih Baru.

  4. Atur hal berikut ini sebagaimana diperlukan:

    Nama    

    Ini akan mencerminkan fungsi parameter tersebut, namun buat sepanjang mungkin.

    Deskripsi    

    Ini dapat berisi detail apa pun yang akan membantu orang-orang menggunakan parameter dengan benar.

    Wajib    

    Lakukan salah satu hal berikut:

    Nilai Apa pun Anda dapat memasukkan nilai apa pun dari tipe data apa pun dalam kueri parameter.

    Daftar Nilai    Anda bisa membatasi nilai untuk daftar tertentu dengan memasukkannya dalam kisi kecil. Anda juga harus memilih Nilai Default dan Nilai Saat Ini di bawah ini.

    Kueri Pilih daftar kueri, yang menyerupai kolom terstruktur Daftar yang dipisahkan dengan koma dan diapit dalam kurung kurawal.

    Misalnya, bidang status Masalah dapat memiliki tiga nilai: {"Baru", "Saat ini", "Ditutup"}. Anda harus membuat kueri daftar sebelumnya dengan membuka Editor Tingkat Lanjut (pilih Beranda > EditorTingkat Lanjut ), menghapus templat kode, memasukkan daftar nilai dalam format daftar kueri, lalu pilih Selesai.

    Setelah selesai membuat parameter, kueri daftar akan ditampilkan dalam nilai parameter Anda.

    Jenis    

    Fungsi ini menentukan tipe data parameter.

    Nilai yang Disarankan    

    Jika diinginkan, tambahkan daftar nilai atau tentukan kueri untuk menyediakan saran untuk input.

    Nilai Default

    Hal ini hanya muncul jika Nilai yang Disarankan diatur ke Daftar nilai,dan menentukan item daftar mana yang merupakan default. Dalam kasus ini, Anda harus memilih default.

    Nilai Saat Ini    

    Bergantung pada di mana Anda menggunakan parameter, jika kueri kosong mungkin tidak mengembalikan hasil. Jika Diperlukan dipilih, Nilai Saat Ini tidak bisa kosong.

  5. Untuk membuat parameter, pilih OK.

Berikut cara untuk mengelola perubahan ke lokasi sumber data dan membantu mencegah kesalahan refresh. Misalnya, dengan mengasumsikan skema dan sumber data yang serupa, buat parameter untuk mengubah sumber data dan membantu mencegah kesalahan refresh data dengan mudah. Terkadang server, database, folder, nama file, atau lokasi berubah. Mungkin manajer database terkadang menukar server, penurunan file CSV bulanan masuk ke folder lain, atau Anda harus beralih antara lingkungan pengembangan/uji/produksi dengan mudah.

Langkah 1: Membuat kueri parameter

Dalam contoh berikut, Anda memiliki beberapa file CSV yang diimpor menggunakan operasi impor folder (Pilih Data > Dapatkan Data> Dari File > Dari Folder) dari folder C:\DataFilesCSV1. Namun, terkadang folder lain terkadang digunakan sebagai lokasi untuk penyimpanan file, C:\DataFilesCSV2. Anda bisa menggunakan parameter dalam kueri sebagai nilai pengganti untuk folder berbeda.

  1. Pilih Beranda> Kelola Parameter > Parameter Baru.

  2. Masukkan informasi berikut dalam kotak dialog Kelola Parameter:

    Nama

    CSVFileDrop

    Deskripsi

    Lokasi jatuhkan file alternatif

    Wajib

    Ya

    Jenis

    Teks

    Nilai yang Disarankan

    Nilai apa pun

    Nilai Saat Ini

    C:\DataFilesCSV1

  3. Pilih OK.

Langkah 2: Menambahkan parameter ke kueri data

  1. Untuk mengatur nama folder sebagai parameter, di Pengaturan Kueri, di bawah Langkah Kueri, pilih Sumber, lalu pilih Edit Pengaturan.

  2. Pastikan opsi Jalur file diatur ke Parameter,lalu pilih parameter yang baru saja Anda buat dari daftar turun bawah.

  3. Pilih OK.

Langkah 3: Perbarui nilai parameter

Lokasi folder baru saja berubah, jadi sekarang Anda bisa cukup memperbarui kueri parameter.

  1. Pilih Data> Kueri & > Kueri, klik kanan kueri parameter, lalu pilih Edit.

  2. Masukkan lokasi baru dalam kotak Nilai Saat Ini, seperti C:\DataFilesCSV2.

  3. Pilih Pengaturan> Tutup & Muat.

  4. Untuk mengonfirmasi hasil, tambahkan data baru ke sumber data, lalu refresh kueri data dengan parameter yang diperbarui (Pilih Data > Refresh Semua).

Terkadang, Anda menginginkan cara yang mudah untuk mengubah filter kueri untuk mendapatkan hasil yang berbeda tanpa mengedit kueri atau membuat salinan yang sedikit berbeda dari kueri yang sama. Dalam contoh ini, kami mengubah tanggal untuk mengubah filter data dengan mudah.

  1. Untuk membuka kueri, temukan yang sebelumnya dimuat dari Editor Power Query, pilih sel dalam data, lalu pilih Kueri >Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

  2. Pilih panah filter di header kolom mana pun untuk memfilter data Anda, lalu pilih perintah filter, seperti Filter Tanggal/Waktu > Setelah. Kotak dialog Filter Baris muncul.

    Memasukkan parameter dalam kotak dialog Filter

  3. Pilih tombol di sebelah kiri kotak Nilai, lalu lakukan salah satu hal berikut:

    • Untuk menggunakan parameter yang sudah ada, pilih Parameter, lalu pilih parameter yang Anda inginkan dari daftar yang muncul di sebelah kanan.

    • Untuk menggunakan parameter baru, pilih Parameter Baru, lalu buat parameter.

  4. Masukkan tanggal baru di kotak Nilai Saat Ini, lalu pilih Beranda> Tutup & Muat.

  5. Untuk mengonfirmasi hasil, tambahkan data baru ke sumber data, lalu refresh kueri data dengan parameter yang diperbarui (Pilih Data > Refresh Semua). Misalnya, ubah nilai filter ke tanggal lain untuk melihat hasil baru.

  6. Masukkan tanggal baru di kotak Nilai Saat Ini.

  7. Pilih Pengaturan> Tutup & Muat.

  8. Untuk mengonfirmasi hasil, tambahkan data baru ke sumber data, lalu refresh kueri data dengan parameter yang diperbarui (Pilih Data > Refresh Semua).

Dalam contoh ini, nilai dalam parameter kueri dibaca dari sel dalam buku kerja Anda. Anda tidak perlu mengubah kueri parameter, Anda cukup memperbarui nilai sel. Misalnya, Anda ingin memfilter kolom berdasarkan huruf pertama, namun dengan mudah mengubah nilai menjadi huruf apa pun dari A ke Z.

  1. Pada lembar kerja di buku kerja tempat kueri yang ingin Anda filter dimuat, buat tabel Excel dengan dua sel: header dan nilai.
     

    MyFilter

    G

  2. Pilih sel di dalam tabel Excel, lalu pilih Data > Dapatkan Data > Dari Tabel/Rentang. Editor Power Query muncul.

  3. Dalam kotak Nama panel Pengaturan Kueri di sebelah kanan, ubah nama kueri menjadi lebih bermakna, seperti FilterCellValue. 

  4. Untuk meneruskan nilai dalam tabel, dan bukan tabel itu sendiri, klik kanan nilai di Pratinjau Data, lalu pilih Telusuri Paling Detail.

    Perhatikan bahwa rumus berubah menjadi = #"Changed Type"{0}[MyFilter]

    Saat Anda menggunakan Tabel Excel sebagai filter di langkah 10, Power Query merujuk nilai Tabel sebagai kondisi filter. Referensi langsung ke Tabel Excel akan menyebabkan kesalahan.

  5. Pilih Pengaturan > Tutup & Muat > Tutup & Muat Ke. Sekarang Anda memiliki parameter kueri bernama "FilterCellValue" yang Anda gunakan di langkah 12.

  6. Dalam kotak dialog Impor Data, pilih Hanya Buat Koneksi, lalu pilih OK.

  7. Buka kueri yang ingin Anda filter dengan nilai dalam tabel FilterCellValue, yang sebelumnya dimuat dari Editor Power Query, dengan memilih sel dalam data, lalu memilih Kueri > Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

  8. Pilih panah filter di header kolom mana pun untuk memfilter data Anda, lalu pilih perintah filter, seperti Filter Teks > Dimulai Dengan. Kotak dialog Filter Baris muncul. 

  9. Masukkan nilai apa pun dalam kotak Nilai, seperti "G" lalu pilih OK. Dalam hal ini, nilai merupakan tempat penampung sementara untuk nilai dalam tabel FilterCellValue yang Anda masukkan dalam langkah berikutnya.

  10. Pilih panah di sisi kanan bilah rumus untuk menampilkan seluruh rumus. Berikut ini adalah contoh kondisi filter dalam rumus:

    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

  11. Pilih nilai filter. Dalam rumus, pilih "G".

  12. Dengan menggunakan M Intellisense, masukkan beberapa huruf pertama tabel FilterCellValue yang anda buat, lalu pilih dari daftar yang muncul.

  13. Pilih Beranda > Tutup > Tutup & Muat.

Hasil

Kueri Anda sekarang menggunakan nilai di Tabel Excel yang Anda buat untuk memfilter hasil kueri. Untuk menggunakan nilai baru, edit konten sel dalam tabel Excel asli di langkah 1, ubah "G" menjadi "V", lalu refresh kueri.

Anda bisa mengontrol apakah kueri parameter diperbolehkan atau tidak.

  1. Di Editor Power Query, pilih Opsi File> dan Pengaturan > Opsi Kueri > Editor Power Query.

  2. Di panel sebelah kiri, di bawah GLOBAL,pilih Editor Power Query.

  3. Di panel sebelah kanan, di bawah Parameter, pilih atau kosongkan Selalu perbolehkan parameterisasi dalam dialog sumber data dan transformasi.

Lihat Juga

Bantuan Power Query untuk Excel

Menggunakan perintah Parameter Kueri (docs.com)

Perlu bantuan lainnya?

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×