Calendario de consultas de energía

Hace unas semanas escribí una publicación en el blog sobre cómo crear un calendario gráfico usando una tabla dinámica. Recibí algunas preguntas sobre cómo insertar nombres de eventos en el calendario. Generalmente, los campos de valores de la tabla dinámica admiten valores numéricos (como el día 1, 2, 3) y no valores de texto (como la fecha límite de presentación o la fiesta de cumpleaños). Afortunadamente, esta tarea se puede realizar con Power Query.

Aunque uso una aplicación de calendario real en lugar de Excel para mantener mi calendario, hay algunas transformaciones PQ interesantes involucradas en esta publicación, por lo que espero que estas técnicas te resulten útiles en otras situaciones que puedas encontrar.

Descripción general

Estamos intentando tomar una lista de eventos almacenados en una tabla de Excel, como esta:

Y colóquelos en un calendario gráfico, como este:

Y, por supuesto, queremos que sea fácil cambiar de mes, por lo que nos gustaría una segmentación para controlar el calendario, como esta:

Se necesitan varios pasos para que esto funcione, pero una vez que funciona, el calendario se adapta dinámicamente a su lista de eventos. Por lo tanto, un rápido clic derecho y una actualización mostrarán su lista de eventos actualizada en el calendario.

Nota: los siguientes pasos suponen que todos los eventos de una sola fecha están incluidos en la fila de la tabla para la fecha.

Detalles

He creado un vídeo y una narrativa completa a continuación como referencia.

Video

Narrativo

Procederemos con los siguientes pasos:

• Cargar lista de eventos
• Crear consulta de calendario
• Mostrar calendario en Excel

Bueno, no hay mejor momento que el presente, así que comencemos.

Nota: dependiendo de su versión de Excel, es posible que tenga o no los mismos comandos, pantallas y opciones que mis capturas de pantalla a continuación, que se crearon con Excel O365 para Windows.

Cargar lista de eventos

Primero, necesitamos cargar la lista de eventos en Power Query. Para hacer esto, seleccionamos cualquier celda en la tabla de eventos y hacemos clic en el comando Datos Desde tabla/rango .

La única transformación que debemos realizar en la ventana resultante del Editor de Power Query es cambiar el tipo de datos de la columna Fecha a Fecha . Una forma de hacerlo es seleccionar la columna Fecha y seleccionar Fecha en el menú desplegable Inicio Tipo de datos . Los resultados deberían parecerse un poco a esto:

Además, para realizar un seguimiento de todo, le daremos a esta consulta un nombre descriptivo, como EventList , escribiéndolo en el campo Nombre.

En este punto, hacemos clic en el comando Cerrar y cargar en… , seleccionamos Solo crear conexión y desactivamos la casilla Agregar al modelo de datos , como se muestra a continuación.

Una vez completado esto, es hora de pasar al siguiente paso.

Crear consulta de calendario

A continuación, necesitamos crear la consulta que genera el calendario. Pero antes de hacer eso, primero necesitaremos configurar algunas consultas de ayuda. Específicamente, necesitaremos crear una consulta para calcular el primer día que se mostrará, el último día que se mostrará y todas las fechas intermedias.

Consulta del primer día

Para crear la consulta FirstDay, hacemos clic derecho en la consulta EventList desde el panel Consultas y conexiones y seleccionamos Referencia . Esto crea una nueva consulta cuyo origen es la consulta EventList. Luego, realizamos las siguientes transformaciones:

1. Seleccione la columna Fecha y haga clic en Agregar columna Fecha Inicio del mes
2. Seleccione la nueva columna Inicio de mes y haga clic en Inicio Tipo de datos Número entero
3. Seleccione la columna Inicio de mes y haga clic en Transformar Estadísticas Mínimo
4. Asigne a la consulta el nombre Primer Día

Esto debería proporcionar un número de serie de fecha (no una fecha formateada), como se muestra a continuación:

Cerramos y cargamos para… Solo creamos conexión, no agregamos al modelo de datos.

A continuación, básicamente enjuagamos y repetimos para obtener la última fecha.

Consulta del último día

1. Haga clic derecho en la consulta EventList y seleccione Referencia
2. Seleccione la columna Fecha y haga clic en Agregar columna Fin de mes
3. Seleccione la columna Fin de mes y Inicio Tipo de datos Número entero
4. Seleccione la columna Fin de mes y Transformar Estadísticas Máximo
5. Asigne a la consulta el nombre LastDay
6. Cerrar y cargar en… Solo crear conexión, sin modelo de datos

Ahora que tenemos el primer y el último día, necesitamos crear una tabla que incluya todos los días entre ellos (inclusive).

Consulta de rango de fechas

Comenzaremos con una consulta en blanco seleccionando Datos Obtener datos De otras fuentes Consulta en blanco .

En la barra de fórmulas, simplemente ingresamos la siguiente fórmula…

={PrimerDía..ÚltimoDía}

… y pulsamos la tecla Enter de nuestro teclado. Power Query genera una lista de números que comienzan en el primer día y terminan en el último día.

Ahora sólo necesitamos un par de transformaciones adicionales:

1. Herramientas de lista Transformar A tabla
2. Cambie el tipo de datos a Fecha haciendo clic en Inicio Tipo de datos Fecha
3. Cambie el nombre de la columna haciendo doble clic en la etiqueta de la columna y escribiendo la etiqueta de la columna deseada Fecha
4. Nombra la consulta DateRange

