Grupna zamjena teksta u Power Queryju s funkcijom List.Accumulate

Kako brzo i masovno zamijeniti tekst prema referenc listi formulama – to smo već sredili. Sada pokušajmo to učiniti u Power Queryju.

Kao što se često dešava Izvoditi ovaj zadatak je mnogo lakši od objašnjavanja zašto radi, ali hajde da probamo oboje 🙂

Dakle, imamo dvije “pametne” dinamičke tabele kreirane iz običnih raspona sa prečicom na tastaturi Ctrl+T ili tim Početna – Formatirajte kao tabelu (Početna — Format kao tabela):

Pozvao sam prvi sto podaci, drugi sto – imenikkoristeći polje Naziv tabele (ime tabele) tabulator konstruktor (Dizajn).

Zadatak: zamijeniti adrese u tabeli podaci sva pojavljivanja iz kolone Naći priručnik njihovim odgovarajućim ispravnim kolegama iz kolone Zamjenik. Ostatak teksta u ćelijama treba ostati netaknut.

Korak 1. Učitajte direktorij u Power Query i pretvorite ga u listu

Nakon što postavite aktivnu ćeliju na bilo koje mjesto u referentnoj tablici, kliknite na karticu podaci (Datum)ili na kartici power query (ako imate staru verziju Excel-a i instalirali ste Power Query kao dodatak na zasebnoj kartici) na dugme Iz tabele/opseg (Iz tabele/dometa).

Referentna tabela će biti učitana u Power Query uređivač upita:

Kako se ne bi miješali, automatski dodat korak modificirani tip (promijenjena vrsta) u desnom panelu, primijenjeni koraci se mogu bezbedno izbrisati, ostavljajući samo korak izvor (Izvor):

Sada, da izvršimo dalje transformacije i zamjene, trebamo ovu tabelu pretvoriti u listu (listu).

Lirska digresija

• sto je dvodimenzionalni niz koji se sastoji od nekoliko redova i kolona.
• Snimi (Snimanje) – jednodimenzionalni niz-niz, koji se sastoji od nekoliko polja-elemenata sa imenima, npr. [Ime = “Maša”, Spol = “ž”, Starost = 25)
  
• Popis – jednodimenzionalni niz-kolona, ​​koji se sastoji od nekoliko elemenata, na primjer {1, 2, 3, 10, 42} {XNUMX} {XNUMX} or { "Vjera nada ljubav" }
  

Da bismo riješili naš problem, prvenstveno će nas zanimati tip Popis.

Trik je u tome što stavke liste u Power Queryju mogu biti ne samo banalni brojevi ili tekst, već i druge liste ili zapisi. Upravo u tako škakljivoj listi (listi), koja se sastoji od zapisa (zapisa) trebamo okrenuti naš imenik. U Power Query sintaksičkoj notaciji (unosi u uglastim zagradama, liste u vitičastim zagradama) ovo bi izgledalo ovako:

{

    [ Find = “Sv. Petersburg”, Zamijeni = “Sv. Petersburg” ] ,

    [ Find = “Sv. Petersburg”, Zamijeni = “Sv. Petersburg” ] ,

    [ Pronađi = “Petar”, Zamijeni = “Sv. Petersburg” ] ,

itd

}

Takva transformacija se izvodi pomoću posebne funkcije M jezika ugrađene u Power Query – Table.ToRecords. Da biste je primijenili direktno u traku formule, dodajte ovu funkciju tamošnjem kodu koraka izvor.

Bilo je:

Posle:

Nakon dodavanja funkcije Table.ToRecords, izgled naše tabele će se promeniti – pretvoriće se u listu zapisa. Sadržaj pojedinačnih zapisa može se vidjeti na dnu okna za prikaz klikom na pozadinu ćelije pored bilo koje riječi rekord (ali ni jednom rečju!)

Pored navedenog, ima smisla dodati još jedan potez – za keširanje (baferovanje) naše kreirane liste. Ovo će primorati Power Query da učita našu listu pretraživanja jednom u memoriju i da je neće ponovo izračunati kada joj kasnije pristupimo da bismo je zamijenili. Da biste to učinili, umotajte našu formulu u drugu funkciju - List.Buffer:

Takvo keširanje će dati vrlo primjetno povećanje brzine (za nekoliko puta!) s velikom količinom početnih podataka koje treba obrisati.

Ovim je završena priprema priručnika.

Ostaje kliknuti na Početna – Zatvori i učitaj – Zatvori i učitaj na… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u..), odaberite opciju Samo stvorite vezu (Samo kreirajte vezu) i vratite se u Excel.

Korak 2. Učitavanje tabele podataka

Ovdje je sve otrcano. Kao i ranije sa priručnikom, dolazimo do bilo kojeg mjesta u tabeli, kliknemo na karticu podaci dugme Iz tabele/opseg i naš sto podaci ulazi u Power Query. Automatski dodan korak modificirani tip (promijenjena vrsta) također možete ukloniti:

Sa njim nisu potrebne nikakve posebne pripremne radnje i prelazimo na ono najvažnije.

Korak 3. Izvršite zamjene pomoću funkcije List.Accumulate

Dodajmo izračunatu kolonu u našu tablicu podataka pomoću naredbe Dodavanje kolone – Prilagođena kolona (Dodaj kolonu — Prilagođena kolona): i u prozor koji se otvori unesite naziv dodane kolone (npr. ispravljena adresa) i našu magičnu funkciju List.Akumuliraj:

