Sign in with Microsoft
Sign in or create an account.

Hanya dengan menggunakan Editor Power Query, Anda telah membuat rumus Power Query di sepanjang waktu. Mari kita lihat bagaimana Power Query bekerja dengan melihat ke balikan. Anda bisa mempelajari cara memperbarui atau menambahkan rumus hanya dengan menonton Editor Power Query dalam tindakan.  Anda bahkan bisa menggulung rumus Anda sendiri dengan Editor Tingkat Lanjut.           

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

Bagian Editor Kueri

  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 mudah untuk perintah di pita

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

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

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

Sampel Rumus Editor Kueri

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

Menggunakan daftar klub 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. Simak cara langkah-langkah kueri dan rumus terkait dibuat untuk setiap tugas dalam panel kueri Pengaturan di bawah Langkah yang Diterapkan dan di bilah Rumus.

Browser Anda tidak mendukung video.

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 Transformasi Data di bagian bawah. Editor Power Query muncul.

  3. Untuk mengubah nama kueri default, di panel Desain Pengaturan, di bawah Properti,hapus "Hasil [Edit]" lalu masukkan "Uefa explorer".

  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 "Year' di dalamnya, pilih panah filter di Kolom1, kosongkan kotak centang di samping "Year", 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

Hasil panduan - beberapa baris pertama

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

Langkah dan tugas kueri

Rumus

Sumber

