Membuat PowerQuery bisa menggunakan data/parameters yang dinamis

Powerquery adalah salah satu alat di Excel yang sangat powerfull namun akan menimbulkan kesulitan bagi pihak yang tidak memiliki pengetahuan tentang PQ ataupun bagi yang menguasai PQ kurang luwes karena setiap kali mesti masuk ke PQ untuk mengubah data/Query. Tidak seperti Excel yang jika data kita ubah maka data lain yang terhubung akan ikut berubah (dinamis)

Contohnya :

  • Sedot data web mesti dilakukan satu persatu didalam query, padahal kita inginnya kita ubah Nama emiten maka data yang tersedot akan disedot ulang tanpa perlu mengubah query satu persatu (kita ubah parameter pada query) ini bisa kiota lihat contohnya pada artike IDX Template : Cara mudah sedot data profil emiten BEI dari idx.co.id
  • Data yang mirip namun berbeda nama/folder mesti diconvert secara manual satu persatu, padahal kita ingin hanya dengan mengubah nama dan atau folder pada Excel maka data bisa berubah (dinamis)

I. Buat parameters agar Data berubah sesuai nama emiten

  1. Disini kita akan bikin dulu tabel parameter yang kita buat di Excel
  2. Buka Microsoft Excel dan bikin new workbook
  3. Pada sheet 1 kita rename menjadi "Dashboard" atau nama lain terserah anda
  4. Isi seperti gambar dibawah ini, yang penting adalah kode emiten harus diisi, yang lain bisa kita tarik menggunakan fungsi VLOOKUP (jika kita punya data lengkap seluruh Emiten)
  5. Sebenarnya gak penting mau ditaruh di range mana saja yang penting adalah mesti berupa tabel
  6. Header case sensitif , jadi jangan sampai salah
  7. Cara membuat range diatas menjadi tabel :
  8. Blok range A1:B4
  9. Klik di menu Home
  10. Pilih Format as table
  11. Pilih design yang diinginkan
  12. Klik OK
  13. Ini hasilnya
  14. Jangan lupa namai dengan Parameters
  15. Jadi intinya nanti Query kita di PQ akan mengambil data di sheet diatas pada parameter Kode Emiten (baris ke 1)
  16. Sekarang kita bikin Fungsi di PQ, fungsi ini kita kasih nama fParameters
  17. Kita klik pada Menu Data - Get Data - From Other Source - Blank Query
  18. Jika pada versi Excel 2013/2016 sedikit berbeda
  19. Isi nama Query dengan fParameters
  20. klik Advanced Editor
  21. lalu copas kode berikut ini
  22.  
  23. Nanti penampakan seperti ini
  24. Intinya jika kita beri fungsi parameters dengan nama tabel dan baris maka dia akan mengambil data dari tabel yang diberikan tersebut
  25. perlu diingat bahwa tabel tidak masalah mau ditaruh di sheet mana dan range mana Excel akan bisa mencari
  26. Jadi jika kita bikin rumus
  27. fParameters(Parameters,1) maka akan menghasilkan baris 1  dari tabel Parameters yaitu ACST
  28. Jadi jika rumus ini kita buat di Query PQ maka kita tinggal ganti nama Emiten trus kita refresh untuk sedot data baru
  29. Misalnya kita akan menarik data profil Emiten
  30. https://www.idx.co.id/perusahaan-tercatat/profil-perusahaan-tercatat/
  31. link JSON adalah
  32. https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
  33. Caranya bisa dibaca di http://eoditor.com/2020/12/download-data-bursa-efek-indonesia-idx-co-id-gunakan-powerquery/
  34. Sekarang kita coba buat Query di Excel untuk menarik data Emiten
  35. Klik Menu Data - New Query - From Web (ini menggunakan Office 2016)
  36. paste link JSON diatas lalu OK
  37. Akan muncul PowerQuery Editor
  38. kita akan mengambil data Profiles maka kita klik Profil
  39. Akan muncul Record karena kita ingin data ke bawah maka kita klik saja di Record
  40. Lalu kita klik To table
  41. Klik Close  & Load
  42. ini hasilnya
  43.  
  44. Ini adalah Data AALI sesuai Link https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
  45. Untuk mengubah menjadi data Adaro maka kita ubah AALI diatas menjadi ADRO 
  46. Cuman kita mesti edit Query dengan masuk ke powerquery
  47. Nah gimana caranya jika kita hanya mengubah di Excel namun data bisa ikut berubah
  48. Kita edit denga ubah kata AALI dengan rumus
  49. fParameters(nama tabel, nomer baris)
  50. Nama tabel=Parameters
  51. No Baris=1
  52. Jadi
  53. fParameters("Parameters", 1)
  54. Klik di Query trus pilih Edit
  55. Klik Advanced Editor
  56. Ganti AALI dengan rumus "&fParameters("Parameters", 1)"
  57. kenapa ada tanda "" karena rumusnya adalah string jadi penambahan juga harus berupa string
  58. Klik Done
  59. Klik Close &Load
  60. ini hasilnya
  61. Coba kita ganti menjadi ADRO di tabel parameter dan Klik Refresh di Query
  62.  
  63. ini hasilnya
  64. Gampang kan

Catatan :

  • Kita bisa bikin daftar Emiten lalu kita buat daftar sehingga jika kita lakukan pilih nama maka akan muncul kode emiten (Gunakan Data Validation)
  • Trus kombinasikan dengan Rumus Vlookup
  • dan Kombinasikan dengan VBA agar bisa lebih dari satu Query bisa direfresh sekaligus

Download File

PQ Parameters Example (19.5 KiB, 3 downloads)
Print Friendly, PDF & Email
Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *