Membuat rumus Power Query di Excel

Editor Power Query menyediakan kueri data dan pengalaman pembentukan untuk Excel yang bisa Anda gunakan untuk membentuk ulang data dari banyak sumber data. Untuk menampilkan jendela Editor Power Query, impor data dari sumber data eksternaldalam lembar kerja Excel, pilih sel dalam data, lalu pilih Kueri > Edit. Berikut ini adalah ringkasan komponen utama.

1. Pita Editor Power Query yang Anda gunakan untuk membentuk data Anda

2. Panel Kueri yang Anda gunakan untuk menemukan sumber data dan tabel

3. Menu konteks yang merupakan pintasan yang mudah untuk perintah di pita

4. Pratinjau Data yang menampilkan hasil langkah-langkah yang diterapkan pada data

5. Panel Pengaturan Kueri yang mencantumkan properti dan setiap langkah dalam kueri

Di balik layar, setiap langkah dalam kueri didasarkan pada rumus yang terlihat di bilah rumus.

Mungkin ada saat-saat ketika Anda ingin mengubah atau membuat rumus. Rumus menggunakan Power Query Bahasa Rumus, yang bisa Anda gunakan untuk menyusun ekspresi sederhana dan kompleks. Untuk informasi selengkapnya tentang sintaks, argumen, keterangan, fungsi, dan contoh, lihat Power Query bahasa rumus M.

Menggunakan daftar kejuaraan sepak bola sebagai contoh, gunakan Power Query untuk mengambil data mentah yang Anda temukan di situs web dan mengubahnya menjadi tabel yang diformat dengan baik. Tonton bagaimana langkah-langkah kueri dan rumus terkait dibuat untuk setiap tugas di panel Pengaturan Kueri di bawah Langkah yang Diterapkan dan di bilah Rumus.

Prosedur

1. Untuk mengimpor data, pilih Data > Dari Web, masukkan "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" dalam kotak URL , lalu pilih OK.

2. Dalam kotak dialog Navigator , pilih tabel Hasil [Edit] di sebelah kiri, lalu pilih Ubah Data di bagian bawah. Editor Power Query muncul.

3. Untuk mengubah nama kueri default, di panel Pengaturan Kueri , di bawah Properti, hapus "Hasil [Edit]" lalu masukkan "Juara UEFA".

4. Untuk menghapus kolom yang tidak diinginkan, pilih kolom pertama, keempat, dan kelima, lalu pilih Beranda > Hapus Kolom > Hapus Kolom Lain.

5. Untuk menghapus nilai yang tidak diinginkan, pilih Kolom1, pilih Beranda > Ganti Nilai, masukkan "detail" dalam kotak Nilai untuk Ditemukan, lalu pilih OK.

6. Untuk menghapus baris yang memiliki kata "Tahun' di dalamnya, pilih panah filter di Kolom1, kosongkan kotak centang di samping "Tahun", lalu pilih OK.

7. Untuk mengganti nama header kolom, klik ganda setiap header kolom, lalu ubah "Kolom1" menjadi "Tahun", "Kolom4" menjadi "Pemenang", dan "Kolom5" menjadi "Skor Akhir".

8. Untuk menyimpan kueri, pilih Beranda > Tutup & Muat.

Hasil

Tabel berikut ini adalah ringkasan dari setiap langkah yang diterapkan dan rumus terkait.

Langkah dan tugas kueri	Rumus
Sumber Menyambungkan ke sumber data web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigasi Pilih tabel untuk disambungkan	=Source{2}[Data]
Tipe yang Diubah Mengubah tipe data (yang Power Query lakukan secara otomatis)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Kolom Lain yang Dihapus Menghapus kolom lain agar hanya menampilkan kolom yang diminati	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Nilai yang Diganti Mengganti nilai untuk membersihkan nilai dalam kolom yang dipilih	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Baris yang Difilter Memfilter nilai dalam kolom	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Kolom yang Diganti Namanya Mengubah header kolom agar bermakna	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Penting    Hati-hati mengedit langkah-langkah Sumber, Navigasi,  dan Tipe yang Diubah karena dibuat oleh Power Query untuk menentukan dan menyiapkan sumber data.

Memperlihatkan atau menyembunyikan bilah rumus

Bilah rumus diperlihatkan secara default, tetapi jika bilah rumus tidak terlihat, Anda dapat memutar ulang bilah rumus.

• Pilih TampilkanTata Letak > > Bilah Rumus.

