EXCEL FEED: Re: [belajar-excel] Power Query untuk menggabungkan beberapa field/header menjadi satu field dari satu tabel

Minggu, 21 Juni 2020

Re: [belajar-excel] Power Query untuk menggabungkan beberapa field/header menjadi satu field dari satu tabel

Ok,

aku pelajari dulu.

maturnuwun pencerahannya

-zainul

On Jun 21, 2020 21:53, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:

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_ulum@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

__._,_.___

Posted by: zainul_ulum@yahoo.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Visit Your Group