Menggunakan fungsi kustom

Jika Anda menemukan diri Anda dalam situasi di mana Anda perlu menerapkan kumpulan transformasi yang sama ke kueri atau nilai yang berbeda, membuat fungsi kustom Power Query yang dapat digunakan kembali sebanyak yang Anda butuhkan bisa bermanfaat. Fungsi kustom Power Query adalah pemetaan dari sekumpulan nilai input ke satu nilai output, dan dibuat dari fungsi dan operator M asli.

Anda bisa membuat fungsi kustom Power Query Anda sendiri secara manual menggunakan bahasa rumus Power Query M, atau antarmuka pengguna Power Query menawarkan fitur untuk mempercepat, menyederhanakan, dan meningkatkan proses pembuatan dan pengelolaan fungsi kustom.

Pertama, kita akan membahas langkah-langkah dasar untuk membuat fungsi kustom dengan kode diUI , lalu kita akan fokus menggunakan antarmuka untuk mengubah tindakan kompleks menjadi fungsi yang dapat digunakan kembali.

Penting

Artikel ini menguraikan cara membuat fungsi kustom dengan Power Query menggunakan transformasi umum yang dapat diakses di antarmuka pengguna Power Query. Ini berfokus pada konsep inti untuk membuat fungsi kustom, dan tautan ke artikel lain dalam dokumentasi Power Query untuk informasi selengkapnya tentang transformasi tertentu yang dirujuk dalam artikel ini.

Membuat fungsi kustom dari kode di UI

Nota

Langkah-langkah berikut dapat diikuti di Power BI Desktop atau menggunakan pengalaman Power Query yang ditemukan di Excel untuk Windows.

1. Gunakan pengalaman konektor untuk menyambungkan ke data Anda di tempat datanya disimpan. Saat Anda telah memilih data, pilih tombol Mengubah Data atau Edit. Ini akan membawa Anda ke pengalaman Power Query.
2. Klik kanan pada tempat kosong di panel Kueri di sebelah kiri.
3. Pilih Kueri Kosong
4. Di jendela kueri kosong baru pilih menu Beranda, lalu Editor tingkat lanjut.
5. Ganti templat dengan fungsi kustom Anda. Misalnya:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Pilih Selesai.

Untuk informasi selengkapnya tentang mengembangkan fungsi kustom dengan bahasa rumus Power Query M, lihat artikel ini: Memahami Power Query M Functions. Di bagian berikut, ada tutorial yang menjelaskan bagaimana Anda bisa menggunakan antarmuka pengguna Power Query untuk mengembangkan fungsi kustom tanpa menulis kode, dan instruksi tentang cara memanggil fungsi kustom Anda dalam kueri Anda.

Membuat fungsi kustom dari tutorial referensi tabel

Nota

Contoh berikut dibuat menggunakan pengalaman desktop yang ditemukan di Power BI Desktop dan juga dapat diikuti menggunakan pengalaman Power Query yang ditemukan di Excel untuk Windows.

Anda dapat mengikuti contoh ini dengan mengunduh file sampel yang digunakan dalam artikel ini dari tautan unduhan berikut. Untuk kesederhanaan, artikel ini menggunakan konektor Folder. Untuk mempelajari lebih lanjut tentang konektor Folder, buka folder . Tujuan dari contoh ini adalah untuk membuat fungsi kustom yang dapat diterapkan ke semua file di folder tersebut sebelum menggabungkan semua data dari semua file ke dalam satu tabel.

Mulailah dengan menggunakan pengalaman konektor Folder untuk menavigasi ke folder tempat file Anda berada dan pilih Mengubah Data atau Edit. Langkah-langkah ini membawa Anda ke pengalaman Power Query. Klik kanan pada nilai Biner pilihan Anda dari bidang Konten dan pilih opsi Tambahkan sebagai Kueri Baru. Untuk contoh ini, pilihan dibuat untuk file pertama dari daftar, yang kebetulan merupakan file April 2019.csv.

Opsi ini secara efektif membuat kueri baru dengan langkah navigasi langsung ke file tersebut sebagai Biner, dan nama kueri baru ini adalah jalur file dari file yang dipilih. Ganti nama kueri ini menjadi File Sampel.

Buat parameter baru dengan nama Parameter File dan jenis Biner. Gunakan kueri File Sampel sebagai Nilai Default dan Nilai Saat Ini .

Nota

Kami menyarankan agar Anda membaca artikel tentang parameter untuk lebih memahami cara membuat dan mengelola parameter di Power Query.

Fungsi kustom dapat dibuat menggunakan jenis parameter apa pun. Tidak ada persyaratan untuk fungsi kustom apa pun untuk memiliki biner sebagai parameter.

Jenis parameter biner hanya ditampilkan di dalam dialog Parameter pada menu dropdown Jenis ketika Anda memiliki kueri yang dievaluasi menjadi biner.

Anda dapat membuat fungsi kustom tanpa parameter. Ini biasanya terlihat dalam skenario di mana input dapat disimpulkan dari lingkungan tempat fungsi sedang dipanggil. Misalnya, fungsi yang mengambil tanggal dan waktu lingkungan saat ini, dan membuat string teks tertentu dari nilai tersebut.