Los resultados deberían parecerse un poco a esto:

Cerrar y cargar en… consulta de solo conexión, sin modelo de datos.

Una vez completadas nuestras consultas auxiliares, ahora podemos crear la consulta que genera el calendario.

Consulta de calendario

Para esta consulta, utilizamos el comando Datos Obtener datos Combinar consultas Combinar . En el cuadro de diálogo Combinar resultante, seleccionamos DateRange en el primer menú desplegable, EventList en el segundo menú desplegable y luego seleccionamos la columna Fecha de ambos como se muestra a continuación.

Para completar los siguientes pasos, necesitarás poder ver la barra de fórmulas, así que si no puedes verla, marca la casilla Ver Barra de fórmulas .

Después de hacer clic en Aceptar, tenemos algunas transformaciones:

1. Expanda la columna EventList haciendo clic en el icono de expansión en el lado derecho de la columna y desactive todas las casillas de verificación excepto Evento.
2. Seleccione la columna Fecha y Agregar columna Fecha Mes Inicio del mes
3. Seleccione la columna Fecha y Agregar columna Fecha Semana Semana del año
4. Seleccione la columna Fecha y Agregar columna Fecha Día Nombre del día
5. Seleccione la columna Fecha y Agregar columna Fecha Día Día
6. Primero seleccione la columna Día , mantenga presionada la tecla Ctrl y luego seleccione la columna Evento . Con ambas columnas seleccionadas, Transformar Fusionar columnas . En el cuadro de diálogo resultante, seleccione Espacio como Separador y asigne a la nueva columna el nombre DayEvent
7. Mire la barra de fórmulas y expándala para que pueda ver todo el código de este paso (es posible que deba hacer clic en la flecha hacia abajo en el lado derecho de la barra de fórmulas). Desea reemplazar el delimitador de espacio ” ” con el carácter de avance de línea “#(lf)” . Entonces, cambie este Combiner.CombineTextByDelimiter(” “, QuoteStyle.None) a esto: Combiner.CombineTextByDelimiter( “#(lf)” , QuoteStyle.None) y luego presione Enter en su teclado.

Antes de continuar, asegurémonos de que nuestros resultados sean algo como esto:

Nombres de días… si no queremos que los nombres de los días estén completamente escritos y preferimos la abreviatura de tres letras, podemos seleccionar la columna Nombre del día y seleccionar Transformar Extraer Primeros caracteres y luego ingresar 3 en el cuadro de diálogo resultante.

A continuación, eliminamos la columna Fecha , que no es necesaria en el futuro, por lo que simplemente seleccionamos la columna Fecha y Eliminar/eliminar columna.

Pivote: la siguiente transformación es un Pivote y, para realizarla, seleccionamos la columna Nombre del día y hacemos clic en Transformar Columna de pivote . En el cuadro de diálogo resultante, seleccionamos DayEvent como la columna de valores y expandimos las Opciones avanzadas. Seleccionamos Mínimo , como se muestra a continuación.

A continuación, podemos simplemente hacer clic y arrastrar para ajustar el orden de las columnas como deseemos, como domingo, lunes, martes, etc.

Finalmente, simplemente eliminamos las columnas que no queremos enviar a Excel, como la columna Semana del año, seleccionando la columna y presionando la tecla Eliminar en nuestro teclado.

Cerramos y cargamos en… tabla , en una hoja de trabajo nueva o existente. Ahora, sólo necesitamos limpiar la forma en que mostramos el calendario en Excel.

Mostrar calendario en Excel

En este punto, el calendario se muestra en Excel, así:

Aquí hay algunas cosas de formato que podemos realizar opcionalmente si lo deseamos:

1. Alineación superior seleccionando toda la tabla y haciendo clic en el comando Inicio Alineación superior (grupo Alineación)
2. Ajustar texto seleccionando toda la tabla y haciendo clic en el comando Inicio Ajustar texto (grupo Alineación)
3. Establecer un ancho de columna uniforme para todas las columnas
4. Dígale a la tabla que no cambie los anchos de las columnas haciendo clic en el comando Herramientas de tabla Diseño Propiedades y desmarcando la casilla de verificación Ajustar anchos de columnas.
5. Agregue una segmentación para seleccionar meses rápidamente haciendo clic en Herramientas de tabla Diseño Insertar segmentación y seleccionando Inicio de mes.
6. Ocultar la columna de la hoja de trabajo Inicio de mes (haga clic con el botón derecho en la etiqueta de la columna y Ocultar)
7. Seleccione entre una variedad de opciones de formato, incluidos estilos de tabla y bandas en la pestaña de diseño Herramientas de tabla.

Bueno, hubo muchos pasos, ¡pero lo logramos!

Y ahora, puede utilizar fácilmente la segmentación para filtrar la tabla del calendario para el mes deseado.

Además, si agrega eventos a la tabla de eventos o desea actualizar la altura de las filas, simplemente seleccione cualquier celda en la tabla del calendario, haga clic derecho y actualice.

Consulte el archivo de muestra para obtener un libro de trabajo en pleno funcionamiento: PQCal.xlsx

Si tiene otros consejos de Power Query, compártalos publicando un comentario a continuación.