Edit rumus di bilah rumus

1. Untuk membuka kueri, temukan yang sebelumnya dimuat dari Editor Power Query, pilih sel dalam data, lalu pilih Kueri > Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

2. Di panel Pengaturan Kueri , di bawah Langkah yang Diterapkan, pilih langkah yang ingin Anda edit.

3. Di bilah rumus, temukan dan ubah nilai parameter, lalu pilih ikon Enter atau tekan Enter. Misalnya, ubah rumus ini agar juga mempertahankan Kolom2:
   
   Sebelum: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Setelah:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Pilih ikon Enter atau tekan Enter untuk melihat hasil baru yang ditampilkan dalam Pratinjau Data.

5. Untuk melihat hasil dalam lembar kerja Excel, pilih Beranda > Tutup & Muat.

Membuat rumus di bilah rumus

Untuk contoh rumus sederhana, mari kita konversi nilai teks menjadi kapitalisasi huruf yang tepat menggunakan fungsi Text.Proper.

1. Untuk membuka kueri kosong, di Excel pilih Data > Dapatkan Data > Dari Sumber Lain > Kueri Kosong. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

2. Di bilah rumus, masukkan=Text.Proper("text value"), lalu pilih ikon Enter atau tekan Enter.
   
   Hasilnya ditampilkan dalam Pratinjau Data .

3. Untuk melihat hasil dalam lembar kerja Excel, pilih Beranda > Tutup & Muat.

Hasil:

 Saat Anda membuat rumus, Power Query memvalidasi sintaks rumus. Namun, saat Anda menyisipkan, mengurutkan ulang, atau menghapus langkah menengah dalam kueri Anda mungkin berpotensi memutus kueri.  Selalu verifikasi hasil dalam Pratinjau Data.

Penting    Hati-hati mengedit langkah-langkah Sumber, Navigasi,  dan Tipe yang Diubah karena dibuat oleh Power Query untuk menentukan dan menyiapkan sumber data.

Mengedit rumus menggunakan kotak dialog

Metode ini menggunakan kotak dialog yang berbeda-beda tergantung pada langkahnya. Anda tidak perlu mengetahui sintaks rumus.

1. Untuk membuka kueri, temukan yang sebelumnya dimuat dari Editor Power Query, pilih sel dalam data, lalu pilih Kueri > Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

2. Di panel Pengaturan Kueri , di bawah Langkah yang Diterapkan, pilih ikon Edit Pengaturan langkah yang ingin Anda edit atau klik kanan langkah tersebut, lalu pilih Edit Pengaturan.

3. Dalam kotak dialog, buat perubahan Anda, lalu pilih OK.

Menyisipkan langkah

Setelah Anda menyelesaikan langkah kueri yang menyusun ulang data Anda, langkah kueri ditambahkan di bawah langkah kueri saat ini. tetapi ketika Anda menyisipkan langkah kueri di tengah langkah-langkah, kesalahan mungkin terjadi dalam langkah-langkah berikutnya. Power Query menampilkan peringatan Sisipkan Langkah saat Anda mencoba menyisipkan langkah baru dan langkah baru mengubah bidang, seperti nama kolom, yang digunakan dalam salah satu langkah yang mengikuti langkah yang disisipkan.

1. Di panel Pengaturan Kueri , di bawah Langkah yang Diterapkan, pilih langkah yang Anda inginkan untuk segera mengawali langkah baru dan rumus terkaitnya.

2. Pilih ikon Tambahkan Langkah di sebelah kiri bilah rumus. Atau, klik kanan langkah lalu pilih Sisipkan Langkah Setelah. Rumus baru dibuat dalam format :
   
   = <nameOfTheStepToReference>, seperti =Production.WorkOrder.

3. Ketik rumus baru menggunakan format:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Misalnya, anggapLah Anda memiliki tabel dengan kolom Jenis Kelamin dan Anda ingin menambahkan kolom dengan nilai "Ms." atau "Mr.", tergantung pada jenis kelamin orang tersebut. Rumusnya adalah:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Mengulangi langkah

• Di panel Pengaturan Kueri di bawah Langkah yang Diterapkan, klik kanan langkah, lalu pilih Pindah ke Atas atau Pindah ke Bawah.

Hapus langkah

• Pilih ikon Hapus di sebelah kiri langkah, atau klik kanan langkah, lalu pilih Hapus atau Hapus Hingga Selesai. Ikon Hapus juga tersedia di sebelah kiri bilah rumus.

