Calcular días entre fechas con Power Query / Power BI

¿Alguna vez has querido saber cuántos días tienes entre 2 fechas? ¿Tal vez cuántos lunes? ¿Cuántos domingos? ¿Quizás los sábados y los domingos?

Bueno, en esta publicación te mostraré cómo puedes hacerlo con Power Query dentro de Excel y/o Power BI y cómo puedes utilizar esto en otros escenarios.

Paso 1: Descarga el archivo que te servirá de guía

Te recomiendo que descargues el archivo zip para que puedas realizar el ejercicio. El archivo zip contiene 2 archivos, uno que es el archivo en el que estaremos trabajando (tiene el sufijo «Begin») y el otro que es la versión final (tiene un sufijo «Complete»). Puedes hacer clic en el siguiente botón para descargar el archivo.

En el archivo notarás que tenemos una tabla:

Lo que queremos hacer es crear una nueva columna que nos dará la respuesta a preguntas como:

• ¿Cuántos lunes tenemos entre esas 2 fechas?
• ¿Cuántos sábados y domingos tenemos entre esas 2 fechas?

Por supuesto, esta es una versión muy simplificada de un escenario del mundo real que me presentaron en el foro oficial de Power Query de Microsoft.

Sugerencia: si alguna vez tienes alguna pregunta sobre Power Query o sobre cómo lograr algo específico con Power Query, puedes publicar tus preguntas en ese foro. No solo es supervisado por increíbles miembros de la comunidad, sino también por miembros del equipo de Power Query en Microsoft que generalmente van más allá en sus respuestas. Solo dale un vistazo a estas respuestas de Ehren y cómo va más allá para proporcionar una solución personalizada para la publicación original.

Ahora que tienes el archivo, adelante, ábrelo y ejecuta el editor de Power Query:

Una vez estés dentro de la ventana del Editor de Query, notarás que tenemos 1 consulta y 2 parámetros. La consulta tiene los datos de la tabla que ves en la Sheet1 (Hoja1):

Paso 2: Crear la función personalizada

Power Query no tiene una función nativa para calcular exactamente lo que queremos, por lo que necesitamos crear nuestra propia función personalizada y esa es la razón principal por la que ya tenemos 2 parámetros en este archivo.

Para crear la función, necesitamos crear una nueva consulta en blanco:

y una vez que tengamos esta nueva consulta en blanco, ingresaremos el siguiente código en la barra de fórmulas:

= List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0))

esta fórmula creará una lista de fechas desde Start date hasta End date (los valores de los parámetros de los mismos nombres), y una vez que hayamos ingresado el primer paso, cambiaremos el nombre de nuestra consulta actual a ‘Sample Query’.

Luego, vamos a expandir el panel Consultas a la izquierda y haremos clic en nuestra consulta actual (Sample Query), haz clic derecho en ella y selecciona la opción Crear función como se muestra en la siguiente imagen:

Una vez que hagas clic en ella, aparecerá una nueva ventana donde se te solicitará que ingreses un nombre para tu nueva función y te proporcionará una lista de todos los parámetros que la función está utilizando. En mi caso, le pondré ‘DaysInCondition‘ a esta nueva función, pero puedes ponerle el nombre que quieras. Esta operación creará una nueva carpeta donde verás todos tus parámetros en uso y también tu ‘Sample Query’ y la función que creaste a partir de esa ‘Sample Query’.

Paso 3: Modificar la consulta de muestra

Dirígete a la Sample Query, ya que agregaremos más pasos a esa consulta. Queremos crear una solución que solo nos dé los lunes entre 2 fechas. Para hacer eso, usaremos una función List.Select que requiere una lista como primer parámetro y luego una condición como segundo parámetro.

Para ingresar un nuevo paso, hacemos clic en el ícono ‘fx’ en la barra de fórmulas, y luego reemplazamos el código en esa barra de fórmulas con este:

List.Select(Source, (_)=>Date.DayOfWeek(_, Day.Monday) = 0)

o también puedes usar este:

List.Select(Source, each Date.DayOfWeek(_, Day.Monday) = 0)

El segundo parámetro de la función List.Select es en realidad una función que se realiza fila por fila (o elemento por elemento dentro de la lista). La operación realizada para cada elemento de la lista produce un simple TRUE/FALSE, donde usamos la función Date.DayOfWeek para averiguar si la fecha actual es un lunes, y si es así, permanecerá en la lista, de lo contrario se eliminará de la lista.

Puedes cambiar la parte Day.Monday de la función a Day.Tuesday y esto asegurará que la numeración de los días de la semana comience desde el martes y finalice el lunes, donde el martes es 0 y el lunes es 6. Esto te da mucha flexibilidad para escribir el código como lo desees. Por ejemplo, si solo deseas tener los días de lunes a viernes, puedes usar Date.DayOfWeek(_, Day.Monday) < 5.

Una vez tengamos esta lista, todo lo que tenemos que hacer es hacer clic en el botón Estadísticas y hacer un conteo sencillo para averiguar cuántos lunes tenemos entre esas fechas como se muestra en la siguiente imagen:

La mejor parte de crear una solución de esta manera es que todo lo que hacemos en la Consulta de muestra se traducirá en una función, específicamente la función DaysInCondition que vemos en nuestro panel de Consultas. Este es un proceso automatizado creado por Power Query y algo que realmente me encanta de la herramienta.

Paso 4: Invocar la función

Ahora que nuestra función está lista, es hora de usarla con los valores dentro de nuestra consulta de Fechas. Continúa y haz clic en la consulta Fechas, luego ve al menú Agregar columna/cinta y selecciona la opción Invocar función personalizada e ingresa los valores para ambos parámetros de las columnas de esa consulta como se muestra en la siguiente imagen:

Una vez que hagas clic en Aceptar, notarás que Power Query agregará una nueva columna con el resultado que esperábamos exactamente, es decir cuántos lunes tenemos entre esas 2 fechas:

Por supuesto, puedes utilizar esta técnica de List.Select en cualquier lista. Considérala como una forma más eficiente de crear un filtro para los valores dentro de una lista. Por lo general, la mayoría de las personas intentan convertir la lista en una tabla y luego aplicar el filtro en la columna de la tabla, pero todos esos pasos podrían simplificarse haciendo el uso de la función List.Select.

¿Qué te ha parecido el artículo? ¿tienes alguna situación similar que deseas desarrollar con Power BI o las herramientas del Excel Moderno? Deja tus sugerencias y cualquier comentario en la sección de comentarios debajo de este artículo.