Ostaje kliknuti na OK – i dobijamo stupac sa izvršenim zamjenama:

Zapiši to:

• Pošto je Power Query osjetljiv na velika i mala slova, nije bilo zamjene u pretposljednjem redu, jer u direktoriju imamo “SPb”, a ne “SPb”.
• Ako postoji nekoliko podnizova koje treba zamijeniti odjednom u izvornim podacima (na primjer, u 7. redu morate zamijeniti i "S-Pb" i "Prospectus"), onda to ne stvara nikakve probleme (za razliku od zamjene formulama iz prethodni metod).
• Ako u izvornom tekstu nema ništa za zamjenu (9. red), onda nema grešaka (za razliku od, opet, zamjene formulama).

Brzina takvog zahtjeva je vrlo, vrlo pristojna. Na primjer, za tabelu početnih podataka veličine 5000 redova, ovaj upit je ažuriran za manje od jedne sekunde (bez baferovanja, inače, oko 3 sekunde!)

Kako funkcionira funkcija List.Accumulate

U principu, ovo bi mogao biti kraj (da ja pišem, a da vi čitate) ovaj članak. Ako želite ne samo moći, već i razumjeti kako to funkcionira “ispod haube”, onda ćete morati zaroniti malo dublje u zečju rupu i pozabaviti se funkcijom List.Accumulate, koja je izvršila svu masovnu zamjenu radi za nas.

Sintaksa za ovu funkciju je:

=Lista.Akumuliraj(lista, sjeme, Akumulator)

gdje

• lista je lista čije elemente ponavljamo. 
• sjeme – početno stanje
• Akumulator – funkcija koja izvodi neku operaciju (matematičku, tekstualnu, itd.) na sljedećem elementu liste i akumulira rezultat obrade u posebnoj varijabli.
  

Općenito, sintaksa za pisanje funkcija u Power Queryju izgleda ovako:

(argument1, argument2, … argumentN) => neke akcije sa argumentima

Na primjer, funkcija sumiranja bi se mogla predstaviti kao:

(a, b) => a + b

Za List.Accumulate, ova funkcija akumulatora ima dva potrebna argumenta (mogu se imenovati bilo čim, ali uobičajena imena su stanje и struja, kao u službenoj pomoći za ovu funkciju, gdje:

• stanje – varijabla u kojoj se akumulira rezultat (njena početna vrijednost je gore navedena sjeme)
  
• struja – sljedeća iterirana vrijednost sa liste lista
  

Na primjer, pogledajmo korake logike sljedeće konstrukcije:

=Lista.Akumuliraj({3, 2, 5}, 10, (stanje, trenutna) => stanje + struja)

Ovo je posljednje akumulirano stanje vrijednost je naša funkcija List.Accumulate i kao rezultat daje:

Ako malo maštate, onda pomoću funkcije List.Accumulate možete simulirati, na primjer, Excel funkciju CONCATENATE (u Power Queryju njen analog se zove Text.Combine) koristeći izraz:

Ili čak potražite maksimalnu vrijednost (imitacija Excelove MAX funkcije, koja se u Power Queryju zove List.Max):

Međutim, glavna karakteristika List.Accumulate je mogućnost obrade ne samo jednostavnih tekstualnih ili numeričkih lista kao argumenata, već i složenijih objekata – na primjer, liste-sa-liste ili liste-iz-zapisa (zdravo, Imenik!)

Pogledajmo ponovo konstrukciju koja je izvršila zamjenu u našem problemu:

List.Akumuliraj(imenik, [Adresa], (stanje, trenutno) => Text.Replace(stanje, trenutno[Pronađi], trenutno[Zamijeni]) )

Šta se stvarno dešava ovde?

1. Kao početna vrijednost (sjeme) uzimamo prvi nespretni tekst iz kolone [Adresa] naš sto: 199034, Sankt Peterburg, ul. Beringa, d. 1
2. Zatim List.Accumulate ponavlja elemente liste jedan po jedan – priručnik. Svaki element ove liste je zapis koji se sastoji od para polja “Šta pronaći – čime zamijeniti” ili, drugim riječima, sljedeći red u direktoriju.
3. Funkcija akumulatora stavlja u varijablu stanje početna vrijednost (prva adresa 199034, Sankt Peterburg, ul. Beringa, d. 1) i na njemu obavlja funkciju akumulatora – operaciju zamjene pomoću standardne M-funkcije Text.Replace (analogno Excelovoj funkciji SUBSTITUTE). Njegova sintaksa je:

   Text.Replace( originalni tekst, ono što tražimo, čime zamjenjujemo)

   i evo nas:

• stanje je naša prljava adresa, koja leži u stanje (doći tamo od sjeme)
  
• trenutno[Pretraga] – vrijednost polja Naći od sljedećeg ponavljanog unosa liste imenik, koji se nalazi u varijabli struja
• trenutni[Zamijeni] – vrijednost polja Zamjenik od sljedećeg ponavljanog unosa liste imenikleži u struja

Tako se za svaku adresu svaki put izvodi puni ciklus nabrajanja svih redova u direktoriju, zamjenjujući tekst iz polja [Pronađi] vrijednošću iz polja [Zamijeni].

Nadam se da ste dobili ideju 🙂

• Grupno zamijenite tekst na listi pomoću formula
  
• Regularni izrazi (RegExp) u Power Queryju