Membuat kueri parameter (Power Query)

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

Anda mungkin cukup akrab 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 dapat menyimpan dan mengambil nilai dari sel di Excel.
  • Parameter disimpan dalam kueri parameter sederhana, tetapi terpisah dari kueri data yang digunakan. Setelah dibuat, Anda dapat menambahkan parameter ke kueri sesuai kebutuhan.

catatan Jika 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 untuk mengubah nilai tersebut. Anda cukup mengubah nilai parameter. Setelah Anda membuat parameter, parameter disimpan dalam kueri parameter khusus yang dapat Anda ubah secara 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 sesuai kebutuhan:

    Nama Ini harus mencerminkan fungsi parameter, tetapi tetap sesingkat mungkin.
    Deskripsi Ini dapat berisi detail apa pun yang akan membantu orang menggunakan parameter dengan benar.
    Wajib Lakukan salah satu langkah berikut:

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

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

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

    Misalnya, bidang status Masalah dapat memiliki tiga nilai: {"Baru", "Sedang Berlangsung", "Ditutup"}. Anda harus membuat kueri daftar terlebih dahulu dengan membuka Editor Lanjutan (pilih HomeEditor>Lanjutan), menghapus templat kode, memasukkan daftar nilai dalam format daftar kueri, lalu memilih Selesai.

    Setelah selesai membuat parameter, kueri daftar akan ditampilkan dalam nilai parameter Anda.
    Tipe Tindakan ini menentukan tipe data parameter.
    Nilai yang Disarankan Jika diinginkan, tambahkan daftar nilai atau tentukan kueri untuk memberikan saran input.
    Nilai default Hal 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 Tergantung pada tempat Anda menggunakan parameter, jika parameter ini kosong, kueri mungkin tidak akan mengembalikan hasil. Jika Diperlukan dipilih, Nilai Saat Ini tidak boleh 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 lokasi berubah. Mungkin manajer database kadang-kadang menukar server, penurunan file CSV bulanan masuk ke folder yang berbeda, atau Anda perlu dengan mudah beralih antara lingkungan pengembangan/pengujian/produksi.

Langkah 1: Membuat kueri parameter

Dalam contoh berikut, Anda memiliki beberapa file CSV yang diimpor menggunakan operasi folder impor (Pilih Data>Dapatkan Data>Dari FilesFrom>Folder) dari folder C:\DataFilesCSV1. Namun, terkadang folder yang berbeda terkadang digunakan sebagai lokasi untuk menjatuhkan file, C:\DataFilesCSV2. Anda dapat menggunakan parameter dalam kueri sebagai nilai pengganti folder yang 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
    Tipe Teks
    Nilai yang Disarankan Nilai apa pun
    Nilai Saat Ini C:\DataFilesCSV1

  3. Pilih OK.

Langkah 2: Tambahkan parameter ke kueri data

  1. Untuk mengatur nama folder sebagai parameter, di Pengaturan Kueri, di bawah Langkah-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: Perbarui nilai parameter

Lokasi folder baru saja berubah, jadi sekarang Anda cukup 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 Beranda>,Tutup, & Muat.
  4. Untuk mengonfirmasi hasil, 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 mudah untuk mengubah filter kueri guna mendapatkan hasil yang berbeda tanpa mengedit kueri atau membuat salinan kueri yang sama yang sedikit berbeda. Dalam contoh ini, kami mengubah tanggal untuk mengubah filter data dengan mudah.

  1. Untuk membuka kueri, temukan kueri yang sebelumnya dimuat dari Editor Power Query, pilih sel dalam data, lalu pilih Edit Kueri>. 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>/WaktuSetelahnya. Kotak dialog Filter Baris akan 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 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, tambahkan data baru ke sumber data, lalu refresh kueri data dengan parameter yang diperbarui (Pilih Refresh Data>Semua). Misalnya, ubah nilai filter ke tanggal yang berbeda untuk melihat hasil baru.

  6. Masukkan tanggal baru dalam kotak Nilai Saat Ini .

  7. Pilih Beranda>,Tutup, & Muat.

  8. Untuk mengonfirmasi hasil, 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 di buku kerja Anda. Anda tidak perlu mengubah kueri parameter, Anda cukup memperbarui nilai sel. Misalnya, Anda ingin memfilter kolom menurut huruf pertama, tetapi dengan mudah mengubah nilai ke huruf apa pun dari A sampai Z.

  1. Di 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 akan muncul.

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

  4. Untuk meneruskan nilai dalam tabel, dan bukan tabel itu sendiri, klik kanan nilai dalam Pratinjau Data, lalu pilih Telusuri Lebih Rendah.
    Perhatikan bahwa rumus berubah menjadi = #"Changed Type"{0}[MyFilter]
    Ketika 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. Kini Anda memiliki parameter kueri bernama "FilterCellValue" yang Anda gunakan pada 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 Edit Kueri>. 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 akan muncul.

  9. Masukkan nilai apa pun dalam kotak Nilai , seperti "G", lalu pilih OK. Dalam kasus ini, nilai adalah placeholder sementara untuk nilai dalam tabel FilterCellValue yang Anda masukkan pada langkah berikutnya.

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

    = Table.SelectRows(#"Tipe yang Diubah", setiap Text.StartsWith([name], "g"))

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

  12. Menggunakan M Intellisense, masukkan beberapa huruf pertama dari tabel FilterCellValue yang Anda buat, lalu pilih dari daftar yang muncul.

  13. Pilih Beranda>, tutup, tutup>,& muat.

Hasil

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

Mengontrol penggunaan kueri parameter

Anda dapat mengontrol apakah kueri parameter diizinkan atau tidak.

  1. Di Editor Power Query, pilih>Opsi File dan Pengaturan>Opsi KueriEditor>Power Query.
  2. Di panel di sebelah kiri, di bawah GLOBAL, pilih Editor Power Query.
  3. Di panel di sebelah kanan, di bawah Parameter, pilih atau hapus Selalu izinkan parameterisasi dalam dialog transformasi dan sumber data.

Lihat Juga

Bantuan Power Query untuk Excel

Gunakan parameter kueri (docs.com)