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

classic Classic list List threaded Threaded
4 messages Options
Kid
Reply | Threaded
Open this post in threaded view
|

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

Kid
Administrator
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 [hidden email]
[belajar-excel] <[hidden email]> 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 <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
>
>

re-ts2fbk_ver02.xlsx (93K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Zainul Ulum
 

Ok,
aku pelajari dulu.
maturnuwun pencerahannya
-zainul

On Jun 21, 2020 21:53, "'Mr. Kid' [hidden email] [belajar-excel]" <[hidden email]> 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 [hidden email] [belajar-excel] <[hidden email]> 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: [hidden email]
Reply via web post [hidden email] [hidden email] 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: [hidden email]
posting ke milis, kirimkan ke: [hidden email]
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@.../
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: [hidden email]
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : [hidden email]
---------------------------------------------------------------------

.

__,_._,___
Reply | Threaded
Open this post in threaded view
|

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

Zainul Ulum
In reply to this post by Kid
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' [hidden email] [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 [hidden email] [belajar-excel] <[hidden email]> 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
 



2FA9DF68C5E6472D9D25E2DD997A7BFB.png (206 bytes) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|

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

Kid
Administrator
In reply to this post by Kid
mas Zain,

  Sebelumnya sudah aku coba pakai unpivot tetapi tidak berhasil karena saya
> lakukan filldown value yang berisi null.

> yup, hal ini karena semua hal disiapkan di query sumber data, padahal
yang disiapkan di query sumber data cukup yang dipakai secara umum oleh
sebagian besar query proses yang membutuhkan
> biasanya, query sumber data berisi proses pembersihan data seperti
memastikan tidak ada baris yang error karena pengubahan tipe data, mungkin
pemeriksaan tidak boleh ada yang duplicate, semua kode kolom sudah ada di
tabel referensi, ada atau tidak item baru untuk referensi, dan semacamnya.

  Apakah berarti dengan menggabungkan beberapa langkah (table temporay) di
> PQ menjadi satu langkah proses perhitungan / query akan lebih cepat?

> secara umum iya, besaran peningkatan performa bisa jadi tidak terasa
ketika query-nya masih sederhana, sumber datanya tidak bervariasi (format
maupun jumlah source dan jumlah records)
> tetapi, logika prosesnya memang lebih cepat karena proses pembentukan
tabel bisa dikurangi
> sepeti di VBA misalnya,
    dim x as long, y as long, z as long
    x=7
    y=x*2
    z=y*45+78
    debug.print "hasil = " & z

   tidak akan terasa perbedaan kinerjanya dengan
   debug.print  "hasil = " & 7*2*45+78

   tapi ketika runtime sudah berisi proses yang kompleks, seperti recursive
atau nested loop, dan sebagainya, barulah bisa terasa.
   *** jangan lupa, datatype tetap mempengaruhi kinerja (secara basic
memang demikian, jadi hal ini tidak perlu dibahas)

  Tentang perhitungan bertype float, apakah berlaku juga diperhitungan
> trigonometry ? missal sin(30.23) atau cos(30.256)?

> Saya belum mengujinya je...
> tapi biasanya, untuk trigonometri tidak berpengaruh
   jadi, sin(2.3) bisa lebih konsisten. Tapi kalau ada proses di dalamnya,
seperti sin(2.3-30), maka proses aritmatikanya yang berpotensi besar
terpengaruh oleh sifat tipe float

  Apakah di PQ tersedia fungsi perkalian matrix dan inverse seperti fungsi
> di excel =MMULT() dan  =MINVERSE()?

> sampai saat ini (termasuk di Power BI), belum ada built-i function
demikian
> tapi kalau di Power BI, bisa memanfaatkan koneksi ke R, MatLab, dan
sebagainya
> kalau di Excel PQ, biasanya disusun sendiri function buatan untuk proses
tersebut memanfaatkan tipe data List (baca:array), melalui function
List.Generate() maupun List.Accumulate()
   *** tapi memang jadi rumit, walau pemakaian akhirnya juga sesederhana
penggunaan Excel Function MMULT()
> sepertinya di inet banyak deh... tinggal copy script function nya dan
siap dipakai
   *** jangan lupa, Excel PQ tidak bisa memanfaatkan add-in, jadi pastikan
function buatan sendiri selalu terdistribusi kepada seluruh client pengguna

Regards,
Kid



On Mon, Jun 22, 2020 at 6:43 AM Zainul Ulum [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> 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 <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *'Mr. Kid' [hidden email] [belajar-excel]
> <[hidden email]>
> *Sent: *Sunday, June 21, 2020 9:53 PM
> *To: *BeExcel <[hidden email]>
> *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 [hidden email]
> [belajar-excel] <[hidden email]> 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 <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
>
>
>
>