Zamkatimu
Ngati mumadziwa pang'ono mawu okhazikika, ndiye kuti simuyenera kuwatsatsa. Ngati simuli pamutuwu, ndiye kuti mawu okhazikika (Regular Expressions = RegExp = "regexps" = "regulars") ndi chilankhulo chomwe, pogwiritsa ntchito zilembo zapadera ndi malamulo, zingwe zofunikira zimafufuzidwa m'mawuwo, zimachotsedwa. kapena kusinthidwa ndi malemba ena . Ichi ndi chida champhamvu kwambiri komanso chokongola, dongosolo la kukula kwapamwamba kuposa njira zina zonse zogwirira ntchito ndi malemba.
Ndalongosola kale mwatsatanetsatane komanso ndi mndandanda wa zitsanzo za moyo momwe mungawonjezere chithandizo cha nthawi zonse ku Excel pogwiritsa ntchito macros osavuta - ngati simunawerenge nkhaniyi, ndikupangira kuti muwerenge musanapitirize. Mupeza zinthu zambiri zatsopano, ndikukutsimikizirani 🙂
Komabe, funso limakhala lotseguka - momwe mungawonjezere luso logwiritsa ntchito mawu pafupipafupi mu Power Query? Power Query, ndithudi, ndi yabwino payokha ndipo imatha kuchita zambiri ndi malemba (kudula, gluing, kuyeretsa, etc.), koma ngati mungathe kuwoloka ndi mphamvu ya mawu okhazikika, ingakhale bomba chabe.
Tsoka ilo, palibe ntchito zomangidwira zogwirira ntchito ndi RegExps mu Power Query, ndipo thandizo lazachuma la Microsoft ndi chithandizo chaukadaulo zimayankha funsoli molakwika. Komabe, pali njira yozungulira malire awa 🙂
Chinsinsi cha njira
Lingaliro lalikulu ndi losavuta kuchititsa manyazi.
Pamndandanda wazomangamanga a Power Query, pali ntchito Tsamba la webu. Kufotokozera kwa ntchitoyi patsamba lovomerezeka la Microsoft ndilachidule kwambiri:
Kutanthauziridwa, izi zitha kukhala: "Kubwezera zomwe zili mu chikalata cha HTML chomwe chasinthidwa kukhala zigawo zake, komanso chiwonetsero cha chikalata chonse ndi thupi lake ma tag atachotsedwa." Kufotokozera kotero, moona.
Kawirikawiri ntchitoyi imagwiritsidwa ntchito poitanitsa deta kuchokera pa intaneti ndipo imalowetsedwa m'malo, mwachitsanzo, tikasankha pa tabu. Deta lamulo Kuchokera pa intaneti (Deta - Kuchokera pa intaneti). Timapereka ntchitoyi tsamba lawebusayiti ngati mkangano, ndipo imatibwezera zomwe zili mkati mwamatebulo, atachotsa kale ma tag onse.
Chomwe chithandizo sichikunena ndikuti kuwonjezera pa chilankhulo cha HTML ntchito Tsamba la webu imathandizira zolemba za JavaScript, yomwe tsopano ikupezeka paliponse pamasamba pa intaneti. Ndipo JavaScript, nthawi zonse yakhala ikugwira ntchito ndi mawu okhazikika ndipo imakhala ndi ntchito za RegExps! Kotero kuti tigwiritse ntchito mawu okhazikika mu Power Query, tidzafunika kudyetsa Web.Page ntchito monga mtsutso ku pulogalamu yaing'ono ya JavaScript yomwe idzachita ntchito yonse ya Power Query.
Momwe zimawonekera mu JavaScript yoyera
Pali maphunziro ambiri atsatanetsatane akugwira ntchito ndi mawu okhazikika mu JavaScript pa intaneti (mwachitsanzo, imodzi, ziwiri).
Mwachidule komanso chosavuta, JavaScript code idzawoneka motere:
Pano:
- var str = 'Lipirani ngongole 123 ndi 789 za soseji'; - pangani kusintha str ndipo perekani malemba amene tikambirana.
- var pattern = /d+/gi; - pangani mawu okhazikika ndikuyika muzosintha chitsanzo.
Mawuwa amayamba ndi slash (/).
Mawu enieni apa, mwachitsanzo, ndi d+ imayimira mndandanda uliwonse wa manambala.
Kupyolera mu kagawo kakang'ono pambuyo pa mawuwo, pali zina zowonjezera zowonjezera (zosintha) - zikhoza kufotokozedwa mwanjira iliyonse:
- g - kumatanthauza kufufuza kwapadziko lonse, mwachitsanzo, mutapeza machesi, musasiye, koma pitirizani kufufuza mpaka kumapeto kwa malemba. Ngati chosinthirachi sichinakhazikitsidwe, ndiye kuti script yathu ingobweza machesi oyamba (123)
- i - fufuzani mosaganizira za makalata
- m - kusaka kwamizere yambiri (yogwiritsidwa ntchito pomwe mawu oyambira agawidwa m'mizere ingapo)
- var zotsatira = str.match(pattern).join(';'); - fufuzani m'mawu oyambira (str) ndi mawu operekedwa nthawi zonse (chitsanzo) ndikuyika zotsatira muzosintha chifukwa, kuwagwirizanitsa ndi semicolon pogwiritsa ntchito lamulo Funsani
- document.write(zotsatira); - onetsani zomwe zili muzotsatira zosinthika
Komanso zindikirani kuti zingwe (kupatula mawu okhazikika) mu JavaScript ndizotsekeredwa mu apostrophes, osati mawu monga momwe zilili mu Power Query kapena VBA.
Pazotulutsa, script iyi idzatipatsa chifukwa chake manambala onse omwe amapezeka muzolemba zoyambira:
123, 789
Maphunziro achidule a JavaScript atha, zikomo nonse. Ndikukhulupirira kuti mwapeza malingaliro 🙂
Zatsala kusamutsa zomangamanga ku Power Query.
Sakani ndi Kutulutsa Ntchito Yamalemba mwa Mafotokozedwe Okhazikika mu Power Query
Timachita izi:
1. Tsegulani Excel ndikupanga Power Query yatsopano yopanda kanthu pa tabu Deta - Pezani zambiri / Pangani pempho - Kuchokera kwina - Pempho lopanda kanthu (Deta - Pezani zambiri / Funso Latsopano - Kuchokera kwina - Funso lopanda kanthu). Ngati muli ndi mtundu wakale wa Excel 2010-2013 ndi Power Query yomwe mulibe, koma idayikidwa ngati chowonjezera chosiyana, ndiye kuti zonsezi zikhala pa tabu. Kufunsa MphamvuNdipo ayi Deta.
2. Pazenera lopanda kanthu la mkonzi wamafunso omwe amatsegula, pagawo lakumanja, lowetsani nthawi yomweyo dzina lantchito yathu yamtsogolo (mwachitsanzo, fxRegExpExtract)
3. Tiyeni tipite ku tabu Onani - Advanced Editor (Onani - Advanced Editor), timachotsa M-code yonse ya pempho lopanda kanthu ndikuyika nambala ya ntchito yathu yayikulu pamenepo:
Yang'anani manja anu:
Mu mzere woyamba, timanena kuti ntchito yathu idzakhala ndi mfundo zitatu za malemba: txt - mawu oyamba akuwunikidwa, regex - mawonekedwe anthawi zonse, delim - zilembo za delimiter zowonetsa zotsatira.
Kenako timatcha ntchito Tsamba la webu, kupanga JavaScript code yomwe yafotokozedwa pamwambapa mu mkangano wake. Timayika ndikuyika mikangano yathu yosinthika mu code.
Chidutswa:
[Deta]{0}[Ana]{0}[Ana]{1}[Mawu]{0}
… zimafunika kuti “tigwere” mu tebulo ndi zotsatira zomwe tikufuna. Mfundo ndi yakuti ntchito Tsamba la webu Zotsatira zake, zimapanga matebulo angapo okhala ndi zisa omwe amabwereza kapangidwe ka tsamba lawebusayiti. Popanda chidutswa cha M-code, ntchito yathu ingatulutse izi:
… ndipo tiyenera kudina mawuwo kangapo Table, motsatizana “kugwa” m’matebulo a ana okhala m’zipilala ana:
M'malo mwa mawu onsewa, nthawi yomweyo timawonetsa mu code ya ntchito yathu yomwe ili ndi tebulo ndi mzere (Malemba) timafunikira.
Apa, kwenikweni, ndi zinsinsi zonse. Zimatsalira kukanikiza batani chitsiriziro pawindo mkonzi wapamwamba, kumene tinayika kachidindo yathu, ndipo mukhoza kupita ku zokoma kwambiri - yesani ntchito yathu kuntchito.
Nazi zitsanzo zingapo za mbewu.
Chitsanzo 1. Kutenganso nambala ya akaunti ndi tsiku kuchokera ku malongosoledwe amalipiro
Tili ndi sitetimenti yaku banki yofotokoza (zolinga) zolipirira, pomwe muyenera kutulutsa manambala ndi masiku amalipilo olipidwa m'magawo osiyana:
Timayika tebulo mu Power Query m'njira yokhazikika Deta - Kuchokera pa Table/Range (Deta - Kuchokera ku Twokhoza/Rangelo).
Kenaka timawonjezera ndime yowerengeka ndi ntchito yathu kudzera Onjezani Column - Imbani Mwambo Ntchito (Onjezani Mgawo - Itanitsani Ntchito Yachizolowezi) ndipo lowetsani mfundo zake.
Monga mawu okhazikika (kukangana regex) template yomwe timagwiritsa ntchito:
(d{3,5}|d{2}.d{2}.d{4})
… kumasuliridwa m’chinenero cha anthu tanthawuzo:
manambala kuyambira manambala 3 mpaka 5 (nambala za akaunti)
or
zidutswa za mawonekedwe "2-bit number - point - 2-bit number - point - 4-bit number", ndiye kuti, madeti a fomu DD.MM.YYYY.
Monga delimiter character (mkangano delim) lowetsani semicolon.
Pambuyo pang'anani OK ntchito yathu yamatsenga imasanthula zonse zoyambira molingana ndi zomwe timalankhula nthawi zonse ndikupangira gawo lathu ndi manambala opezeka ndi masiku a ma invoice:
Imakhalabe kuilekanitsa ndi semicolon pogwiritsa ntchito lamulo Kunyumba - Gawani Mzere - Wolemba Delimiter (Kunyumba - Gawani ndime - Wolemba delimiter) ndipo timapeza zomwe tikufuna:
Kukongola!
Chitsanzo 2: Tulutsani maimelo pamawu
Tiyerekeze kuti tili ndi tebulo ili ngati deta yoyambira:
... kuchokera pomwe tikufunika kutulutsa ma imelo omwe amapezeka pamenepo (kuti amveke bwino, ndidawawunikira mofiira m'mawuwo).
Monga m'chitsanzo cham'mbuyomu, timayika tebulo mu Power Query m'njira yokhazikika kudzera Deta - Kuchokera pa Table/Range (Deta - Kuchokera ku Twokhoza/Rangelo).
Kenaka timawonjezera ndime yowerengeka ndi ntchito yathu kudzera Onjezani Column - Imbani Mwambo Ntchito (Onjezani Mgawo - Itanitsani Ntchito Yachizolowezi) ndipo lowetsani mfundo zake.
Kuyika ma adilesi a imelo ndi ntchito yovuta kwambiri ndipo pali mawu ambiri anthawi zonse amaloto oyipa kuti athetse. Ndinagwiritsa ntchito imodzi mwazosavuta - osati zabwino, koma zimagwira ntchito nthawi zambiri:
[w|.|-]*@w*.[w|.]*
Monga olekanitsa (delim) mutha kulowa mu semicolon ndi danga.
Dinani OK ndipo timapeza gawo lomwe lili ndi ma adilesi a imelo otengedwa kuchokera ku "phala" loyambirira:
Zamatsenga!
PS
Monga momwe mwambi umanenera: "Palibe chinthu chabwino choterocho chomwe sichingapangidwenso bwino." Power Query ndiyozizira yokha, ndipo ikaphatikizidwa ndi mawu okhazikika, imatipatsa mphamvu zopanda mphamvu komanso kusinthasintha pokonza deta iliyonse. Ndikukhulupirira kuti tsiku lina Microsoft idzawonjezera thandizo la RegExp mu Power Query ndi Power BI zosintha ndipo zovina zonse zomwe zili pamwambazi ndi maseche zidzakhala zakale. Chabwino, pakali pano, inde.
Ndikufunanso kuwonjezera kuti ndikosavuta kusewera ndi mawu okhazikika patsamba https://regexr.com/ - mumkonzi wapaintaneti. Kumeneko mu gawo Makhalidwe a Community Pali mitundu yambiri ya nyengo zokhazikika zomwe zakonzedwa nthawi zonse. Yesani - mphamvu zonse zamafotokozedwe okhazikika tsopano zili pantchito yanu mu Power Query!
- Kodi mawu okhazikika (RegExp) ndi momwe angawagwiritsire ntchito mu Excel
- Kusaka kwa mawu osamveka mu Power Query
- Kusonkhanitsa matebulo kuchokera kumafayilo osiyanasiyana pogwiritsa ntchito Power Query