Paragraf
Yen sampeyan paling ora kenal karo ekspresi biasa, mula sampeyan ora perlu ngiklanake. Yen sampeyan ora cukup ing subyek, banjur ekspresi reguler (Ekspresi Reguler = RegExp = "regexps" = "reguler") minangka basa ing ngendi, nggunakake karakter lan aturan khusus, substrings sing perlu ditelusuri ing teks, diekstrak. utawa diganti karo teks liyane. Iki minangka alat sing kuat lan apik banget, urutan gedhene sing luwih dhuwur tinimbang kabeh cara liya kanggo nggarap teks.
Aku wis njlèntrèhaké kanthi rinci lan kanthi akeh conto saka urip carane sampeyan bisa nambah dhukungan ekspresi reguler menyang Excel nggunakake makro sing prasaja - yen sampeyan durung maca artikel iki, aku menehi saran supaya maca sadurunge nerusake. Sampeyan bakal nemokake akèh iku anyar, Aku njamin 🙂
Nanging, pitakonan tetep mbukak - carane nambah kemampuan kanggo nggunakake ekspresi reguler ing Power Query? Power Query, mesthi, apik ing dhewe lan bisa nindakake akeh karo teks (nglereni, gluing, reresik, etc.), Nanging yen sampeyan bisa nyabrang karo daya saka ekspresi biasa, iku bakal mung bom.
Sayange, ora ana fungsi sing dibangun kanggo nggarap RegExps ing Power Query, lan bantuan Microsoft resmi lan dhukungan teknis njawab pitakonan iki kanthi negatif. Nanging, ana cara watara watesan iki 🙂
Intine metode kasebut
Ide utama iku prasaja kanggo disgrace.
Ing dhaptar kapabilitas Power Query sing dibangun, ana fungsi Web.Kaca. Katrangan fungsi iki ing situs bantuan Microsoft resmi banget ringkes:
Diterjemahake, iki bakal dadi: "Ngasilake isi dokumen HTML sing dipérang dadi struktur komponen, uga minangka perwakilan saka dokumen lengkap lan awak sawise tag wis dibusak." Dadi-dadi katrangan, terus terang.
Biasane fungsi iki digunakake nalika ngimpor data saka web lan diganti kanthi otomatis, contone, nalika kita milih ing tab. Data Command Saka Internet (Data - Saka web). We menehi fungsi kaca web minangka bantahan, lan bali isi kanggo kita ing wangun tabel, wis dibusak sadurunge kabeh tags.
Apa sing ora diomongake bantuan kasebut yaiku saliyane basa markup HTML fungsi Web.Kaca ndhukung skrip JavaScript, sing saiki ana ing ngendi-endi ing situs web ing Internet. Lan JavaScript, ing siji, tansah bisa digunakake karo ekspresi biasa lan wis dibangun ing fungsi kanggo RegExps! Dadi kanggo ngleksanakake ekspresi reguler ing Power Query, kita kudu Feed fungsi Web.Page minangka argumen kanggo program JavaScript cilik sing bakal nindakake kabeh karya kanggo Power Query.
Apa sing katon ing JavaScript murni
Ana akeh tutorial rinci babagan nggarap ekspresi reguler ing JavaScript ing Internet (contone, siji, loro).
Singkat lan disederhanakake, kode JavaScript bakal katon kaya iki:
Kene:
- var str = 'Mbayar tagihan 123 lan 789 kanggo sosis'; - nggawe variabel str lan nemtokake teks sumber sing bakal kita analisis.
- pola var = /d+/gi; - nggawe ekspresi reguler lan sijine ing variabel pola.
Ekspresi kasebut diwiwiti kanthi garis miring (/).
Ekspresi dhewe ing kene, umpamane d+ stands for sembarang urutan digit.
Liwat fraksi sawise ekspresi, ana paramèter panelusuran tambahan (modifiers) - bisa ditemtokake ing urutan apa wae:
- g - tegese panelusuran global, yaiku sawise nemokake sing cocog, sampeyan ora kudu mandheg, nanging terus telusuran nganti pungkasan teks. Yen modifier iki ora disetel, skrip kita mung bakal ngasilake pertandhingan pisanan (123)
- i – nggoleki tanpa preduli kanggo cilik saka huruf
- m - Panelusuran multi-baris (digunakake nalika teks sumber dipérang dadi sawetara baris)
- var hasil = str.match(pola).join(';'); - nindakake panelusuran ing teks sumber (str) kanthi ekspresi reguler (pola) lan sijine asil ing variabel asil, concatenating karo titik koma nggunakake printah Gabung
- document.write(hasil); - nampilake isi variabel asil
Uga elinga yen string teks (ora kalebu ekspresi reguler) ing JavaScript dilampirake ing apostrof, dudu kuotasi kaya sing ana ing Power Query utawa VBA.
Ing output, skrip iki bakal menehi kabeh nomer sing ditemokake ing teks sumber:
123, 789
Kursus singkat JavaScript wis rampung, matur nuwun kabeh. Muga-muga sampeyan entuk logika 🙂
Iku tetep kanggo nransfer construction iki kanggo Power Query.
Telusuri lan Ekstrak Fungsi Teks kanthi Ekspresi Reguler ing Power Query
Kita nindakake ing ngisor iki:
1. Bukak Excel lan gawe Power Query kosong anyar ing tab Data - Entuk data / Gawe panjaluk - Saka sumber liyane - Panjaluk kosong (Data - Entuk data / Pitakonan anyar - Saka sumber liya - Pitakonan kosong). Yen sampeyan duwe versi lawas Excel 2010-2013 lan Power Query sampeyan ora duwe built-in, nanging wis diinstal minangka tambahan kapisah, banjur kabeh iki bakal ing tab Pitakonan DayaLan ora Data.
2. Ing jendhela kosong editor pitakon sing mbukak, ing panel tengen, langsung ketik jeneng fungsi mangsa ngarep (contone, fxRegExpExtract)
3. Ayo menyang tab Deleng - Editor Lanjut (Deleng - Editor Lanjut), kita mbusak kabeh M-kode saka panjalukan kosong lan nempel kode superfungsi kita ana:
Watch tangan sampeyan:
Ing baris pisanan, kita ujar manawa fungsi kita bakal duwe telung argumen teks: txt - teks asli sing dianalisis, regex - pola ekspresi reguler, delim - karakter delimiter kanggo nampilake asil.
Sabanjure kita nelpon fungsi Web.Kaca, mbentuk kode JavaScript sing diterangake ing ndhuwur ing argumentasi. Kita nempel lan ngganti argumen variabel kita menyang kode.
pecahan:
[Data]{0}[Bocah]{0}[Bocah]{1}[Teks]{0}
... dibutuhake kanggo "tiba" menyang meja kanthi asil sing dibutuhake. Intine yaiku fungsi kasebut Web.Kaca minangka asil, iku gawé sawetara nested tabel sing mbaleni struktur kaca web. Tanpa potongan M-kode iki, fungsi kita bakal ngasilake iki:
… lan kita kudu ngeklik tembung kasebut kaping pirang-pirang Tabel, berturut-turut "mudhun" menyang tabel nested anak ing kolom anak:
Tinimbang kabeh kutipan iki, kita langsung nuduhake ing kode fungsi kita sing nested tabel lan kolom (Tèks) dhewe butuh.
Kene, ing kasunyatan, kabeh rahasia. Iku tetep kanggo mencet tombol Rampung ing jendhela editor majeng, ngendi kita masang kode kita, lan sampeyan bisa nerusake menyang paling éca - nyoba fungsi kita ing karya.
Ing ngisor iki sawetara conto wiji.
Conto 1. Njupuk nomer akun lan tanggal saka gambaran pembayaran
Kita duwe statement bank kanthi katrangan (tujuan) pembayaran, ing ngendi sampeyan kudu narik nomer lan tanggal invoice sing dibayar menyang kolom sing kapisah:
Kita mbukak tabel menyang Power Query kanthi cara standar Data - Saka Tabel / Range (Data - Saka Tsaged/Rmalaikat).
Banjur kita nambah kolom diwilang karo fungsi kita liwat Tambah Kolom - Telpon Fungsi Kustom (Tambah Kolom - Nyetel Fungsi Kustom) lan ketik argumene:
Minangka ekspresi reguler (argumen regex) Cithakan sing kita gunakake:
(d{3,5}|d{2}.d{2}.d{4})
… diterjemahake menyang basa manungsa tegese:
angka saka 3 nganti 5 digit (nomer akun)
or
fragmen saka wangun "nomer 2-bit - titik - angka 2-bit - titik - angka 4-bit", yaiku, tanggal wangun DD.MM.YYYY.
Minangka karakter delimiter (argumen delim) mlebu titik koma.
Sawise ngeklik OK fungsi sihir kita nganalisa kabeh data awal miturut ekspresi reguler lan mbentuk kolom kanggo kita kanthi nomer lan tanggal invoice sing ditemokake:
Iku tetep kanggo misahake kanthi titik koma nggunakake printah Ngarep — Split Column — Miturut Delimiter (Ngarep - Pisah kolom - Miturut delimiter) lan entuk apa sing dikarepake:
Kaendahan!
Conto 2: Ekstrak alamat email saka teks
Contone, kita duwe tabel ing ngisor iki minangka data awal:
... saka ngendi kita kudu narik alamat email sing ditemokake ing kono (kanggo gamblang, aku nyorot kanthi abang ing teks kasebut).
Kaya ing conto sadurunge, kita mbukak tabel menyang Power Query ing cara standar liwat Data - Saka Tabel / Range (Data - Saka Tsaged/Rmalaikat).
Banjur kita nambah kolom diwilang karo fungsi kita liwat Tambah Kolom - Telpon Fungsi Kustom (Tambah Kolom - Nyetel Fungsi Kustom) lan ketik argumene:
Parsing alamat email minangka tugas sing luwih angel lan ana akeh ekspresi reguler sing beda-beda derajat ngipi elek kanggo ngatasi. Aku nggunakake salah siji opsi sing prasaja - ora becik, nanging bisa digunakake ing pirang-pirang kasus:
[w|.|-]*@w*.[w|.]*
Minangka pemisah (delim) sampeyan bisa ngetik titik koma lan spasi.
Klik ing OK lan entuk kolom kanthi alamat e-mail sing diekstrak saka teks asli "bubur":
Sihir!
PS
Kaya sing dicritakake: "Ora ana barang sing apik sing ora bisa digawe luwih apik." Power Query kelangan dhewe, lan yen digabungake karo ekspresi biasa, menehi daya lan keluwesan sing ora nyata kanggo ngolah data teks. Muga-muga Microsoft bakal nambah dhukungan RegExp ing Power Query lan nganyari Power BI lan kabeh tarian ing ndhuwur kanthi rebana bakal dadi barang sing kepungkur. Nah, kanggo saiki, ya.
Aku uga pengin nambah sing trep kanggo muter karo ekspresi biasa ing situs https://regexr.com/ - tengen ing editor online. Ana ing bagean Pola Komunitas Ana pirang-pirang musim reguler sing wis siap kanggo kabeh kesempatan. Eksperimen - kabeh kekuwatan ekspresi reguler saiki kasedhiya ing Power Query!
- Apa ekspresi reguler (RegExp) lan cara nggunakake ing Excel
- Panelusuran teks kabur ing Power Query
- Nglumpukake tabel saka macem-macem file nggunakake Power Query