Para el siguiente ejemplo, tenemos las ventas para el mes de enero y febrero para el año 2023, para las provincias de Sevilla y Huelva.
A través de Power Query, debemos de crear un informe con las fechas donde no hubo ventas.
Con una celda dentro del modelo, vamos a la pestaña de datos, dentro de obtener y transformar datos, hacemos clic en de una tabla o rango.
El proceso lo vamos a realizar en diferentes columnas, para no crear una función muy larga.
Vamos a realizar una agrupación por provincia, para ello, teniendo seleccionada la columna provincia, hacemos clic con botón alternativo de ratón, y, seleccionamos agrupar por.
Se abre la ventana agrupar por, dejamos el nombre que aparece por defecto, desplegamos la ventana operación, y, seleccionamos todas las filas.
Aceptamos, vemos que se ha añadido una nueva columna con tablas, si hacemos clic en la primera tabla, vemos las ventas para Sevilla.
Si hacemos clic en la segunda tabla, vemos las ventas para Huelva.
Vamos a agregar una columna personalizada, para ello, vamos a la pestaña agregar columna, y, hacemos clic en columna personalizada.
Vamos a calcular la diferencia de días entre la primera fecha y la ultima fecha para cada provincia, para ello, vamos a usar la función Date.Day, que devuelve el día de una fecha, esta función tiene un argumento que es la fecha con la que trabajar, y, nos devolverá un número.
Date.Day(dateTime as any) as nullable number
En la Ventana de formula, ponemos la función, abrimos un paréntesis, como argumento usamos la función List.Max, y, como argumento de List.Max, de la ventana columnas disponibles, seleccionamos la columna Recuento, abrimos un corchete ponemos Fecha, cerramos corchete, y, cerramos paréntesis, es decir, vamos a conseguir la fecha máxima para cada provincia.
Date.Day( List.Max([Recuento][Fecha]))
Ahora, debemos de restar con el día de la fecha mínima, para ello, ponemos el signo menos (-), volvemos a usar Date.Day, pero en este caso, usamos List.Min.
Pero, al resultado de esta expresión, debemos de sumar 1, en caso contrario, devolverá un día menos, cosa que haremos con la función #duration, donde solo usamos el argumento día que ponemos en 1, el resto de los argumentos lo ponemos en cero.
Aceptamos, y, tenemos una nueva columna con la diferencia de días.
Agregamos una nueva columna personalizada.
En este caso, vamos a crear el intervalo de fechas, desde la fecha mínima sumando 19 días para la provincia de Sevilla, y, sumando 7 días para la provincia de Huelva, para ello, vamos a usar la función List.Dates, que devuelve una lista de valores de fecha, a partir de una fecha inicial, un numero de días que le indiquemos, y, aumentando el numero de días que le indiquemos.
Esta función tiene tres argumentos, que es la fecha inicial, numero de días y paso.
List.Dates(start as date, count as number, step as duration) as list
Esta función devuelve una lista.
En la ventana de fórmula de columna personalizada, ponemos la función, como argumento start, ponemos el valor mínimo de la tabla recuento y columna fecha.
List.Dates(
List.Min([Recuento][Fecha])
Coma, como argumento count, ponemos la columna personalizado, que es la diferencia de días.
Table.FromValue(
List.Dates(
List.Min([Recuento][Fecha]),
[Personalizado]
Coma, como argumento step, con la función #duration, ponemos 1 día.
Cerramos paréntesis.
List.Dates(
List.Min([Recuento][Fecha]),
[Personalizado],
#duration(1,0,0,0))
Como dije anteriormente, esta función devuelve una lista, y, necesitamos una tabla, por lo que después del signo igual, vamos a usar la función Table.FromValue, que crea una tabla a partir de una lista, esta función tiene dos argumentos:
Table.FromValue(value as any, optional options as nullable record) as table
El argumento value es la lista para transformar, tiene un segundo argumento que es opcional, pero que nosotros no necesitamos, solo necesitamos el primer argumento.
Aceptamos, tenemos una nueva columna con tablas, si hacemos clic en la primera tabla, veremos las fechas desde la primera fecha de venta hasta la última, incluyendo los días que no hubo ventas.
Lo mismo para la provincia de Huelva.
Ahora, debemos de unir la columna recuento con esta, para ello, vamos a usar una función llamada Table.NestedJoin, que combina las filas de una tabla con otra tabla.
Sintaxis:
Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number, optional keyEqualityComparers as nullable list) as table
Tiene varios argumentos, pero nosotros solo necesitamos los cinco primeros argumentos, agregamos una nueva columna personalizada, en la ventana de formula, ponemos la función, abrimos un paréntesis, el primer argumento es tabla1, pues de la ventana columnas disponibles, seleccionamos personalzido.1.
Table.NestedJoin(
[Personalizado.1]
Coma, el siguiente argumento es la columna a traer de la tabla, pues, entre comillas dobles, ponemos Value.
Table.NestedJoin(
[Personalizado.1],
"Value"
Coma, el siguiente argumento es la segunda tabla, pues seleccionamos la columna recuento.
Table.NestedJoin(
[Personalizado.1],
"Value",
[Recuento]
Coma, la columna a traernos es fecha.
Table.NestedJoin(
[Personalizado.1],
"Value",
[Recuento],
"Fecha"
Coma, el nombre para la nueva columna va a ser Datos.
Cerramos paréntesis.
Aceptamos, tenemos una nueva columna con tablas, si hacemos clic en la primera tabla, vemos las fechas junto con una tabla.
Lo mismo para Huelva, hacemos clic en expandir.
Desmarcamos usar el nombre de columna original como prefijo.
Expandimos la columna datos.
Pues, ya tenemos nuestro modelo.
Quitamos las columnas de provincia a personalizado.1.
Quitamos la columna fecha.
Pulsamos CTRL mas A para seleccionar el modelo completo.
Hacemos clic con botón alternativo de ratón sobre uno de los encabezados y seleccionamos reemplazar los valores.
Reemplazamos null por sin datos.
Aceptamos.
Todo este proceso ha sido correcto, el problema que se presenta es que solo nos presenta 20 días para cada provincia, aunque el mes tenga más días.
He pensado que podía dividir la tabla en dos, una primera tabla con fechas desde el día 1 de cada mes hasta el día 15, y, una segunda tabla con las fechas desde el día 16 hasta el día 31.
Empezamos desde el principio, con el modelo solo cargado en Power Query.
Hacemos clic con botón alternativo de ratón en la zona de consultas, desplegamos nueva consulta, desplegamos otros orígenes, y, seleccionamos consulta en blanco.
Hacemos clic con botón alternativo de ratón sobre la consulta, y, seleccionamos editor avanzado.
Borramos las comillas dobles después de Origen.
Vamos a usar la función Table.SelectRows, donde nos vamos a traer de la tabla ventas, cada día de la columna fecha, cosa que hacemos con la función Date.Day, que devuelve el día de una fecha, siempre que sea mayor o igual a 1, y, también si es menor o igual a 15.
let
Origen =
Table.SelectRows(
Ventas, each Date.Day([Fecha])>=1 and Date.Day([Fecha])<=15)
In
Origen
Tenemos una tabla desde el primer día del mes hasta el día 15.
Ahora, seguimos exactamente los pasos anteriores, hasta llegar a columnas quitadas.
Detallo los pasos.
1.
2.
3.
4.
5.
6.
7.
8.
9.
Vemos que tenemos desde el 3 de enero hasta el día 15 de febrero.
Creamos otra consulta en blanco, vamos al editor avanzado, en este caso, las condiciones son mayor o igual a 16, y, menor o igual a 31.
Table.SelectRows(
Ventas, each Date.Day([Fecha])>=16 and Date.Day([Fecha])<=31),
El resto de los pasos son idénticos a los anteriores.
Vemos que tenemos las ventas desde el día 16 de enero hasta el día 20 de febrero que fue el último día de ventas.
Vamos a la pestaña de inicio, desplegamos anexar consultas, y, seleccionamos anexar consultas para crear una nueva.
En la ventana anexar, seleccionamos tres o mas tablas, llevamos consulta2 a la ventana tablas para anexar.
Aceptamos.
Ordenamos la columna fecha de manera ascendente.
Pulsamos CTRL mas A para seleccionar todo el modelo, hacemos clic en uno de los encabezados con botón alternativo de ratón, y, seleccionamos reemplazar los valores.
Sustituimos null por sin datos.
Cerramos y cargamos como una conexión.
Cargamos el modelo en una nueva hoja.
Vemos que para el mes de enero, las fechas van desde el día 3 y de manera consecutiva llega al día 21 que fue el último día de ventas para el mes de enero, y, para febrero comienza el día 1, y, termina el día 20.
Pues ya tenemos nuestro ejercicio resuelto.
Miguel Angel Franco Garcia
Comments