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. 
6. Sebenarnya gak penting mau ditaruh di range mana saja yang penting adalah mesti berupa tabel
7. Header case sensitif , jadi jangan sampai salah
8. Cara membuat range diatas menjadi tabel :
9. Blok range A1:B4
10. Klik di menu Home
11. Pilih Format as table
12. Pilih design yang diinginkan
13. Klik OK
14. 
15. Ini hasilnya
16. Jangan lupa namai dengan Parameters
17. 
18. Jadi intinya nanti Query kita di PQ akan mengambil data di sheet diatas pada parameter Kode Emiten (baris ke 1)
19. Sekarang kita bikin Fungsi di PQ, fungsi ini kita kasih nama fParameters
20. Kita klik pada Menu Data - Get Data - From Other Source - Blank Query
21. Jika pada versi Excel 2013/2016 sedikit berbeda
22. 
24. Isi nama Query dengan fParameters
25. klik Advanced Editor
26. lalu copas kode berikut ini

27. let Parameter=(TableName,RowNumber) =>
     
    let
    	Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
    	value = Source{RowNumber-1}[Value]
    in
    	value
    
    in Parameter
    

     

28. Nanti penampakan seperti ini
29. 
30. Intinya jika kita beri fungsi parameters dengan nama tabel dan baris maka dia akan mengambil data dari tabel yang diberikan tersebut
31. perlu diingat bahwa tabel tidak masalah mau ditaruh di sheet mana dan range mana Excel akan bisa mencari
32. Jadi jika kita bikin rumus
33. fParameters(Parameters,1) maka akan menghasilkan baris 1  dari tabel Parameters yaitu ACST
34. Jadi jika rumus ini kita buat di Query PQ maka kita tinggal ganti nama Emiten trus kita refresh untuk sedot data baru
35. Misalnya kita akan menarik data profil Emiten
36. https://www.idx.co.id/perusahaan-tercatat/profil-perusahaan-tercatat/
37. link JSON adalah
38. https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
39. Caranya bisa dibaca di http://eoditor.com/2020/12/download-data-bursa-efek-indonesia-idx-co-id-gunakan-powerquery/
41. Sekarang kita coba buat Query di Excel untuk menarik data Emiten
42. Klik Menu Data - New Query - From Web (ini menggunakan Office 2016)
43. 
45. paste link JSON diatas lalu OK
46. Akan muncul PowerQuery Editor
47. kita akan mengambil data Profiles maka kita klik Profil
48. 
50. Akan muncul Record karena kita ingin data ke bawah maka kita klik saja di Record
51. 
53. Lalu kita klik To table
54. 
56. Klik Close  & Load
57. 
59. ini hasilnya
60.  
61. Ini adalah Data AALI sesuai Link https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
62. Untuk mengubah menjadi data Adaro maka kita ubah AALI diatas menjadi ADRO 
63. Cuman kita mesti edit Query dengan masuk ke powerquery
64. Nah gimana caranya jika kita hanya mengubah di Excel namun data bisa ikut berubah
65. Kita edit denga ubah kata AALI dengan rumus
66. fParameters(nama tabel, nomer baris)
67. Nama tabel=Parameters
68. No Baris=1
69. Jadi
70. fParameters("Parameters", 1)
71. Klik di Query trus pilih Edit
72. 
74. Klik Advanced Editor
75. Ganti AALI dengan rumus "&fParameters("Parameters", 1)"
76. kenapa ada tanda "" karena rumusnya adalah string jadi penambahan juga harus berupa string
77. Klik Done
78. Klik Close &Load
79. 
81. ini hasilnya
82. 
84. Coba kita ganti menjadi ADRO di tabel parameter dan Klik Refresh di Query
85.  
87. ini hasilnya
88. 
90. 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

[wpfilebase tag=file id=322 tpl=download-button /]