Reemplazo de texto masivo en Power Query con la función List.Accumulate

Cómo reemplazar de forma rápida y masiva el texto de acuerdo con la lista de referencias con fórmulas: ya lo hemos solucionado. Ahora intentemos hacerlo en Power Query.

como suele pasar realizar esta tarea es mucho más fácil que explicar porque funciona, pero intentemos hacer ambas cosas 🙂

Entonces, tenemos dos tablas dinámicas "inteligentes" creadas a partir de rangos ordinarios con un atajo de teclado Ctrl+T o equipo Inicio – Formato como tabla (Inicio — Formatear como tabla):

Llamé a la primera mesa Datos, la segunda mesa – Directoriousando el campo Nombre de la tabla (Nombre de la tabla) de la pestaña. Constructor (Diseño).

Tarea: reemplazar en direcciones en la tabla Datos todas las ocurrencias de una columna Encontrar Manual a sus correspondientes contrapartes correctas de la columna Suplente. El resto del texto en las celdas debe permanecer intacto.

Paso 1. Cargue el directorio en Power Query y conviértalo en una lista

Habiendo establecido la celda activa en cualquier lugar en la tabla de referencia, haga clic en la pestaña Datos (Fecha)o en la pestaña Power Query (si tiene una versión antigua de Excel e instaló Power Query como complemento en una pestaña separada) en el botón De tabla/rango (De tabla/rango).

La tabla de referencia se cargará en el editor de consultas de Power Query:

Para no interferir, un paso añadido automáticamente tipo modificado (Tipo cambiado) en el panel derecho, los pasos aplicados se pueden eliminar de forma segura, dejando solo el paso Fuente (Fuente):

Ahora, para realizar más transformaciones y reemplazos, necesitamos convertir esta tabla en una lista (lista).

Digresión lírica

• Mesa es una matriz bidimensional que consta de varias filas y columnas.
• Grabar (Grabar) – cadena de matriz unidimensional, que consta de varios elementos de campo con nombres, por ejemplo [Nombre = “Masha”, Sexo = “f”, Edad = 25]
  
• Lista – una columna de matriz unidimensional, que consta de varios elementos, por ejemplo {1, 2, 3, 10, 42} or { "Fe Esperanza Amor" }
  

Para resolver nuestro problema, nos interesará principalmente el tipo Lista.

El truco aquí es que los elementos de la lista en Power Query pueden ser no solo números o texto banales, sino también otras listas o registros. Es en una lista tan complicada (lista), que consta de registros (registros) que necesitamos convertir en nuestro directorio. En la notación sintáctica de Power Query (entradas entre corchetes, listas entre corchetes) esto se vería así:

{

    [ Buscar = “St. Petersburgo”, Reemplazar = “St. Petersburgo” ] ,

    [ Buscar = “St. Petersburgo”, Reemplazar = “St. Petersburgo” ] ,

    [ Buscar = “Peter”, Reemplazar = “St. Petersburgo” ] ,

etc.

}

Tal transformación se realiza utilizando una función especial del lenguaje M integrado en Power Query: Tabla.ToRecords. Para aplicarlo directamente en la barra de fórmulas, agregue esta función al código de paso allí Fuente.

Era:

Después:

Después de agregar la función Table.ToRecords, la apariencia de nuestra tabla cambiará: se convertirá en una lista de registros. El contenido de los registros individuales se puede ver en la parte inferior del panel de visualización haciendo clic en el fondo de la celda junto a cualquier palabra Record (¡pero no en una sola palabra!)

Además de lo anterior, tiene sentido agregar un trazo más: almacenar en caché (buffer) nuestra lista creada. Esto obligará a Power Query a cargar nuestra lista de búsqueda una vez en la memoria y no volver a calcularla cuando accedamos a ella más tarde para reemplazarla. Para hacer esto, envuelva nuestra fórmula en otra función: Lista.Buffer:

Tal almacenamiento en caché dará un aumento muy notable en la velocidad (¡varias veces!) Con una gran cantidad de datos iniciales para borrar.

Esto completa la preparación del manual.

Queda por hacer clic en Inicio – Cerrar y cargar – Cerrar y cargar a… (Inicio — Cerrar y cargar — Cerrar y cargar a...), Seleccione una opción Solo crea una conexión (Solo crear conexión) y volver a Excel.

Paso 2. Cargando la tabla de datos

Todo es trillado aquí. Como antes con el libro de referencia, subimos a cualquier lugar de la tabla, hacemos clic en la pestaña Datos . Desde tabla/rango y nuestra mesa Datos entra en Power Query. Paso añadido automáticamente tipo modificado (Tipo cambiado) también puedes eliminar:

No se requieren acciones preparatorias especiales para hacer con él, y pasamos a lo más importante.

Paso 3. Realice reemplazos usando la función List.Accumulate

Agreguemos una columna calculada a nuestra tabla de datos usando el comando Agregar una columna: columna personalizada (Añadir columna — Columna personalizada): e ingrese el nombre de la columna agregada en la ventana que se abre (por ejemplo, dirección corregida) y nuestra función mágica Lista.Acumular:

Queda por hacer clic en OK – y nos sale una columna con las sustituciones realizadas:

Tenga en cuenta que:

• Dado que Power Query distingue entre mayúsculas y minúsculas, no hubo reemplazo en la penúltima línea, porque en el directorio tenemos "SPb", no "SPb".
• Si hay varias subcadenas para reemplazar a la vez en los datos de origen (por ejemplo, en la séptima línea debe reemplazar tanto "S-Pb" como "Folleto"), esto no crea ningún problema (a diferencia de reemplazar con fórmulas de el método anterior).
• Si no hay nada que reemplazar en el texto de origen (novena línea), entonces no se producen errores (a diferencia, de nuevo, del reemplazo por fórmulas).

La velocidad de tal solicitud es muy, muy decente. Por ejemplo, para una tabla de datos iniciales con un tamaño de 5000 filas, esta consulta se actualizó en menos de un segundo (sin almacenamiento en búfer, por cierto, ¡unos 3 segundos!)

Cómo funciona la función List.Accumulate

En principio, este podría ser el final (para que yo escriba y usted lea) este artículo. Si desea no solo poder, sino también comprender cómo funciona "bajo el capó", entonces tendrá que sumergirse un poco más en la madriguera del conejo y lidiar con la función List.Accumulate, que hizo todo el reemplazo masivo trabaja para nosotros.

La sintaxis de esta función es:

=Lista.Acumular(lista, dispersores , acumulador)

donde

• lista es la lista cuyos elementos estamos iterando. 
• dispersores - estado inicial
• acumulador – una función que realiza alguna operación (matemática, texto, etc.) en el siguiente elemento de la lista y acumula el resultado del procesamiento en una variable especial.
  

En general, la sintaxis para escribir funciones en Power Query se ve así:

(argumento1, argumento2, … argumentoN) => algunas acciones con argumentos

Por ejemplo, la función de suma podría representarse como:

(a, b) => a + b

Para List.Accumulate , esta función de acumulador tiene dos argumentos obligatorios (pueden tener cualquier nombre, pero los nombres habituales son estado и corriente, como en la ayuda oficial para esta función, donde:

• estado – una variable donde se acumula el resultado (su valor inicial es el mencionado anteriormente dispersores )
  
• corriente – el siguiente valor iterado de la lista lista
  

Por ejemplo, echemos un vistazo a los pasos de la lógica de la siguiente construcción:

=Lista.Acumular({3, 2, 5}, 10, (estado, actual) => estado + actual)

Este es el último acumulado estado el valor es nuestra función List.Accumulate y genera como resultado:

Si fantasea un poco, entonces, usando la función List.Accumulate, puede simular, por ejemplo, la función de Excel CONCATENAR (en Power Query, su análogo se llama Texto.Combinar) utilizando la expresión:

O incluso buscar el valor máximo (imitación de la función MAX de Excel, que en Power Query se llama Lista.Máx.):

Sin embargo, la característica principal de List.Accumulate es la capacidad de procesar no solo texto simple o listas numéricas como argumentos, sino también objetos más complejos, por ejemplo, listas de listas o listas de registros (¡hola, Directorio!)

Veamos nuevamente la construcción que realizó el reemplazo en nuestro problema:

Lista.Acumular(Directorio, [Dirección], (estado, actual) => Text.Replace (estado, actual [Buscar], actual [Reemplazar]) )

¿Qué está pasando aquí realmente?

1. Como valor inicial (dispersores ) tomamos el primer texto torpe de la columna [Dirección] nuestra mesa: 199034, San Petersburgo, calle. Beringa, D. 1
2. Luego, List.Accumulate itera sobre los elementos de la lista uno por uno: Manual. Cada elemento de esta lista es un registro que consta de un par de campos "Qué encontrar - Qué reemplazar" o, en otras palabras, la siguiente línea en el directorio.
3. La función de acumulador pone en una variable estado valor inicial (primera dirección 199034, San Petersburgo, calle. Beringa, D. 1) y realiza una función de acumulador en él: la operación de reemplazo usando la función M estándar Texto.Reemplazar (análogo a la función SUSTITUIR de Excel). Su sintaxis es:

   Text.Replace (texto original, lo que estamos buscando, con lo que estamos reemplazando)

   y aquí tenemos:

• estado es nuestra dirección sucia, que se encuentra en estado (llegar desde dispersores )
  
• actual[Buscar] – valor del campo Encontrar de la siguiente entrada iterada de la lista Directorio, que se encuentra en la variable corriente
• actual[Reemplazar] – valor del campo Suplente de la siguiente entrada iterada de la lista Directorioacostado en corriente

Por lo tanto, para cada dirección, se ejecuta un ciclo completo de enumeración de todas las líneas en el directorio cada vez, reemplazando el texto del campo [Buscar] con el valor del campo [Reemplazar].

Espero que hayas entendido la idea 🙂

• Reemplazo masivo de texto en una lista usando fórmulas
  
• Expresiones regulares (RegExp) en Power Query