Masseteksterstatning i Power Query med List.Accumulate-funktion

Sådan udskiftes teksten hurtigt og samlet i henhold til referencelisten med formler – det har vi allerede ordnet. Lad os nu prøve at gøre det i Power Query.

Som det ofte sker udføre denne opgave er meget nemmere end at forklare hvorfor det virker, men lad os prøve at gøre begge dele 🙂

Så vi har to "smarte" dynamiske tabeller oprettet fra almindelige områder med en tastaturgenvej Ctrl+T eller hold Hjem – Formater som en tabel (Hjem — Formater som tabel):

Jeg ringede til det første bord data, det andet bord – Vejviserbruger felt Tabelnavn (Tabelnavn) fanen Constructor (Design).

Opgave: Erstat i adresser i tabellen data alle forekomster fra en kolonne At finde Håndbog til deres tilsvarende korrekte modstykker fra kolonnen Erstatning. Resten af ​​teksten i cellerne skal forblive urørt.

Trin 1. Indlæs mappen i Power Query, og gør den til en liste

Når du har indstillet den aktive celle til et hvilket som helst sted i referencetabellen, skal du klikke på fanen data (Dato)eller på fanen Strømforespørgsel (hvis du har en gammel version af Excel, og du har installeret Power Query som et tilføjelsesprogram på en separat fane) på knappen Fra bord/sortiment (Fra tabel/område).

Referencetabellen indlæses i Power Query-forespørgselseditoren:

For ikke at forstyrre, et automatisk tilføjet trin ændret type (Ændret type) i det højre panel kan de anvendte trin sikkert slettes, så kun trinnet bliver tilbage Kilde (Kilde):

For nu at udføre yderligere transformationer og udskiftninger skal vi lave denne tabel om til en liste (liste).

Lyrisk digression

• Bordlampe er en todimensional matrix bestående af flere rækker og kolonner.
• Optag (Record) – en-dimensionel array-string, bestående af flere felt-elementer med navne, f.eks [Navn = "Masha", Køn = "f", Alder = 25]
  
• Liste – en endimensionel array-søjle, bestående af flere elementer, f.eks {1, 2, 3, 10, 42} or { "Tro håb kærlighed" }
  

For at løse vores problem vil vi primært være interesseret i typen Liste.

Tricket her er, at listeelementer i Power Query ikke kun kan være banale tal eller tekst, men også andre lister eller poster. Det er i sådan en vanskelig liste (liste), der består af poster (records), at vi skal vende vores mappe. I Power Query syntaktisk notation (indgange i firkantede parenteser, lister i krøllede parenteser) vil dette se sådan ud:

{

    [ Find = "St. Petersborg", Erstat = "St. Petersborg” ] ,

    [ Find = "St. Petersborg", Erstat = "St. Petersborg” ] ,

    [ Find = "Peter", Erstat = "St. Petersborg” ] ,

etc.

}

En sådan transformation udføres ved hjælp af en speciel funktion af M-sproget indbygget i Power Query - Table.ToRecords. For at anvende det direkte i formellinjen skal du tilføje denne funktion til trinkoden der Kilde.

Det var:

Efter:

Efter tilføjelse af Table.ToRecords-funktionen vil udseendet af vores tabel ændre sig - det bliver til en liste over poster. Indholdet af individuelle poster kan ses nederst i visningsruden ved at klikke i cellebaggrunden ud for ethvert ord Optage (men ikke med et eneste ord!)

Ud over ovenstående giver det mening at tilføje et streg mere - for at cache (buffere) vores oprettede liste. Dette vil tvinge Power Query til at indlæse vores opslagsliste én gang i hukommelsen og ikke genberegne den igen, når vi senere får adgang til den for at erstatte den. For at gøre dette skal du pakke vores formel ind i en anden funktion - Liste.Buffer:

En sådan caching vil give en meget mærkbar stigning i hastigheden (med flere gange!) med en stor mængde indledende data, der skal ryddes.

Dette afslutter udarbejdelsen af ​​håndbogen.

Det er tilbage at klikke på Hjem – Luk og indlæs – Luk og indlæs til... (Hjem — Luk&indlæs — Luk&indlæs til..), vælg en indstilling Bare opret en forbindelse (Opret kun forbindelse) og vende tilbage til Excel.

Trin 2. Indlæsning af datatabellen

Alt er banalt her. Som før med opslagsbogen kommer vi op til et hvilket som helst sted i tabellen, klik på fanen data . Fra tabel/område og vores bord data kommer ind i Power Query. Automatisk tilføjet trin ændret type (Ændret type) du kan også fjerne:

Der kræves ingen særlige forberedende handlinger med det, og vi går videre til det vigtigste.

Trin 3. Udfør udskiftninger ved hjælp af List.Accumulate-funktionen

Lad os tilføje en beregnet kolonne til vores datatabel ved hjælp af kommandoen Tilføjelse af en kolonne – Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne): og indtast navnet på den tilføjede kolonne i vinduet, der åbnes (f.eks. rettet adresse) og vores magiske funktion Liste. Akkumuler:

Det er tilbage at klikke på OK – og vi får en klumme med de foretagne udskiftninger:

Noter det:

• Da Power Query skelner mellem store og små bogstaver, var der ingen erstatning i næstsidste linje, fordi vi i mappen har "SPb", ikke "SPb".
• Hvis der er flere delstrenge, der skal erstattes på én gang i kildedataene (f.eks. skal du i 7. linje erstatte både "S-Pb" og "Prospekt"), så giver det ingen problemer (i modsætning til at erstatte med formler fra den foregående metode).
• Hvis der ikke er noget at erstatte i kildeteksten (9. linje), så opstår der ingen fejl (i modsætning til, igen, fra erstatning med formler).

Hastigheden af ​​en sådan anmodning er meget, meget anstændig. For eksempel, for en tabel med indledende data med en størrelse på 5000 rækker, blev denne forespørgsel opdateret på mindre end et sekund (uden buffer, i øvrigt, omkring 3 sekunder!)

Sådan fungerer List.Accumulate-funktionen

I princippet kan dette være slutningen (for mig at skrive, og for dig at læse) denne artikel. Hvis du ikke kun vil kunne, men også forstå, hvordan det fungerer "under motorhjelmen", så bliver du nødt til at dykke lidt dybere ned i kaninhullet og beskæftige dig med List.Accumulate-funktionen, som foretog den store udskiftning. arbejde for os.

Syntaksen for denne funktion er:

=Liste. Akkumuler(liste, frø, akkumulator)

hvor

• liste er listen, hvis elementer vi gentager os. 
• frø – udgangstilstand
• akkumulator – en funktion, der udfører en eller anden operation (matematisk, tekst osv.) på det næste element på listen og akkumulerer resultatet af behandlingen i en speciel variabel.
  

Generelt ser syntaksen for at skrive funktioner i Power Query sådan ud:

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

For eksempel kan summeringsfunktionen være repræsenteret som:

(a, b) => a + b

For List.Accumulate har denne akkumulatorfunktion to påkrævede argumenter (de kan hedde hvad som helst, men de sædvanlige navne er tilstand и strøm, som i den officielle hjælp til denne funktion, hvor:

• tilstand – en variabel, hvor resultatet akkumuleres (dens begyndelsesværdi er den, der er nævnt ovenfor frø)
  
• strøm – den næste itererede værdi fra listen liste
  

Lad os for eksempel tage et kig på trinene i logikken i følgende konstruktion:

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

Dette er det seneste akkumulerede tilstand værdien er vores liste. Akkumuler funktion og output som et resultat:

Hvis du fantaserer lidt, så ved hjælp af List.Accumulate-funktionen kan du simulere f.eks. Excel-funktionen CONCATENATE (i Power Query hedder dens analog Tekst. Kombiner) ved hjælp af udtrykket:

Eller endda søg efter den maksimale værdi (efterligning af Excels MAX-funktion, som i Power Query kaldes Liste.Max):

Men hovedfunktionen ved List.Accumulate er evnen til at behandle ikke kun simpel tekst eller numeriske lister som argumenter, men mere komplekse objekter – for eksempel lister-fra-lister eller lister-fra-poster (hej, Directory!)

Lad os se igen på den konstruktion, der udførte udskiftningen i vores problem:

List.Accumulate(Vejviser, [Adresse], (tilstand, nuværende) => Tekst.Erstat(tilstand, nuværende[Find], nuværende[Erstat]) )

Hvad foregår der egentlig her?

1. Som startværdi (frø) tager vi den første klodsede tekst fra spalten [Adresse] vores bord: 199034, Sankt Petersborg, str. Beringa, d. 1
2. Derefter itererer List.Accumulate over elementerne på listen én efter én – Håndbog. Hvert element på denne liste er en post bestående af et par felter "Hvad skal man finde - Hvad skal man erstatte med" eller med andre ord den næste linje i mappen.
3. Akkumulatorfunktionen sætter ind i en variabel tilstand startværdi (første adresse 199034, Sankt Petersborg, str. Beringa, d. 1) og udfører en akkumulatorfunktion på den – udskiftningsoperationen ved hjælp af standard M-funktionen Tekst.Erstat (analogt med Excels SUBSTITUTE-funktion). Dens syntaks er:

   Text.Replace( original tekst, hvad vi leder efter, hvad vi erstatter med)

   og her har vi:

• tilstand er vores beskidte adresse, som ligger i tilstand (at komme derfra frø)
  
• aktuelle [Søg] – feltværdi At finde fra den næste gentagede post på listen Vejviser, som ligger i variablen strøm
• nuværende [Erstat] – feltværdi Erstatning fra den næste gentagede post på listen Vejviserligger i strøm

For hver adresse køres således en fuld cyklus af opregning af alle linjer i biblioteket hver gang, og teksten fra [Find]-feltet erstattes med værdien fra [Erstat]-feltet.

Håber du fik ideen 🙂

• Masserstat tekst i en liste ved hjælp af formler
  
• Regulære udtryk (RegExp) i Power Query