Klik kanan Parameter File dari panel Kueri . Pilih opsi Referensi.

Ganti nama kueri yang baru dibuat dari Parameter File (2) menjadi file Sampel Transformasi .

Klik kanan kueri file Sampel Transformasi baru ini dan pilih opsi Buat Fungsi .

Operasi ini secara efektif membuat fungsi baru yang ditautkan dengan kueri pada file Transform Sample. Setiap perubahan yang Anda buat pada kueri Transform Sampel file secara otomatis direplikasi ke fungsi kustom Anda. Selama pembuatan fungsi baru ini, gunakan Transform file sebagai nama fungsi .

Setelah Anda membuat fungsi, perhatikan bahwa grup baru dibuat untuk Anda dengan nama fungsi Anda. Grup baru ini berisi:

• Semua parameter yang dirujuk dalam file Sampel Transformasi kueri Anda.
• Kueri file Sampel Transformasi Anda, umumnya dikenal sebagai kueri sampel .
• Fungsi anda yang baru dibuat, dalam hal ini Transform file.

Menerapkan transformasi ke kueri sampel

Setelah fungsi baru Anda dibuat, pilih kueri dengan nama Transformasi File Sampel. Kueri ini sekarang ditautkan dengan file transformasi fungsi, sehingga setiap perubahan yang dilakukan pada kueri ini akan tercermin dalam fungsi. Koneksi inilah yang dikenal sebagai konsep kueri sampel yang ditautkan ke fungsi.

Transformasi pertama yang perlu terjadi pada kueri ini adalah yang menginterpretasikan biner. Anda bisa mengklik kanan biner dari panel pratinjau dan memilih opsi CSV untuk menafsirkan biner sebagai file CSV.

Format semua file CSV dalam folder sama. Semuanya memiliki header yang mencakup empat baris teratas pertama. Header kolom terletak di baris 5 dan data dimulai dari baris 6 ke bawah, seperti yang ditunjukkan pada gambar berikutnya.

Serangkaian langkah transformasi Berikutnya yang perlu diterapkan ke file Transform Sample adalah:

1. Hapus empat baris teratas—Tindakan ini menyingkirkan baris yang dianggap sebagai bagian header file.

   

   Nota

   Untuk mempelajari selengkapnya tentang cara menghapus baris atau memfilter tabel menurut posisi baris, buka Filter menurut posisi baris.

2. Mempromosikan judul tabel—Judul untuk tabel akhir Anda sekarang berada di baris pertama tabel. Anda dapat mempromosikannya seperti yang ditunjukkan pada gambar berikutnya.

   

Setelah mempromosikan header kolom Anda, Power Query secara default secara otomatis menambahkan langkah Tipe yang Diubah baru yang secara otomatis mendeteksi tipe data untuk setiap kolom. Kueri file Transformasi Sampel Anda terlihat seperti gambar berikut.

Nota

Untuk mempelajari selengkapnya tentang cara mempromosikan dan menurunkan header, buka Promosikan atau demosi header kolom.

Perhatian

Fungsi file transformasi Anda bergantung pada langkah-langkah yang dilakukan dalam kueri file Sampel Transformasi. Namun, jika Anda mencoba memodifikasi kode secara manual untuk file transformasi fungsi, akan muncul peringatan yang berbunyi The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Memanggil fungsi kustom sebagai kolom baru

Dengan fungsi kustom yang sekarang dibuat dan semua langkah transformasi dimasukkan, Anda dapat kembali ke kueri asli tempat Anda memiliki daftar file dari folder (file CSV dalam contoh ini). Di dalam tab Tambahkan Kolom di pita, pilih Panggil Fungsi Kustom dari grup Umum. Di dalam jendela Panggil Fungsi Kustom, masukkan Tabel Output sebagai nama kolom baru . Pilih nama fungsi Anda, Transformasi file, dari dropdown kueri Fungsi . Setelah Anda memilih fungsi dari menu dropdown, parameter untuk fungsi ditampilkan dan Anda dapat memilih kolom mana dari tabel yang akan digunakan sebagai argumen untuk fungsi ini. Pilih kolom Konten sebagai nilai/argumen yang diteruskan untuk parameter file .

Setelah Anda memilih OK, kolom baru dengan nama Tabel Output dibuat. Kolom ini memiliki nilai Tabel dalam selnya, seperti yang ditunjukkan pada gambar berikutnya. Untuk kesederhanaan, hapus semua kolom dari tabel ini kecuali nama dan Tabel Output.

Nota

Untuk mempelajari selengkapnya tentang cara memilih atau menghapus kolom dari tabel, buka Memilih atau menghapus kolom.

Fungsi Anda diterapkan ke setiap baris tunggal dari tabel menggunakan nilai dari kolom Konten sebagai argumen untuk fungsi Anda. Sekarang setelah data diubah menjadi bentuk yang Anda cari, Anda dapat memperluas kolom Tabel Output dengan memilih ikon Perluas. Jangan gunakan awalan apa pun untuk kolom yang diperluas.

