содржина
Ако барем малку сте запознаени со регуларните изрази, тогаш не треба да ги рекламирате. Ако не сте сосема во темата, тогаш регуларните изрази (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 за колбас'; – креирајте променлива ул и да му го доделиме изворниот текст што ќе го анализираме.
- var шема = /d+/gi; – креирајте редовен израз и ставете го во променлива шема.
Изразот започнува со коса црта (/).
Самиот израз овде, на пример, е d+ се залага за која било низа од цифри.
Преку фракцијата по изразот, има дополнителни параметри за пребарување (модификатори) - тие можат да се наведат по кој било редослед:
- g – значи глобално пребарување, односно по наоѓање на совпаѓање не треба да прекинете, туку да продолжите со пребарувањето до крајот на текстот. Ако овој модификатор не е поставен, тогаш нашата скрипта ќе го врати само првото појавување (123)
- i – пребарување без да се земат предвид буквите
- m – пребарување со повеќе линии (се користи кога изворниот текст е поделен на неколку линии)
- var result = str.match(шема).приклучи се(';'); – изврши пребарување во изворниот текст (ул) со дадениот правилен израз (шема) и ставете ги резултатите во променлива резултира, поврзувајќи ги со точка-запирка користејќи ја командата се приклучат
- документ.напише (резултат); – прикажување на содржината на променливата резултат
Исто така, имајте предвид дека текстуалните низи (со исклучок на редовните изрази) во JavaScript се затворени во апострофи, а не наводници како што се во Power Query или VBA.
На излезот, оваа скрипта ќе ни ги даде како резултат сите броеви пронајдени во изворниот текст:
123, 789
Краткиот курс за JavaScript заврши, ви благодариме на сите. Се надевам дека ја разбирате логиката 🙂
Останува да се пренесе оваа конструкција на Power Query.
Пребарување и екстракција на текстуална функција со регуларен израз во Power Query
Ние го правиме следново:
1. Отворете Excel и креирајте ново празно Power Query во табулаторот Податоци – Добијте податоци / Креирајте барање – Од други извори – Празно барање (Податоци - Добијте податоци / Ново барање - Од други извори - Празно барање). Ако имате стара верзија на Excel 2010-2013 и Power Query, немате вградено, но е инсталирано како посебен додаток, тогаш сето ова ќе биде на јазичето Барање за напојувањеИ не податоци.
2. Во празниот прозорец на уредувачот за прашања што се отвора, во десниот панел, веднаш внесете го името на нашата идна функција (на пример, fxRegExpExtract)
3. Ајде да одиме на јазичето Преглед – Напреден уредник (Преглед - Напреден уредник), го бришеме целиот М-код на празното барање и го залепуваме кодот на нашата суперфункција таму:
Внимавајте на рацете:
Во првата линија, велиме дека нашата функција ќе има три текстуални аргументи: txt – се анализира оригиналниот текст, регекс - шема на регуларен израз, делим — разграничувач за прикажување резултати.
Следно ја повикуваме функцијата Веб страна, формирајќи го JavaScript кодот опишан погоре во неговиот аргумент. Ние ги залепуваме и заменуваме нашите променливи аргументи во кодот.
Фрагмент:
[Податоци]{0}[Children]{0}[Children]{1}[Text]{0}
… е потребно за да „паднеме“ во табелата со резултатите што ни се потребни. Поентата е дека функцијата Веб страна како резултат на тоа, произведува неколку вгнездени табели кои ја повторуваат структурата на веб-страницата. Без ова парче М-код, нашата функција би го издала ова:
… и ќе треба да кликнеме на зборот неколку пати Табела, сукцесивно „паѓаат“ во табели вгнездени за деца во колони Деца:
Наместо целиот овој цитат, веднаш укажуваме во кодот на нашата функција која вгнездена табела и колона (Текст) ни треба.
Тука, всушност, се сите тајни. Останува да го притиснете копчето Заврши во прозорецот напреден уредник, каде што ја вметнавме нашата шифра и можете да продолжите до највкусното – пробајте ја нашата функција на работа.
Еве неколку примери за семиња.
Пример 1. Враќање на бројот и датумот на сметката од описот на плаќањето
Имаме извод од банка со опис (цел) на плаќањата, каде што треба да ги извлечете броевите и датумите на платените фактури во посебни колони:
Ја вчитуваме табелата во Power Query на стандарден начин Податоци – од табела/опсег (Податоци - од Тспособен/Рангел).
Потоа додаваме пресметана колона со нашата функција преку Додај колона - Повикај прилагодена функција (Додај колона - повикај ја прилагодената функција) и внесете ги неговите аргументи:
Како редовен израз (аргумент регекс) шаблон што го користиме:
(д{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 е кул сам по себе и кога се комбинира со регуларни изрази, ни дава целосно нереална моќ и флексибилност во обработката на какви било текстуални податоци. Се надевам дека Мајкрософт некогаш ќе додаде поддршка за RegExp во ажурирањата на Power Query и Power BI и сите горенаведени танци со тамбура ќе станат минато. Па, засега, да.
Исто така, сакам да додадам дека е погодно да се игра со регуларни изрази на страницата https://regexr.com/ – токму во онлајн уредникот. Таму во делот Модели на заедницата Има огромен број на готови редовни сезони за сите прилики. Експериментирајте – целата моќ на регуларните изрази сега ви е на услуга во Power Query!
- Што се правилни изрази (RegExp) и како да се користат во Excel
- Нејасно пребарување на текст во Power Query
- Составување табели од различни датотеки користејќи Power Query