Agrupando o Resumiendo Datos

Agrupar o resumir datos para crear reportes

El Caso

Una de las tareas más comunes transmitidas a un usuario de Excel es el trabajo de agrupar y resumir datos. Este patrón muestra cómo crear un informe convincente de sólo una única fuente de datos, que puede actualizarse en cualquier momento con un solo clic.

El escenario utiliza una tabla de ventas simples que incluye un listado de todos los productos (camisetas) vendidas, la fecha de venta, el canal de ventas y los dólares de ventas totales para el producto en esa fecha específica.

Nuestro objetivo es crear un informe que muestre para cada día en el período de venta:

  • Total de ventas por canal,
  • Una lista de todos los productos vendidos en dicho día (separados por coma),
  • El nombre del producto con el mayor monto de ventas, y
  • El % de participación del total de ventas que tuvo el producto con mayor ventas.

Descarga el Archivo

Nuestros Objetivos

Vamos a comenzar desde una tabla de origen que tiene las siguientes columnas:

  • Fecha
  • Nombre de Producto
  • Canal
  • Monto

Nuestro objetivo es crear un informe final que resume esos datos y lista:

  • La cantidad total por día para cada canal,
  • La cantidad de productos vendidos en ese canal,
  • Todos los productos vendidos en ese canal, y
  • El producto más vendido.

Podemos romper estos en tres sub-objetivos separados:

  1. Crear la columna que resume todas las cantidades por fecha y canal,
  2. Crear una nueva columna que representa una lista de todos los productos separados por una comas, y
  3. Crear dos columnas adicionales que nos den el nombre del producto más vendido y su proporción de las ventas totales.

Averigüemos como podemos ir de la tabla de Origen al resultado deseado.

El Inicio

Una vez abierto el libro de Excel, diríjase a la hoja con el nombre Ventas donde encontrarás una tabla. Selecciona cualquier celda dentro de la tabla, clic en la cinta de Power Query y selecciona Desde Tabla.

Con esto veremos el editor de consultas de Power Query.

Objetivo #1: Agrupar las ventas 

Nuestro primer paso se inicia mediante la agrupación de las filas en nuestra tabla utilizando algunos criterios. Da clic en el botón Agrupar Por y configúrela utilizando los siguientes criterios:

Esto nos dará una tabla con una menor cantidad de filas debido a que han sido agrupadas por Fecha y Canal. Incluso hemos conseguido crear algunos nuevos datos al crear una SUMA de la columna Monto y un Conteo de Filas Distintas (produciendo un recuento de productos distintos por canal por día).  También tenemos una lista de todos los productos en el paso final, lo que hará un poco más de sentido después…

Una Buena Práctica: Definir Tipos de Datos

Cada vez que usted maneje una fecha en Power Query, es recomendable que defina específicamente el tipo de datos de la columna de fecha. Si no lo hace, los datos podrían ser tratados como tipo “cualquiera”, lo que significa que podría aterrizar como texto o un valor en su resultado en lugar de una fecha. Para hacerlo simplemente selecciona la columna, ve la sección de Inicio y cambia el Tipo de Datos a Fecha:

Así que ahora tenemos un total de ventas y unos productos totales por canal, terminando así nuestro primer objetivo.

Objetivo #2: Crear una lista de Productos Vendidos por Día separados por coma

Nuestro siguiente paso es crear una fórmula para una nueva columna que de alguna manera:

  1. Toma todos los productos que se vendían en esa fecha y canal específico
  2. Crea una lista de eso productos
  3. Transforma esa lista en una tabla
  4. Transpone dicha tabla en una tabla con varias columnas
  5. Fusiona todas esas columnas delimitadas por una coma

Esta es la fórmula que hace precisamente eso utilizando Table.ToList Table.Transpose Table.FromList Table.Column y la función Combiner:
Table.ToList(
Table.Transpose(
Table.FromList(
Table.Column([Products], "ProductName")
)
),
Combiner.CombineTextByDelimiter(", ")
)

Lo que esta fórmula está haciendo...

Para explicar mejor lo que esta fórmula está haciendo, vamos a hacer cada parte de esa fórmula como un nuevo paso. La fórmula definida anteriormente, básicamente, hace todas estas medidas en un solo paso.

Comenzamos con la función más interna y es Table.Column([Products], “ProductName”):

lo que esta fórmula hace es simplemente extraer una columna de una tabla y presentarlo a nosotros como una lista. Así que terminamos teniendo una lista con los valores de la columna Nombre del producto.

