Skupna zamjena teksta u Power Queryju s funkcijom List.Accumulate

Kako brzo i skupno zamijeniti tekst prema popisu referenci s formulama - to smo već riješili. Pokušajmo to učiniti u Power Queryju.

Kao što se često događa izvoditi ovaj zadatak je puno lakši od objašnjavanja zašto radi, ali pokušajmo oboje 🙂

Dakle, imamo dvije "pametne" dinamičke tablice kreirane od običnih raspona tipkovničkim prečacem Ctrl+T ili tim Početna – Format kao tablica (Početna — Formatiraj kao tablicu):

Pozvao sam prvi stol Datum, druga tablica – Imenikpomoću polja Naziv tablice (Naziv tablice) kartica Konstruktor (Oblikovati).

Zadatak: zamijenite adrese u tablici Datum sva pojavljivanja iz stupca Pronaći Priručnik njihovim odgovarajućim ispravnim pandanima iz stupca Zamjena. Ostatak teksta u ćelijama trebao bi ostati netaknut.

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

Nakon što postavite aktivnu ćeliju na bilo koje mjesto u referentnoj tablici, kliknite karticu Datum (Datum)ili na kartici Upit snage (ako imate staru verziju Excela i instalirali ste Power Query kao dodatak na posebnoj kartici) na gumbu Iz tablice/raspona (Iz tablice/raspona).

Referentna tablica će se učitati u uređivač upita Power Query:

Da ne smeta, automatski dodan korak modificirani tip (Promijenjena vrsta) na desnoj ploči, primijenjene korake moguće je sigurno izbrisati, ostavljajući samo korak izvor (Izvor):

Sada, za izvođenje daljnjih transformacija i zamjena, moramo ovu tablicu pretvoriti u popis (popis).

Lirska digresija

• Stol je dvodimenzionalni niz koji se sastoji od nekoliko redaka i stupaca.
• Zapis (Zapis) – jednodimenzionalni niz-niz, koji se sastoji od nekoliko polja-elemenata s imenima, na primjer [Ime = “Masha”, Spol = “ž”, Dob = 25]
  
• Popis – jednodimenzionalni niz-stupac, koji se sastoji od nekoliko elemenata, na primjer {1, 2, 3, 10, 42} or { "Vjera nada ljubav" }
  

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

Trik je u tome što stavke popisa u Power Queryju mogu biti ne samo banalni brojevi ili tekst, već i drugi popisi ili zapisi. U tako lukavom popisu (popisu), koji se sastoji od zapisa (zapisa), moramo okrenuti naš imenik. U sintaktičkoj notaciji Power Queryja (unosi u uglatim zagradama, popisi u vitičastim zagradama) ovo bi izgledalo ovako:

{

    [ Pronađite = “St. Petersburg”, Zamijeni = “St. Petersburg” ] ,

    [ Pronađite = “St. Petersburg”, Zamijeni = “St. Petersburg” ] ,

    [ Find = “Petar”, Replace = “St. Petersburg” ] ,

i tako dalje

}

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

Bilo je:

Nakon:

Nakon dodavanja funkcije Table.ToRecords, izgled naše tablice će se promijeniti – pretvorit će se u popis zapisa. Sadržaj pojedinačnih zapisa može se vidjeti na dnu okna prikaza klikom na pozadinu ćelije pored bilo koje riječi Zapis (ali ni jednom riječju!)

Uz gore navedeno, ima smisla dodati još jedan potez – za keširanje (međuspremnik) našeg kreiranog popisa. Ovo će prisiliti Power Query da jednom učita naš popis za traženje u memoriju i da ga ne ponovno izračunava kada mu kasnije pristupimo da ga zamijenimo. Da biste to učinili, zamotajte našu formulu u drugu funkciju – List.Buffer:

Takvo predmemoriranje će dati vrlo vidljivo povećanje brzine (nekoliko puta!) s velikom količinom početnih podataka koje treba izbrisati.

Time je priprema priručnika završena.

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

Korak 2. Učitavanje tablice podataka

Ovdje je sve banalno. Kao i prije s referentnom knjigom, dolazimo do bilo kojeg mjesta u tablici, kliknemo na karticu Datum dugme Iz tablice/raspona i naš stol Datum ulazi u Power Query. Automatski dodan korak modificirani tip (Promijenjena vrsta) također možete ukloniti:

S njim nisu potrebne nikakve posebne pripremne radnje i prelazimo na najvažniju stvar.

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

