jueves, 9 de julio de 2020

Power Query: Repetir y numerar filas como n de N

Recientemente una lectora preguntaba la posibilidad de pasar de un listado donde se indicaban repeticiones por registro a un listado desglosado con tantas filas repitas y numeradas como se indicase...
Fácil de entender viendo la imagen siguiente:
Power Query: Repetir y numerar filas como n de N

Vemos como pasamos de una sola fila donde se indican 5 repeticiones a cinco filas numeradas como 1 de 5, 2 de 5, ..., 5 de 5.. y así con cada registro.
La usuaria pedía una macro, pero pensé que Power Query podría ofrecer un resultado igual de válido... y así fue, y así lo voy a explicar. ;-)
Comenzamos cargando la tabla al Editor de consultas de Power Query, desde la Ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango.
Ya en el editor empezaremos a incorporar etapas o pasos.
El primer paso es añadir una columna personalizada con la siguiente función, que emplea Table.Repeat:
Table.Repeat(
  		Table.FromRecords({
        	[Col1=1]
            }),
        [Repetición])

Power Query: Repetir y numerar filas como n de N

El resultado sería:
Power Query: Repetir y numerar filas como n de N
Donde vemos que Table.Repeat devuelve una tabla con tantas filas como se especifiquen en el segundo argumento.. en mi ejemplo el valor dado en el campo 'Repetición' Table.Repeat(table as table, count as number) as table
Este paso es básico, ya que nos permite repetir las filas tal como esperábamos...
En el siguiente paso expandiremos la columna recién añadida, y añadiremos una columna de índice:
Power Query: Repetir y numerar filas como n de N

Para la siguiente etapa debemos recordar cómo obtener un acumulado creciente condicionado con Power Query, para ello revisa esta entrada del blog
Así pues añadimos una nueva columna personalizada:
let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])

Power Query: Repetir y numerar filas como n de N

El resultado es un incremental basado en la anterior columna personalizada, obtenida con Table.Repeat, condicionada al campo 'Id' original...
Power Query: Repetir y numerar filas como n de N


Ya tenemos todo lo que necesitamos. La nueva columna 'Acum' que crece por cada registro, y el total para cada registro en el campo 'Repetición' de la tabla original.
Por tanto bastará seleccionar la columna 'Acum' y luego 'Repetición' y desde la ficha Transformar > Columna de texto > Combinar columnas, personalizando el separador por ' de '
Power Query: Repetir y numerar filas como n de N
Finalizamos eliminando columnas que ya no necesitamos... como 'Col1' e 'Índice'. LLegando al resultado mostrado al imagen del post.

El código completo visto desde el editor avanzado sería:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblVentas"]}[Content],
    ColADD = Table.AddColumn(Origen, "Personalizado", each Table.Repeat(
                                                                Table.FromRecords({
                                                                    [Col1=1]
                                                                }),
                                                                [Repetición])),
    Expandido = Table.ExpandTableColumn(ColADD, "Personalizado", {"Col1"}, {"Col1"}),
    ÍndiceAgregado = Table.AddIndexColumn(Expandido, "Índice", 1, 1),

///
RunTotal = Table.AddColumn(ÍndiceAgregado, "Acum", each let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])),
    #"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(RunTotal, {{"Acum", type text}, 
        {"Repetición", type text}}, "es-ES"),
        {"Acum", "Repetición"},
        Combiner.CombineTextByDelimiter(" de ", QuoteStyle.None),"n de N"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columnas combinadas",{"Col1", "Índice"})
in
    #"Columnas quitadas"

Power Query: Repetir y numerar filas como n de N
Ya podemos Cerrar y cargar en... nuestra consulta en nuestra hoja de cálculo destino.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.