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.

Meskipun Anda bisa membuat fungsi kustom Power Query Anda sendiri secara manual menggunakan kode seperti yang dijelaskan dalam Memahami fungsi Power Query M, antarmuka pengguna Power Query menawarkan fitur untuk mempercepat, menyederhanakan, dan meningkatkan proses pembuatan dan pengelolaan fungsi kustom.

Artikel ini berfokus pada pengalaman ini, hanya disediakan melalui antarmuka pengguna Power Query, dan cara mendapatkan hasil maksimal darinya.

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 tambahan dalam dokumentasi Power Query untuk informasi selengkapnya tentang transformasi tertentu yang dirujuk dalam artikel ini.

Membuat fungsi kustom dari referensi tabel

Catatan

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 Ubah 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.

Catatan

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 menu dropdown Jenis dialog Parameter saat Anda memiliki kueri yang mengevaluasi ke 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 file Sampel Transformasi. Setiap perubahan yang Anda buat pada kueri file Sampel Transformasi secara otomatis direplikasi ke fungsi kustom Anda. Selama pembuatan fungsi baru ini, gunakan File transformasi 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 kueri file Sampel Transformasi Anda.
• Kueri file Sampel Transformasi Anda, umumnya dikenal sebagai kueri sampel.
• Fungsi yang baru Anda buat, dalam hal ini Transformasi file.

Menerapkan transformasi ke kueri sampel

Dengan fungsi baru Anda dibuat, pilih kueri dengan nama File Sampel Transformasi. Kueri ini sekarang ditautkan dengan fungsi file Transformasi, sehingga setiap perubahan yang dilakukan pada kueri ini 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 menginterpretasikan 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 Sampel Transformasi adalah:

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

   

   Catatan

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

2. Promosikan header—Header 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 Sampel Transformasi Anda terlihat seperti gambar berikutnya.

Catatan

Untuk mempelajari selengkapnya tentang cara mempromosikan dan menurunkan header, buka Mempromosikan atau menurunkan 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 fungsi file Transformasi, Anda akan disambut dengan 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 digabungkan, 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 menu 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 akan 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.

Catatan

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.

Catatan

Apa yang telah Anda baca sejauh ini pada dasarnya adalah proses yang sama yang terjadi selama pengalaman Gabungkan file , tetapi dilakukan secara manual.

Kami menyarankan agar Anda juga membaca artikel tentang Gabungkan gambaran umum file dan Gabungkan 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 atas apa yang saat ini Anda bangun. 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 kueri File Sampel Transformasi dan filter bidang Negara menggunakan nilai dari parameter Pasar.

Catatan

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

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

Tetapi kueri file CSV memiliki 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 salah satu argumen yang diteruskan ke fungsi file Transformasi selama langkah Fungsi Kustom yang Dipanggil.

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

Sekarang Anda dapat kembali ke Tabel Output yang Diperluas di Langkah yang Diterapkan. Periksa kueri Anda untuk memvalidasi bahwa hanya baris di mana Negara sama dengan Panama muncul di 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:

• Origin = PTY
• Tujuan = LAX
• Maskapai = CM
• FlightID = 1090

Kode M berikut menunjukkan bahwa kumpulan transformasi.

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"

Catatan

Untuk mempelajari selengkapnya 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.