Membuat kueri parameter (Power Query)

Berlaku Untuk
Excel untuk Microsoft 365 Excel untuk Microsoft 365 untuk Mac

Anda mungkin cukup terbiasa dengan kueri parameter dengan penggunaannya di SQL atau Microsoft Query. Namun, parameter Power Query memiliki perbedaan utama:

  • Parameter dapat digunakan dalam langkah kueri apa pun. Selain berfungsi sebagai filter data, parameter dapat digunakan untuk menentukan hal-hal seperti jalur file atau nama server.
  • Parameter tidak meminta input. Sebagai gantinya, Anda dapat 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 tempat parameter digunakan. Setelah dibuat, Anda dapat menambahkan parameter ke kueri sesuai kebutuhan.

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

Membuat parameter

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> LainLuncurkan Editor Power Query.

  2. Di Editor Power Query, pilih Home>Manage Parameter Baru > 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.
    Diperlukan Lakukan salah satu langkah berikut:

    Nilai Apa Pun Anda bisa memasukkan nilai apa pun dari 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.

Menggunakan parameter untuk mengubah sumber data

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 folder Files>From) dari folder C:\DataFilesCSV1. Namun terkadang folder lain kadang-kadang digunakan sebagai lokasi untuk menjatuhkan file, C:\DataFilesCSV2. Anda bisa menggunakan parameter dalam kueri sebagai nilai pengganti untuk folder yang berbeda.

  1. PilihParameter BaruKelola>Beranda>.

  2. Masukkan informasi berikut dalam kotak dialog Kelola Parameter :

    Nama CSVFileDrop
    Deskripsi Lokasi peletakan file alternatif
    Diperlukan 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 tabKueri Koneksi Data>&> Kueri, klik kanan kueri parameter, lalu pilih Edit.
  2. Masukkan lokasi baru dalam kotak Nilai Saat Ini , seperti C:\DataFilesCSV2.
  3. Pilih Tutup Beranda>& Muat.
  4. Untuk mengonfirmasi hasil Anda, tambahkan data baru ke sumber data, lalu refresh kueri data dengan parameter yang diperbarui (Pilih Refresh Data>Semua).

Menggunakan parameter untuk memfilter data

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 pilihEditKueri>. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

  2. Pilih panah filter di header kolom apa pun untuk memfilter data Anda, lalu pilih perintah filter, seperti Filter >Tanggal/WaktuSetelah. 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 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 Tutup Beranda>& Muat.

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

  6. Masukkan tanggal baru dalam kotak Nilai Saat Ini .

  7. Pilih Tutup Beranda>& Muat.

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

Menggunakan nilai sel untuk memfilter data

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 diubah 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 Tutup Beranda>& 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 memilihEditKueri>. 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> TeksDimulai 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 Tutup Beranda>>& 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.

Mengontrol penggunaan kueri parameter

Anda dapat mengontrol apakah kueri parameter diperbolehkan atau tidak diperbolehkan.

  1. Di Editor Power Query, pilih Opsi File> danOpsi> KueriPengaturan>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.

Lihat Juga

Power Query untuk Bantuan Excel

Menggunakan Parameter Kueri (docs.com)