[belajar-excel] Digest Number 4884

Milis Belajar Microsoft Excel Group

4 Messages

Digest #4884

1a

Transform Table dengan Power Query by "Zainul Ulum" zainul_ulum

2a

Re: lookup data by "HAIRUL ALAM"

3a

Re: Transform Table dengan Power Query [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com

3b

Re: Transform Table dengan Power Query [1 Attachment] by iq_bal76id

Messages

1a

Transform Table dengan Power Query

Wed Oct 23, 2019 5:42 am (PDT) . Posted by:

"Zainul Ulum" zainul_ulum

Rekan Be-Excel,

Saya ingin merubah table dengan bantuan Power Query.

Table semula:
-------------------------
ID|X |Y |Z |DESC |STATUS
-------------------------
1A|23|34|100|CP-01|NEW

Tabel yang diinginkan :
-----------------------------
ID|DESC |STATUS|Coord|XYZ
-----------------------------
1A|CP-01|NEW |X |23
1A|CP-01|NEW |Y |34
1A|CP-01|NEW |Z |100

Pada file terlampir adalah power query yang sudah saya buat dan menghasilkan table yang diinginkan tetapi menurut saya masih kurang ringkes dan praktis,

Mohon bantuannya, apakah ada cara yang lebih praktis seperti menggunakan join table atau cara yang lainnya?

Terimakasih,
Wassalam,
-zainul

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

2a

Re: lookup data

Wed Oct 23, 2019 7:52 am (PDT) . Posted by:

"HAIRUL ALAM"

Terimakasih mas ade, iya bisa ternyata dengan mengurutkan datanya
Pada Senin, 21 Oktober 2019 18.19.40 WIB, 'M.Ade Alfarid' QA@kkmi.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

Mas hairul,

Coba urutkan/filter kolom date nya

 

ade

 

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Monday, October 21, 2019 5:24 PM
To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] lookup data

 

 

Salam Master

 

Saya belum mudeng

 

Maaf

 

Assalamualaikum

 

Pada Senin, 21 Oktober 2019 16..05.02 GMT+7, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> menulis:

 

 

 

karena datanya tidak di-sort berdasar kolom kunci lookup nya

 

On Mon, Oct 21, 2019 at 3:38 PM HAIRUL ALAM ha_mj76@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

 

Salam Para Master

 

Selamat Sore, semoga semua dalam keadaan sehat

 

Terlampir data lookup, Maaf dari data tersebut ada rumus yang tidak berfungsi dan saya tidak paham dimana letak kesalahannya karena ada data yang tidak bernilai, mohon solusinya

 

Terimakasih Banyak

 

Assalamalaikum

Hairul Alam

#yiv9275747797 #yiv9275747797 -- #yiv9275747797ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9275747797 #yiv9275747797ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9275747797 #yiv9275747797ygrp-mkp #yiv9275747797hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9275747797 #yiv9275747797ygrp-mkp #yiv9275747797ads {margin-bottom:10px;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad {padding:0 0;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad p {margin:0;}#yiv9275747797 #yiv9275747797ygrp-mkp .yiv9275747797ad a {color:#0000ff;text-decoration:none;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc {font-family:Arial;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc #yiv9275747797hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ygrp-lc .yiv9275747797ad {margin-bottom:10px;padding:0 0;}#yiv9275747797 #yiv9275747797actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9275747797 #yiv9275747797activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9275747797 #yiv9275747797activity span {font-weight:700;}#yiv9275747797 #yiv9275747797activity span:first-child {text-transform:uppercase;}#yiv9275747797 #yiv9275747797activity span a {color:#5085b6;text-decoration:none;}#yiv9275747797 #yiv9275747797activity span span {color:#ff7900;}#yiv9275747797 #yiv9275747797activity span .yiv9275747797underline {text-decoration:underline;}#yiv9275747797 .yiv9275747797attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9275747797 .yiv9275747797attach div a {text-decoration:none;}#yiv9275747797 .yiv9275747797attach img {border:none;padding-right:5px;}#yiv9275747797 .yiv9275747797attach label {display:block;margin-bottom:5px;}#yiv9275747797 .yiv9275747797attach label a {text-decoration:none;}#yiv9275747797 blockquote {margin:0 0 0 4px;}#yiv9275747797 .yiv9275747797bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9275747797 .yiv9275747797bold a {text-decoration:none;}#yiv9275747797 dd.yiv9275747797last p a {font-family:Verdana;font-weight:700;}#yiv9275747797 dd.yiv9275747797last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9275747797 dd.yiv9275747797last p span.yiv9275747797yshortcuts {margin-right:0;}#yiv9275747797 div.yiv9275747797attach-table div div a {text-decoration:none;}#yiv9275747797 div.yiv9275747797attach-table {width:400px;}#yiv9275747797 div.yiv9275747797file-title a, #yiv9275747797 div.yiv9275747797file-title a:active, #yiv9275747797 div.yiv9275747797file-title a:hover, #yiv9275747797 div.yiv9275747797file-title a:visited {text-decoration:none;}#yiv9275747797 div.yiv9275747797photo-title a, #yiv9275747797 div.yiv9275747797photo-title a:active, #yiv9275747797 div.yiv9275747797photo-title a:hover, #yiv9275747797 div.yiv9275747797photo-title a:visited {text-decoration:none;}#yiv9275747797 div#yiv9275747797ygrp-mlmsg #yiv9275747797ygrp-msg p a span.yiv9275747797yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9275747797 .yiv9275747797green {color:#628c2a;}#yiv9275747797 .yiv9275747797MsoNormal {margin:0 0 0 0;}#yiv9275747797 o {font-size:0;}#yiv9275747797 #yiv9275747797photos div {float:left;width:72px;}#yiv9275747797 #yiv9275747797photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv9275747797 #yiv9275747797photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9275747797 #yiv9275747797reco-category {font-size:77%;}#yiv9275747797 #yiv9275747797reco-desc {font-size:77%;}#yiv9275747797 .yiv9275747797replbq {margin:4px;}#yiv9275747797 #yiv9275747797ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9275747797 #yiv9275747797ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9275747797 #yiv9275747797ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9275747797 #yiv9275747797ygrp-mlmsg select, #yiv9275747797 input, #yiv9275747797 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9275747797 #yiv9275747797ygrp-mlmsg pre, #yiv9275747797 code {font:115% monospace;}#yiv9275747797 #yiv9275747797ygrp-mlmsg * {line-height:1.22em;}#yiv9275747797 #yiv9275747797ygrp-mlmsg #yiv9275747797logo {padding-bottom:10px;}#yiv9275747797 #yiv9275747797ygrp-msg p a {font-family:Verdana;}#yiv9275747797 #yiv9275747797ygrp-msg p#yiv9275747797attach-count span {color:#1E66AE;font-weight:700;}#yiv9275747797 #yiv9275747797ygrp-reco #yiv9275747797reco-head {color:#ff7900;font-weight:700;}#yiv9275747797 #yiv9275747797ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov li a {font-size:130%;text-decoration:none;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9275747797 #yiv9275747797ygrp-sponsor #yiv9275747797ov ul {margin:0;padding:0 0 0 8px;}#yiv9275747797 #yiv9275747797ygrp-text {font-family:Georgia;}#yiv9275747797 #yiv9275747797ygrp-text p {margin:0 0 1em 0;}#yiv9275747797 #yiv9275747797ygrp-text tt {font-size:120%;}#yiv9275747797 #yiv9275747797ygrp-vital ul li:last-child {border-right:none !important;}#yiv9275747797

