Categoría en Pildoras Power Query

Reemplazar varios valores a la vez en varias columnas al mismo tiempo.

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:

  1. Aplicar una función para que me realice todos los reemplazos a la vez.
  2. 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.

Webinar: Simulación de Seguridad a Nivel de Página con formato condicional y RLS

El pasado martes 28 de julio tuve el placer de impartir un webinar para el PUG de Barcelona en el que profundicé en la simulación de seguridad a nivel de página aplicando tanto RLS como el formato condicional en la navegación. Como insistí en el webinar no es seguridad a nivel de página, ya que si se tiene la URL de la página cualquier usuario puede acceder, pero se ocultan de la navegación la posibilidad de ir a las páginas a las que no se tiene permiso y como no es fácil disponer de la URL de las páginas, pues se puede considerar que cumple la función de que ciertos usuarios puedan no acceder a partes de un informe.

Os dejo el enlace al video que el PUG de Barcelona ha subido a Youtube para que podáis verlo.

Considero muy interesante la opción que planteo de crear la tabla de páginas automáticamente utilizando como origen el archivo layout del propio pbix.

La verdad es que estoy muy contento con el resultado final del webinar y el encuentro con posterior ciberquedada que contó con la presencia de Ana María Bisbé fue muy gratificante.

Os dejo también un enlace a los materiales:

https://github.com/PUG-Barcelona/general/tree/master/Eventos/20200728_RLS_Seguridad_P%C3%A1ginas

Webscrapping con Power BI

Hace unas semanas tuve el privilegio de compartir una hora en el Power BI User Group de Panamá con Pablo Moreno y un centenar de asistentes, hablando de webscrapping con Power BI. Repasé varios aspectos de power query como creación de funciones o uso del RelativePath con Web.Contents para poder actualizar los datos. Con permiso de Pablo que gustoso me ha cedido la grabación la ponemos a disposición de quien la necesite.

Píldoras Power Query : Agrupar por la línea más reciente

Una de las necesidades que pueden surgirnos en el tratamiento de la información es el que tengamos que quedarnos con una sola línea de un conjunto de datos agrupados. Por ejemplo, la línea de venta más reciente. En SQL por ejemplo, eso lo solventamos con ROW_NUMBER ()

select * from
(select * ,
row_number() OVER(Partition by Cliente order by Fecha desc) as Row
FROM [Tabla].[dbo].[Ejemplo]) h
where h.Row=1

Lo que estamos haciendo con esa sentencia es crear un número de orden en todas las lineas de venta para cada cliente, ordenándolas por fecha descendente y quedándonos solo con la que el row es 1, la más reciente.

Vamos a ver la forma de hacer lo mismo en Power Query.

Vamos a crear una tabla de ejemplo como esta:

Este sería el código para crearla:

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDHyNhUKVYHIWEEkzA2NUORMIZJmBhagCWM0I0yNLJEkUAYZWyEIgE3ytTUXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Fecha = _t, Importe = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Id", Int64.Type}, {"Fecha", type date}, {"Importe", Int64.Type}})
in
#"Tipo cambiado"

A partir de aquí podemos hacer dos cosas, crear una función que nos haga el proceso o hacerlo directamente. Vamos a ver aquí la opción de hacerlo directamente sin la función ya que es lo más sencillo.

1º Ordenamos por fecha descendente

2º Agrupamos la tabla por Id y en la columna de Agregación elegimos en la operación Todas las filas

Esto nos va a crear la siguiente tabla:

Y ahora agregamos la siguiente columna personalizada:

Si vemos la información de la tabla Recuento (pinchando en la celda pero no en la palabra Table) para el id 1 nos muestra tres líneas ordenadas por fecha

Si hacemos lo mismo en personalizado, vemos que esa tabla se ha quedado sólo con la primera línea, que al estar ordenada por fecha descendiente sería la de la última venta:

Eliminamos la Columa Recuento, expandimos la de Personalizado y ya lo tendríamos listo, la última venta de cada id.

Este sería el código completo del ejemplo

let
Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjAyDHyNhUKVYHIWEEkzA2NUORMIZJmBhagCWM0I0yNLJEkUAYZWyEIgE3ytTUXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Fecha = _t, Importe = _t]),
#"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Id", Int64.Type}, {"Fecha", type date}, {"Importe", Int64.Type}}),
#"Filas ordenadas" = Table.Sort(#"Tipo cambiado",{{"Fecha", Order.Descending}}),
#"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"Id"}, {{"Recuento", each _, type table [Id=number, Fecha=date, Importe=number]}}),
#"Personalizada agregada" = Table.AddColumn(#"Filas agrupadas", "Personalizado", each Table.FirstN([Recuento],1)),
#"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada",{"Recuento"}),
#"Se expandió Personalizado" = Table.ExpandTableColumn(#"Columnas quitadas", "Personalizado", {"Fecha", "Importe"}, {"Fecha", "Importe"}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Se expandió Personalizado",{{"Fecha", type date}, {"Importe", Int64.Type}})
in
#"Tipo cambiado1"