Dodajmo izračunati stupac našoj podatkovnoj tablici pomoću naredbe Dodavanje stupca – prilagođeni stupac (Dodaj stupac — prilagođeni stupac): i unesite naziv dodanog stupca u prozor koji se otvori (npr. ispravljena adresa) i našu čarobnu funkciju Popis.Akumulirati:

Ostaje kliknuti na OK – i dobivamo stupac s izvršenim zamjenama:

Imajte na umu da:

• Budući da Power Query razlikuje velika i mala slova, nije bilo zamjene u pretposljednjem redu, jer u direktoriju imamo “SPb”, a ne “SPb”.
• Ako postoji nekoliko podnizova za zamjenu odjednom u izvornim podacima (na primjer, u 7. retku trebate zamijeniti i "S-Pb" i "Prospectus"), to ne stvara probleme (za razliku od zamjene formulama iz prethodna metoda).
• Ako se u izvornom tekstu (9. redak) nema što zamijeniti, tada se ne pojavljuju pogreške (za razliku, opet, od zamjene formulama).

Brzina takvog zahtjeva je vrlo, vrlo pristojna. Na primjer, za tablicu početnih podataka s veličinom od 5000 redaka, ovaj upit je ažuriran za manje od jedne sekunde (bez međuspremnika, usput, oko 3 sekunde!)

Kako funkcionira funkcija List.Accumulate

U principu, ovo bi mogao biti kraj (ja da napišem, a vi da pročitate) ovaj članak. Ako želite ne samo biti u mogućnosti, nego i razumjeti kako to radi "ispod haube", tada ćete morati zaroniti malo dublje u zečju rupu i pozabaviti se funkcijom List.Accumulate, koja je izvršila svu skupnu zamjenu raditi za nas.

Sintaksa za ovu funkciju je:

=List.Accumulate(popis, sjeme, akumulator)

gdje

• popis 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 posebnu varijablu.
  

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

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

Na primjer, funkcija zbrajanja može se predstaviti kao:

(a, b) => a + b

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

• su – varijabla u kojoj se akumulira rezultat (njegova početna vrijednost je gore navedena sjeme)
  
• struja – sljedeća iterirana vrijednost s popisa popis
  

Na primjer, pogledajmo korake logike sljedeće konstrukcije:

=List.Accumulate({3, 2, 5}, 10, (stanje, trenutno) => stanje + struja)

Ovo je najnovije prikupljeno su vrijednost je naša funkcija List.Accumulate i izlazi kao rezultat:

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

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

Međutim, glavna značajka List.Accumulate je mogućnost obrade ne samo jednostavnog teksta ili numeričkih popisa kao argumenata, već i složenijih objekata – na primjer, popisa-iz-popisa ili popisa-iz-zapisa (pozdrav, Imenik!)

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

List.Accumulate(Imenik, [Adresa], (stanje,trenutno) => Text.Replace(stanje, trenutno[Traži], trenutno[Zamijeni]) )

Što se ovdje zapravo događa?

1. Kao početna vrijednost (sjeme) uzimamo prvi nespretni tekst iz stupca [Adresa] naš stol: 199034, St. Petersburg, str. Beringa, d. 1
2. Zatim List.Accumulate ponavlja elemente liste jedan po jedan – Priručnik. Svaki element ovog popisa je zapis koji se sastoji od para polja "Što pronaći - Čime zamijeniti" ili, drugim riječima, sljedeći redak u imeniku.
3. Funkcija akumulator stavlja u varijablu su početna vrijednost (prva adresa 199034, St. Petersburg, str. Beringa, d. 1) i na njemu obavlja funkciju akumulatora – operaciju zamjene pomoću standardne M-funkcije Tekst. Zamijeni (analogno Excelovoj funkciji SUBSTITUTE). Njegova sintaksa je:

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

   i ovdje imamo:

• su je naša prljava adresa, koja leži u su (dolazak tamo od sjeme)
  
• trenutno [Traži] – vrijednost polja Pronaći od sljedećeg ponovljenog unosa liste Imenik, koji leži u varijabli struja
• trenutno [Zamijeni] – vrijednost polja Zamjena od sljedećeg ponovljenog unosa liste Imenikležanje u struja

Stoga se za svaku adresu svaki put izvodi puni ciklus nabrajanja svih redaka u direktoriju, zamjenjujući tekst iz polja [Traži] s vrijednošću iz polja [Zamijeni].

Nadam se da ste shvatili ideju 🙂

• Skupna zamjena teksta na popisu pomoću formula
  
• Regularni izrazi (RegExp) u Power Queryju