Sostituzione di testo in blocco in Power Query con la funzione List.Accumulate

Come sostituire rapidamente e in blocco il testo in base all'elenco di riferimento con formule: l'abbiamo già risolto. Ora proviamo a farlo in Power Query.

Come spesso accade eseguire questo compito è molto più semplice della spiegazione perché funziona, ma proviamo a fare entrambe le cose 🙂

Quindi, abbiamo due tabelle dinamiche "intelligenti" create da intervalli ordinari con una scorciatoia da tastiera Ctrl+T o squadra Home – Formatta come una tabella (Home — Formatta come tabella):

Ho chiamato il primo tavolo Dati, la seconda tavola – elencousando il campo Nome della tabella (Nome tabella) linguetta Costruttore (Design).

Compito: sostituire negli indirizzi nella tabella Dati tutte le occorrenze da una colonna Trovare Manuale alle corrispondenti controparti corrette dalla colonna Sostituire. Il resto del testo nelle celle dovrebbe rimanere intatto.

Passaggio 1. Carica la directory in Power Query e trasformala in un elenco

Dopo aver impostato la cella attiva in un punto qualsiasi della tabella di riferimento, fare clic sulla scheda Dati (Data)o nella scheda Power Query (se hai una versione precedente di Excel e hai installato Power Query come componente aggiuntivo in una scheda separata) sul pulsante Dalla tabella/intervallo (Dalla tabella/intervallo).

La tabella di riferimento verrà caricata nell'editor di query di Power Query:

Per non interferire, un passaggio aggiunto automaticamente tipo modificato (Tipo modificato) nel pannello di destra, i passaggi applicati possono essere tranquillamente cancellati, lasciando solo il passaggio Fonte (Fonte):

Ora, per eseguire ulteriori trasformazioni e sostituzioni, dobbiamo trasformare questa tabella in un elenco (elenco).

Digressione lirica

• Table è una matrice bidimensionale composta da più righe e colonne.
• Registra (Registra) – stringa di matrice unidimensionale, costituita da diversi elementi di campo con nomi, ad esempio [Nome = “Masha”, Sesso = “f”, Età = 25]
  
• Lista – una colonna array unidimensionale, costituita ad esempio da più elementi {1, 2, 3, 10, 42} or { "Fede Speranza Amore" }
  

Per risolvere il nostro problema, saremo principalmente interessati al tipo Lista.

Il trucco qui è che gli elementi dell'elenco in Power Query possono essere non solo numeri o testo banali, ma anche altri elenchi o record. È in un elenco (elenco) così complicato, composto da record (record) che dobbiamo trasformare la nostra directory. Nella notazione sintattica di Power Query (voci tra parentesi quadre, elenchi tra parentesi graffe) sarebbe simile a:

{

    [ Trova = “S. Pietroburgo”, Sostituisci = “St. Pietroburgo”] ,

    [ Trova = “S. Pietroburgo”, Sostituisci = “St. Pietroburgo”] ,

    [ Trova = “Pietro”, Sostituisci = “S. Pietroburgo”] ,

ecc.

}

Tale trasformazione viene eseguita utilizzando una funzione speciale del linguaggio M integrato in Power Query: Tabella.ToRecords. Per applicarlo direttamente nella barra della formula, aggiungi questa funzione al codice del passaggio lì Fonte.

Era:

Dopo:

Dopo aver aggiunto la funzione Table.ToRecords, l'aspetto della nostra tabella cambierà: si trasformerà in un elenco di record. Il contenuto dei singoli record può essere visualizzato nella parte inferiore del riquadro di visualizzazione facendo clic sullo sfondo della cella accanto a qualsiasi parola Record (ma non in una sola parola!)

Oltre a quanto sopra, ha senso aggiungere un altro tratto: per memorizzare nella cache (buffer) il nostro elenco creato. Ciò forzerà Power Query a caricare il nostro elenco di ricerca una volta in memoria e a non ricalcolarlo nuovamente quando in seguito lo accediamo per sostituirlo. Per fare ciò, avvolgi la nostra formula in un'altra funzione: List.Buffer:

Tale memorizzazione nella cache darà un notevole aumento della velocità (di diverse volte!) Con una grande quantità di dati iniziali da cancellare.

Questo completa la preparazione del manuale.

Resta da cliccare Home – Chiudi e carica – Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in..), seleziona un'opzione Basta creare una connessione (Crea solo connessione) e tornare a Excel.

Passaggio 2. Caricamento della tabella dati

Tutto è banale qui. Come prima con il libro di consultazione, saliamo in qualsiasi punto della tabella, facciamo clic sulla scheda Dati pulsante Dalla tabella/intervallo e la nostra tavola Dati entra in Power Query. Passaggio aggiunto automaticamente tipo modificato (Tipo modificato) puoi anche rimuovere:

Non sono necessarie azioni preparatorie speciali e passiamo alla cosa più importante.

Passaggio 3. Eseguire le sostituzioni utilizzando la funzione List.Accumulate

Aggiungiamo una colonna calcolata alla nostra tabella di dati usando il comando Aggiunta di una colonna – Colonna personalizzata (Aggiungi colonna — Colonna personalizzata): e inserisci il nome della colonna aggiunta nella finestra che si apre (ad esempio, indirizzo corretto) e la nostra funzione magica Elenca.Accumula:

Resta da cliccare OK – e otteniamo una colonna con le sostituzioni effettuate:

Nota che:

• Poiché Power Query fa distinzione tra maiuscole e minuscole, non c'è stata alcuna sostituzione nella penultima riga, perché nella directory abbiamo "SPb", non "SPb".
• Se ci sono più sottostringhe da sostituire contemporaneamente nei dati di origine (ad esempio, nella 7a riga è necessario sostituire sia "S-Pb" che "Prospetto"), ciò non crea alcun problema (a differenza della sostituzione con formule da metodo precedente).
• Se non c'è nulla da sostituire nel testo di origine (9a riga), non si verificano errori (a differenza, ancora, della sostituzione con formule).

La velocità di una tale richiesta è molto, molto decente. Ad esempio, per una tabella di dati iniziali con una dimensione di 5000 righe, questa query è stata aggiornata in meno di un secondo (senza buffering, tra l'altro, circa 3 secondi!)

Come funziona la funzione List.Accumulate

In linea di principio, questa potrebbe essere la fine (per me da scrivere e per te da leggere) questo articolo. Se vuoi non solo essere in grado di farlo, ma anche capire come funziona "sotto il cofano", allora dovrai immergerti un po' più a fondo nella tana del coniglio e occuparti della funzione List.Accumulate, che ha fatto tutta la sostituzione di massa lavora per noi.

La sintassi per questa funzione è:

=Elenco.Accumula(stratagemma, seme, accumulatore)

where

• stratagemma è l'elenco su cui stiamo iterando gli elementi. 
• seme - stato iniziale
• accumulatore – una funzione che esegue alcune operazioni (matematiche, testuali, ecc.) sull'elemento successivo della lista e accumula il risultato dell'elaborazione in un'apposita variabile.
  

In generale, la sintassi per la scrittura di funzioni in Power Query è simile alla seguente:

(argomento1, argomento2, …argomentoN) => alcune azioni con argomenti

Ad esempio, la funzione di somma può essere rappresentata come:

(a, b) => a + b

Per List.Accumulate , questa funzione di accumulatore ha due argomenti obbligatori (possono essere nominati in qualsiasi modo, ma i nomi usuali sono stato и corrente, come nella guida ufficiale per questa funzione, dove:

• stato – una variabile in cui viene accumulato il risultato (il suo valore iniziale è quello sopra menzionato seme)
  
• corrente – il valore iterato successivo dall'elenco stratagemma
  

Ad esempio, diamo un'occhiata ai passaggi della logica della seguente costruzione:

=Elenco.Accumula({3, 2, 5}, 10, (stato, corrente) => stato + corrente)

Questo è l'ultimo accumulato stato il valore è la nostra funzione List.Accumulate e restituisce come risultato:

Se si fantastica un po', poi utilizzando la funzione List.Accumulate si può simulare, ad esempio, la funzione di Excel CONCATENATE (in Power Query il suo analogo si chiama Testo.Combina) usando l'espressione:

O anche cercare il valore massimo (imitazione della funzione MAX di Excel, che in Power Query viene chiamata Lista.Max):

Tuttavia, la caratteristica principale di List.Accumulate è la capacità di elaborare non solo semplici elenchi di testo o numerici come argomenti, ma oggetti più complessi, ad esempio elenchi da elenchi o elenchi da record (ciao, Directory!)

Esaminiamo nuovamente la costruzione che ha eseguito la sostituzione nel nostro problema:

Lista.Accumulate(elenco, [Indirizzo], (stato,corrente) => Text.Replace(stato, corrente[Trova], corrente[Sostituisci]) )

Cosa sta succedendo davvero qui?

1. Come valore iniziale (seme) prendiamo il primo testo goffo dalla colonna [Indirizzo] la nostra tavola: 199034, San Pietroburgo, str. Beringa, m. 1
2. Quindi List.Accumulate scorre gli elementi dell'elenco uno per uno - Manuale. Ciascun elemento di questa lista è un record costituito da una coppia di campi “Cosa trovare – Con cosa sostituire” o, in altre parole, la riga successiva della directory.
3. La funzione accumulatore inserisce in una variabile stato valore iniziale (primo indirizzo 199034, San Pietroburgo, str. Beringa, m. 1) ed esegue su di esso una funzione di accumulatore: l'operazione di sostituzione utilizzando la funzione M standard Testo.Sostituisci (analogo alla funzione SOSTITUZIONE di Excel). La sua sintassi è:

   Text.Replace( testo originale, cosa stiamo cercando, con cosa stiamo sostituendo)

   e qui abbiamo:

• stato è il nostro indirizzo sporco, che si trova in stato (arrivando da seme)
  
• corrente[Cerca] – valore del campo Trovare dalla voce successiva iterata dell'elenco elenco, che si trova nella variabile corrente
• corrente[Sostituisci] – valore del campo Sostituire dalla voce successiva iterata dell'elenco elencoche giace in corrente

Pertanto, per ogni indirizzo, viene eseguito ogni volta un ciclo completo di enumerazione di tutte le righe della directory, sostituendo il testo del campo [Trova] con il valore del campo [Sostituisci].

Spero che tu abbia avuto l'idea 🙂

• Sostituisci in blocco il testo in un elenco usando le formule
  
• Espressioni regolari (RegExp) in Power Query