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

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"

FranM

3 comentarios

Juan Publicado el7:05 pm - 05/05/2021

EXCELENTE!!

robert trucios Publicado el5:02 pm - 15/10/2021

excelente…esto lo hacia pero con una formula muy pesada.

    José Manuel Ortega Publicado el6:23 am - 14/09/2022

    Muchísimas gracias, Fran! SÚPER ÚTIL!!!! Saludos

Deja una respuesta