Bulkteksterstatning i Power Query med List.Accumulate-funksjon

Hvordan raskt og i bulk erstatte teksten i henhold til referanselisten med formler – vi har allerede ordnet det. La oss nå prøve å gjøre det i Power Query.

Som ofte skjer utføre denne oppgaven er mye enklere enn å forklare hvorfor det fungerer, men la oss prøve å gjøre begge deler 🙂

Så vi har to "smarte" dynamiske tabeller laget fra vanlige områder med en hurtigtast Ctrl+T eller team Hjem – Formater som en tabell (Hjem — Formater som tabell):

Jeg ringte det første bordet Data, det andre bordet – Directorybruker felt Tabellnavn (Tabellnavn) tab Constructor (Design).

Oppgave: Bytt ut adresser i tabellen Data alle forekomster fra en kolonne Å finne Håndbok til deres tilsvarende korrekte motstykker fra kolonnen Erstatning. Resten av teksten i cellene skal forbli urørt.

Trinn 1. Last inn katalogen i Power Query og gjør den om til en liste

Etter å ha satt den aktive cellen til et hvilket som helst sted i referansetabellen, klikk på fanen Data (Dato)eller på fanen Strømforespørsel (hvis du har en gammel versjon av Excel og du installerte Power Query som et tillegg på en egen fane) på knappen Fra bord/sortiment (Fra tabell/område).

Referansetabellen vil bli lastet inn i Power Query-spørringseditoren:

For ikke å forstyrre, et automatisk lagt trinn modifisert type (Endret type) i det høyre panelet kan de anvendte trinnene trygt slettes, og bare trinnet blir igjen kilde (Kilde):

Nå, for å utføre ytterligere transformasjoner og erstatninger, må vi gjøre denne tabellen om til en liste (liste).

Lyrisk digresjon

• Bord er en todimensjonal matrise som består av flere rader og kolonner.
• Ta opp (Rekord) – endimensjonal array-string, bestående av flere felt-elementer med navn, for eksempel [Navn = "Masha", Kjønn = "f", Alder = 25]
  
• Liste – en endimensjonal array-kolonne, bestående av flere elementer, for eksempel {1, 2, 3, 10, 42} or { "Tro håp kjærlighet" }
  

For å løse problemet vårt vil vi først og fremst være interessert i typen Liste.

Trikset her er at listeelementer i Power Query ikke bare kan være banale tall eller tekst, men også andre lister eller poster. Det er i en så vanskelig liste (liste), som består av poster (poster) at vi må snu katalogen vår. I Power Query syntaktisk notasjon (oppføringer i firkantede parenteser, lister i krøllete parenteser) vil dette se slik ut:

{

    [ Finn = "St. Petersburg", Erstatt = "St. Petersburg» ] ,

    [ Finn = "St. Petersburg", Erstatt = "St. Petersburg» ] ,

    [ Finn = "Peter", Erstatt = "St. Petersburg» ] ,

og så videre

}

En slik transformasjon utføres ved å bruke en spesiell funksjon av M-språket innebygd i Power Query – Table.ToRecords. For å bruke den direkte i formellinjen, legg til denne funksjonen i trinnkoden der kilde.

Det var:

Etter:

Etter å ha lagt til Table.ToRecords-funksjonen, vil utseendet til tabellen endres – den blir til en liste over poster. Innholdet i individuelle poster kan ses nederst i visningsruten ved å klikke i cellebakgrunnen ved siden av et ord Record (men ikke med et eneste ord!)

I tillegg til det ovennevnte, er det fornuftig å legge til ett slag til - for å bufre (buffere) den opprettede listen vår. Dette vil tvinge Power Query til å laste opp oppslagslisten vår én gang i minnet og ikke beregne den på nytt når vi senere får tilgang til den for å erstatte den. For å gjøre dette, pakk inn formelen vår i en annen funksjon - List.buffer:

Slik caching vil gi en veldig merkbar økning i hastighet (med flere ganger!) med en stor mengde innledende data som skal slettes.

Dette fullfører utarbeidelsen av håndboken.

Det gjenstår å klikke på Hjem – Lukk og last – Lukk og last til... (Hjem — Lukk&last — Lukk&last til..), Velg et alternativ Bare opprette en forbindelse (Bare opprette forbindelse) og gå tilbake til Excel.

Trinn 2. Laster datatabellen

Alt er banalt her. Som før med oppslagsboken kommer vi opp til et hvilket som helst sted i tabellen, klikk på fanen Data knapp Fra Tabell/Rekkevidde og bordet vårt Data kommer inn i Power Query. Automatisk lagt til trinn modifisert type (Endret type) du kan også fjerne:

Det kreves ingen spesielle forberedende handlinger med det, og vi går videre til det viktigste.

Trinn 3. Utfør erstatninger ved å bruke List.Accumulate-funksjonen

La oss legge til en beregnet kolonne til datatabellen vår ved å bruke kommandoen Legge til en kolonne – egendefinert kolonne (Legg til kolonne – egendefinert kolonne): og skriv inn navnet på kolonnen som er lagt til i vinduet som åpnes (f.eks. rettet adresse) og vår magiske funksjon Liste. Akkumulere:

Det gjenstår å klikke på OK – og vi får en kolonne med erstatningene som er gjort:

Noter det:

• Siden Power Query skiller mellom store og små bokstaver, var det ingen erstatning i den nest siste linjen, fordi vi i katalogen har "SPb", ikke "SPb".
• Hvis det er flere understrenger som skal erstattes samtidig i kildedataene (for eksempel i 7. linje må du erstatte både "S-Pb" og "Prospekt"), så skaper ikke dette noen problemer (i motsetning til å erstatte med formler fra forrige metode).
• Hvis det ikke er noe å erstatte i kildeteksten (9. linje), oppstår ingen feil (i motsetning til, igjen, fra erstatning med formler).

Hastigheten på en slik forespørsel er veldig, veldig grei. For eksempel, for en tabell med innledende data med en størrelse på 5000 rader, ble denne spørringen oppdatert på mindre enn et sekund (uten bufring, forresten, ca. 3 sekunder!)

Hvordan List.Accumulate-funksjonen fungerer

I prinsippet kan dette være slutten (for meg å skrive, og for deg å lese) denne artikkelen. Hvis du ikke bare vil kunne, men også forstå hvordan det fungerer "under panseret", må du dykke litt dypere ned i kaninhullet og håndtere List.Accumulate-funksjonen, som gjorde hele utskiftingen jobbe for oss.

Syntaksen for denne funksjonen er:

=Liste.akkumuler(liste, seed, akkumulator)

hvor

• liste er listen hvis elementer vi itererer over. 
• seed - opprinnelige tilstand
• akkumulator – en funksjon som utfører en operasjon (matematisk, tekst osv.) på det neste elementet i listen og akkumulerer resultatet av behandlingen i en spesiell variabel.
  

Generelt ser syntaksen for å skrive funksjoner i Power Query slik ut:

(argument1, argument2, … argumentN) => noen handlinger med argumenter

For eksempel kan summeringsfunksjonen representeres som:

(a, b) => a + b

For List.Accumulate har denne akkumulatorfunksjonen to nødvendige argumenter (de kan hete hva som helst, men de vanlige navnene er stat и nåværende, som i den offisielle hjelpen for denne funksjonen, hvor:

• stat – en variabel hvor resultatet er akkumulert (den opprinnelige verdien er den som er nevnt ovenfor seed)
  
• nåværende – neste itererte verdi fra listen liste
  

La oss for eksempel ta en titt på trinnene i logikken til følgende konstruksjon:

=Liste.akkumuler({3, 2, 5}, 10, (tilstand, strøm) => tilstand + strøm)

Dette er det siste akkumulerte stat verdien er listen vår. Akkumuler funksjon og utdata som et resultat:

Hvis du fantaserer litt, kan du ved å bruke List.Accumulate-funksjonen simulere for eksempel Excel-funksjonen CONCATENATE (i Power Query kalles dens analoge Tekst.Kombiner) ved å bruke uttrykket:

Eller til og med søk etter maksimumsverdien (imitasjon av Excels MAX-funksjon, som i Power Query kalles Liste.Maks):

Imidlertid er hovedfunksjonen til List.Accumulate muligheten til å behandle ikke bare enkel tekst eller numeriske lister som argumenter, men mer komplekse objekter – for eksempel lister-fra-lister eller lister-fra-poster (hei, Directory!)

La oss se igjen på konstruksjonen som utførte erstatningen i problemet vårt:

List.Accumulate(Directory, [Adresse], (tilstand, gjeldende) => Tekst.Erstatt(tilstand, nåværende[Finn], nåværende[Erstatt]) )

Hva skjer egentlig her?

1. Som startverdi (seed) tar vi den første klønete teksten fra spalten [Adresse] vårt bord: 199034, St. Petersburg, str. Beringa, d. 1
2. Deretter itererer List.Accumulate over elementene i listen én etter én – Håndbok. Hvert element i denne listen er en post som består av et par felt "What to find - What to replace with" eller, med andre ord, neste linje i katalogen.
3. Akkumulatorfunksjonen legger inn en variabel stat startverdi (første adresse 199034, St. Petersburg, str. Beringa, d. 1) og utfører en akkumulatorfunksjon på den – utskiftingsoperasjonen med standard M-funksjon Tekst.Erstatt (analogt med Excels ERSTATT-funksjon). Syntaksen er:

   Text.Replace( originaltekst, hva vi leter etter, hva vi erstatter med )

   og her har vi:

• stat er vår skitne adresse, som ligger i stat (å komme dit fra seed)
  
• gjeldende[Søk] – feltverdi Å finne fra den neste gjentatte oppføringen i listen Directory, som ligger i variabelen nåværende
• gjeldende [Erstatt] – feltverdi Erstatning fra den neste gjentatte oppføringen i listen Directoryligger i nåværende

For hver adresse kjøres således en full syklus med oppregning av alle linjer i katalogen hver gang, og erstatter teksten fra [Finn]-feltet med verdien fra [Erstatt]-feltet.

Håper du fikk ideen 🙂

• Masserstatt tekst i en liste ved hjelp av formler
  
• Regulære uttrykk (RegExp) i Power Query