Înlocuire în bloc de text în Power Query cu funcția List.Acumulate

Cum să înlocuiți rapid și în bloc textul conform listei de referințe cu formule - l-am rezolvat deja. Acum să încercăm să o facem în Power Query.

Așa cum se întâmplă adesea efectua această sarcină este mult mai ușoară decât explicarea de ce funcționează, dar hai să încercăm să le facem pe amândouă 🙂

Deci, avem două tabele dinamice „inteligente” create din intervale obișnuite cu o comandă rapidă de la tastatură Ctrl+T sau echipa Acasă – Formatați ca tabel (Acasă — Format ca tabel):

Am sunat la prima masă Date, al doilea tabel - directorfolosind câmp Numele tabelului (numele tabelului) fila Constructor (Proiecta).

Sarcină: înlocuiți adresele din tabel Date toate aparițiile dintr-o coloană A găsi Manual la omologii lor corecti corespunzători din coloană Substitui. Restul textului din celule ar trebui să rămână neatins.

Pasul 1. Încărcați directorul în Power Query și transformați-l într-o listă

După ce setați celula activă în orice loc din tabelul de referință, faceți clic pe filă Date (Data)sau pe filă Interogare de alimentare (dacă aveți o versiune veche de Excel și ați instalat Power Query ca supliment într-o filă separată) pe butonul De la masă/gamă (De la masă/gamă).

Tabelul de referință va fi încărcat în editorul de interogări Power Query:

Pentru a nu interfera, un pas adăugat automat tip modificat (Tipul schimbat) în panoul din dreapta, pașii aplicați pot fi șterși în siguranță, lăsând doar pasul Sursă (Sursă):

Acum, pentru a efectua transformări și înlocuiri ulterioare, trebuie să transformăm acest tabel într-o listă (listă).

Digresiune lirică

• Tabel este o matrice bidimensională formată din mai multe rânduri și coloane.
• Înregistrare (Înregistrare) – matrice-șir unidimensional, format din mai multe câmpuri-elemente cu nume, de exemplu [Nume = „Masha”, Sex = „f”, Vârsta = 25]
  
• Listă – o matrice-coloană unidimensională, constând din mai multe elemente, de exemplu {1, 2, 3, 10, 42} or { "Credință speranță iubire" }
  

Pentru a ne rezolva problema, ne va interesa în primul rând tipul Listă.

Trucul aici este că elementele din listă din Power Query pot fi nu numai numere sau text banale, ci și alte liste sau înregistrări. Într-o listă (listă) atât de complicată, constând din înregistrări (înregistrări), trebuie să ne întoarcem directorul. În notația sintactică Power Query (intrări între paranteze pătrate, liste între paranteze), aceasta ar arăta astfel:

{

    [ Găsiți = „Sf. Petersburg”, Înlocuiește = „Sf. Petersburg”] ,

    [ Găsiți = „Sf. Petersburg”, Înlocuiește = „Sf. Petersburg”] ,

    [ Găsiți = „Petru”, Înlocuiți = „Sf. Petersburg”] ,

etc

}

O astfel de transformare este efectuată folosind o funcție specială a limbajului M încorporată în Power Query - Table.ToRecords. Pentru a o aplica direct în bara de formule, adăugați această funcție la codul de pas de acolo Sursă.

Era:

După:

După adăugarea funcției Table.ToRecords, aspectul tabelului nostru se va schimba – se va transforma într-o listă de înregistrări. Conținutul înregistrărilor individuale poate fi văzut în partea de jos a panoului de vizualizare făcând clic în fundalul celulei de lângă orice cuvânt Record (dar nu într-un singur cuvânt!)

În plus față de cele de mai sus, este logic să adăugați încă o lovitură – pentru a stoca în cache (buffer) lista noastră creată. Acest lucru va forța Power Query să încarce lista noastră de căutare o dată în memorie și să nu o recalculeze din nou când o vom accesa ulterior pentru a o înlocui. Pentru a face acest lucru, înfășurați formula noastră într-o altă funcție - Listă.Buffer:

O astfel de stocare în cache va da o creștere foarte vizibilă a vitezei (de câteva ori!), cu o cantitate mare de date inițiale care urmează să fie șters.

Aceasta completează pregătirea manualului.

Rămâne să dai clic pe Acasă – Închideți și încărcați – Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la..), selecteaza o optiune Doar creați o conexiune (Creați doar conexiune) și reveniți la Excel.

Pasul 2. Încărcarea tabelului de date

Totul este banal aici. Ca și înainte cu cartea de referință, ajungem în orice loc din tabel, facem clic pe filă Date buton Din masă/gamă și masa noastră Date intră în Power Query. Pas adăugat automat tip modificat (Tipul schimbat) mai poti elimina:

Nu sunt necesare acțiuni pregătitoare speciale pentru a fi făcute cu el și trecem la cel mai important lucru.

Pasul 3. Efectuați înlocuiri folosind funcția List.Acumulate

Să adăugăm o coloană calculată la tabelul nostru de date folosind comanda Adăugarea unei coloane – coloană personalizată (Adăugați coloană — coloană personalizată): și introduceți numele coloanei adăugate în fereastra care se deschide (de exemplu, adresa corectata) și funcția noastră magică Listează.Acumulează:

Rămâne să dai clic pe OK – și obținem o coloană cu înlocuirile făcute:

Rețineți că:

• Deoarece Power Query face distincție între majuscule și minuscule, nu a existat nicio înlocuire în penultima linie, deoarece în director avem „SPb”, nu „SPb”.
• Dacă există mai multe subșiruri de înlocuit simultan în datele sursă (de exemplu, în a 7-a linie trebuie să înlocuiți atât „S-Pb” cât și „Prospectus”), atunci acest lucru nu creează probleme (spre deosebire de înlocuirea cu formule din metoda anterioară).
• Dacă nu există nimic de înlocuit în textul sursă (linia a 9-a), atunci nu apar erori (spre deosebire, din nou, de la înlocuirea cu formule).

Viteza unei astfel de solicitări este foarte, foarte decentă. De exemplu, pentru un tabel de date inițiale cu o dimensiune de 5000 de rânduri, această interogare a fost actualizată în mai puțin de o secundă (fără a pune în tampon, apropo, aproximativ 3 secunde!)

Cum funcționează funcția List.Acumulate

În principiu, acesta ar putea fi sfârșitul (pentru mine să scriu, iar tu să citești) acest articol. Dacă doriți nu numai să puteți, ci și să înțelegeți cum funcționează „sub capotă”, atunci va trebui să vă scufundați puțin mai adânc în gaura iepurelui și să vă ocupați de funcția List.Acumulate, care a făcut toată înlocuirea în vrac. lucrează pentru noi.

Sintaxa pentru această funcție este:

=Lista.Acumulare(listă, sămânţă, acumulator)

Unde

• listă este lista ale cărei elemente iterăm. 
• sămânţă - stare initiala
• acumulator – o funcție care efectuează o anumită operație (matematică, text etc.) asupra următorului element al listei și acumulează rezultatul prelucrării într-o variabilă specială.
  

În general, sintaxa pentru scrierea funcțiilor în Power Query arată astfel:

(argument1, argument2, … argumentN) => unele acțiuni cu argumente

De exemplu, funcția de însumare poate fi reprezentată ca:

(a, b) => a + b

Pentru List.Accumulate , această funcție de acumulator are două argumente necesare (pot fi denumite orice, dar denumirile obișnuite sunt de stat и curent, ca și în ajutorul oficial pentru această funcție, unde:

• de stat – o variabilă în care se acumulează rezultatul (valoarea sa inițială este cea menționată mai sus sămânţă)
  
• curent – următoarea valoare repetată din listă listă
  

De exemplu, să aruncăm o privire la pașii logicii următoarei construcții:

=Lista.Acumulare({3, 2, 5}, 10, (stare, curent) => stare + curent)

Acesta este cel mai recent acumulat de stat valoarea este funcția noastră List.Acumulate și rezultă ca rezultat:

Dacă fantezi puțin, atunci folosind funcția List.Acumulate, poți simula, de exemplu, funcția Excel CONCATENATE (în Power Query, analogul său se numește Text.Combină) folosind expresia:

Sau chiar căutați valoarea maximă (imitație a funcției MAX din Excel, care în Power Query este numită Listă.Max):

Cu toate acestea, principala caracteristică a List.Accumulate este capacitatea de a procesa nu numai liste simple de text sau numerice ca argumente, ci și obiecte mai complexe – de exemplu, liste-din-liste sau liste-din-înregistrări (bună ziua, Director!)

Să ne uităm din nou la construcția care a efectuat înlocuirea în problema noastră:

List.Acumulate(director, [Abordare], (state,current) => Text.Replace(stare, curent[Găsi], curent[Înlocuire]) )

Ce se întâmplă cu adevărat aici?

1. Ca valoare inițială (sămânţă) luăm primul text stângaci din coloană [Abordare] masa noastra: 199034, Sankt Petersburg, str. Beringa, d. 1
2. Apoi List.Acumulate iterează peste elementele listei unul câte unul – Manual. Fiecare element al acestei liste este o înregistrare formată dintr-o pereche de câmpuri „Ce să găsiți – Cu ce ​​să înlocuiți” sau, cu alte cuvinte, următoarea linie din director.
3. Funcția acumulator pune într-o variabilă de stat valoarea inițială (prima adresă 199034, Sankt Petersburg, str. Beringa, d. 1) și îndeplinește pe acesta o funcție de acumulator – operația de înlocuire folosind funcția M standard Text.Înlocuiește (analog cu funcția SUBSTITUTE din Excel). Sintaxa sa este:

   Text.Replace (textul original, ce căutăm, cu ce înlocuim)

   si aici avem:

• de stat este adresa noastră murdară, care se află în de stat (ajunge acolo de la sămânţă)
  
• curent[Căutare] – valoarea câmpului A găsi de la următoarea intrare repetată a listei director, care se află în variabilă curent
• curent[Înlocuiește] – valoarea câmpului Substitui de la următoarea intrare repetată a listei directorîntins în curent

Astfel, pentru fiecare adresă se rulează de fiecare dată un ciclu complet de enumerare a tuturor liniilor din director, înlocuind textul din câmpul [Find] cu valoarea din câmpul [Replace].

Sper că ai prins ideea 🙂

• Înlocuiți în bloc textul dintr-o listă folosind formule
  
• Expresii regulate (RegExp) în Power Query