Penanganan kesalahan
Mirip dengan bagaimana Excel dan bahasa DAX memiliki IFERROR
fungsi, Power Query memiliki sintaksnya sendiri untuk menguji dan menangkap kesalahan.
Seperti disebutkan dalam artikel tentang menangani kesalahan di Power Query, kesalahan bisa muncul baik di langkah atau tingkat sel. Artikel ini berfokus pada bagaimana Anda dapat menangkap dan mengelola kesalahan berdasarkan logika spesifik Anda sendiri.
Catatan
Untuk menunjukkan konsep ini, artikel ini menggunakan Buku Kerja Excel sebagai sumber datanya. Konsep yang diperlihatkan di sini berlaku untuk semua nilai di Power Query dan tidak hanya yang berasal dari Buku Kerja Excel.
Contoh sumber data untuk demonstrasi ini adalah Buku Kerja Excel dengan tabel berikut.
Tabel dari Buku Kerja Excel ini memiliki kesalahan Excel seperti #NULL!, #REF!, dan #DIV/0! di kolom Laju Standar. Saat Anda mengimpor tabel ini ke editor Power Query, gambar berikut ini memperlihatkan tampilannya.
Perhatikan bagaimana kesalahan dari buku kerja Excel diperlihatkan dengan [Error]
nilai di setiap sel.
Dalam artikel ini, Anda mempelajari cara mengganti kesalahan dengan nilai lain. Selain itu, Anda juga mempelajari cara menangkap kesalahan dan menggunakannya untuk logika spesifik Anda sendiri.
Dalam hal ini, tujuannya adalah untuk membuat kolom Tingkat Akhir baru di sumber data sampel yang menggunakan nilai dari kolom Laju Standar. Jika ada kesalahan, maka ia menggunakan nilai dari kolom Tarif Khusus yang sesuai.
Untuk membuat kolom kustom baru, buka menu Tambahkan kolom dan pilih Kolom kustom. Di jendela Kolom kustom, masukkan rumus try [Standard Rate] otherwise [Special Rate]
. Beri nama kolom baru ini Tingkat Akhir.
Rumus ini mencoba mengevaluasi kolom Laju Standar dan mengeluarkan nilainya jika tidak ada kesalahan yang ditemukan. Jika kesalahan ditemukan di kolom Laju Standar, maka output adalah nilai yang ditentukan setelah otherwise
pernyataan, yang dalam hal ini adalah kolom Tarif Khusus.
Setelah Anda menambahkan tipe data yang benar ke semua kolom dalam tabel, gambar berikut ini memperlihatkan tampilan tabel akhir.
Catatan
Sebagai pendekatan alternatif, Anda juga dapat memasukkan rumus try [Standard Rate] catch ()=> [Special Rate]
, yang setara dengan rumus sebelumnya, tetapi menggunakan kata kunci tangkapan dengan fungsi yang tidak memerlukan parameter.
Kata catch
kunci diperkenalkan ke Power Query pada mei 2022.
Menggunakan sumber data sampel yang sama dengan bagian sebelumnya, tujuan baru adalah membuat kolom baru untuk Tingkat Akhir. Jika nilai dari Tarif Standar ada, maka nilai tersebut digunakan. Jika tidak, nilai dari kolom Tarif Khusus digunakan, kecuali untuk baris dengan kesalahan apa pun #REF!
.
Catatan
Satu-satunya tujuan untuk mengecualikan #REF!
kesalahan adalah untuk tujuan demonstrasi. Dengan konsep yang ditampilkan dalam artikel ini, Anda dapat menargetkan bidang apa pun pilihan Anda dari catatan kesalahan.
Saat Anda memilih salah satu spasi kosong di samping nilai kesalahan, Anda mendapatkan panel detail di bagian bawah layar. Panel detail berisi alasan kesalahan, DataFormat.Error
, dan pesan kesalahan, Invalid cell value '#REF!'
:
Anda hanya dapat memilih satu sel pada satu waktu, sehingga Anda hanya dapat memeriksa komponen kesalahan secara efektif dari satu nilai kesalahan pada satu waktu. Pada titik ini Anda membuat kolom kustom baru dan menggunakan try
ekspresi .
Untuk membuat kolom kustom baru, buka menu Tambahkan kolom dan pilih Kolom kustom. Di jendela Kolom kustom, masukkan rumus try [Standard Rate]
. Beri nama kolom baru ini Semua Kesalahan.
try
Ekspresi mengonversi nilai dan kesalahan menjadi nilai rekaman yang menunjukkan apakah try
ekspresi menangani kesalahan atau tidak, dan nilai yang tepat atau rekaman kesalahan.
Anda dapat memperluas kolom yang baru dibuat ini dengan nilai rekaman dan melihat bidang yang tersedia untuk diperluas dengan memilih ikon di samping header kolom.
Operasi ini mengekspos tiga bidang baru:
- Semua Errors.HasError—menampilkan apakah nilai dari kolom Laju Standar mengalami kesalahan atau tidak.
- Semua Errors.Value—jika nilai dari kolom Laju Standar tidak memiliki kesalahan, kolom ini menampilkan nilai dari kolom Laju Standar. Untuk nilai dengan kesalahan, bidang ini tidak tersedia, dan selama operasi perluas kolom ini memiliki
null
nilai. - Semua Errors.Error—jika nilai dari kolom Laju Standar mengalami kesalahan, kolom ini menampilkan catatan kesalahan untuk nilai dari kolom Laju Standar. Untuk nilai tanpa kesalahan, bidang ini tidak tersedia, dan selama operasi perluas kolom ini memiliki
null
nilai.
Untuk penyelidikan lebih lanjut, Anda dapat memperluas kolom Semua Kesalahan.Kesalahan untuk mendapatkan tiga komponen rekaman kesalahan:
- Alasan kesalahan
- Pesan kesalahan
- Detail kesalahan
Setelah melakukan operasi perluas, bidang Semua Kesalahan.Kesalahan.Pesan menampilkan pesan kesalahan tertentu yang memberi tahu Anda dengan tepat kesalahan Excel apa yang dimiliki setiap sel. Pesan kesalahan berasal dari bidang Pesan Kesalahan dari catatan kesalahan.
Sekarang dengan setiap pesan kesalahan di kolom baru, Anda dapat membuat kolom kondisional baru dengan nama Tingkat Akhir dan klausul berikut:
- Jika nilai di kolom All Errors.Errors.Message sama dengan
null
, maka outputnya adalah nilai dari kolom Laju Standar. - Jika tidak, jika nilai di kolom Semua Errors.Errors.Message tidak sama
Invalid cell value '#REF!'.
, maka outputnya adalah nilai dari kolom Tarif Khusus. - Lain, null.
Setelah hanya menyimpan kolom Akun, Tarif Standar, Tarif Khusus, dan Tingkat Akhir, dan menambahkan jenis data yang benar untuk setiap kolom, gambar berikut menunjukkan seperti apa tabel akhir.
Atau, Anda juga dapat membuat kolom kustom baru menggunakan try
kata kunci dan catch
.
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null