Continuamos nuestra serie de tips de power query con un doble tip que nos puede ahorrar muchísimo trabajo.
¿Os habéis encontrado alguna vez la necesidad de reemplazar varias cadenas de valores en varias columnas diferentes?
Al final nos encontramos un montón de pasos de valor reemplazado. Vamos a ver algunos trucos para automatizar este proceso.
Pues bien, nos encontramos con una base de datos en la que en origen nos presenta las vocales acentuadas sin tilde y entre @ y la Ñ la muestra como @N@
Aquí un ejemplo
Y ese error tipográfico en origen aparece en cada uno de los campos de texto en los que hay elementos con tilde o Ñ. Haciéndolo manualmente tendría que hacer mínimo 6 transformaciones por cada tabla, contando con que tengo que modificar el código para coger más de una columna donde hacerlo o seleccionarlas una a una.
Vamos a ver la forma de:
- Aplicar una función para que me realice todos los reemplazos a la vez.
- Que esa función se aplique en cada una de las columnas que yo seleccione de una manera más sencilla que con ctrl: Eligiendo las columnas con elegir columna.
La función de reemplazo.
1º Vamos a crear una tabla con los reemplazos con una columna Find y otra Replace
Quedaría así:
Y este es el código usado con Especificar Datos:
let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnB0UNJROtyoFKsD5LiCOZ0QjieY0wvh+IM5kyGcUDBnFoTjB+ZMVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t])
in
Origen
En 2º lugar creamos nuestra función.
La función la he cogido del blog de Jhon MacDougall, MVP Microsoft https://www.howtoexcel.org/power-query/bulk-replace-values/ y es relativamente sencilla de entender. He puesto en castellano los comentarios para mejor comprensión:
¡!IMPORTANTE!! siempre que compartáis en la red algo que no es vuestro nunca está de más hacer la mención de la fuente.
let
BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convertimos la tabla FindReplaceTable en una lista usando la función Table.ToRows
FindReplaceList = Table.ToRows(FindReplaceTable),
//Contamos el numero de filas en la tabla FindReplaceTable para determinar
//cuantas iteraciones necesitaremos
Counter = Table.RowCount(FindReplaceTable),
//Definimos una función de iteración sobre la lista
//con la función Table.ReplaceValue
BulkReplaceValues = (DataTableTemp, n) =>
let
//Reemplazamos los valores usando usando el nth item en FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
/reemplazamos nulos por vacíos en 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
//Si no estamos en el final de FindReplaceList
//entonces seguimos iterando Table.ReplaceValue de nuevo
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
/evaluamos la subfunción desde la primera fila
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace
Ya tenemos nuestra tabla de reemplazos y tenemos nuestra función para reemplazar, ya solo queda invocar la función.
Para ello insertaremos un nuevo paso en la tabla donde queramos realizar el reemplazo.
A nuestra tabla de reemplazos la he llamado “Reemplazos” y a la función la he llamado fxReemplazar
Pulso en fx tras el último paso y llamo a la función de la siguiente forma:
= fxReemplazar(#»Tipo cambiado», Reemplazos, {«DAT_ENTIDAD_FEDERATIVA»})
Donde fxReemplazar es el nombre de nuestra función
“Tipo cambiado” el nombre del último paso
Reemplazos el nombre de la tabla donde tengo los valores que quiero reemplazar
y DAT_ENTIDAD_FEDERATIVA es la columna sobre la que quiero hacer los reemplazos.
El primer objetivo conseguido: reemplazar varios valores al mismo tiempo sin tener que llamar cada vez a la función de reemplazar valores, pero lo hemos hecho sobre una sola columna y queremos hacerla sobre varias al mismo tiempo. Para ello vamos a poner en juego la función Table.ColumnNames que devuelve una lista de los nombres de las columnas de la tabla.
Si en el código sustituimos {«DAT_ENTIDAD_FEDERATIVA»}) por Table.ColumnNames(#»Tipo cambiado») realizaría la sustitución por todas y cada uno de las columnas de nuestra tabla tal y como estuviera en el paso anterior.
El problema de esto es que en todas aquellas columnas en las que el tipo de datos no fuera texto nos daría error:
Lo que vamos a hacer entonces es crear un paso anterior en el que con “Elegir columnas” vamos a elegir aquellas donde queremos que nos realice la búsqueda y reemplazo
Automáticamente nuestro siguiente paso de invocación de la fórmula cambia a esta manera
= fxReemplazar(#»Otras columnas quitadas», Reemplazos, Table.ColumnNames(#»Otras columnas quitadas»))
Y nuestra nueva tabla nos muestra las columnas que hemos elegido con las sustituciones realizadas:
Obsérvese Estado de México correctamente.
Pero claro, necesitamos todas las columnas que hemos perdido al elegir las columnas, pero con Power Query podemos hacer un poco de magia y para obviar el paso de Elegir columnas, pero sin eliminarlo porque nos sirve llamar las columnas donde queremos realizar los reemplazos, simplemente cambiando el nombre del paso de origen en nuestra función obtenemos nuestro objetivo.
Es decir en = fxReemplazar(#»Otras columnas quitadas», Reemplazos, Table.ColumnNames(#»Otras columnas quitadas»))
Sustituimos #»Otras columnas quitadas», por #»Tipo cambiado», que es justo el paso anterior a la elección de columnas de manera que nos saltamos el paso de elección de columnas, que no lo tiene en cuenta como tal, pero si lo tiene en cuenta a la hora de realizar el reemplazo
Este sería como quedaría definitivamente nuestro código.
Os adjunto un pbix con un set de datos más pequeñito pero en el que podéis ver los resultados ya que no puedo pasaros el contenido del original sobre el que he hecho el ejemplo.
3 comentarios