Substitució de text massiu a Power Query amb la funció List.Acumulate

Com substituir de manera ràpida i massiva el text segons la llista de referència per fórmules: ja ho hem resolt. Ara intentem fer-ho a Power Query.

Com passa sovint realitzar aquesta tasca és molt més fàcil que explicar Per què funciona, però intentem fer les dues coses 🙂

Per tant, tenim dues taules dinàmiques "intel·ligents" creades a partir d'intervals normals amb una drecera de teclat Ctrl+T o equip Inici – Format com a taula (Inici — Format com a taula):

Vaig trucar a la primera taula dades, la segona taula - directoriutilitzant camp Nom de la taula (Nom de la taula) llengüeta constructor (Disseny).

Tasca: substituir les adreces de la taula dades totes les ocurrències d'una columna Trobar Instruccions als seus corresponents homòlegs correctes de la columna Suplent. La resta del text de les cel·les ha de romandre intacte.

Pas 1. Carregueu el directori a Power Query i convertiu-lo en una llista

Després d'haver establert la cel·la activa a qualsevol lloc de la taula de referència, feu clic a la pestanya dades (Data)o a la pestanya Power Consulta (si teniu una versió antiga d'Excel i heu instal·lat Power Query com a complement en una pestanya independent) al botó De taula/gama (Des de la taula/gama).

La taula de referència es carregarà a l'editor de consultes de Power Query:

Per no interferir, un pas afegit automàticament tipus modificat (Tipus canviat) al tauler dret, els passos aplicats es poden suprimir de manera segura, deixant només el pas font (Font):

Ara, per realitzar més transformacions i substitucions, hem de convertir aquesta taula en una llista (llista).

Digressió lírica

• Taula és una matriu bidimensional que consta de diverses files i columnes.
• Registre (enregistrament) – matriu-cadena unidimensional, que consta de diversos camps-elements amb noms, per exemple [Nom = "Masha", Sexe = "f", Edat = 25]
  
• llista – una matriu-columna unidimensional, formada per diversos elements, per exemple {1, 2, 3, 10, 42} or { "Fe esperança Amor" }
  

Per resoldre el nostre problema, ens interessarà principalment el tipus llista.

El truc aquí és que els elements de la llista de Power Query poden ser no només números o text banals, sinó també altres llistes o registres. És en una llista (llista) tan complicada, que consta de registres (registres) que hem de girar el nostre directori. En la notació sintàctica de Power Query (entrades entre claudàtors, llistes entre claudàtors) això seria el següent:

