Buscar y reemplazar a granel en Power Query

Imagine que estamos usando Power Query para limpiar datos y necesitamos reemplazar valores como parte del proceso de transformación.

Para cada valor que queremos reemplazar, debemos crear un paso de Reemplazar valores en la consulta. Si tenemos decenas o incluso cientos de valores para reemplazar, puede ser bastante tedioso.

Si ya tenemos una lista de valores que debemos reemplazar, ¡sería más fácil si pudiéramos hacerlo todo en un solo paso según la lista!

En esta publicación, vamos a aprender cómo realizar una búsqueda y reemplazo masivos en una consulta avanzada en función de una lista de valores.

Descargar archivo de ejemplo

Contenidos
  1. Videotutorial
  2. La puesta en marcha
  3. Uso de valores de reemplazo en Power Query
  4. Código M para la función de consulta
  5. Cómo usar esta función
  6. Conclusiones

Videotutorial

La puesta en marcha

En este ejemplo, tenemos una tabla pequeña con una columna llamada Título del trabajo y la tabla se ha llamado MyData . Estos son los datos que vamos a transformar con nuestra función de buscar y reemplazar.

También tenemos una tabla con dos columnas llamada Buscar y reemplazar y se llama MyFindReplace . Esta es una tabla de los pares de elementos para encontrar y reemplazar.

Las columnas en realidad pueden tener cualquier nombre, ya que no nos referiremos a ellas por su nombre. Lo importante es que la columna de búsqueda está a la izquierda y la columna de reemplazo está a la derecha.

Luego necesitamos importar estas tablas a Power Query. Vaya a la pestaña Datos y luego presione el comando Desde tabla/rango para importarlos al editor de consultas de energía.

Uso de valores de reemplazo en Power Query

Cuando estamos dentro del editor de consultas de energía, podemos realizar un paso de Reemplazar valores desde la pestaña Transformar o el menú contextual .

Seleccione la columna en la que queremos reemplazar valores y vaya a la pestaña Transformar y presione el comando Reemplazar valores .

También podemos acceder a este comando haciendo clic con el botón derecho en el encabezado de la columna y luego seleccionando Reemplazar valores en el menú.

Cualquiera de estas opciones abrirá el menú Reemplazar valores y luego podemos ingresar el Valor para buscar y el valor para Reemplazar con .

Esto luego reemplazará cada instancia de this en toda la columna.

= Table.ReplaceValue(#"Changed Type","Text to find","Text to replace",Replacer.ReplaceText,{"Job Title"})

Si observamos el código M que se genera a partir de esto, podemos ver que está usando la función de consulta de energía Table.ReplaceValue para realizar el reemplazo.

También usaremos esta función para nuestra solución, pero no hay una manera fácil de usar esta función basada en una lista de valores. Tendremos que crear nuestra propia función de consulta personalizada para esto.

Código M para la función de consulta

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =    let        //Convert the FindReplaceTable to a list using the Table.ToRows function        //so we can reference the list with an index number        FindReplaceList = Table.ToRows(FindReplaceTable),        //Count number of rows in the FindReplaceTable to determine        //how many iterations are needed        Counter = Table.RowCount(FindReplaceTable),        //Define a function to iterate over our list         //with the Table.ReplaceValue function        BulkReplaceValues = (DataTableTemp, n) =         let             //Replace values using nth item in FindReplaceList            ReplaceTable = Table.ReplaceValue(                DataTableTemp,                //replace null with empty string in nth item                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},                Replacer.ReplaceText,                DataTableColumn                )        in            //if we are not at the end of the FindReplaceList            //then iterate through Table.ReplaceValue again            if n = Counter - 1                 then ReplaceTable                else @BulkReplaceValues(ReplaceTable, n + 1),        //Evaluate the sub-function at the first row        Output = BulkReplaceValues(DataTable, 0)       in        Outputin    BulkReplace

En una publicación anterior sobre la replicación de la función TRIM de Excel en power query , repasamos los pasos para crear una función de consulta. Tendremos que seguir los mismos pasos con el código M anterior .

Esta función de consulta toma 3 argumentos.

  1. DataTable es la tabla que contiene las columnas en las que queremos buscar y reemplazar valores.
  2. FindReplaceTable es una tabla de dos columnas. La primera columna contiene valores para encontrar y la segunda columna contiene valores para reemplazarlos. Cada fila de la tabla consta de un par de valores de buscar y reemplazar.
  3. DataTableColumn es una lista de los nombres de columna en los que queremos buscar y reemplazar valores.

Luego, la función convierte nuestra FindReplaceTable en una lista de pares de buscar y reemplazar y los iteramos y aplicamos una función Table.ReplaceValue a cada par.

Cómo usar esta función

La forma en que usamos esta función de consulta será un poco diferente a nuestro ejemplo de función TRIM . En ese caso, aplicamos la función a cada fila de una columna agregando una columna personalizada.

Para usar esta función, necesitamos aplicarla a toda la columna.

Necesitamos agregar un paso de consulta. Desde la consulta en la que queremos usar esta función, podemos hacer clic en el pequeño ícono fx a la derecha de la barra de fórmulas.

= fBulkReplace(#"Changed Type", MyFindReplace, {"Job Title"})

Entonces podemos entrar en la función anterior.

El primer argumento hará referencia al paso anterior en la consulta. En nuestro caso, este fue un paso llamado Changed Type .

El segundo argumento hará referencia al nombre de la consulta que contiene la tabla de valores de buscar y reemplazar. En nuestro caso, esto se llamó MyFindReplace .

El tercer argumento hará referencia al nombre de la columna en la que queremos reemplazar los valores. Debe estar en un formato de lista usando llaves. En nuestro caso esto fue {"Job Title"}.

Tenga en cuenta que podríamos aplicar buscar y reemplazar a varias columnas usando este argumento. Por ejemplo, colocar {"Job Title", "Job Description"}el tercer argumento aplicaría buscar y reemplazar a las columnas Título del trabajo y Descripción del trabajo.

Conclusiones

Con algo de ingenio, podemos crear una función que puede realizar pasos de búsqueda y reemplazo masivos basados ​​en una lista de valores.

La clave es crear una función recursiva basada en el índice de la lista de valores de buscar y reemplazar.

Incluso podemos usar esta solución para buscar y reemplazar en varias columnas simultáneamente para ahorrar aún más tiempo.

¡Otro gran uso de las funciones de consulta recursiva en Power Query!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.