Dalam contoh ini, mari kita konversi teks dalam kolom menjadi kapitalisasi huruf yang tepat menggunakan kombinasi rumus dalam Editor Lanjutan. 

Misalnya, Anda memiliki tabel Excel, yang disebut Pesanan, dengan kolom NamaProduk yang ingin Anda konversi menjadi kapitalisasi huruf yang tepat. 

Sebelum:

Setelah:

Ketika membuat kueri tingkat lanjut, Anda membuat serangkaian langkah rumus kueri berdasarkan ekspresi izinkan. Gunakan ekspresi izinkan untuk menetapkan nama dan menghitung nilai yang kemudian dirujuk oleh klausul dalam , yang menentukan Langkah. Contoh ini mengembalikan hasil yang sama dengan yang ada di bagian "Buat rumus di bilah rumus".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Anda akan melihat bahwa setiap langkah dibuat pada langkah sebelumnya dengan merujuk pada langkah demi nama. Sebagai pengingat, Power Query Bahasa Rumus peka huruf besar kecil.

Fase 1: Membuka Editor Lanjutan

1. Di Excel, pilih Data > Dapatkan Data > Sumber Lain > Kueri Kosong. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

2. Di Editor Power Query, pilih beranda > Editor Lanjutan, yang terbuka dengan templat ekspresi izinkan.

Fase 2: Tentukan sumber data

1. Buat ekspresi izinkan menggunakan fungsi Excel.CurrentWorkbook sebagai berikut:
   
   let#x1
   
   in
       
   
   #x4Source

2. Untuk memuat kueri ke lembar kerja, pilih Selesai, lalu pilih Beranda > Tutup & Muat > Tutup & Muat.

Hasil:

Fase 3: Memajukan baris pertama ke header

1. Untuk membuka kueri, dari lembar kerja pilih sel dalam data, lalu pilih Kueri > Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel (Power Query).

2. Di Editor Power Query, pilih beranda > Editor Lanjutan, yang terbuka dengan pernyataan yang Anda buat di Fase 2: Tentukan sumber data.

3. Dalam ekspresi izinkan, tambahkan fungsi #"Baris Pertama sebagai Header" dan Table.PromoteHeaders sebagai berikut:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x4in
   

4. Untuk memuat kueri ke lembar kerja, pilih Selesai, lalu pilih Beranda > Tutup & Muat > Tutup & Muat.

Hasil:

Fase 4: Mengubah setiap nilai dalam kolom menjadi kapitalisasi huruf yang tepat

1. Untuk membuka kueri, dari lembar kerja pilih sel dalam data, lalu pilih Kueri > Edit. Untuk informasi selengkapnya, lihat Membuat, memuat, atau mengedit kueri di Excel.

2. Di Editor Power Query, pilih beranda > Editor Lanjutan, yang terbuka dengan pernyataan yang Anda buat di Fase 3: Promosikan baris pertama ke header.

3. Dalam ekspresi izinkan, konversi setiap nilai kolom NamaProduk menjadi teks yang tepat menggunakan fungsi Table.TransformColumns, yang merujuk ke langkah rumus kueri "Baris Pertama sebagai Header" sebelumnya, menambahkan #"Mengapitalkan Setiap Word" ke sumber data, lalu menetapkan #"Kapitalkan Setiap Word" ke hasil dalam.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Untuk memuat kueri ke lembar kerja, pilih Selesai, lalu pilih Beranda > Tutup & Muat > Tutup & Muat.

Hasil:

Anda bisa mengontrol perilaku bilah rumus di Editor Power Query untuk semua buku kerja Anda.

Menampilkan atau menyembunyikan bilah rumus

1. Pilih Opsi> File dan Pengaturan > Opsi Kueri.

2. Di panel kiri, di bawah GLOBAL, pilih Editor Power Query.

3. Di panel kanan, di bawah Tata Letak, pilih atau kosongkan Tampilkan Bilah Rumus.

Mengaktifkan atau menonaktifkan M Intellisense

1. Pilih Opsi> File dan Pengaturan > Opsi Kueri .

2. Di panel kiri, di bawah GLOBAL, pilih Editor Power Query.

3. Di panel kanan, di bawah Rumus, pilih atau kosongkan Aktifkan M Intellisense di bilah rumus, editor tingkat lanjut, dan dialog kolom kustom.

Catatan    Mengubah pengaturan ini akan diterapkan saat berikutnya Anda membuka jendela Editor Power Query.