змест
Калі вы хоць трохі знаёмыя з рэгулярнымі выразамі, то вам не трэба іх афішаваць. Калі вы не зусім у тэме, то рэгулярныя выразы (Regular Expressions = RegExp = “regexps” = “regulars”) — гэта мова, дзе з дапамогай спецыяльных сімвалаў і правілаў у тэксце шукаюцца патрэбныя падрадкі, яны здабываюцца або заменены іншым тэкстам. Гэта вельмі магутны і прыгожы інструмент, на парадак праўзыходны ўсе іншыя спосабы працы з тэкстам.
Я ўжо падрабязна і з мноствам прыкладаў з жыцця апісаў, як можна дадаць падтрымку рэгулярных выразаў у Excel з дапамогай простых макрасаў - калі вы не чыталі гэты артыкул, я настойліва рэкамендую вам прачытаць яго, перш чым працягнуць. Вы адкрыеце для сябе шмат новага, гарантую 🙂
Аднак застаецца адкрытым пытанне - як дадаць магчымасць выкарыстоўваць рэгулярныя выразы ў Power Query? Power Query, вядома, добры сам па сабе і можа многае рабіць з тэкстам (разразанне, склейванне, ачыстка і г.д.), але калі б вы змаглі скрыжаваць яго з сілай рэгулярных выразаў, гэта была б проста бомба.
На жаль, убудаваных функцый для працы з RegExps у Power Query няма, а афіцыйная даведка і тэхнічная падтрымка Microsoft адказваюць на гэтае пытанне адмоўна. Аднак ёсць спосаб абыйсці гэтае абмежаванне 🙂
Сутнасць метаду
Галоўная ідэя - проста зганьбіць.
У спісе ўбудаваных магчымасцяў Power Query ёсць функцыя Вэб-старонка. Апісанне гэтай функцыі на афіцыйным сайце даведкі Microsoft вельмі лаканічна:
У перакладзе гэта будзе: «Вяртае змесціва HTML-дакумента, разбітае на складовыя структуры, а таксама прадстаўленне поўнага дакумента і яго цела пасля выдалення тэгаў». Так сабе апісанне, шчыра кажучы.
Звычайна гэтая функцыя выкарыстоўваецца пры імпарце дадзеных з Інтэрнэту і аўтаматычна замяняецца, напрыклад, калі мы выбіраем на ўкладцы Дата Каманда З Інтэрнэту (Дадзеныя — з інтэрнэту). Мы даем функцыі вэб-старонку ў якасці аргументу, і яна вяртае нам яе змесціва ў выглядзе табліц, папярэдне ачысціўшы ўсе тэгі.
У даведцы НЕ сказана, што ў дадатак да мовы разметкі HTML функцыя Вэб-старонка падтрымлівае скрыпты JavaScript, які цяпер паўсюдна сустракаецца на вэб-сайтах у Інтэрнэце. А JavaScript, у сваю чаргу, заўсёды ўмеў працаваць з рэгулярнымі выразамі і мае ўбудаваныя функцыі для RegExps! Такім чынам, каб рэалізаваць рэгулярныя выразы ў Power Query, нам трэба будзе перадаць функцыі Web.Page у якасці аргумента невялікай праграме JavaScript, якая будзе выконваць усю працу для Power Query.
Як гэта выглядае ў чыстым JavaScript
У інтэрнэце ёсць шмат падрабязных урокаў па працы з рэгулярнымі выразамі ў JavaScript (напрыклад, адзін, два).
Карацей і спрошчана, код JavaScript будзе выглядаць так:
Тут:
- var str = 'Аплаціць рахункі 123 і 789 за каўбасу'; – стварыць зменную str і прызначыць яму зыходны тэкст, які мы будзем аналізаваць.
- вар шаблон = /d+/gi; – стварыць рэгулярны выраз і змясціць яго ў зменную шаблон.
Выраз пачынаецца з касой рысы (/).
Сам выраз тут, напрыклад, ёсць d+ абазначае любую паслядоўнасць лічбаў.
Праз дроб пасля выразу ідуць дадатковыя параметры пошуку (мадыфікатары) – іх можна ўказваць у любым парадку:
- g – азначае глабальны пошук, г.зн. знайшоўшы супадзенне, трэба не спыняцца, а працягваць пошук да канца тэксту. Калі гэты мадыфікатар не ўсталяваны, наш скрыпт верне толькі першае супадзенне (123)
- i – пошук без уліку рэгістра літар
- m – шматрадковы пошук (выкарыстоўваецца, калі зыходны тэкст падзелены на некалькі радкоў)
- var result = str.match(pattern).join(';'); – выканаць пошук у зыходным тэксце (str) па зададзеным рэгулярным выразе (шаблон) і змясціць вынікі ў зменную вынік, аб'яднаўшы іх кропкай з коскі з дапамогай каманды далучыцца
- document.write(вынік); – адлюстраванне змесціва выніковай зменнай
Таксама звярніце ўвагу, што тэкставыя радкі (за выключэннем рэгулярных выразаў) у JavaScript заключаны ў апострафы, а не ў двукоссе, як у Power Query або VBA.
На выхадзе гэты скрыпт выдасць нам у выніку ўсе лічбы, знойдзеныя ў зыходным тэксце:
123, 789
Кароткі курс JavaScript скончаны, дзякуй усім. Спадзяюся, вы разумееце логіку 🙂
Засталося перанесці гэтую канструкцыю ў Power Query.
Функцыя пошуку і вымання тэксту з дапамогай рэгулярнага выразу ў Power Query
Мы робім наступнае:
1. Адкрыйце Excel і стварыце новы пусты Power Query на ўкладцы Дадзеныя – Атрымаць даныя / Стварыць запыт – З іншых крыніц – Пусты запыт (Дадзеныя — Атрымаць даныя / Новы запыт — З іншых крыніц — Пусты запыт). Калі ў вас старая версія Excel 2010-2013 і Power Query ў вас не ўбудаваны, а ўсталяваны як асобная надбудова, то ўсё гэта будзе на ўкладцы Запыт на харчаваннеІ не Дата.
2. У якое адкрылася пустым акне рэдактара запытаў, на правай панэлі адразу ўводзім назву нашай будучай функцыі (напрыклад, fxRegExpExtract)
3. Пяройдзем да ўкладкі Выгляд - пашыраны рэдактар (Прагляд — пашыраны рэдактар), мы сціраем увесь М-код пустога запыту і ўстаўляем туды код нашай суперфункцыі:
Сачыце за рукамі:
У першым радку мы кажам, што наша функцыя будзе мець тры тэкставыя аргументы: TXT – арыгінальны тэкст, які аналізуецца, regex - шаблон рэгулярнага выразу, размежаваць — знак-падзельнік для адлюстравання вынікаў.
Далей мы выклікаем функцыю Вэб-старонка, утвараючы код JavaScript, апісаны вышэй у сваім аргументе. Мы ўстаўляем і падстаўляем нашы зменныя аргументы ў код.
Фрагмент:
[Даныя]{0}[Дзеці]{0}[Дзеці]{1}[Тэкст]{0}
… патрэбна, каб «трапіць» у табліцу з патрэбнымі вынікамі. Справа ў тым, што функцыя Вэб-старонка у выніку ствараецца некалькі ўкладзеных табліц, якія паўтараюць структуру вэб-старонкі. Без гэтай часткі M-кода наша функцыя выдавала б наступнае:
... і нам прыйшлося б некалькі разоў пстрыкнуць слова табліца, паслядоўна «правальваючыся» ў даччыныя табліцы, укладзеныя ў слупкі дзеці:
Замест усёй гэтай цытаты мы адразу паказваем у кодзе нашай функцыі, якая ўкладзеная табліца і слупок (Тэкст) нам трэба.
Вось, уласна, і ўсе сакрэты. Засталося націснуць кнопку Заканчэнне у акне пашыраны рэдактар, куды мы ўставілі наш код, і можна прыступаць да самага смачнага - паспрабаваць нашу функцыю ў працы.
Вось некалькі прыкладаў насення.
Прыклад 1. Атрыманне нумара рахунку і даты з апісання плацяжу
У нас ёсць выпіска з банкаўскага рахунку з апісаннем (прызначэннем) плацяжоў, дзе ў асобныя графы трэба вывесці нумары і даты аплачаных рахункаў:
Загружаем табліцу ў Power Query стандартным спосабам Дадзеныя – з табліцы/дыяпазону (Дадзеныя — ад Тздольны/Ранёл).
Затым мы дадаем вылічальны слупок з нашай функцыяй праз Дадаць слупок – выклікаць карыстальніцкую функцыю (Дадаць слупок — выклікаць карыстальніцкую функцыю) і ўвядзіце яго аргументы:
Як рэгулярны выраз (аргумент regex) шаблон, які мы выкарыстоўваем:
(г{3,5}|d{2}.d{2}.d{4})
… у перакладзе на чалавечую мову азначае:
лічбы ад 3 да 5 лічбаў (нумары рахункаў)
or
фрагменты выгляду “2-разрадны лік – кропка – 2-разрадны лік – кропка – 4-разрадны лік”, гэта значыць даты выгляду ДД.ММ.ГГГГ.
У якасці раздзяляльніка (арг размежаваць) увядзіце кропку з коскай.
Пасля націску на кнопку OK наша магічная функцыя аналізуе ўсе зыходныя дадзеныя ў адпаведнасці з нашым рэгулярным выразам і фармуе для нас слупок са знойдзенымі нумарамі і датамі рахункаў:
Засталося аддзяліць яго кропкай з коскі з дапамогай каманды Дадому — Раздзяліць слупок — Раздзяляльнікам (Галоўная старонка — Раздзяліць слупок — Па раздзяляльніку) і мы атрымліваем тое, што хацелі:
Прыгажосць!
Прыклад 2: Выманне адрасоў электроннай пошты з тэксту
Выкажам здагадку, у нас ёсць наступная табліца ў якасці зыходных дадзеных:
… адкуль нам трэба атрымаць знойдзеныя там адрасы электроннай пошты (для нагляднасці я вылучыў іх у тэксце чырвоным).
Як і ў папярэднім прыкладзе, мы загружаем табліцу ў Power Query стандартным спосабам праз Дадзеныя – з табліцы/дыяпазону (Дадзеныя — ад Тздольны/Ранёл).
Затым мы дадаем вылічальны слупок з нашай функцыяй праз Дадаць слупок – выклікаць карыстальніцкую функцыю (Дадаць слупок — выклікаць карыстальніцкую функцыю) і ўвядзіце яго аргументы:
Разбор адрасоў электроннай пошты - больш складаная задача, і для яе вырашэння існуе куча рэгулярных выразаў рознай ступені кашмару. Я выкарыстаў адзін з простых варыянтаў - не ідэальны, але ў большасці выпадкаў цалкам працуе:
[w|.|-]*@w*.[w|.]*
У якасці падзельніка (размежаваць) вы можаце ўвесці кропку з коскай і прабел.
Націсніце на OK і атрымліваем калонку з адрасамі электроннай пошты, вынятыя з арыгінальнага тэксту «каша»:
Магія!
PS
Як гаворыцца: «Няма такога добрага, каб нельга было зрабіць яшчэ лепш». Power Query сам па сабе круты, а ў спалучэнні з рэгулярнымі выразамі ён дае нам зусім нерэальную магутнасць і гнуткасць у апрацоўцы любых тэкставых даных. Спадзяюся, Microsoft калі-небудзь дадасць падтрымку RegExp у абнаўленнях Power Query і Power BI, і ўсе вышэйпералічаныя танцы з бубнам застануцца ў мінулым. Ну, пакуль што так.
Таксама хачу дадаць, што з рэгулярнымі выразамі зручна гуляць на сайце https://regexr.com/ – прама ў онлайн-рэдактары. Там у секцыі Шаблоны супольнасці Існуе велізарная колькасць гатовых звычайных сезонаў на ўсе выпадкі жыцця. Эксперыментуйце - уся моц рэгулярных выразаў цяпер да вашых паслуг у Power Query!
- Што такое рэгулярныя выразы (RegExp) і як іх выкарыстоўваць у Excel
- Пошук недакладнага тэксту ў Power Query
- Зборка табліц з розных файлаў з дапамогай Power Query