Konten
Jika Anda setidaknya sedikit akrab dengan ekspresi reguler, maka Anda tidak perlu mengiklankannya. Jika Anda tidak cukup dalam subjek, maka ekspresi reguler (Ekspresi Reguler = RegExp = "regexps" = "regulars") adalah bahasa di mana, menggunakan karakter dan aturan khusus, substring yang diperlukan dicari dalam teks, mereka diekstraksi atau diganti dengan teks lain. Ini adalah alat yang sangat kuat dan indah, urutan besarnya lebih unggul dari semua cara lain untuk bekerja dengan teks.
Saya telah menjelaskan secara rinci dan dengan banyak contoh dari kehidupan bagaimana Anda dapat menambahkan dukungan ekspresi reguler ke Excel menggunakan makro sederhana - jika Anda belum membaca artikel ini, saya sangat menyarankan Anda membacanya sebelum melanjutkan. Anda akan menemukan banyak hal baru, saya jamin
Namun, pertanyaannya tetap terbuka – bagaimana cara menambahkan kemampuan untuk menggunakan ekspresi reguler di Power Query? Power Query, tentu saja, bagus dengan sendirinya dan dapat melakukan banyak hal dengan teks (memotong, menempelkan, membersihkan, dll.), tetapi jika Anda dapat menyilangkannya dengan kekuatan ekspresi reguler, itu hanya akan menjadi bom.
Sayangnya, tidak ada fungsi bawaan untuk bekerja dengan RegExps di Power Query, dan bantuan resmi dan dukungan teknis Microsoft menjawab pertanyaan ini secara negatif. Namun, ada cara untuk mengatasi keterbatasan ini
Esensi dari metode ini
Ide utamanya sederhana untuk mempermalukan.
Dalam daftar kemampuan Power Query bawaan, ada fungsi Halaman web. Deskripsi fungsi ini di situs bantuan resmi Microsoft sangat ringkas:
Diterjemahkan, ini akan menjadi: "Mengembalikan konten dokumen HTML yang dipecah menjadi struktur komponennya, serta representasi dokumen lengkap dan tubuhnya setelah tag dihapus." Begitu-begitu deskripsi, terus terang.
Biasanya fungsi ini digunakan saat mengimpor data dari web dan secara otomatis diganti, misalnya ketika kita memilih pada tab Data perintah Dari internet (Data — Dari web). Kami memberikan fungsi halaman web sebagai argumen, dan mengembalikan isinya kepada kami dalam bentuk tabel, setelah sebelumnya menghapus semua tag.
Apa yang TIDAK dikatakan oleh bantuan adalah bahwa selain bahasa markup HTML fungsi Halaman web mendukung skrip JavaScript, yang sekarang ada di mana-mana di situs web di Internet. Dan JavaScript, pada gilirannya, selalu dapat bekerja dengan ekspresi reguler dan memiliki fungsi bawaan untuk RegExps! Jadi untuk menerapkan ekspresi reguler di Power Query, kita perlu memasukkan fungsi Halaman Web sebagai argumen ke program JavaScript kecil yang akan melakukan semua pekerjaan untuk Power Query.
Seperti apa tampilannya dalam JavaScript murni
Ada banyak tutorial terperinci tentang bekerja dengan ekspresi reguler dalam JavaScript di Internet (misalnya, satu, dua).
Singkatnya dan disederhanakan, kode JavaScript akan terlihat seperti ini:
Sini:
- var str = 'Bayar tagihan 123 dan 789 untuk sosis'; – membuat variabel str dan menetapkan teks sumber yang akan kita analisis.
- pola var = /d+/gi; – buat ekspresi reguler dan masukkan ke dalam variabel belt hold.
Ekspresi dimulai dengan garis miring (/).
Ekspresi itu sendiri di sini, misalnya, adalah d+ singkatan dari setiap urutan digit.
Melalui pecahan setelah ekspresi, ada parameter pencarian tambahan (pengubah) – mereka dapat ditentukan dalam urutan apa pun:
- g – berarti pencarian global, yaitu setelah menemukan kecocokan, Anda tidak boleh berhenti, tetapi melanjutkan pencarian hingga akhir teks. Jika pengubah ini tidak disetel, maka skrip kami hanya akan mengembalikan kecocokan pertama (123)
- i - cari tanpa memperhatikan huruf besar
- m – pencarian multi-baris (digunakan ketika teks sumber dibagi menjadi beberapa baris)
- var hasil = str.match(pattern).join(';'); – melakukan pencarian di teks sumber (str) dengan ekspresi reguler yang diberikan (belt hold) dan masukkan hasilnya ke dalam variabel mengakibatkan, menggabungkannya dengan titik koma menggunakan perintah ikut
- document.write(hasil); – menampilkan isi variabel hasil
Perhatikan juga bahwa string teks (tidak termasuk ekspresi reguler) di JavaScript diapit oleh apostrof, bukan tanda kutip seperti di Power Query atau VBA.
Pada output, skrip ini akan memberi kita semua angka yang ditemukan dalam teks sumber:
123, 789
Kursus singkat JavaScript telah selesai, terima kasih semuanya. Semoga Anda mendapatkan logika
Tetap mentransfer konstruksi ini ke Power Query.
Cari dan Ekstrak Fungsi Teks dengan Ekspresi Reguler di Power Query
Kami melakukan hal berikut:
1. Buka Excel dan buat Power Query kosong baru di tab Data – Dapatkan data / Buat permintaan – Dari sumber lain – Permintaan kosong (Data — Dapatkan data / Kueri baru — Dari sumber lain — Kueri kosong). Jika Anda memiliki versi lama Excel 2010-2013 dan Power Query Anda tidak memiliki bawaan, tetapi diinstal sebagai add-in terpisah, maka semua ini akan ada di tab Kueri DayaDan tidak Data.
2. Di jendela kosong editor kueri yang terbuka, di panel kanan, segera masukkan nama fungsi masa depan kita (misalnya, fxRegExpExtract)
3. Ayo pergi ke tab Lihat – Editor Tingkat Lanjut (Lihat — Editor Lanjutan), kami menghapus seluruh kode-M dari permintaan kosong dan menempelkan kode superfungsi kami di sana:
Perhatikan tangan Anda:
Pada baris pertama, kita mengatakan bahwa fungsi kita akan memiliki tiga argumen teks: txt – teks asli yang sedang dianalisis, regex – pola ekspresi reguler, batasi — karakter pembatas untuk menampilkan hasil.
Selanjutnya kita panggil fungsi Halaman web, membentuk kode JavaScript yang dijelaskan di atas dalam argumennya. Kami menempelkan dan mengganti argumen variabel kami ke dalam kode.
Pecahan:
[Data]{0}[Anak]{0}[Anak]{1}[Teks]{0}
… diperlukan untuk “jatuh” ke dalam tabel dengan hasil yang kita butuhkan. Intinya adalah bahwa fungsi Halaman web sebagai hasilnya, ini menghasilkan beberapa tabel bersarang yang mengulangi struktur halaman web. Tanpa kode-M ini, fungsi kita akan menampilkan ini:
… dan kita harus mengklik kata itu beberapa kali tabel, berturut-turut "jatuh" ke dalam tabel bersarang anak di kolom anak-anak:
Alih-alih semua kutipan ini, kami segera menunjukkan dalam kode fungsi kami yang bersarang tabel dan kolom (Teks) kita butuh.
Di sini, pada kenyataannya, adalah semua rahasia. Tetap menekan tombol Finish di jendela editor tingkat lanjut, di mana kami memasukkan kode kami, dan Anda dapat melanjutkan ke yang paling lezat – coba fungsi kami di tempat kerja.
Berikut adalah beberapa contoh benih.
Contoh 1. Mengambil nomor rekening dan tanggal dari deskripsi pembayaran
Kami memiliki laporan mutasi bank dengan deskripsi (tujuan) pembayaran, di mana Anda perlu mengeluarkan nomor dan tanggal faktur yang dibayar ke dalam kolom terpisah:
Kami memuat tabel ke Power Query dengan cara standar melalui Data – Dari Tabel/Rentang (Data — Dari Tmampu/Rmalaikat).
Kemudian kami menambahkan kolom terhitung dengan fungsi kami melalui Tambahkan Kolom – Panggil Fungsi Kustom (Tambahkan Kolom — Aktifkan Fungsi Kustom) dan masukkan argumennya:
Sebagai ekspresi reguler (argumen regex) template yang kami gunakan:
(d{3,5}|d{2}.d{2}.d{4})
… diterjemahkan ke dalam bahasa manusia artinya:
angka dari 3 hingga 5 digit (nomor rekening)
or
fragmen bentuk "angka 2-bit - titik - angka 2-bit - titik - angka 4-bit", yaitu, tanggal formulir DD.MM.YYYY.
Sebagai karakter pembatas (argumen batasi) masukkan titik koma.
Setelah mengklik OK fungsi ajaib kami menganalisis semua data awal sesuai dengan ekspresi reguler kami dan membentuk kolom untuk kami dengan nomor dan tanggal faktur yang ditemukan:
Tetap memisahkannya dengan titik koma menggunakan perintah Beranda — Pisahkan Kolom — Berdasarkan Pembatas (Beranda — Pisahkan kolom — Menurut pembatas) dan kami mendapatkan apa yang kami inginkan:
Keindahan!
Contoh 2: Ekstrak alamat email dari teks
Misalkan kita memiliki tabel berikut sebagai data awal:
… dari mana kita perlu mengeluarkan alamat email yang ditemukan di sana (untuk kejelasan, saya menyorotnya dengan warna merah di teks).
Seperti pada contoh sebelumnya, kami memuat tabel ke Power Query dengan cara standar melalui Data – Dari Tabel/Rentang (Data — Dari Tmampu/Rmalaikat).
Kemudian kami menambahkan kolom terhitung dengan fungsi kami melalui Tambahkan Kolom – Panggil Fungsi Kustom (Tambahkan Kolom — Aktifkan Fungsi Kustom) dan masukkan argumennya:
Mengurai alamat email adalah tugas yang lebih sulit dan ada banyak ekspresi reguler dari berbagai tingkat mimpi buruk untuk menyelesaikannya. Saya menggunakan salah satu opsi sederhana – tidak ideal, tetapi cukup berfungsi dalam banyak kasus:
[w|.|-]*@w*.[w|.]*
Sebagai pemisah (batasi) Anda dapat memasukkan titik koma dan spasi.
Klik OK dan kami mendapatkan kolom dengan alamat email yang diekstraksi dari teks asli "bubur":
Sihir!
PS
Seperti kata pepatah: “Tidak ada hal baik yang tidak bisa dibuat lebih baik lagi.” Power Query sendiri keren, dan ketika dikombinasikan dengan ekspresi reguler, itu memberi kita kekuatan dan fleksibilitas yang sama sekali tidak realistis dalam memproses data teks apa pun. Saya berharap Microsoft suatu hari nanti akan menambahkan dukungan RegExp dalam pembaruan Power Query dan Power BI dan semua tarian di atas dengan rebana akan menjadi masa lalu. Nah, untuk saat ini, ya.
Saya juga ingin menambahkan bahwa nyaman untuk bermain dengan ekspresi reguler di situs https://regexr.com/ – tepat di editor online. Ada di bagian Pola Komunitas Ada sejumlah besar musim reguler siap pakai untuk semua kesempatan. Eksperimen – semua kekuatan ekspresi reguler kini siap melayani Anda di Power Query!
- Apa itu ekspresi reguler (RegExp) dan bagaimana menggunakannya di Excel
- Pencarian teks kabur di Power Query
- Merakit tabel dari file yang berbeda menggunakan Power Query