Sustituzione di testu in massa in Power Query cù a funzione List.Accumulate

Cumu rimpiazzà rapidamente è in massa u testu secondu a lista di riferimentu cù e formule - avemu digià urdinatu. Avà pruvemu à fà in Power Query.

Cum'è spessu succede Fà stu compitu hè assai più faciule ch'è spiegà perchè funziona, ma pruvemu di fà i dui 🙂

Dunque, avemu dui tavuli dinamichi "intelligenti" creati da intervalli ordinali cù un shortcut di tastiera Ctrl+T o squadra Home - Formate cum'è una tavola (Casa - Formate cum'è Table):

Aghju chjamatu a prima tavola Dati, a seconda tavola - Directoryusendu campu Nome di a tavola (Nome di a tavula) Insignia regala (Cuncepimentu).

Task: rimpiazzà in indirizzi in a tavula Dati tutte l'occurrence da una colonna Da truvà Manuale à i so contraparti curretti currispundenti da a colonna Sustituitu. U restu di u testu in e cellule ùn deve esse micca toccu.

Passu 1. Load u cartulare in Power Query è trasfurmà in una lista

Dopu avè stabilitu a cellula attiva in ogni locu in a tabella di riferimentu, cliccate nantu à a tabulazione Dati (Data)o nantu à a tabulazione dumanda di putenza (se avete una versione antica di Excel è avete installatu Power Query cum'è add-in in una tabulazione separata) nantu à u buttone Da tavola / gamma (Da a Tavola / Gamma).

A tabella di riferimentu serà caricata in l'editore di query Power Query:

Per ùn interferiscenu micca, un passu aghjuntu automaticamente tipu mudificatu (Tipu cambiatu) in u panel dritta, i passi applicata pò esse sguassati sicuru, lassannu solu u passu surghjente (Source):

Avà, per fà più trasfurmazioni è rimpiazzamenti, avemu bisognu di trasfurmà sta tavula in una lista (lista).

Digressione lirica

• Table hè un array bidimensionale custituitu da parechje fila è colonne.
• Record (registru) - array-string unidimensionale, custituitu da parechji campi-elementi cù nomi, per esempiu [Nome = "Masha", Genere = "f", Età = 25]
  
• List - un array-colonna unidimensionale, custituitu da parechji elementi, per esempiu {1, 2, 3, 10, 42} or { "Faith Hope Love" }
  

Per risolve u nostru prublema, avemu da esse primurosu interessatu in u tipu List.

U truccu quì hè chì l'articuli di lista in Power Query pò esse micca solu numeri banali o testu, ma ancu altri listi o registri. Hè in una lista cusì complicata (lista), custituita di registri (records) chì avemu bisognu di turnà u nostru repertoriu. In a notazione sintattica di Power Query (entrate in parentesi quadrate, elenchi in parentesi curly) questu pare cusì:

{

    [ Trova = "St. Petersburg", Replace = "St. Petersburg"] ,

    [ Trova = "St. Petersburg", Replace = "St. Petersburg"] ,

    [ Truvà = "Petru", Sustituisce = "St. Petersburg"] ,

etc.

}

Una tale trasfurmazioni hè realizata utilizendu una funzione speciale di a lingua M integrata in Power Query - Table.ToRecords. Per applicà direttamente in a barra di formula, aghjunghje sta funzione à u codice di passu surghjente.

Hè statu:

Dopu:

Dopu avè aghjustatu a funzione Table.ToRecords, l'aspettu di a nostra tavula cambierà - turnerà in una lista di registri. U cuntenutu di i registri individuali pò esse vistu in u fondu di u pannellu di vista clicchendu in u fondu di a cellula vicinu à ogni parolla. Santuzza (ma micca in una sola parolla!)

In più di ciò chì sopra, hè sensu per aghjunghje un colpu più - per cache (buffer) a nostra lista creata. Questu furzà Power Query à carricà a nostra lista di ricerca una volta in memoria è micca ricalculata di novu quandu avemu accessu dopu per rimpiazzà. Per fà questu, imbulighjate a nostra formula in una altra funzione - Lista.Buffer:

Tali caching darà un incrementu assai notevuli in a veloce (di parechje volte!) Cù una grande quantità di dati iniziali per esse sbulicati.

Questu cumpleta a preparazione di u manuale.

Resta da cliccà Casa - Chiudi è Carica - Chiudi è Carica à... (Home — Close&Load — Close&Load to..), sceglite una opzione Basta à creà una cunnessione (Crea solu cunnessione) è torna à Excel.

Step 2. Loading a tavula di dati

Tuttu hè banale quì. Cum'è prima cù u libru di riferimentu, avemu cullatu in ogni locu in a tavula, cliccate nantu à a tabulazione Dati buttone Da Table/Range è a nostra tavola Dati entra in Power Query. Passu aghjuntu automaticamente tipu mudificatu (Tipu cambiatu) pudete ancu sguassà:

Nisuna azzione preparatoria speciale ùn deve esse fatta cun ella, è andemu à u più impurtante.

Step 3. Eseguite rimpiazzamenti cù a funzione List.Accumulate

Aghjunghjite una colonna calculata à a nostra tabella di dati cù u cumandimu Aghjunghjite una Colonna - Colonna Personalizzata (Aggiungi colonna - Colonna persunalizata): è inserite u nome di a colonna aghjuntu in a finestra chì si apre (per esempiu, indirizzu currettu) è a nostra funzione magica Lista.Acumulà:

Resta da cliccà OK - è avemu una colonna cù i rimpiazzamenti fatti:

Nota:

• Siccomu Power Query hè sensible à u casu, ùn ci era micca sustituzione in a penultima linea, perchè in u cartulare avemu "SPb", micca "SPb".
• Se ci sò parechje substrings per rimpiazzà à una volta in i dati di fonte (per esempiu, in a 7a linea avete bisognu di rimpiazzà "S-Pb" è "Prospectus"), questu ùn crea micca prublemi (cuntrariu di rimpiazzà cù formule da u metudu precedente).
• Se ùn ci hè nunda di rimpiazzà in u testu fonte (9a linea), allora ùn ci hè micca errore (cuntrariu, di novu, da rimpiazzà da formule).

A rapidità di una tale dumanda hè assai, assai decente. Per esempiu, per una tabella di dati iniziali cù una dimensione di 5000 fila, sta dumanda hè stata aghjurnata in menu di un secondu (senza buffering, per via, circa 3 seconde!)

Cumu funziona a funzione List.Accumulate

In principiu, questu puderia esse a fine (per mè per scrive, è per voi per leghje) stu articulu. Sè vo vulete micca solu pudè, ma ancu capisce cumu si travaglia "sottu u cappucciu", allura vi tuccherà à immerse un pocu più in u foru di cunigliu è trattà cù a funzione List.Accumulate, chì hà fattu tuttu u rimpiazzamentu di massa. travaglià per noi.

A sintassi per sta funzione hè:

=Liste.Accumulate(lists, sumente, accumulatore)

induva

• lists hè a lista di i so elementi chì iteremu. 
• sumente - statu iniziale
• accumulatore - una funzione chì esegue una certa operazione (matematica, testu, etc.) nantu à l'elementu prossimu di a lista è accumula u risultatu di trasfurmazioni in una variàbile speciale.
  

In generale, a sintassi per e funzioni di scrittura in Power Query hè cusì:

(argument1, argument2, ... argumentN) => alcune azzioni cù argumenti

Per esempiu, a funzione di sommaria pò esse rapprisintata cum'è:

(a, b) => a + b

Per List.Accumulate , sta funzione di l'accumulatore hà dui argumenti necessarii (pò esse chjamatu qualcosa, ma i nomi di solitu sò statu и oghji, cum'è in l'aiutu ufficiale per questa funzione, induve:

• statu – una variàbile induve u risultatu hè accumulatu (u so valore iniziale hè quellu mintuatu sopra sumente)
  
• oghji - u prossimu valore iteratu da a lista lists
  

Per esempiu, fighjemu un ochju à i passi di a logica di a custruzzione seguente:

=Liste.Accumulate({3, 2, 5}, 10, (statu, currente) => statu + currente)

Questu hè l'ultimu accumulatu statu u valore hè a nostra funzione List.Accumulate è outputs com'è u risultatu:

Sè vo fantasize un pocu, dopu cù a funzione List.Accumulate, pudete simule, per esempiu, a funzione Excel CONCATENATE (in Power Query, u so analogu hè chjamatu Testu.Combine) usendu l'espressione:

O ancu cercate u valore massimu (imitazione di a funzione MAX di Excel, chì in Power Query hè chjamatu Lista.Max):

Tuttavia, a funzione principale di List.Accumulate hè a capacità di processà micca solu listi simplici di testu o numerichi cum'è argumenti, ma ogetti più cumplessi - per esempiu, listes-from-lists o lists-from-records (hello, Directory!)

Fighjemu novu à a custruzzione chì hà realizatu a sustituzione in u nostru prublema:

Lista.Accumulate(Directory, [Indirizzu], (statu, currenti) => Testu.Sustituisci (statu, currenti[Truvà], currenti[Sustituisci]) )

Chì succede veramente quì?

1. Cum'è valore iniziale (sumente) pigliemu u primu testu goffa da a colonna [Indirizzu] a nostra tavola: 199034, San Petruburgu, str. Beringa, d. 1
2. Allora List.Accumulate iterate nantu à l'elementi di a lista unu per unu - Manuale. Ogni elementu di sta lista hè un registru custituitu da un paru di campi "Cosa truvà - Chì rimpiazzà cù" o, in altre parolle, a prossima linea in u cartulare.
3. A funzione accumulatore mette in una variabile statu valore iniziale (primu indirizzu 199034, San Petruburgu, str. Beringa, d. 1) è esegue una funzione d'accumulatore nantu à questu - l'operazione di rimpiazzamentu cù a funzione M standard Testu.Replace (analoga à a funzione SOSTITUTI di Excel). A so sintassi hè:

   Text.Replace (testu originale, ciò chì circamu, ciò chì avemu rimpiazzatu)

   è quì avemu:

• statu hè u nostru indirizzu bruttu, chì si trova in statu (Vendu quì da sumente)
  
• attuale [Cerca] - valore di u campu Da truvà da a prossima entrata iterata di a lista Directory, chì si trova in a variabile oghji
• currente [Sustituitu] - valore di u campu Sustituitu da a prossima entrata iterata di a lista Directorystendu in oghji

Cusì, per ogni indirizzu, un ciculu sanu di enumerazione di tutte e linee in u cartulare hè eseguitu ogni volta, rimpiazzà u testu da u campu [Truvà] cù u valore da u campu [Sustituite].

Spergu chì avete l'idea 🙂

• Sustituisce u testu in massa in una lista cù e formule
  
• Espressioni Regular (RegExp) in Power Query