No hay nada más molesto para hacer informes o análisis que datos sumamente sucios. Con esto me refiero a malos ingresos de datos, normalmente proveniente de encuestas u hojas de cálculo.
Power Query es una buena herramienta de ETL pero es importante usarla bien para no reventar de pasos insostenibles en nuestro script. Para eso ya escribimos un post que nos ayude a reducir pasos. Lo que veremos en este artículo esta enfocado en simular lo que podemos hacer dentro de "Agregar Columna Personalizada" pero transformando la columna que necestamos limpiar sin crear otra columna con el código personalizado deseado.
En el proceso iremos agregando codiciones varias para ver el poder que tenemos.
Antes de iniciar me gustaría aclarar que este artículo mostrará técnicas avanzadas de power query para usarse como ETL en respuesta a procesamiento de datos. Eso no quita que haya mejores prácticas. Nada superará a hacer el procesamiento en un único origen de verdad como warehouse o lakehouse que sería la mejor de las prácticas.
¿Cuántes veces tuvieron que crear una columna personalizada en power query porque no existía un modo en la interfaz para reemplazar valores con un sencillo if?
Me quedó media larga la pregunta pero ciertamente ocurre que a veces necesitamos limpiar un conjunto de datos con una columna numérica mal escriba y caemos en hacer muchisimas operaciones de "Reemplazar Valores". Lo cierto es que reemplazar valores solo cambia una cadena de texto por otra. Eso esta bien para cosas pequeñas como errores de tipeo tradicionales. Sin embargo, con una condición más complicado, digamos, si queremos que salga un determinado texto tras encontrar una determinada cadena sin reemplazarla, entonces se complicaría. Ejemplo, cada vez que encontremos el texto "hombr" debería salír "Varón". Si aparece homb, hombre, hombrrrre, hombre pues, hombrecito, hombreton o algo similar, lo reconocería como hombre y le pondríamos "Varón".
Vamos a ver tres ejemplos de reemplazo y limpiezas.
Supongamos que enviamos una encuesta de sueldos a un grupo de personas que trabajan remoto. Tenemos una tabla con salarios y una descripción que no todos llenan sobre el pago en dólares.
Lo primero que podríamos pensar es agregar otra columna más limpia, pero podríamos limpiar esta. ¿Qué tal si reemplazamos "USD" cuando encuentra la palabra "dólares" y "ARS" cuando no lo hace?
Veamos el proceso. Cuando queremos reemplazar bajo condición en una columna necesitamos usar la función ReplaceValues de tabla. Veamos la teoría:
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
Esta función nos deja reemplazar una cadena oldValue (en este caso el mismo valor de la columna porque queremos reemplazar cada aparición sin importar su valor) con un newValue (resultado de una condición que armemos) en una clásica condición de reemplazo Replacer y la columna columnsToSearch en la cual buscará el oldValue para cambiar por el newValue. Basados en la tabla anterior nos quedaría algo así:
= Table.ReplaceValue(
#"Paso Anterior",
each [#"Pagos en dólares"] ,
each if Text.Contains([#"Pagos en dólares"], "dólares")
then "ARS"
else "USD",
Replacer.ReplaceValue,{"Pagos en dólares"}
)
De este modo en cada aparición del valor propio de la columna hace un reemplazo de lo que tenga por la condición elegida. Dejandonos con un pobre pero inicial resultado:
La lógica se cumplió y aprendimos a hacer un reemplazo en la columna. Sin embargo, la limpieza fue medio pobre y no hemos contemplado los escenarios correctamente. Recordemos que tenemos muchos null y también hay casos que tienen "Parte del salario en dolares", lo cual no quedaría contemplado con ARS y USD.
Para mejorar nuestro limpieza sobre la columna vamos a realizar una condición entre las dos columnas. Por vivir en este páis tengo claro que no existe un Salario menos a 15000 ARS en la industria remota de tiempo completo (FullTime) y sería también dificil que una persona tenga un salario de 15000 USD mensuales.. Entonces voy a usar ese conocimiento para limpiar con una condición numérica la primera elección y luego preguntar por la palabra "parte" cuando recibie en ambas monedas el salario.
Veamos el caso
= Table.ReplaceValue(
#"Renamed Columns",
each [#"Pagos en dólares"],
each if [Salario]< 15000
then "USD"
else if Text.Contains([#"Pagos en dólares"], "parte") and [#"Pagos en dólares"] <> null
then "Híbrido"
else "ARS",
Replacer.ReplaceValue,
{"Pagos en dólares"}
)
Hacemos la primera condición coladores si el número es menor a 15000 entonces USD. Para la segunda condición en el if vamos a ir por "parte" y sumamos que no sea nulo porque sino Text.Contains ignora preguntar a los nulos y esas filas quedarían nulas aunque existiera el else.
De este modo el reemplazo quedaría más completo dejando nuestra columna con todas las opciones posibles:
Condición IN SQL en Power Query
Los casos anteriores suelen ser un clásico, pero que ocurre cuando tenemos algo más complejo. Cuando necesitamos una serie de reemplazos masivos del estilo "IN" de SQL. Por ejemplo, veamos la siguiente imagen y digamos que necesitamos reemplazar todos las apariciones de cadenas de texto que conlleven a Hombre, Varón, Macho y Masculino. Algo tipo hombr, var, mach, masc. Si encontramos algo con eso, entonces reemplacemos por "Varón".
La condición semejante a IN en SQL se construye con List.Contains. Veamos la teoría:
List.Contains(list as list, value as any, optional equationCriteria as any) as logical
La función necesita una lista de valores bajo los cuales comparar y el valor a recibir. Dicho de otro modo si algun string de la lista coincide con value entonces true.
Para poder realizar esta compleja tarea necesitamos dos operaciones. Por un lado construir la lista de valores a reemplazar en cada valor de nuestra columna puesto que los comparadores masivos hacen comparación de valores exactos. Dicho de otro modo el reemplazo ejecutado se vería tipo:
List.Contains({"hombre", "Hombre", "Hombre.", "Macho lomo plateado", "Masculino", "[entre otros….]"} , [#"Me identifico (Género)"])
Necesitamos construir esa lista de manera tal que por cada coincidencia podamos reemplazarlo por "Varón".
En nuestro editor de consulta vamos a crear una variable. Una variable no es más que un paso más que no está relacionado con el "Paso anterior" y vive en nuestro script para usarlo. La generación de la lista sería filtrar la tabla por valores únicos cuando el texto contenga lo deseado y convertirlo a lista. Veamos:
Lista_de_varones = Table.ToList(
Table.SelectRows(
Table.Distinct(#"Paso Origen"[[#"Me identifico (género)"]]),
each (Text.Contains(Text.Lower([#"Me identifico (género)"]), "hombr")
or Text.Contains(Text.Lower([#"Me identifico (género)"]), "var")
or Text.Contains(Text.Lower([#"Me identifico (género)"]), "mach")
or Text.Contains(Text.Lower([#"Me identifico (género)"]), "masc")
) and (not Text.Contains(Text.Lower([#"Me identifico (género)"]), "trans")
)
)
)
Fijense que si bien hace referencia a un paso anterior, no lo vamos a usar en el siguiente. A la tabla de una única columna género distintiva le filtramos las filas cuando contenga lo antes acordado "hombr", "var", "mach", "masc" y le agregue que no contenga "trans" puesto que sería otro género. Así obtenemos una lista con todos los resultados para nuestro IN de SQL. Son más de 50 resultados, pero solo mostraré algunos porque esto se descontroló
Con esa variable en lista que llamaremos al paso reemplazador que veníamos construyendo para aplicarlo sobre nuestra columna:
= Table.ReplaceValue(
#"Paso Origen",
each [#"Me identifico (género)"],
each if List.Contains( Lista_de_varones, [#"Me identifico (género)"] )
then "Varón"
else [#"Me identifico (género)"],
Replacer.ReplaceText,
{"Me identifico (género)"}
)
Fijense como se acortó la lista. Pasamos de casi 150 valores distintos de género a tener casi 80.
De este modo podríamos repetirlo con apariciones para Mujer, No Binario, Mujer Trans, Varón Trans y dejar al resto en Otro.
Antes de concluir me gustaría hacer incapie en que este es un proceso muy pesado. Recorrer el conjunto para obtener la lista que luego usamos de reemplazo puede tardar mucho si la lista demora en generarse. El reemplazo es rápido pero la lista no. Por ello recomiendo fuertemente hardcodear la lista si el origen de datos es una encuesta cerrada como este caso. Hacemos la ejecución para conocer los valores y ya conociendolos los registramos en otro origen:
Pueden copiar a notepadd++ y generar un macro que ponga comiilas y comas en menos de un minuto.
Ahora si llegamos al final del post y hemos aprendido a reemplazar valores de una columna según condiciones personalizadas en cualquier otra columna de la misma fila inclusive con múltiples reemplazos de porciones de cadenas de texto. Ojalá les sirva para limpiar esos datos sucios que nos llegan.