Crear una Tabla Calendario con PowerQuery en Excel o Power BI

Poco a poco PowerQuery está ganando seguidores en Excel desde que Power BI se ha convertido en la herramienta número uno en Business Intelligence.

En esta entrada vamos a mostrar cómo crear una Tabla Calendario o Tabla de Fechas en PowerQuery para poder utilizarla en nuestros modelos de datos o para cruzarla con BUSCARV en Excel y así a partir de una fecha para crear columnas con el mes en texto, día de la semana, etc.

Si empezamos por Excel el editor de PowerQuery lleva ya muchos años, pero por miedo o desconocimiento no ha tenido mucho éxito, pero si descubres las infinitas posibilidades que tiene para la limpieza y transformación de nuestros datos en Excel no podrás dejar de estudiar para utilizarla.

_________________________________________________________

Creamos la Tabla Calendario en 10 pasos, excepto el paso 8, funciona igual en Excel y Power BI

En Excel se abre una ventana nueva que pertenece al archivo .xlsx, todo lo que transformemos luego se volcará en Excel o Power Pivot.

Desde Datos>ObtenerDatos>Abrir Editor de PowerQuery (1)

Tabla Calendario Tabla FechasPower BI Excel con PowerQuery

En la ventana de PowerQuery hacemos clic derecho debajo de Queries(0) y añadimos una BlankQuery o Consulta en Blanco (2)

En la barra de fórmulas añadimos el código M, genera fechas desde 1/1/2019 hasta la fecha de hoy (3)

= List.Dates(#date(2019,1,1), Number.From(DateTime.LocalNow())-Number.From(#date(2019,1,1)),#duration(1,0,0,0))

Microsoft ha incluido el lenguaje M dentro de PowerQuery, es cierto que estudiar este lenguaje te ayudará a crear grandes transformaciones de datos y a editar la Query, pero no es necesario ya que cuenta con numerosas transfomaciones predefinidas.

Pulsamos Convertir en tabla para convertirlo en una tabla (4)

En PowerQuery haciendo doble clic en la columna o nombre de tabla cambiamos los nombres del campo y tabla (5)

Creamos columnas que nos aportan granularidad a las fechas, por ejemplo creamos una columna AÑO, PowerQuery cuenta con funciones predefinidas, por ejemplo Añadir Columnas (en el menú). Seleccionamos la columna Date y Añadir Columnas>Fecha>Año (6)

Hacemos lo mismo para crear el mes en Texto, pero en este caso en la pestaña Transformar usamos Formato > Poner cada palabra en Mayúsculas para mostrar el mes en texto. En Excel deberíamos crear la fórmula NOMPROPIO(TEXTO(celda fecha;»mmmm»)), mucho más fácil en PowerQuery 🙂

Ya tenemos la tabla calendario ahora solo tenemos que pasarla a Excel. Al llevarla a tabla, lo que hacemos es crearla en PowerQuery y volcarla en una hoja de Excel para poder ser utilizada.

Cerrar y cargar en > Tabla (8) también podríamos enviarla a PowerPivot o crear solo conexión como aparece marcado en la imagen.

Vemos como queda en Excel, una tabla de datos para poder ser usada para cruzar con otras con BuscarV. (9)

En Power BI abrimos el editor de PowerQuery , está en Inicio>Transformar datos, realizamos los mismos pasos que en Excel y para llevarlo al desktop de Power BI Cerrar y aplicar (10)