Bulk Text Ersatz an Power Query mat List.Accumulate Funktioun

Wéi séier an am bulk den Text no der Referenzlëscht mat Formelen ze ersetzen - mir hunn et schonn zortéiert. Loosst eis elo probéieren et an der Power Query ze maachen.

Wéi oft geschitt Maachen dës Aufgab ass vill méi einfach wéi z'erklären firwat et funktionnéiert, awer loosst eis probéieren béid ze maachen 🙂

Also, mir hunn zwee "intelligent" dynamesch Dëscher erstallt aus gewéinleche Beräicher mat enger Tastatur Ofkiirzung Ctrl+T oder Team Home - Format als Dësch (Home - Format als Table):

Ech genannt den éischten Dësch Daten, den zweeten Dësch - Guidebenotzt Feld Dësch Numm (Numm vum Dësch) Reiter Builder (Design).

Aufgab: ersetzen an Adressen an der Tabell Daten all Optriede vun enger Kolonn Fannen Handbuch un hir entspriechend korrekt Géigespiller aus der Kolonn Substitute. De Rescht vum Text an den Zellen soll onberéiert bleiwen.

Schrëtt 1. Lued de Verzeechnes an Power Query a verwandelt et an eng Lëscht

Nodeems Dir déi aktiv Zell op all Plaz an der Referenztabelle gesat hutt, klickt op de Tab Daten (Datum)oder op der Tab Muecht Ufro (wann Dir eng al Versioun vun Excel hutt an Dir Power Query als Add-in op enger separater Tab installéiert hutt) op de Knäppchen Vun Dësch / Gamme (Vum Dësch / Range).

D'Referenztabell gëtt an den Power Query Query Editor gelueden:

Fir net ze stéieren, en automatesch addéierte Schrëtt geännert Typ (Ännert Typ) am richtege Panel kënnen déi applizéiert Schrëtt sécher geläscht ginn, nëmmen de Schrëtt bleift Source (Source):

Elo, fir weider Transformatiounen an Ersatz ze maachen, musse mir dës Tabell an eng Lëscht (Lëscht) ëmsetzen.

Lyresch Digressioun

• Dësch ass eng zweedimensional Array, déi aus verschiddene Reihen a Kolonnen besteet.
• Rekord (Rekord) - eendimensional Array-String, besteet aus verschiddene Felder-Elementer mat Nimm, zum Beispill [Numm = "Masha", Geschlecht = "f", Alter = 25]
  
• Lëscht - eng eendimensional Array-Kolonn, déi aus verschiddenen Elementer besteet, zum Beispill {1, 2, 3, 10, 42} or { "Faith Hope Love" }
  

Fir eise Problem ze léisen, wäerte mir haaptsächlech un den Typ interesséiert sinn Lëscht.

Den Trick hei ass datt d'Lëschtartikelen an der Power Query net nëmmen banal Zuelen oder Text kënne sinn, awer och aner Lëschten oder Opzeechnungen. Et ass an esou enger komplizéierter Lëscht (Lëscht), déi aus Opzeechnungen (Records) besteet, déi mir eis Verzeechnes mussen ëmdréinen. An Power Query syntaktesch Notatioun (Entréen a véiereckege Klammeren, Lëschten a Curly Klammeren) géif dëst ausgesinn:

{

    [Find = "St. Petersburg", Ersatz = "St. Petersburg"] ,

    [Find = "St. Petersburg", Ersatz = "St. Petersburg"] ,

    [ Fannt = "Peter", Ersetzen = "St. Petersburg"] ,

etc.

}

Esou eng Transformatioun gëtt mat enger spezieller Funktioun vun der M Sprooch a Power Query agebaut - Table.ToRecords. Fir se direkt an der Formelbar z'applizéieren, füügt dës Funktioun un de Schrëttcode do Source.

Et wor:

Nodeem:

Nodeems Dir d'Table.ToRecords Funktioun bäigefüügt hutt, ännert d'Erscheinung vun eisem Dësch - et gëtt an eng Lëscht vun records. Den Inhalt vun eenzelnen Opzeechnungen kann um Enn vun der Vue Scheif gesi ginn andeems Dir op den Zellhannergrond nieft all Wuert klickt Rekord (awer net an engem eenzege Wuert!)

