Daptar eusi
Upami anjeun sahenteuna rada wawuh sareng ungkapan biasa, maka anjeun henteu kedah ngiklankeunana. Upami anjeun henteu cekap dina subjek, maka éksprési biasa (Ucapan Biasa = RegExp = "regexps" = "biasa") mangrupikeun basa dimana, ngagunakeun karakter sareng aturan khusus, substrings anu diperyogikeun dipilarian dina téks, aranjeunna diekstrak. atawa diganti ku téks séjén. Ieu mangrupikeun alat anu kuat sareng éndah, urutan gedéna langkung luhur tibatan sadaya cara damel sareng téks.
Kuring parantos ngajelaskeun sacara rinci sareng seueur conto tina kahirupan kumaha anjeun tiasa nambihan dukungan éksprési rutin ka Excel nganggo makro saderhana - upami anjeun henteu acan maca tulisan ieu, kuring nyarankeun pisan yén anjeun maca éta sateuacan neraskeun. Anjeun bakal manggihan loba hal anyar, kuring ngajamin 🙂
Nanging, patarosan tetep kabuka - kumaha cara nambihan kamampuan ngagunakeun ekspresi biasa dina Power Query? Kakuatan Query, tangtosna, alus sorangan sarta bisa ngalakukeun loba jeung téks (motong, gluing, beberesih, jeung sajabana), tapi lamun bisa meuntas eta kalawan kakuatan ekspresi biasa, eta bakal jadi ngan bom.
Hanjakalna, teu aya fungsi anu diwangun pikeun damel sareng RegExps dina Power Query, sareng bantosan Microsoft resmi sareng dukungan téknis ngajawab patarosan ieu négatip. Sanajan kitu, aya jalan sabudeureun watesan ieu 🙂
Intina tina metode
Gagasan utama nyaéta basajan pikeun aib.
Dina daptar kamampuan Power Query anu diwangun, aya fungsi Kaca wéb. Katerangan ngeunaan fungsi ieu dina situs pitulung Microsoft resmi pisan singket:
Ditarjamahkeun, ieu bakal kieu: "Mulangkeun eusi dokumen HTML direcah jadi struktur komponén na, kitu ogé ngagambarkeun dokumén lengkep jeung awakna sanggeus tag geus dihapus". Katerangan kitu-kitu, terus terang.
Biasana pungsi ieu dianggo nalika ngimpor data tina wéb sareng digentos sacara otomatis, contona, nalika urang milih dina tab. data parentah Tina Internét (Data - Tina wéb). Kami masihan fungsi halaman wéb salaku argumen, sareng éta ngabalikeun eusina ka kami dina bentuk tabel, saatos ngabersihkeun sadaya tag.
Naon pitulung NOT nyebutkeun éta salian basa markup HTML fungsi Kaca wéb ngarojong Aksara JavaScript, anu ayeuna aya di mana-mana dina situs wéb dina Internét. Jeung JavaScript, kahareupna geus salawasna geus bisa digawekeun ku ungkapan biasa na geus diwangun-di fungsi pikeun RegExps! Jadi pikeun nerapkeun éksprési biasa dina Power Query, urang bakal perlu eupan fungsi Web.Page salaku argumen pikeun program JavaScript leutik nu bakal ngalakukeun sagala pagawean pikeun Power Query.
Naon eta Sigana mah dina JavaScript murni
Aya loba tutorials lengkep dina gawé bareng éksprési biasa dina JavaScript on Internét (contona, hiji, dua).
Dina pondok tur disederhanakeun, kode JavaScript bakal kasampak kawas kieu:
Ieuh:
- var str = 'Bayar tagihan 123 sareng 789 kanggo sosis'; - nyieun variabel str sareng napelkeun téks sumber anu bakal urang analisa.
- pola var = /d+/gi; - Jieun ekspresi biasa teras nahan kana variabel corak.
Babasan dimimitian ku garis miring (/).
Babasan sorangan di dieu, contona, nyaéta d+ nangtung pikeun sagala runtuyan digit.
Ngaliwatan fraksi sanggeus éksprési, aya parameter pilarian tambahan (modifiers) - aranjeunna bisa dieusian dina urutan naon:
- g - hartina pilarian global, ie sanggeus manggihan hiji patandingan, anjeun teu kudu eureun, tapi nuluykeun pilarian nepi ka ahir téks. Upami modifier ieu henteu disetel, maka naskah kami ngan ukur bakal ngabalikeun pertandingan anu munggaran (123)
- i – milarian tanpa merhatikeun kasus hurup
- m - Pilarian multi-garis (dipaké nalika téks sumber dibagi kana sababaraha baris)
- var hasil = str.match(pola).join(';'); - ngalakukeun pilarian dina téks sumber (str) ku éksprési biasa (corak) jeung nempatkeun hasilna dina variabel hasil, concatenating aranjeunna kalayan titik koma maké paréntah gabung
- document.write(hasil); – mintonkeun eusi variabel hasil
Catet ogé yén senar téks (kaasup éksprési biasa) dina JavaScript diwengku ku tanda kutip, sanés kutipan sapertos dina Power Query atanapi VBA.
Dina kaluaran, skrip ieu bakal masihan urang salaku hasilna sadaya nomer anu aya dina téks sumber:
123, 789
Kursus pondok JavaScript parantos réngsé, hatur nuhun sadayana. Miharep anjeun meunang logika 🙂
Tetep mindahkeun konstruksi ieu ka Power Query.
Pilarian sareng Ékstrak Fungsi Téks ku Éksprési Biasa dina Power Query
Urang ngalakukeun ieu:
1. Buka Excel sareng jieun Query Power kosong anyar dina tab Data - Kéngingkeun data / Jieun pamundut - Tina sumber sanés - Paménta kosong (Data — Kéngingkeun data / Paménta énggal — Tina sumber sanés — Paménta kosong). Upami anjeun gaduh vérsi Excel 2010-2013 sareng Power Query anu lami anjeun henteu gaduh built-in, tapi dipasang salaku tambihan anu misah, maka sadaya ieu bakal aya dina tab. Patarosan KakuatanSareng henteu data.
2. Dina jandela kosong pangropéa query anu muka, dina panel katuhu, lebetkeun nami fungsi hareup urang (contona, fxRegExpExtract)
3. Hayu urang buka tab Témbongkeun - Editor Maju (Témbongkeun - Éditor Canggih), urang mupus sakabéh M-kode tina pamundut kosong tur nempelkeun kodeu superfunction urang dinya:
Jaga leungeun anjeun:
Dina baris kahiji, urang nyebutkeun yén fungsi urang bakal mibanda tilu argumen téks: txt - téks aslina keur dianalisis, regex - pola ekspresi biasa, delim - karakter delimiter pikeun mintonkeun hasil.
Salajengna urang nelepon fungsi Kaca wéb, ngabentuk kode JavaScript ditétélakeun di luhur dina argumen na. Urang nempelkeun tur ngagantikeun argumen variabel urang kana kode.
sempalan:
[Data]{0}[Barudak]{0}[Barudak]{1}[Téks]{0}
... dipikabutuh pikeun "turun" kana tabél kalayan hasil anu urang peryogikeun. Intina nyaéta fungsi Kaca wéb hasilna, eta ngahasilkeun sababaraha tabel nested nu ngulang struktur hiji kaca web. Tanpa sapotong M-code ieu, fungsi urang bakal ngahasilkeun ieu:
… sareng urang kedah ngaklik kecap sababaraha kali meja, successively "ragrag ngaliwatan" kana tabel anak nested dina kolom barudak:
Gantina sagala cutatan ieu, urang langsung nunjukkeun dina kode fungsi urang nu nested tabel sarta kolom (naskah) urang butuh.
Di dieu, kanyataanna, sadayana rusiah. Tetep pencét tombol ngabereskeun dina jandela redaktur canggih, dimana urang diselapkeun kode urang, sarta anjeun bisa neruskeun ka paling nikmat - coba fungsi kami di tempat gawé.
Ieu sababaraha conto siki.
Conto 1. Retrieving nomer rekening jeung tanggal tina pedaran pamayaran
Kami gaduh pernyataan bank kalayan katerangan (tujuan) pamayaran, dimana anjeun kedah narik nomer sareng tanggal invoice anu dibayar kana kolom anu misah:
Kami ngamuat méja kana Power Query dina cara standar Data - Ti Table / rentang (Data - Tina Tbisa / Rmalaikat).
Teras we nambahkeun kolom diitung kalawan fungsi kami via Tambahkeun Kolom - Telepon Fungsi Adat (Tambahkeun Kolom - Ngajantenkeun Fungsi Kustom) sareng lebetkeun argumen na:
Salaku éksprési biasa (argumen regex) template anu kami anggo:
(d{3,5}|d{2}.d{2}.d{4})
… ditarjamahkeun kana basa manusa hartina:
angka ti 3 nepi ka 5 digit (nomer akun)
or
fragmen bentuk "nomer 2-bit - titik - angka 2-bit - titik - angka 4-bit", nyaeta, kaping tina formulir DD.MM.YYYY.
Salaku karakter delimiter (argumen delim) asupkeun titik koma.
Sanggeus ngaklik OK fungsi magic urang nganalisa sakabéh data awal nurutkeun éksprési biasa urang jeung ngabentuk kolom pikeun urang jeung nomer kapanggih jeung kaping tina invoices:
Tetep misahkeunana ku titik koma ngagunakeun paréntah Imah — Split Column — Ku Delimiter (Imah - Kolom pamisah - Ku pembatas) sareng urang kéngingkeun naon anu dipikahoyong:
Kageulisan!
Conto 2: Ékstrak alamat surélék tina téks
Anggap urang gaduh tabel di handap ieu salaku data awal:
... ti mana urang kudu tarik kaluar alamat surélék kapanggih aya (pikeun kajelasan, kuring disorot aranjeunna beureum dina téks).
Saperti dina conto saméméhna, urang ngamuat tabel kana Power Query dina cara baku via Data - Ti Table / rentang (Data - Tina Tbisa / Rmalaikat).
Teras we nambahkeun kolom diitung kalawan fungsi kami via Tambahkeun Kolom - Telepon Fungsi Adat (Tambahkeun Kolom - Ngajantenkeun Fungsi Kustom) sareng lebetkeun argumen na:
Parsing alamat email mangrupikeun tugas anu langkung hese sareng aya sakumpulan ekspresi biasa tina rupa-rupa tingkat ngimpina pikeun ngabéréskeunana. Kuring nganggo salah sahiji pilihan saderhana - henteu idéal, tapi lumayan tiasa dianggo dina kalolobaan kasus:
[w|.|-]*@w*.[w|.]*
Salaku pemisah (delim) anjeun bisa ngasupkeun titik koma jeung spasi.
Klik OK sarta kami meunang kolom kalawan alamat e-mail sasari tina téks aslina "bubur":
Sihir!
PS
Sakumaha paribasa: "Teu aya hal anu saé anu henteu tiasa dilakukeun langkung saé." Power Query nyaéta tiis sorangan, sarta lamun digabungkeun jeung ungkapan biasa, méré urang kakuatan lengkep unrealistic sarta kalenturan dina ngolah sagala data téks. Kuring miharep Microsoft someday bakal nambahan rojongan RegExp dina Power Query jeung Power BI apdet sarta sakabeh tarian di luhur kalawan rebana bakal jadi hal kaliwat. Muhun, pikeun ayeuna, enya.
Kuring ogé hoyong tambahkeun yén éta merenah pikeun maénkeun kalayan ungkapan biasa dina situs https://regexr.com/ - katuhu dina redaktur online. Aya dina bagian Pola Komunitas Aya sajumlah ageung usum biasa anu siap-siap pikeun sadaya waktos. Ékspérimén - sadaya kakuatan éksprési biasa ayeuna sayogi pikeun anjeun dina Power Query!
- Naon éksprési biasa (RegExp) sareng kumaha ngagunakeunana dina Excel
- Pilarian téks kabur dina Power Query
- Assembling tabel tina file béda ngagunakeun Power Query