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
- Disini kita akan bikin dulu tabel parameter yang kita buat di Excel
- Buka Microsoft Excel dan bikin new workbook
- Pada sheet 1 kita rename menjadi "Dashboard" atau nama lain terserah anda
- 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)
- Sebenarnya gak penting mau ditaruh di range mana saja yang penting adalah mesti berupa tabel
- Header case sensitif , jadi jangan sampai salah
- Cara membuat range diatas menjadi tabel :
- Blok range A1:B4
- Klik di menu Home
- Pilih Format as table
- Pilih design yang diinginkan
- Klik OK
- Ini hasilnya
- Jangan lupa namai dengan Parameters
- Jadi intinya nanti Query kita di PQ akan mengambil data di sheet diatas pada parameter Kode Emiten (baris ke 1)
- Sekarang kita bikin Fungsi di PQ, fungsi ini kita kasih nama fParameters
- Kita klik pada Menu Data - Get Data - From Other Source - Blank Query
- Jika pada versi Excel 2013/2016 sedikit berbeda
- Isi nama Query dengan fParameters
- klik Advanced Editor
- lalu copas kode berikut ini
-
let Parameter=(TableName,RowNumber) => let Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content], value = Source{RowNumber-1}[Value] in value in Parameter
- Nanti penampakan seperti ini
- Intinya jika kita beri fungsi parameters dengan nama tabel dan baris maka dia akan mengambil data dari tabel yang diberikan tersebut
- perlu diingat bahwa tabel tidak masalah mau ditaruh di sheet mana dan range mana Excel akan bisa mencari
- Jadi jika kita bikin rumus
- fParameters(Parameters,1) maka akan menghasilkan baris 1 dari tabel Parameters yaitu ACST
- Jadi jika rumus ini kita buat di Query PQ maka kita tinggal ganti nama Emiten trus kita refresh untuk sedot data baru
- Misalnya kita akan menarik data profil Emiten
- https://www.idx.co.id/perusahaan-tercatat/profil-perusahaan-tercatat/
- link JSON adalah
- https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
- Caranya bisa dibaca di http://eoditor.com/2020/12/download-data-bursa-efek-indonesia-idx-co-id-gunakan-powerquery/
- Sekarang kita coba buat Query di Excel untuk menarik data Emiten
- Klik Menu Data - New Query - From Web (ini menggunakan Office 2016)
- paste link JSON diatas lalu OK
- Akan muncul PowerQuery Editor
- kita akan mengambil data Profiles maka kita klik Profil
- Akan muncul Record karena kita ingin data ke bawah maka kita klik saja di Record
- Lalu kita klik To table
- Klik Close & Load
- ini hasilnya
- Ini adalah Data AALI sesuai Link https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten=AALI&language=id-id
- Untuk mengubah menjadi data Adaro maka kita ubah AALI diatas menjadi ADRO
- Cuman kita mesti edit Query dengan masuk ke powerquery
- Nah gimana caranya jika kita hanya mengubah di Excel namun data bisa ikut berubah
- Kita edit denga ubah kata AALI dengan rumus
- fParameters(nama tabel, nomer baris)
- Nama tabel=Parameters
- No Baris=1
- Jadi
- fParameters("Parameters", 1)
- Klik di Query trus pilih Edit
- Klik Advanced Editor
- Ganti AALI dengan rumus "&fParameters("Parameters", 1)"
- kenapa ada tanda "" karena rumusnya adalah string jadi penambahan juga harus berupa string
- Klik Done
- Klik Close &Load
- ini hasilnya
- Coba kita ganti menjadi ADRO di tabel parameter dan Klik Refresh di Query
-
- ini hasilnya
- 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