Anda dapat memverifikasi bahwa Anda memiliki data dari semua file di folder dengan memeriksa nilai di kolom Nama atau Tanggal. Untuk kasus ini, Anda dapat memeriksa nilai dari kolom Tanggal, karena setiap file hanya berisi data selama satu bulan dari tahun tertentu. Jika Anda melihat lebih dari satu, itu berarti Anda berhasil menggabungkan data dari beberapa file ke dalam satu tabel.

Nota

Sejauh ini, apa yang telah Anda baca pada dasarnya adalah proses yang sama yang terjadi selama pengalaman Menggabungkan file, tetapi dilakukan secara manual.

Sebaiknya Anda juga membaca artikel tentang Gabungkan gambaran umum file dan Menggabungkan file CSV untuk lebih memahami cara kerja pengalaman menggabungkan file di Power Query dan peran yang dimainkan fungsi kustom.

Menambahkan parameter baru ke fungsi kustom yang sudah ada

Bayangkan bahwa ada persyaratan baru di samping apa yang telah Anda bangun saat ini. Persyaratan baru mengharuskan sebelum Menggabungkan file, Anda memfilter data di dalamnya untuk hanya mendapatkan baris di mana Negara sama dengan Panama.

Untuk membuat persyaratan ini terjadi, buat parameter baru yang disebut Market dengan jenis data teks. Untuk Nilai Saat Ini, masukkan nilai Panama.

Dengan parameter baru ini, pilih file Sampel Transformasi kueri dan filter bidang Negara menggunakan nilai dari parameter Pasar.

Nota

Untuk mempelajari selengkapnya tentang cara memfilter kolom menurut nilai, buka Filter nilai.

Menerapkan langkah baru ini ke kueri Anda secara otomatis memperbarui file transformasi menjadi fungsi, yang sekarang memerlukan dua parameter berdasarkan dua parameter yang digunakan dalam file Sampel Transformasi Anda di.

Tetapi file CSV pada kueri ada tanda peringatan di sampingnya. Sekarang setelah fungsi Anda diperbarui, fungsi memerlukan dua parameter. Jadi, langkah di mana Anda memanggil fungsi menghasilkan nilai kesalahan, karena hanya satu argumen yang diteruskan ke fungsi file Transformasi selama langkah Memanggil Fungsi Kustom.

Untuk memperbaiki kesalahan, klik dua kali Fungsi Kustom yang Dipanggil di Langkah yang Diterapkan untuk membuka jendela Panggil Fungsi Kustom. Dalam parameter Pasar, secara manual masukkan nilai Panama.

Sekarang Anda dapat kembali ke Tabel Output yang Diperluas di Langkah-Langkah Diterapkan. Periksa kueri Anda untuk memvalidasi bahwa hanya baris di mana Negara sama dengan Panama muncul dalam kumpulan hasil akhir kueri File CSV.

Membuat fungsi kustom dari bagian logika yang dapat digunakan kembali

Jika Anda memiliki beberapa kueri atau nilai yang memerlukan serangkaian transformasi yang sama, Anda dapat membuat fungsi kustom yang bertindak sebagai bagian logika yang dapat digunakan kembali. Nantinya, fungsi kustom ini dapat dipanggil terhadap kueri atau nilai pilihan Anda. Fungsi kustom ini dapat menghemat waktu Anda dan membantu Anda dalam mengelola serangkaian transformasi Anda di lokasi pusat, yang dapat Anda ubah kapan saja.

Misalnya, bayangkan kueri yang memiliki beberapa kode sebagai string teks dan Anda ingin membuat fungsi yang mendekode nilai tersebut, seperti dalam tabel sampel berikut:

kode
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Anda mulai dengan memiliki parameter yang memiliki nilai yang berfungsi sebagai contoh. Untuk kasus ini, ini adalah nilai PTY-CM1090-LAX.

Dari parameter tersebut, Anda membuat kueri baru tempat Anda menerapkan transformasi yang Anda butuhkan. Untuk kasus ini, Anda ingin membagi kode PTY-CM1090-LAX menjadi beberapa komponen:

• Asal = PTY
• Destination = LAX
• Maskapai Penerbangan = CM
• FlightID = 1090

Kode M berikut menunjukkan kumpulan transformasi tersebut.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Nota

Untuk mempelajari lebih lanjut tentang bahasa rumus Power Query M, buka bahasa rumus Power Query M.

Anda kemudian dapat mengubah kueri tersebut menjadi fungsi dengan melakukan klik kanan pada kueri dan memilih Buat Fungsi. Terakhir, Anda dapat memanggil fungsi kustom Anda ke dalam salah satu kueri atau nilai Anda, seperti yang ditunjukkan pada gambar berikutnya.

Setelah beberapa transformasi lagi, Anda dapat melihat bahwa Anda mencapai output yang Anda inginkan dan menerapkan logika untuk transformasi tersebut dari fungsi kustom.