Koneksi 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 tipedata (yang dilakukan Power Query 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 di 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 Nama

Header kolom yang diubah menjadi bermakna

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Penting    Berhati-hatilah mengedit langkah-langkah Sumber,Navigasi , dan Tipe yang Diubah karena langkah-langkah tersebut dibuat oleh Power Query untuk menetapkan dan menyiapkan   sumber data.

Memperlihatkan atau menyembunyikan bilah rumus

Bilah rumus diperlihatkan secara default, tetapi jika tidak terlihat, Anda dapat kembali memainkannya.

  • Pilih Tampilkan > Tata > Bilah Rumus.

Edit sebuah 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 Kueri Pengaturan, di bawah Langkah yang Diterapkan,pilih langkah yang ingin Anda edit.

  3. Di bilah rumus, temukan dan ubah nilai parameter, lalu pilih ikon Enter Ikon Enter di sebelah kiri bilah rumus di Power Query atau tekan Enter. Misalnya, ubah rumus ini agar kolom2 tetap ada:

    Sebelum: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Setelah:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Pilih ikon Ikon Enter di sebelah kiri bilah rumus di Power Query Enter atau tekan Enter untuk melihat hasil baru yang ditampilkan di Pratinjau Data.

  5. Untuk melihat hasil di dalam lembar Excel kerja, pilih Pengaturan> Tutup & Memuat.

Membuat rumus di bilah rumus

Untuk contoh rumus sederhana, mari kita konversi nilai teks menjadi 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. Pada bilah rumus, masukkan=Text.Proper("text value"), lalu pilih ikon Ikon Enter di sebelah kiri bilah rumus di Power Query atau tekan Enter.

    Hasilnya ditampilkan di Pratinjau Data.

  3. Untuk melihat hasil di dalam lembar Excel kerja, pilih Pengaturan> Tutup & Memuat.

Hasil:

Teks.Proper

 Saat Anda membuat rumus, Power Query memvalidasi sintaks rumus. Namun, ketika menyisipkan, mengurutkan ulang, atau menghapus langkah menengah dalam kueri, Anda mungkin berpotensi merusak kueri.  Selalu verifikasi hasilnya di Pratinjau Data.

Penting    Berhati-hatilah mengedit langkah-langkah Sumber,Navigasi , dan Tipe yang Diubah karena langkah-langkah tersebut dibuat oleh Power Query untuk menetapkan dan menyiapkan   sumber data.

Mengedit rumus dengan menggunakan kotak dialog

Metode ini menggunakan kotak dialog yang bervariasi 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 Ikon Pengaturan langkah yang ingin Anda edit atau klik kanan langkah tersebut, lalu pilih Edit Pengaturan.

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

Menyisipkan langkah

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

  1. Di panel Kueri Pengaturan, di bawah Langkah yang Diterapkan,pilih langkah yang ingin Anda langsung awali dengan langkah baru dan rumus terkaitnya.

  2. Pilih ikon Tambahkan Ikon Fungsi di sebelah kiri bilah rumus. Alternatifnya, klik kanan sebuah langkah lalu pilih Sisipkan Langkah Setelah.Rumus baru dibuat dalam format :

    = <nameOfTheStepToReference>, seperti rumus =Production.WorkOrder.

  3. Ketikkan rumus baru menggunakan format:

    =Class.Function(ReferenceStep[,otherparameters])

    Misalnya, dengan anggapan Anda memiliki tabel dengan kolom Jenis Kelamin dan ingin menambahkan kolom dengan nilai "Ms." atau "Mr.", bergantung pada jenis kelamin orang tersebut. Rumusnya adalah:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Contoh rumus

Mengurutkan ulang langkah

  • Di panel Kueri Pengaturan di bawah LangkahyangDiterapkan, klik kanan langkah tersebut, lalu pilih Pindah ke Atas atau Pindah ke Bawah.

Hapus langkah

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

Dalam contoh ini, mari kita konversi teks dalam sebuah kolom menjadi huruf yang tepat menggunakan kombinasi rumus di Editor Tingkat Lanjut. 

Misalnya, Anda memiliki tabel Excel, yang disebut Pesanan, dengan kolom Nama Produk yang ingin Anda konversi menjadi kasus yang tepat. 

Sebelum:

Sebelum

Setelah:

Langkah 4 - Hasil

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

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

Anda akan melihat bahwa setiap langkah dibuat berdasarkan langkah sebelumnya dengan mengacu pada langkah menurut nama. Sebagai pengingat, Bahasa Rumus Power Query bersifat peka huruf besar-kecil.

Fase 1: Membuka Editor Tingkat Lanjut

  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 Tingkat Lanjut, yang terbuka dengan templat ekspresi mari.

Editor2 Tingkat Lanjut

Fase 2: Menentukan sumber data

  1. Buat ekspresi izinkan menggunakan Excel. Fungsi CurrentWorkbook sebagai berikut:

    let#x1

    in
        Source

    Langkah 1 - Editor Tingkat Lanjut

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

Hasil:

Langkah 1 - Hasil

Fase 3: Membuat baris pertama menjadi header

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

  2. Di Editor Power Query, pilih Beranda >Editor Tingkat Lanjut, yang terbuka dengan pernyataan yang Anda buat di Fase 2: Mendefinisikan sumber data.

  3. Dalam ekspresi marikan, tambahkan fungsi #"Baris Pertama sebagai Header" dan Tabel.PromoteHeaders sebagai berikut:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

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

Hasil:

Langkah 3 - Hasil

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

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

  2. Di Editor Power Query, pilih Beranda > Editor Tingkat Lanjut, yang terbuka dengan pernyataan yang Anda buat di Fase 3:Mempromosikan baris pertama ke header.

  3. Di ekspresi mari, konversi setiap nilai kolom Nama ProductName menjadi teks yang tepat dengan menggunakan fungsi Table.TransformColumns, merujuk ke langkah rumus kueri "Baris Pertama sebagai Header" sebelumnya, menambahkan #"Kapitalisasikan Setiap Kata" ke sumber data, lalu menetapkan #"Kapitalisasikan Setiap Kata" 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 & Buka > Tutup & Memuat.

Hasil:

Langkah 4 - 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 hapus 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 kali berikutnya Anda membuka jendela Editor Power Query.

Lihat Juga

Power Query untuk Excel Bantuan

Membuat dan menjalankan fungsi kustom

Menggunakan daftar Langkah yang Diterapkan (docs.com)

Menggunakan fungsi kustom (docs.com)

Rumus M Power Query (docs.com)

Menangani kesalahan (docs.com)

Perlu bantuan lainnya?

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung Microsoft Office Insiders

Apakah informasi ini bermanfaat?

Seberapa puaskah Anda dengan kualitas bahasanya?
Apa yang memengaruhi pengalaman Anda?

Terima kasih atas umpan balik Anda!

×