Reply to sender . Reply to group . Reply via Web Post . All Messages (8) . Top ^

3a

Re: Transform Table dengan Power Query [1 Attachment]

Wed Oct 23, 2019 12:45 pm (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

pilih kolom X, Y, Z -> menu transform -> unpivot columns -> pilih unpivot
columns -> edit formula, ubah Attribute jadi Coord dan Vaue jadi XYZ ->
enter

On Wed, Oct 23, 2019 at 7:42 PM Zainul Ulum zainul_ulum@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> Rekan Be-Excel,
>
>
>
> Saya ingin merubah table dengan bantuan Power Query.
>
>
>
> Table semula:
>
> -------------------------
>
> ID|X |Y |Z |DESC |STATUS
>
> -------------------------
>
> 1A|23|34|100|CP-01|NEW
>
>
>
> Tabel yang diinginkan :
>
> -----------------------------
>
> ID|DESC |STATUS|Coord|XYZ
>
> -----------------------------
>
> 1A|CP-01|NEW |X |23
>
> 1A|CP-01|NEW |Y |34
>
> 1A|CP-01|NEW |Z |100
>
>
>
> Pada file terlampir adalah power query yang sudah saya buat dan
> menghasilkan table yang diinginkan tetapi menurut saya masih kurang ringkes
> dan praktis,
>
>
>
> Mohon bantuannya, apakah ada cara yang lebih praktis seperti menggunakan
> join table atau cara yang lainnya?
>
>
>
> Terimakasih,
>
> Wassalam,
>
> -zainul
>
>
>
>
>
>
>
>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3b

Re: Transform Table dengan Power Query [1 Attachment]

Wed Oct 23, 2019 9:06 pm (PDT) . Posted by:

iq_bal76id

Dear Mas Ulum

Monggo di telisik cara versi ke dua

Thanks

Best Regards

Muhammad Iqbal

+62890909010/0811101854

From: belajar-excel@yahoogroups.com <belajar-excel@yahoogroups.com>
Sent: Wednesday, October 23, 2019 12:59 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Transform Table dengan Power Query [1 Attachment]

Rekan Be-Excel,

Saya ingin merubah table dengan bantuan Power Query.

Table semula:

-------------------------

ID|X |Y |Z |DESC |STATUS

-------------------------

1A|23|34|100|CP-01|NEW

Tabel yang diinginkan :

-----------------------------

ID|DESC |STATUS|Coord|XYZ

-----------------------------

1A|CP-01|NEW |X |23

1A|CP-01|NEW |Y |34

1A|CP-01|NEW |Z |100

Pada file terlampir adalah power query yang sudah saya buat dan menghasilkan table yang diinginkan tetapi menurut saya masih kurang ringkes dan praktis,

Mohon bantuannya, apakah ada cara yang lebih praktis seperti menggunakan join table atau cara yang lainnya?

Terimakasih,

Wassalam,

-zainul

--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

============================================================
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

• Privacy • Unsubscribe • Terms of Use