Membuat kueri parameter (Power Query)

Anda dapat menggunakan parameter untuk mengubah nilai dalam kueri secara otomatis dan menghindari pengeditan kueri setiap kali mengubah nilai. Anda hanya mengubah nilai parameter. Setelah Anda membuat parameter, parameter disimpan dalam kueri parameter khusus yang bisa Anda ubah dengan mudah langsung dari Excel.

1. Pilih Data > Dapatkan Data > Sumber Lain > Luncurkan 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 harus mencerminkan fungsi parameter, tetapi tetap sesingkat mungkin.
   Deskripsi	Ini bisa berisi detail apa pun yang akan membantu orang menggunakan parameter dengan benar.
   Wajib	Lakukan salah satu hal berikut: Nilai Apa Pun Anda bisa memasukkan nilai tipe data apa pun dalam kueri parameter. Daftar Nilai    Anda bisa membatasi nilai ke daftar tertentu dengan memasukkannya dalam kisi kecil. Anda juga harus memilih Nilai Default dan Nilai Saat Ini di bawah ini. Query Pilih kueri daftar, yang menyerupankan kolom Terstruktur Daftar yang dipisahkan oleh koma dan diapit kurung kurawal. Misalnya, bidang status Masalah mungkin memiliki tiga nilai: {"New", "Sedang Berlangsung", "Ditutup"}. Anda harus membuat kueri daftar sebelumnya dengan membuka Editor Lanjutan (pilih Beranda > Editor Lanjutan), menghapus templat kode, memasukkan daftar nilai dalam format daftar kueri, lalu memilih Selesai. Setelah selesai membuat parameter, kueri daftar ditampilkan dalam nilai parameter Anda.
   Jenis	Ini menentukan tipe data parameter.
   Nilai yang Disarankan	Jika diinginkan, tambahkan daftar nilai atau tentukan kueri untuk memberikan saran untuk input.
   Nilai Default	Ini hanya muncul jika Nilai yang Disarankan diatur ke Daftar nilai, dan menentukan item daftar mana yang merupakan default. Dalam hal ini, Anda harus memilih default.
   Nilai Saat Ini	Bergantung pada di mana Anda menggunakan parameter, jika ini kosong, kueri mungkin tidak mengembalikan hasil. Jika Diperlukan dipilih, Nilai Saat Ini tidak bisa kosong.

5. Untuk membuat parameter, pilih OK.

Berikut adalah cara untuk mengelola perubahan pada lokasi sumber data dan membantu mencegah kesalahan refresh. Misalnya, dengan asumsi skema dan sumber data yang serupa, buat parameter untuk mengubah sumber data dengan mudah dan membantu mencegah kesalahan refresh data. Terkadang server, database, folder, nama file, atau perubahan lokasi. Mungkin manajer database kadang-kadang menukar server, setetes file CSV bulanan masuk ke folder lain, atau Anda perlu beralih antara lingkungan pengembangan/pengujian/produksi dengan mudah.

Langkah 1: Membuat kueri parameter

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

1. Pilih Beranda > Kelola Parameter > Parameter Baru.

2. Masukkan informasi berikut dalam kotak dialog Kelola Parameter :

   Nama	CSVFileDrop
   Deskripsi	Lokasi peletakan 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 menurun.

3. Pilih OK.

Langkah 3: Memperbarui nilai parameter

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

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

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

3. Pilih Beranda > Tutup & Muat.

4. Untuk mengonfirmasi hasil Anda, 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 kueri yang sama sedikit berbeda. 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.
   
   

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

   • 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 dalam kotak Nilai Saat Ini , lalu pilih Beranda > Tutup & Muat.

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

6. Masukkan tanggal baru dalam kotak Nilai Saat Ini .

7. Pilih Beranda > Tutup & Muat.

8. Untuk mengonfirmasi hasil Anda, 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, Cukup perbarui nilai sel. Misalnya, Anda ingin memfilter kolom menurut huruf pertama, tetapi dengan mudah mengubah nilai menjadi huruf apa pun dari A ke Z.

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

   MyFilter
   G

2. Pilih sel 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 melewati nilai dalam tabel, bukan tabel itu sendiri, klik kanan nilai dalam 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 mereferensikan nilai Tabel sebagai kondisi filter. Referensi langsung ke Tabel Excel akan menyebabkan kesalahan.

5. Pilih Beranda > 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 apa 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 adalah tempat penampung sementara untuk nilai dalam tabel FilterCellValue yang Anda masukkan di langkah berikutnya.

10. Pilih panah di sisi kanan bilah rumus untuk menampilkan seluruh rumus. Berikut 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 dalam 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 dapat mengontrol apakah kueri parameter diperbolehkan atau tidak diperbolehkan.

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 hapus Selalu izinkan parameterisasi dalam dialog sumber data dan transformasi.