[TIP] Pattern Match in Power Query

Doua add-onuri gratuite oferite de catre Microsoft ce completeaza functionalitatea Excel atat de bine incat incepend cu Excel 2013 Power Pivot vine preinstalat (doar trebuie activat) iar in Excel 2016 Power Query este inclus direct in interfata Excel (nu mai este add-on)
Catalin B.
Moderator
Moderator
Mesaje: 765
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

[TIP] Pattern Match in Power Query

Mesaj de Catalin B. » Sâm Apr 20, 2019 8:00 am

In Power Query nu exista deocamdata posibilitatea cautarii in text a unui tipar de date, asa cum se poate face in VBA.
(doar in Power BI se pot rula scripturi in limbajele integrate R si Python, care contin functii regex)
Totusi, se poate extrage un anumit tipar din text, cu putina imaginatie, importanta este construirea unui proces logic.
Pentru tiparul acesta de exemplu, "##-##-##", Interogarea rezultata este:

Cod: Selectaţi tot

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    List1 = List.Transform({0..9}, each Text.From(_)),
    ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),
    #"Added Custom" = Table.AddColumn(Source, "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";")    )
in
    #"Added Custom"
Design proces logic:
1. Solutia se bazeaza pe inlocuiea oricarui caracter numeric din textul initial cu 1 (sau orice alt text).
List1 = List.Transform({0..9}, each Text.From(_)), returneaza o lista de forma: ={'"0","1","2","3","4","5","6","7","8","9"}
ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}), returneaza o lista de liste, care va stabili caracterul initial si cel de inlocuire:
={{"0","1"},{"1","1"},{"2","1"},{"3","1"},{"4","1"},{"5","1"},{"6","1"},{"7","1"},{"8","1"},{"9","1"}}
Dupa inlocuire, textul initial: "16-24-61 E/2NENE TRACT 23-44-56 COMMERCIAL" devine: "11-11-11 E/1NENE TRACT 11-11-11 COMMERCIAL"
2. In urmatoarea etapa de procesare, se identifica pozitia acestui nou tipar 11-11-11 in textul initial: Text.PositionOf(...,Occurence.All) va returna o lista cu toate pozitiile acestui tipar: {0,23} (in baza zero)
3. Pentru fiecare element din lista obtinuta la punctul 2, extragem din textul initial cate 8 caractere (8 este lungimea tiparului cautat):
List.Transform({0,23},each Text.Range("16-24-61 E/2NENE TRACT 23-44-56 COMMERCIAL",_,8)). Rezultatul este o lista de 0 sau mai multe elemente, in functie de numarul de aparitii ale tiparului cautat in textul initial: ={"16-24-61","23-44-56"}
4. In etapa finala, este nevoie de combinat lista obtinuta intr-un singur text:
=Text.Combine({"16-24-61","23-44-56"}, ";"), rezulta: "16-24-61;23-44-56"

In fisierul anexat, exista si interogarea construita in etape separate, pentru o mai usoara intelegere a etapelor. (in interogarea de mai sus, toate etapele de procesare sunt combinate intr-un singur pas)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...

Catalin B.
Moderator
Moderator
Mesaje: 765
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: [TIP] Pattern Match in Power Query

Mesaj de Catalin B. » Joi Apr 25, 2019 8:22 pm

Update:
Am dat din intamplare zilele trecute peste o solutie foarte ingenioasa, nu stiam ca Power Query poate rula scripturi in paginile web.
https://www.thebiccountant.com/2018/04/ ... va-script/
Practic, se pot extrage tipare utilizand JavaScript regular expressions. Mai mult, asta inseamna ca se pot extrage date si din site-uri care se bazeaza pe JavaScript.
Functia urmatoare face mult mai usor ce am facut in mesajul anterior, pagina web consta de fapt doar din textul de analizat si este construita de script:
<code>
(Text as text, Pattern as text)=>
let
RunScript = Web.Page(
"<script>
document.write(
"""&Text&""".match(
new RegExp('"&Pattern&"','g')
).join(';')
);
</script>"
),
Matches = try RunScript{0}[Data]{0}[Children]{1}[Children]{0}[Text] otherwise null
in
Matches
</code>
Probleme să fie, că soluţii se găsesc...

Scrie răspuns

Înapoi la “Power Query & Power Pivot”