El siguiente paso es transformar esa lista en una tabla para que pueda ser más fácil para nosotros realizamos otro tipo de operaciones. La función que transforma una lista en una tabla se llama Table.FromList y la utilizaremos ahora:
como se puede ver en la imagen superior, la nueva columna tiene valores Table para toda esa columna. Lo hicimos porque queremos utilizar otra función para transponer las filas en columnas. TEsa función es Table.Transpose y dicha fórmula debe de leerse de la siguiente manera:

el resultado de ello es básicamente una tabla transpuesta. Así que si teníamos N cantidad de filas y sólo 1 columna, ahora vamos a tener de 1 fila con cantidad N de columnas.

Nuestro siguiente paso es combinar todas las columnas con un formato similar a “valor1, valor2, Valor3, …, valorN”.

Para ello utilizamos Table.ToList que automáticamente hace el funcionamiento de la concatenación de todas las cadenas en un solo valor. Por defecto, se utiliza un separador de coma pero en este caso queremos ir con una coma seguida de un espacio (, ) y el segundo argumento de Table.ToList nos permite hacerlo añadiendo una función Combiner.

En  nuestro caso utilizaremos la siguiente función Combiner: Combiner.CombineTextByDelimiter(“, “) que hace justamente lo que necesitamos

Y una vez que expandamos la columna haciendo clic en las flechas que van en direcciones opuestas, y una vez que cambiamos el nombre de la columna, este es el resultado:

Creamos la columna que crea la lista de todos los productos que se venden en ese día, así que terminamos nuestro segundo objetivo. Veamos el siguiente.

Objetivo #3: Agregar columnas para Top Producto Vendido y su participación del total

Nuestro siguiente paso es extraer el producto más vendido y su participación del total. Podemos hacerlo a través de una nueva columna personalizada utilizando la función Table.Max como se muestra en la siguiente imagen:

una vez que se crea esta nueva columna, te darás cuenta de que va a rellenarse con los registros (records) de los cuales es un tipo especial de representación de datos en Power Query y lo que necesitamos es extraer el nombre del producto y la cantidad de ese registro.

Podemos hacerlo con un simple clic en el icono de flechas opuestas que están al lado del nombre de esta nueva columna. Haga clic en ese icono y verás que hay una nueva ventana de selección donde puede seleccionar qué columnas desea extraer de ese registro. Por ahora, sólo tienes que seleccionar el nombre del producto y Monto.

Una vez renombramos esas columnas así es como nuestra tabla debería quedar:

Estamos casi alli! nos falta la división de la cantidad de producto superior sobre el total de ventas para que podamos obtener un % de ella. Vamos a hacerlo añadiendo una nueva columna personalizada:

Después de pasar algún tiempo más limpiando los datos, este debe ser el resultado de nuestro trabajo:

Puedes dar clic en Cerrar y Guarda para salvar tus datos en una tabla dentro de una nueva hoja o también dentro de tu Modelo de Datos.

Ten en cuenta que puedes actualizar esto en cualquier momento y va a funcionar como esperarías.

Transformarla en una función

Hay muchas maneras de transformar esto en una función, pero los fundamentos de crear una función es transformar una parte de todo el código en una variable.

¿Cuál elegirías para hacer una variable? Haznos saber en la sección de comentarios

Esta vez, estamos eligiendo una variable simple. Puesto que necesitamos que el paso Source sea una tabla, definamos dicha tabla como una variable en donde la entrada necesita ser una tabla.

  1. Primero vamos a la sección de Vista y damos clic en Editor avanzado
  2. Luego vemos al código M en este Editor y reemplazamos con lo que tratamos de marcar como una variable
  3. Reemplazamos esto con una variable y agregamos la variable como parte del código como se muestra en la siguiente imagen


Ahora necesitamos poner esto en prueba. Vamos a crear un query en blanco o Blank Query y obtengamos todas las tablas dentro de nuestro archivo utilizando la función Excel.CurrentWorkbook():

luego agregamos una columna personalizada utilizando el nombre del a función que hemos creado que es realmente el nombre de la consulta que hemos creado llamada Reporte de Ventas. Debido a que dicho nombre contiene espacios es necesario de la siguiente forma en #”Reporte de Ventas” ccomo se muestra en siguiente imagen donde también podemos ver el resultado final:

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 1100
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.