Mas Kid,
Sebelumnya sudah aku coba pakai unpivot tetapi tidak berhasil karena saya
lakukan filldown value yang berisi null.
Setelah saya pelajari langkah-langkahnya , mohon penjelasan sebagai berikut:
1. Apakah berarti dengan menggabungkan beberapa langkah (table temporay) di PQ
menjadi satu langkah proses perhitungan / query akan lebih cepat?
2. Tentang perhitungan bertype float, apakah berlaku juga diperhitungan
trigonometry ? missal sin(30.23) atau cos(30.256)?
3. Apakah di PQ tersedia fungsi perkalian matrix dan inverse seperti fungsi di
excel =MMULT() dan =MINVERSE()?
Terimakasih,
-zainul
Sent from Mail for Windows 10
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
Sent: Sunday, June 21, 2020 9:53 PM
To: BeExcel
Subject: Re: [belajar-excel] Power Query untuk menggabungkan beberapa
field/header menjadi satu field dari satu tabel [1 Attachment]
hmmm....
Sebenarnya kan, masalah ini sederhana saja....
1. yang namanya mem-pivot itu kolom berisi item kelompok menjadi nama header
kolom di dalam pivot
data yang begini : menjadi pivot yang begini :
key kelompok nilai key A B
1 A 17 1 17 23
1 B 23 2 19
2 A 19
2. yang namanya meng-unpivot itu mengembalikan pivot ke susunan tabel asal
(tabel fakta)
dari data bentuknya seperti pivot di nomor 1 (tabel sisi kanan), menjadi
tabel asal sisi kiri
3. di dalam pivot, bagaimana jika ada value yang kosong (null) ?
> misal nilai 17 di kolom A itu tidak ada, alias kosong alias null (bukan
null string)
> maka hasil unpivot akan kehilangan baris data 1,A,null
key kelompok nilai
1 B 23
2 A 19
*** nah... cakep kan,
berarti masalah tabel dengan kolom STN|BS|PRISM|VA (blok data berisi 4
item akan disusun vertikal dengan header blok di baris pertama setiap blok)
menjadi hasil : (ada 2 baris yang yang dihapus, yaitu PRISM(1) dan VA(1)
yang kelebihan)
STN(1) →nilai kolom STN record ke-1
BS(1) →nilai kolom BS record ke-1
PRISM(1) -> ini masih record ke-1 kan...
VA(1) -> ini juga record ke-1
PRISM(2) -> yang ini punyanya record ke-2
VA(2) -> ini juga punyanya record ke-2
PRISM(3) -> kalo ini dah record ke-3
VA(3) -> yang ini juga record ke-3
PRISM(4) →nilai kolom PRISM record ke-4
VA(4) →nilai kolom VA record ke-4
artinya, di tabel dengan kolom STN|BS|PRISM|VA, pada record ke-2,3, dan 4
(selain baris header kelompok [seperti 1,5,dst), kolom STN dan BS adalah
null... lalu di-unpivot...
done...
Jadi,
A. kenapa harus pakai function buatan sendiri yang memasukkan parameter ?
> ini biasanya karena sumber data akan dipakai dibanyak query proses,
tetapi sebagian besar proses dilakukan di query pengambil data (query sumber
data itu sendiri).
> umumnya, kalau query sumber data tadi di-reference menjadi query baru,
lalu dibuang kolom yang ndak perlu, maka proses unpivot bisa dilakukan.
B. bagaimana jika kolom STN dan BS di record ke-2,3,4 tidak null tapi
nullstring atau ada isinya ?
> filter kolom Value hasil unpivot supaya baris-baris milik STN dan BS dari
record ke-2,3,4,dan semacamnya tersebut tidak ikut serta
dah... masalah itu dah selesai ya...
sekarang yang lainnya...
1. sebisa mungkin (kalau tidak memungkinkan atau belum bisa melakukan, ndak
perlu dipaksakan), satu sumber data dipakai berulang oleh beberapa query proses
*** artinya, proses di query pengambil data tersebut adalah yang secara
umum dibutuhkan di beberapa query proses, misal menguba data type
> disinilah sebenarnya ada masalah dalam filenya mas Zainul
> query input berisi banyak proses, bahkan proses yang hanya dibutuhkan
oleh 1 query proses tertentu tapi tidak dibutuhkan beberapa query proses
lainnya, sudah dibuat
> hal ini perlu ditata lebih baik lagi...
2. sebuah query dalam power query (pq) disusun dari beberapa kalimat query yang
tersusun mulai dari per tabel temporary dan per subquery
*** begini mangsud e
misal ada query bernama qInput berisi langkah proses :
1. Source : ambil data dari excel table misale...
2. changeType : langkah mengubah tipe data setiap kolom
3. addColumnX : langkah membuat kolom baru berisi kalkulasi tertentu
maka di query qInput berisi kalimat query :
a. pembuat temporary table 1 [Source],
b. pembuat temporary table 2 [changeType] (yang membutuhkan hasil temp
table 1 tadi),
c. pembuat temp table 3 [addColumnX]
proses ini jika semakin banyak dan semakin kompleks, maka akan secara
eksponensial memperberat kerja (bahasa simpelnya, menurunkan kinerja)
padanan di SQL bisa mirip begini (mirip ya, tidak sama) :
-- temp table Source :
select blabla from excel.workbook()
-- temp table changetype
select cast(blabla) dst from Source
-- temp table addColumnX
select bliblibli from changetype
hal ini bisa dikurangi jika memanfaatkan potensi subquery, misal menjadi :
1. Source : ambil data dari excel langsung ubah tipe data dan add
column
Contoh : =Table.AddColumn( Table.TransformColumnTypes(
Excel.CurrentWorkbook()blabla , {{blabla,type},{dst}}) , "hitungan" , each
blabla, type )
maka di query qInput yang memanfaatkan subquery akan berisi kalimat query :
a. pembuat temorary table 1 [Source] yang berisi subquery :
AddColumn( from TransformColumnTypes ( from Excel.Workbook()
) )
padananannya di SQL mirip seperti ini :
select blibli
from ( select cast(blabla) dst
from ( select blabla
from excel.workbook() as q1
) as q2
) as q3
3. pq menggunakan basis tipe data float untuk data bilangan, artinya,
se-exact-exact-nya, akan berpotensi berupa approximate number (ndak exact tulen)
*** artinya, jika melakukan kalkulasi dalam pq yang melibatkan bilangan
pecahan, perlu diwaspadai jika kepresisian hasil menjadi faktor utama...
> sifat utama float number adalah secara umum memiliki kepresisian sampai
13 digit...
mangsud e gini,
angka 2.3 relatif float daripada 2300000 (2 juta 3 ratus ribu),
padahal 2300000 itukan sekadar 2.3 x 1e6
jadi, kalau angka 3 di 2.3 itu penting, maka bisa jadi 2300000 / 2 *
1e-6 lebih bisa terjaga kepresisiannya daripada 2.3 / 2
sudah ya.. segitu saja dulu...
lain kali disambung lagi...
terlampir file contoh terkait ocehan di atas...
jika terkendala security, set di power query -> options -> privacy -> pilih
ignore all blabla -> ok
Regards,
Kid
On Sun, Jun 21, 2020 at 12:36 PM Zainul Ulum zainul_u...@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
Rekan Be-Excellers
File terlampir terdiri dari sheet input dan sheet output.
File output adalah hasil yang diharapkap berdasarkan data dari sheet input
dengan urutan sebagai berikut:
File input mempunyai 4 header yaitu :
STN|BS|PRISM|VA
Hasil yang diharapkan di sheet output adalah satu kolom dengan urutan sebagai
berikut:
STN(1) →nilai kolom STN record ke-1
BS(1) →nilai kolom BS record ke-1
PRISM(1)
VA(1)
PRISM(1)
VA(1)
PRISM(2)
VA(2)
PRISM(3)
VA(3)
PRISM(4) →nilai kolom PRISM record ke-4
VA(4) →nilai kolom VA record ke-4
Sementara ini saya menggunakan “manage parameters”, file [ts2fbk_ver02.xlsx],
di power query untuk melakukan proses di atas.
Mohon masukan dari rekan-rekan jika ada cara tanpa menggunakan manage parameter.
Terimakasih,
-zainul
Sent from Mail for Windows 10