{

    [ Trobar = “St. Petersburg", Reemplaça = "St. Petersburg”] ,

    [ Trobar = “St. Petersburg", Reemplaça = "St. Petersburg”] ,

    [ Cerca = "Pere", Reemplaça = "St. Petersburg”] ,

etcètera...

}

Aquesta transformació es realitza mitjançant una funció especial del llenguatge M integrada a Power Query: Taula.ToRecords. Per aplicar-lo directament a la barra de fórmules, afegiu-hi aquesta funció al codi de pas font.

Va ser:

Després:

Després d'afegir la funció Table.ToRecords, l'aspecte de la nostra taula canviarà: es convertirà en una llista de registres. El contingut dels registres individuals es pot veure a la part inferior del panell de visualització fent clic al fons de la cel·la al costat de qualsevol paraula Registre (però no en una sola paraula!)

A més de l'anterior, té sentit afegir un cop més: per emmagatzemar a la memòria cau (bufer) la nostra llista creada. Això obligarà a Power Query a carregar la nostra llista de cerca una vegada a la memòria i no tornar-la a calcular quan més tard hi accedim per substituir-la. Per fer-ho, emboliqui la nostra fórmula en una altra funció: Llista.Buffer:

Aquesta memòria cau donarà un augment molt notable de la velocitat (en diverses vegades!) Amb una gran quantitat de dades inicials per esborrar.

Això completa la preparació del manual.

Queda per clicar Inici – Tancar i carregar – Tancar i carregar a… (Inici — Tancar i carregar — Tancar i carregar a..), seleccioneu una opció Només cal crear una connexió (Només crea connexió) i tornar a Excel.

Pas 2. Carregant la taula de dades

Aquí tot és banal. Com abans amb el llibre de referència, ens aixequem a qualsevol lloc de la taula, clicem a la pestanya dades botó De Taula/Range i la nostra taula dades entra a Power Query. Pas afegit automàticament tipus modificat (Tipus canviat) també pots eliminar:

No cal fer-hi cap acció preparatòria especial, i passem al més important.

Pas 3. Realitzeu substitucions mitjançant la funció List.Acumulate

Afegim una columna calculada a la nostra taula de dades mitjançant l'ordre Afegir una columna: columna personalitzada (Afegeix columna: columna personalitzada): i introduïu el nom de la columna afegida a la finestra que s'obre (per exemple, adreça corregida) i la nostra funció màgica Llista.Acumular:

Queda per clicar OK – i obtenim una columna amb les substitucions fetes:

Tingues en compte que:

• Com que Power Query distingeix entre majúscules i minúscules, no hi va haver cap substitució a la penúltima línia, perquè al directori tenim "SPb", no "SPb".
• Si hi ha diverses subcadenes per substituir alhora a les dades d'origen (per exemple, a la setena línia cal substituir tant "S-Pb" com "Fullet"), això no crea cap problema (a diferència de la substitució per fórmules de el mètode anterior).
• Si no hi ha res a substituir al text d'origen (9a línia), no es produeixen errors (a diferència, de nou, de la substitució per fórmules).

La velocitat d'aquesta petició és molt, molt decent. Per exemple, per a una taula de dades inicials amb una mida de 5000 files, aquesta consulta es va actualitzar en menys d'un segon (sense emmagatzemar a la memòria intermèdia, per cert, uns 3 segons!)

Com funciona la funció List.Acumulate

En principi, aquest podria ser el final (per a mi per escriure, i per a tu per llegir) aquest article. Si no només voleu poder, sinó també entendre com funciona "sota el capó", haureu de submergir-vos una mica més en el forat del conill i fer front a la funció List.Acumulate, que va fer tota la substitució a granel. treballar per a nosaltres.

La sintaxi d'aquesta funció és:

=Llista.Acumular(llista, llavor, acumulador)

where

• llista és la llista dels elements de la qual estem iterant. 
• llavor - estat inicial
• acumulador – una funció que realitza alguna operació (matemàtica, text, etc.) sobre el següent element de la llista i acumula el resultat del processament en una variable especial.
  

En general, la sintaxi per escriure funcions a Power Query té aquest aspecte:

(argument1, argument2, … argumentN) => algunes accions amb arguments

Per exemple, la funció de suma es podria representar com:

(a, b) => a + b

Per a List.Accumulate , aquesta funció acumuladora té dos arguments obligatoris (es poden anomenar qualsevol cosa, però els noms habituals són van ser и corrent, com a l'ajuda oficial d'aquesta funció, on:

• van ser – una variable on s'acumula el resultat (el seu valor inicial és l'esmentat anteriorment llavor)
  
• corrent – el següent valor iterat de la llista llista
  

Per exemple, fem una ullada als passos de la lògica de la construcció següent:

=Llista.Acumular({3, 2, 5}, 10, (estat, corrent) => estat + corrent)

Aquest és el darrer acumulat van ser el valor és la nostra funció List.Acumulate i les sortides com a resultat:

Si fantasiegeu una mica, usant la funció Llista.Acumular, podeu simular, per exemple, la funció d'Excel CONCATENAR (a Power Query, el seu anàleg s'anomena Text.Combinar) utilitzant l'expressió:

O fins i tot cercar el valor màxim (imitació de la funció MAX d'Excel, que a Power Query s'anomena Llista. Màx):

Tanmateix, la característica principal de List.Accumulate és la capacitat de processar no només llistes de text o numèriques simples com a arguments, sinó objectes més complexos, per exemple, llistes-de-llistes o llistes-de-registres (hola, Directori!)

Vegem de nou la construcció que va realitzar la substitució en el nostre problema:

Llista.Acumular(directori, [Adreça], (estat,actual) => Text.Reemplaça (estat, actual[Trobar], actual[Reemplaça]) )

Què està passant realment aquí?

1. Com a valor inicial (llavor) agafem el primer text maldestre de la columna [Adreça] la nostra taula: 199034, Sant Petersburg, str. Beringa, d. 1
2. A continuació, List.Acumulate itera sobre els elements de la llista un per un: Instruccions. Cada element d'aquesta llista és un registre format per un parell de camps "Què trobar - Amb què substituir" o, en altres paraules, la següent línia del directori.
3. La funció acumulador posa en una variable van ser valor inicial (primera adreça 199034, Sant Petersburg, str. Beringa, d. 1) i hi realitza una funció d'acumulador: l'operació de substitució utilitzant la funció M estàndard Text.Reemplaça (anàloga a la funció SUBSTITUTE d'Excel). La seva sintaxi és:

   Text.Replace (text original, què estem buscant, amb què estem substituint)

   i aquí tenim:

• van ser és la nostra adreça bruta, que es troba a van ser (Arribant-hi des de llavor)
  
• actual[Cerca] - valor del camp Trobar de la següent entrada iterada de la llista directori, que rau a la variable corrent
• actual[Reemplaçar] - valor del camp Suplent de la següent entrada iterada de la llista directoriestirat a dins corrent

Així, per a cada adreça, s'executa cada cop un cicle complet d'enumeració de totes les línies del directori, substituint el text del camp [Troba] pel valor del camp [Reemplaça].

Espero que hagis tingut la idea 🙂

• Substituïu de manera massiva el text d'una llista mitjançant fórmules
  
• Expressions regulars (RegExp) a Power Query