Zousätzlech zu den uewe genannte mécht et Sënn fir e méi Schlag ze addéieren - fir eis erstallt Lëscht ze Cache (Puffer). Dëst wäert Power Query forcéiere fir eis Lookup Lëscht eemol an d'Erënnerung ze lueden an et net erëm ze berechnen wa mir spéider Zougang hunn fir se ze ersetzen. Fir dëst ze maachen, wéckelt eis Formel an eng aner Funktioun - Lëscht.Buffer:

Sou Caching gëtt eng ganz merkbar Erhéijung vun der Geschwindegkeet (um e puer Mol!) Mat enger grousser Quantitéit un initialen Daten déi geläscht ginn.

Dëst fäerdeg d'Virbereedung vum Handbuch.

Et bleift ze klicken Home - Zoumaachen a Lueden - Zoumaachen a Lueden fir ... (Home — Close&Load — Close&Load to ..), wielt eng Optioun Schafen einfach eng Verbindung (Erstellt nëmmen eng Verbindung) an zréck op Excel.

Schrëtt 2. Luede d'Daten Dësch

Hei ass alles lëschteg. Wéi virdrun mat der Referenz Buch, mir kommen op all Plaz an der Tabell, klickt op d'Tab Daten Knäppchen Vun Dësch / Gamme an eisen Dësch Daten kritt an Power Query. Automatesch bäigefüügt Schrëtt geännert Typ (Ännert Typ) Dir kënnt och ewechhuelen:

Et gi keng speziell Virbereedungsaktioune erfuerderlech mat deem ze maachen, a mir ginn op dat Wichtegst.

Schrëtt 3. Maacht Ersatz mat der List.Accumulate Funktioun

Loosst eis eng berechent Kolonn op eis Datentabelle mat dem Kommando addéieren Eng Kolonn derbäisetzen - Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn): a gitt den Numm vun der dobäigesater Kolonn an der Fënster déi opmaacht (z.B. korrigéiert Adress) an eis magesch Funktioun Lëscht.Accumuléieren:

Et bleift ze klicken OK - a mir kréien eng Kolonn mat den Ersatzstécker:

Bedenkt datt:

• Well Power Query ka sensibel ass, gouf et keen Ersatz an der virlescht Linn, well am Verzeechnes hu mir "SPb", net "SPb".
• Wann et e puer Substrings gläichzäiteg an de Quelldaten ersat ginn (zum Beispill, an der 7. Zeil musst Dir souwuel "S-Pb" wéi och "Prospekt" ersetzen), da schaaft dëst keng Probleemer (am Géigesaz zum Ersatz mat Formelen aus virdrun Method).
• Wann et näischt am Quelltext ze ersetzen ass (9. Zeil), da komme keng Feeler (am Géigesaz zu der Ersatz duerch Formelen).

D'Vitesse vun esou enger Demande ass ganz, ganz uerdentlech. Zum Beispill, fir en Dësch vun initialen Donnéeën mat enger Gréisst vu 5000 Zeilen, gouf dës Ufro a manner wéi enger Sekonn aktualiséiert (ouni Puffer, iwwregens, ongeféier 3 Sekonnen!)

Wéi d'Lëscht.Accumulate funktionéiert

Am Prinzip kann dëst den Enn sinn (fir mech ze schreiwen, a fir Iech ze liesen) dësen Artikel. Wann Dir wëllt net nëmme kënnen, mee och verstoen wéi et "ënnert der Hood" funktionnéiert, da musst Dir e bësse méi déif an d'Kanéngchenhol tauchen a mat der Lëscht.Accumulate function, which did all the bulk replacement schaffen fir eis.

D'Syntax fir dës Funktioun ass:

=Lëscht.Accumuléieren(Lëscht, Som, ugesammelt)

wou

• Lëscht ass d'Lëscht iwwer deenen hir Elementer mir iteréieren. 
• Som - initial Staat
• ugesammelt - eng Funktioun déi eng Operatioun (mathematesch, Text, etc.) op den nächsten Element vun der Lëscht mécht an d'Resultat vun der Veraarbechtung an enger spezieller Variabel sammelt.
  

