Innehåll
Om du åtminstone är lite bekant med reguljära uttryck behöver du inte marknadsföra dem. Om du inte är riktigt inne på ämnet så är reguljära uttryck (Regular Expressions = RegExp = “regexps” = “regulars”) ett språk där man med hjälp av specialtecken och regler söker efter nödvändiga delsträngar i texten, de extraheras eller ersättas med annan text. Detta är ett mycket kraftfullt och vackert verktyg, en storleksordning överlägsen alla andra sätt att arbeta med text.
Jag har redan beskrivit i detalj och med ett gäng exempel från livet hur du kan lägga till stöd för reguljära uttryck i Excel med hjälp av enkla makron – om du inte har läst den här artikeln rekommenderar jag starkt att du läser den innan du fortsätter. Du kommer att upptäcka mycket nytt, jag garanterar 🙂
Frågan är dock fortfarande öppen – hur lägger man till möjligheten att använda reguljära uttryck i Power Query? Power Query är förstås bra på egen hand och kan göra mycket med text (klippa, limma, rengöra etc.), men om du kunde korsa det med kraften i reguljära uttryck skulle det bara vara en bomb.
Tyvärr finns det inga inbyggda funktioner för att arbeta med RegExps i Power Query, och officiell Microsoft-hjälp och teknisk support svarar nekande på denna fråga. Det finns dock en väg runt denna begränsning 🙂
Kärnan i metoden
Huvudidén är enkel att vanära.
I listan över inbyggda Power Query-funktioner finns en funktion Webbsida. Beskrivningen av den här funktionen på Microsofts officiella hjälpwebbplats är extremt kortfattad:
Översatt skulle detta vara: "Returnerar innehållet i HTML-dokumentet uppdelat i dess komponentstrukturer, samt en representation av hela dokumentet och dess kropp efter att taggarna har tagits bort." So-so beskrivning, ärligt talat.
Vanligtvis används denna funktion vid import av data från webben och ersätts automatiskt, till exempel när vi väljer på fliken Data Kommando Från internet (Data — från webben). Vi ger funktionen en webbsida som ett argument, och den returnerar dess innehåll till oss i form av tabeller, efter att tidigare ha rensat alla taggar.
Vad hjälpen INTE säger är att förutom HTML-kodspråket fungera Webbsida stöder JavaScript-skript, som nu finns överallt på webbplatser på Internet. Och JavaScript har i sin tur alltid kunnat fungera med reguljära uttryck och har inbyggda funktioner för RegExps! Så för att implementera reguljära uttryck i Power Query måste vi mata webbsidans funktioner som ett argument till ett litet JavaScript-program som kommer att göra allt arbete för Power Query.
Hur det ser ut i ren JavaScript
Det finns många detaljerade tutorials om att arbeta med reguljära uttryck i JavaScript på Internet (till exempel en, två).
Kort och förenklat kommer JavaScript-koden att se ut så här:
Här:
- var str = 'Betala räkningar 123 och 789 för korv'; – skapa en variabel str och tilldela den källtexten som vi ska analysera.
- var mönster = /d+/gi; – skapa ett reguljärt uttryck och lägg det i en variabel mönster.
Uttrycket börjar med ett snedstreck (/).
Själva uttrycket här är till exempel d+ står för vilken sekvens av siffror som helst.
Genom bråket efter uttrycket finns det ytterligare sökparametrar (modifierare) – de kan anges i valfri ordning:
- g – betyder global sökning, dvs efter att ha hittat en matchning ska du inte sluta, utan fortsätta sökningen till slutet av texten. Om denna modifierare inte är inställd kommer vårt skript endast att returnera den första matchningen (123)
- i – sök utan hänsyn till versaler
- m – flerradssökning (används när källtexten är uppdelad i flera rader)
- var resultat = str.match(pattern).join(';'); – gör en sökning i källtexten (str) av det givna reguljära uttrycket (mönster) och placera resultaten i en variabel resultera, sammanfoga dem med ett semikolon med kommandot delta
- document.write(result); – visa innehållet i resultatvariabeln
Observera också att textsträngar (exklusive reguljära uttryck) i JavaScript är inneslutna i apostrof, inte citattecken som de är i Power Query eller VBA.
Vid utgången kommer detta skript att ge oss som ett resultat alla siffror som finns i källtexten:
123, 789
JavaScript-kortkursen är över, tack alla. Hoppas du förstår logiken 🙂
Det återstår att överföra denna konstruktion till Power Query.
Sök och extrahera textfunktion med reguljära uttryck i Power Query
Vi gör följande:
1. Öppna Excel och skapa en ny tom Power Query på fliken Data – Hämta data / Skapa förfrågan – Från andra källor – Tom begäran (Data — Hämta data / Ny fråga — Från andra källor — Tom fråga). Om du har en gammal version av Excel 2010-2013 och Power Query som du inte har inbyggd, utan installerades som ett separat tillägg, så kommer allt detta att finnas på fliken StrömfrågaOch inte Data.
2. I det tomma fönstret i frågeredigeraren som öppnas, i den högra panelen, ange omedelbart namnet på vår framtida funktion (till exempel, fxRegExpExtract)
3. Låt oss gå till fliken Visa – Avancerad redigerare (Visa — Avancerad redigerare), raderar vi hela M-koden för den tomma begäran och klistrar in koden för vår superfunktion där:
Se upp dina händer:
På första raden säger vi att vår funktion kommer att ha tre textargument: Text – den ursprungliga texten som analyseras, regex – regelbundet uttrycksmönster, avgränsa — avgränsningstecken för att visa resultat.
Därefter kallar vi funktionen Webbsida, som bildar JavaScript-koden som beskrivs ovan i dess argument. Vi klistrar in och ersätter våra variabelargument i koden.
Fragment:
[Data]{0}[Barn]{0}[Barn]{1}[Text]{0}
… behövs för att "falla igenom" i tabellen med de resultat vi behöver. Poängen är att funktionen Webbsida som ett resultat, producerar den flera kapslade tabeller som upprepar strukturen på en webbsida. Utan denna del av M-kod skulle vår funktion mata ut detta:
… och vi skulle behöva klicka på ordet flera gånger Bord, successivt "faller igenom" i underordnade kapslade tabeller i kolumner Barn:
Istället för allt detta citat anger vi omedelbart i koden för vår funktion vilken kapslad tabell och kolumn (text) vi behöver.
Här finns faktiskt alla hemligheter. Det återstår att trycka på knappen Finish i fönstret avancerad redaktör, där vi infogade vår kod, och du kan gå vidare till det läckraste – prova vår funktion på jobbet.
Här är ett par fröexempel.
Exempel 1. Hämta kontonummer och datum från betalningsbeskrivningen
Vi har ett kontoutdrag med en beskrivning (ändamål) av betalningar, där du måste dra ut siffror och datum för betalda fakturor i separata kolumner:
Vi läser in tabellen i Power Query på vanligt sätt Data – Från tabell/intervall (Data - från Tkunna/Rängel).
Sedan lägger vi till en beräknad kolumn med vår funktion via Lägg till kolumn – Ring anpassad funktion (Lägg till kolumn — Anropa anpassad funktion) och skriv in dess argument:
Som ett reguljärt uttryck (argument regex) mall vi använder:
(d{3,5}|d{2}.d{2}.d{4})
… översatt till mänskligt språk betyder:
nummer från 3 till 5 siffror (kontonummer)
or
fragment av formen "2-bitars nummer - punkt - 2-bitars tal - punkt - 4-bitars tal", det vill säga datum av formen DD.MM.ÅÅÅÅ.
Som avgränsare (argument avgränsa) ange ett semikolon.
Efter att ha klickat på OK vår magiska funktion analyserar alla initiala data enligt vårt reguljära uttryck och bildar en kolumn för oss med hittade nummer och datum för fakturorna:
Det återstår att separera det med semikolon med kommandot Hem — Delad kolumn — Med avgränsare (Hem — Dela kolumn — Med avgränsare) och vi får vad vi ville ha:
Skönhet!
Exempel 2: Extrahera e-postadresser från text
Anta att vi har följande tabell som initialdata:
… varifrån vi måste ta fram e-postadresserna som finns där (för tydlighetens skull har jag markerat dem i rött i texten).
Som i föregående exempel laddar vi in tabellen i Power Query på vanligt sätt via Data – Från tabell/intervall (Data - från Tkunna/Rängel).
Sedan lägger vi till en beräknad kolumn med vår funktion via Lägg till kolumn – Ring anpassad funktion (Lägg till kolumn — Anropa anpassad funktion) och skriv in dess argument:
Att analysera e-postadresser är en svårare uppgift och det finns ett gäng regelbundna uttryck av olika grad av mardröm för att lösa det. Jag använde ett av de enkla alternativen - inte idealiskt, men ganska fungerande i de flesta fall:
[w|.|-]*@w*.[w|.]*
Som separator (avgränsa) kan du ange ett semikolon och ett mellanslag.
Klicka på OK och vi får en kolumn med e-postadresser extraherade från originaltexten "gröt":
Magi!
PS
Som ordspråket säger: "Det finns inget sådant bra som inte kunde göras ännu bättre." Power Query är coolt i sig, och i kombination med reguljära uttryck ger det oss helt orealistisk kraft och flexibilitet vid bearbetning av all textdata. Jag hoppas att Microsoft någon gång kommer att lägga till RegExp-stöd i Power Query och Power BI-uppdateringar och att alla ovanstående danser med en tamburin kommer att bli ett minne blott. Nåväl, för nu, ja.
Jag vill också tillägga att det är bekvämt att spela med reguljära uttryck på sajten https://regexr.com/ – direkt i onlineredigeraren. Där i avsnittet Gemenskapsmönster Det finns ett enormt antal färdiga ordinarie säsonger för alla tillfällen. Experimentera – all kraft med reguljära uttryck finns nu till din tjänst i Power Query!
- Vad är reguljära uttryck (RegExp) och hur man använder dem i Excel
- Luddrig textsökning i Power Query
- Sammansättning av tabeller från olika filer med Power Query