Totalizar por fecha cruzada con centro

Tenemos el modelo que uso frecuentemente, pero solo tengo las columnas de fecha, y, queremos ver todas las combinaciones de fecha y producto, y, el total, para cada combinación.

Pero, podíamos preguntarnos, porque no lo hacemos en Excel, en vez de en Power Query, pues, porque queremos que sea dinámico.

Vamos a cargar el modelo en Power Query, para ello, con una celda dentro del modelo, vamos a la pestaña de datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.

Vamos a crear una nueva consulta, donde nos vamos a quedar con la columna centro, para ello, en la consulta ventas, hacemos clic con botón alternativo de ratón, y, seleccionamos referencia.

La voy a llamar centros.

Tenemos una copia de la tabla ventas, pero vamos a quedarnos con la columna centro, para ello, vamos a la barra de fórmulas, después del nombre de la consulta, abrimos un corchete, ponemos el nombre de la columna, y, cerramos el corchete.

Aceptamos, y, vemos que solo tenemos la columna centro.

Lo siguiente es quitar duplicados, para ello, hacemos clic sobre la columna con botón alternativo de ratón, y, seleccionamos quitar duplicados.

Aunque podemos quitar duplicados mediante una función, voy a borrar el paso de quitar duplicados, para ello, hago clic en la X que aparece a la izquierda del paso.

Vamos a la barra de fórmulas, después del signo igual, ponemos:

= List.Distinct(Ventas[Centro])

Aceptamos, esta función solo deja los registros únicos.

Ahora, vamos a ordenar de manera descendente, para ello, de nuevo, en la barra de fórmulas, después del signo igual, ponemos:

= List.Sort(List.Distinct(Ventas[Centro])

Vamos al final de la función, ponemos una coma, seguido de order, y, elegimos ascendente.

Cerramos paréntesis, y, aceptamos.

= List.Sort(List.Distinct(Ventas[Centro]),Order.Ascending)

Ya lo tenemos ordenado.

Ordenarlos, es porque cuando vayamos a realizar el cruzado, los datos deben de estar ordenados.

Lo siguiente es unir todas las fechas con los centros.

Como hemos referenciado la consulta, quiere decir que cuando haya nuevos datos en la consulta ventas, esta consulta se actualizara de forma automática.

Ahora, volvemos a referencia la consulta ventas.

Como nombre le pongo fechas.

Hacemos clic con botón alternativo de ratón sobre la columna fecha, y, seleccionamos quitar otras columnas.

Quitamos duplicados, para ello, volvemos a hacer clic con botón alternativo de ratón sobre la columna, y, seleccionamos quitar duplicados.

Vemos que, para cada mes, hay días diferentes, pero tenemos que quedarnos con los meses, para poder, calcular el total.

Vamos a la pestaña de transformar, dentro de columna fecha y hora, desplegamos fecha, desplegamos mes, y, seleccionamos inicio del mes.

Voy a añadir un paso, para ello, hago clic en agregar paso.

Aparece el último paso.

Pues, tenemos que extraer el valor mínimo, pues, después del signo igual, ponemos:

= List.Min(#"Inicio del mes calculado")

Aceptamos, y, nos devuelve un error.

Este error es porque debemos de indicar la columna de donde sacar el valor mínimo, pues después del nombre del paso, abrimos un corchete, ponemos el nombre de la columna, y, cerramos corchete.

= List.Min(#"Inicio del mes calculado"[Fecha])

Aceptamos, y, tenemos la fecha mínima.

Selecciono el paso creado, pulso F2, y, le pongo como nombre Fechaminima.

Añado un nuevo paso, pero en esta ocasión, extraigo el valor máximo, para ello, voy a seleccionar el paso Fechaminima, pulso CTRL mas C para copiar, añado un nuevo paso, pulso CTRL mas V para pegar, y, cambio Min por Max.

Le cambio el nombre a Fechamaxima.

Voy a añadir un nuevo paso, y, resto la fecha máxima con la fecha mínima.

= Fechamaxima-Fechaminima

Me devuelve el resultado, pero en formato de fecha.

Lo necesitamos en formato de número, pues, en la barra de fórmulas, después del signo igual, ponemos:

= Number.From(Fechamaxima-Fechaminima)

Esta función tomará cualquier valor, y, lo convertirá en número, puede ser un texto, un valor lógico…

Lo siguiente es crear la lista de fechas desde la fecha mínima, para ello, vamos a usar la función List.Dates, esta función, devuelve una lista de valores de fechas, indicando un comienzo, un tamaño, y, un incremente, este incremento, debemos de especificarlo con la función duration.

Seleccionamos el paso donde hemos realizado la resta, después del signo igual, ponemos la función.

= List.Dates(

Como argumento comienzo, ponemos el paso Fechaminima.

= List.Dates(Fechaminima,

Como argumento tamaño, dejamos la resta, al cual debemos de sumar 1.

= List.Dates(Fechaminima, Number.From(Fechamaxima-Fechaminima)+1,

Como argumento incremento, debemos de poner 1, pero no podemos ponerlo tal cual, por lo que debemos de usar la función #Duration, en la cual debemos de especificar día, hora, minuto, y, segundo, como quiero que vaya sumando un día, pongo como sige:

= List.Dates(Fechaminima, Number.From(Fechamaxima-Fechaminima)+1, #duration(1,0,0,0))

Ya tenemos nuestras fechas creadas.

Pero, es una lista, y, la necesitamos como tabla, teniendo la columna seleccionada, vemos la pestaña transformar, pues, pues dentro del grupo convertir, hacemos clic en a la tabla.

Se abre la ventana a la tabla, donde solo tenemos que aceptar.

Cambiamos el nombre de la columna a fechas.

Vemos que el tipo de datos no ha sido detectado, pues, vamos a la pestaña transformar, dentro del grupo cualquier columna, hacemos clic en detectar tipo de datos.

De nuevo, seleccionamos inicio del mes.

Quitamos duplicados, y, tenemos el inicio de cada mes.

Ahora, debemos de combinar cada centro con cada fecha, para ello, vamos a la pestaña agregar columna, y, seleccionamos columna personalizada.

En la ventana formula de columna personalizada, ponemos Centros.

Aceptamos.

Se agrega una columna con la palabra List.

Hacemos clic en expandir.

Elegimos expandir en nuevas filas.

Podemos ver como para cada fecha, tenemos todos los centros.

Hemos hecho una combinación cruzada.

Vuelvo a referenciar la tabla ventas.

Seleccionamos la columna fecha, y, volvemos a poner el inicio de mes.

Lo siguiente es agrupar, para ello, seleccionamos la columna de fecha, y, centro, botón alternativo de ratón, y, seleccionamos agrupar por.

Como nombre de columna, pongo total, selecciono suma, y, selecciono la columna de total.

Aceptamos.

Tenemos el total para cada fecha y centro.

Ahora, vamos a fusionar, selecciono la tabla fechas.

Desde la pestaña de inicio, dentro del grupo consultas, hacemos clic en combinar consultas, vamos a combinar la consulta fecha, con Ventas (2).

En la ventana inferior, seleccionamos Ventas (2).

De la tabla Fechas, seleccionamos ambas columnas, y, de la tabla Ventas (2), seleccionamos las columnas de fecha y, centro, que son los campos comunes.

Clic en aceptar.

Aparece una columna nueva con el nombre de table.

Clic en expandir.

Desmarcamos la casilla usar el nombre de columna original como prefijo, y, la columna de total.

Aceptamos.

Ya tenemos el total para cada fecha y producto.

Pero, vemos que hay valores nulos, pues, lo vamos a sustituir por cero, para ello, con la columna seleccionada, hacemos clic con botón alternativo de ratón, y, seleccionamos reemplazar los valores.

Se abre la ventana de reemplazar los valores, en la ventana de valor que buscar ponemos null, y, en la ventana reemplazar con, ponemos cero.

Aceptamos.

Ya tenemos el ejercicio resuelto.