Allgemeng gesäit d'Syntax fir Schreiffunktiounen an Power Query esou aus:

(argument1, argument2, … argumentN) => e puer Aktiounen mat Argumenter

Zum Beispill kéint d'Summatiounsfunktioun als duergestallt ginn:

(a, b) => a + b

Fir List.Accumulate huet dës Akkumulatorfunktioun zwee erfuerderlech Argumenter (si kënnen alles genannt ginn, awer déi üblech Nimm sinn Staat и aktuell, wéi an der offizieller Hëllef fir dës Funktioun, wou:

• Staat - eng Variabel wou d'Resultat accumuléiert ass (säin initial Wäert ass deen uewen ernimmt Som)
  
• aktuell - den nächsten iteréierte Wäert vun der Lëscht Lëscht
  

Zum Beispill, loosst eis d'Schrëtt vun der Logik vun der folgender Konstruktioun kucken:

=Lëscht.Accumuléieren({3}, 10, (Staat, aktuell) => Staat + aktuell)

Dëst ass déi lescht cumuléiert Staat de Wäert ass eis Lëscht.Accumuléiert Funktioun an Ausgänge als Resultat:

Wann Dir e bësse fantaséiert, da benotzt d'Lëscht.Accumulate Funktioun, Dir kënnt zum Beispill d'Excel Funktioun CONCATENATE simuléieren (an Power Query gëtt seng Analog genannt Text.Kombinéieren) mam Ausdrock benotzt:

Oder souguer no de maximale Wäert sichen (Imitatioun vun der Excel MAX Funktioun, déi an der Power Query genannt gëtt Lëscht.Max):

Wéi och ëmmer, d'Haaptfeature vu List.Accumulate ass d'Fäegkeet fir net nëmmen einfachen Text oder numeresch Lëschten als Argumenter ze veraarbechten, mee méi komplex Objekter - zum Beispill Lëschte vu Lëschten oder Lëschte vu Rekorder (Moien, Directory!)

Loosst eis nach eng Kéier op d'Konstruktioun kucken, déi den Ersatz an eisem Problem gemaach huet:

Lëscht.Accumuléieren(Guide, [Adress], (state, aktuell) => Text.Replace(state, aktuell[Find], aktuell[Ersetzen]) )

Wat ass hei wierklech lass?

1. Als initial Wäert (Som) mir huelen den éischten knaschtege Text aus der Kolonn [Adress] eisen Dësch: 199034, Sankt Petersburg, Str. Beringa, d. 1
2. Dann List.Accumulate iterates iwwer d'Elementer vun der Lëscht een nom aneren - Handbuch. All Element vun dëser Lëscht ass e Rekord, deen aus e puer Felder besteet "Wat ze fannen - Wat fir ze ersetzen" oder, an anere Wierder, déi nächst Zeil am Verzeechnes.
3. D'Akkumulatorfunktioun setzt an eng Variabel Staat initial Wäert (éischt Adress 199034, Sankt Petersburg, Str. Beringa, d. 1) a mécht eng Akkumulatorfunktioun drop - d'Ersatzoperatioun mat der Standard M-Funktioun Text.Ersetzen (Analog zu der SUBSTITUTE Funktioun vun Excel). Seng Syntax ass:

   Text.Replace (Original Text, wat mir sichen, mat wat mir ersetzen)

   an hei hu mer:

• Staat ass eis dreckeg Adress, déi läit an Staat (vun dohinner kommen Som)
  
• aktuell [Sich] - Feld Wäert Fannen vun der nächster iteréierter Entrée vun der Lëscht Guide, déi an der Variabel läit aktuell
• aktuell [Ersetzen] - Feld Wäert Substitute vun der nächster iteréierter Entrée vun der Lëscht Guideleien an aktuell

Also, fir all Adress gëtt all Kéier e ganzen Zyklus vun der Opzielung vun all Zeilen am Verzeechnes ausgeführt, andeems den Text aus dem Feld [Find] duerch de Wäert aus dem Feld [Ersetzen] ersat gëtt.

Hoffen Dir hutt d'Iddi 🙂

• Bulk ersetzen Text an enger Lëscht mat Formelen
  
• Regelméisseg Ausdréck (RegExp) an Power Query