Tratamiento de Días No Laborales, Parte I.
Este artículo será la primera parte de una serie de cuatro que se dedicarán a ver como trabajar y realizar cálculos con días laborales y no laborales con Power BI.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. El objetivo
2. Acerca de días No Laborales
a. Sábados y Domingos
b. Días Festivos (Feriados)
c. Días Asignados
2. Obtención de días festivos (Días no laborales en Power BI)
a. Días festivos necesarios
b. Cargas festivos de un año a Power Query
3. Preparación (de tabla con días festivos) con Power Query
a. Consolidar dos o más Tablas desde la Web con PQ
b. Creación de función personalizada con Power Query
c. Creación de lista de valores para la Función
d. Función Personalizada y Consolidación
4. Ajuste para relacionar con la tabla de calendario
a. Suposición de Días Asignados
b. Ajustes finales para el Modelo de Datos
El Objetivo
El objetivo inicial es simple, tener una tabla que liste en una columna todas las fechas que corresponda a días no laborales.
Lo previo es así poder relacionarla con la tabla de calendario y de allí utilizar un poco de DAX para marcar mediante un TRUE y FALSE si el día es laboral o no.
De esta manera ya podemos crear los cálculos necesarios utilizando el lenguaje DAX.
En este primer artículo hablaremos la obtención de una tabla con los datos necesarios de los días no laborales dependiendo de nuestro caso particular.
Para abrzar el objetivo degustaremos el fascinante mundo de las funciones personalizadas de Power Query y un pelín del lenguaje M.
«!»: No pasa nada si aún no has leído nuestro más reciente libro: El ADN de Power Query, ya que en este artículo lo desmenuzare lo mejor que pueda.
Para ello hablemos un poco de qué define un día no laboral →
Acerca de Días No Laborales
Antes de querer realizar cualquier tipo de cálculo teniendo en consideración si el día es laboral o no, es necesario determinar cuál pertenece a una categoría u otra.
Esto puede variar tanto como nos imaginemos, pero empecemos por los fines de semana.
Sábados y Domingos
En una primera parte tenemos la variabilidad en los sábados y domingos, puesto que una empresa A puede definir que los sábados son laborales, mientras que una empresa B puede definir que no son laborales (depende también de la región, país, etc.)
En esta situación ya depende de nuestro caso en concreto:
En esta serie de artículos vamos a asumir que los sábados y domingos no son días laborales.
Una segunda fuente de días no laborales la dictan los días festivos (feriados) en cada país.
Los días festivos (feriados)
Estos días que en la mayoría de los países son no laborales, adicional, tienen la característica que son estándares para cada país, por lo tanto, toda empresa en dicho país tienen estos días como un común dominador para que sus empelados disfruten de la festividad y descansen, alguno ejemplos:
- Día de Navidad
- Año Nuevo
Los días Asignados por la Empresa
En la mayoría de compañías existen fechas internas de celebración, recreo y descanso.
El ejemplo clásico es el aniversario, donde tal día puede ser tachado como no laboral, ya que se puede dedicar a celebración en general y la mayoría de las horas de trabajo se está por fuera.
Obtención de Días Festivos
El tratamiento de sábados y domingos es sencillo, por el hecho de que en la tabla de calendario basta con crear una columna calculada que mediante un condicional, IF, que identifique si es sábado o domingo.
Historia diferente para los días festivos, donde son datos estándar que existen para cada país.
En este artículo vamos a realizar la obtenicón de días festivos con el país Colombia, sin embargo, este método servirá para (casi) cualquier país.
Días Festivos Necesarios
Resulta, que para mi caso particular necesito:
Identificar todos los días festivos desde el año 1999 hasta el año presente (2019)
Porque mi empresa tiene datos históricos desde 1999
Te suena la empresa: DISPRODUCTOS 😉
Bien, bien.
Para obtener está información nuestro mejor aliado aquí es Google:
En «la internet» existen muchas fuentes que guardan estos datos
Pero aquí vamos a utilizar la siguiente fuente:
→ http://www.cuandoenelmundo.com/calendario/ ←
Cuando accedemos a la parte de calendario en la página Cuando en el mundo, podemos ver que tenemos las opciones para seleccionar nuestro país, ncluir festivos y selecconar el año:
En la situación de la empresa DISPRODUCTOS L.D el primer año es 1999 y corresponde a datos para una empresa en Colombia, por lo que son dicho parámetros lo que colocamos en la página y pulsamos clic en el botón CREAR
A continuación, la página arroja el calendario para dicho año, y en la parte inferior aparcen los días festivos en dos columnas:
Cargar Festivos de un Año a Power Query
Situados en el archivo de Power BI vamos a la pestaña Inicio y desplegamos las opciones de Obtener datos para luego pulsar clic en Web:
En el cuadro de diálogo pegamos la URL de la págna Cuando en el Mundo que filtramos, es decir: http://www.cuandoenelmundo.com/calendario/colombia/1999
Seguidamente pulsamos clic en el botón Aceptar, con lo cual aparece un nuevo cuadro de diálogo:
Navegador es el nombre del cuadro de diálgo que apreciamos, donde hay muchas tablas que se pueden cargar de la página web
A pesar de ello, las que nos interesa son fácilmente identificables dado que aparecen con el nombre: Días Festivos.
En términos diferentes, notamos que son las dos primeras tablas las que contienen información de los días no laborales, por lo tanto, debemos seleccionar las dos tablas y pulsar clic en el botón Editar:
Preparación con Power Query
La base rítmica que nos espera consiste en preparar la tabla de manera adecuada y con todos los datos necesarios
Dicho con otras palabras, debemos arreglar la tabla para alcanzar el formato tabular y consolidar todos los días festivos en la misma
La primera tarea que no espera es:
Consolidar 2 o más Tablas Desde la Web con Power Query
Como ya enmarqué los datos de los días festivos de 1999 quedaron en dos consultas/tablas diferentes: Días Festivos y Días Festivos (2).
Esto se presenta porque en la página se encuentran en columnas diferentes (Básicamente lista los días festivos de un semestre en su propia columna)
Y no queremos eso …
Más bien queremos que los datos de los días festivos de ese año en concreto queden en una sola tabla
Para ello tenemos que consolidar las dos consultas en un sola, empero, que suceda desde la propia conexión web y no añadiendo pasos adicionales posteriores apoyados con operaciones como las de Anexar
— ¿Cómo lo resolvemos?
• PASO #1:
Eliminar los pasos Tipo Cambiado y Navegación en cualquiera de las dos consultas
(Yo trabajaré sobre la consulta: Días Festivos(2)):
En el panel de resultados de Power Query se ilsutra los metadatos de las tablas que podemos extraer de la página web:
Mejor dicho, lo que estamos viendo son las mismas tablas que no mostró el cuadro de diálogo Navegador unos pasos atrás desde la interfaz (Data), mas, sus metadatos (Capation, Source, ClassName y ID).
• PASO #2:
Utiliza las opciones de filtro de la columna Caption para dejar habilitado únicamente el elemento Días Festivos:
Consecuentemente, el resultado en la interfaz de Power Query se visualiza:
• PASO #3:
La única columna que nos interesa es la que tiene nombre Data, la cual almacena las tablas en cada casilla, el resto de las columnas no nos interesan, así que:
Por lo anterior, seleccionamos la columna Data, pulsamos clic derecho y de allí presionamos en el comando Quitar Otras Columnas
Con lo cual nos quedan nuestras dos tablas con los datos de los días festivos aislados en una única columna:
• PASO #4:
Para consolidar las tablas que contiene la columna Data, sólo basta con pulsar clic en el botón de la parte superior derecha (ese que tiene dos flechas en direcciones opuestas):
En el deshabilitamos la casilla: Usar el nombre de la columna original como prefijo y pulsamos en el botón Aceptar.
y booom !!!
Han quedado consolidas nuestras dos tablas:
La consulta: Días Festivos, la podemos eliminar, la cargamos previamente para observar que los días festivos quedaban en dos tablas separadas.
Pero hasta ahora resolvimos una primera parte.
Puesto que solo tenemos los datos para el año 1999 y los necesitamos hasta el año actual (2019)
Que no cunda el pánico, porque el truco que viene enseguida es brutal y altamente eficiente, dado que hacer la tarea anterior uno a uno de forma manual no es óptimo, por ello vamos a:
Crear función personalizada con Power Qury
Crearemos una función que realice los pasos anteriores por nosotros automáticamente y ello lo conseguiremos manipulando un poco el lenguaje M.
No te preocupes que el paso a paso será sencillo
Lo primero es entender:
— Los elementos de una función personalizada y su funcionamiento, para ello veamos el siguiente pseudo código:
Una función es un algoritmo que ejecuta una serie de pasos automáticamente para dar solución a una tarea en particular, dado que se le proporcionó unos datos iniciales (parámetros/argumentos)
— El pseudo-código muestra que:
- Debe asignársele un nombre a la función para su identificación
- Indicar los parámetros necesarios para que el algoritmo se ejecute
- El algoritmo/código en si para la ejecución de los pasos automáticamente
Estos principios generales son así para la gran mayoría de lenguajes de programación
Lo que necesitamos es convertir la tarea ejecutada en Power Quey de consolidación de dos tablas de la web en una función
El código que resuelve la tarea, es decir, el algoritmo de consolidación desde a web con líneas M ya está creado
Así es
Pulsemos clic derecho encima de la consulta: Días Festivos (2) para luego seleccionar Editor Avanzado
Y allí está el código:
Nuestra consigna se basa en indicarle a Power Query que dicho código será utilizado para una función, para ello debemos indicarle:
- Un nombre
- Además, debemos decirle cuáles son los parámetros que va a recibir y que va a ser la fuente variable en nuestro algoritmo
Para el caso, sólo queremos variar la parte del año:
Queda claro que sólo necesitamos un parámetro.
Las siguientes son las modificaciones al código y su explicación
Véase que una vez pulsamos en el botón listo del Editor Avanzado, la consulta ahora se muestra como una función:
De hecho, en la parte: Escribir parámetro podemos indicar cualquier año, por ejemplo: 2000 y automáticamente se genera una consulta nueva con la tabla creada con los días festivos del año 2000
Sin embargo, nosotros necesitamos pasar varios años
Antes de eso, renombramos la consulta:
Creación de Lista de Valores para la Función
Vamos a crear una tabla con todos los años desde 1999 hasta el actual (2019)
Para ello iniciaremos con una lista …
Pulsamos clic derecho encima del Panel de consultas, para luego seleccionar la opción Nueva Consulta y allí pulsar clic en el comando Consulta en blanco
En la barra de fórmulas vamos a poner el siguiente código:
= { 1999 .. 2019 }
Y automáticamente se genera una lista de valores consecutivos arrancando desde 1999 hasta el 2019
— Pero, ¿Qué pasa si queremos que la cota superior (2019) se vaya actualizando a medida que pasan los años?
Si fuera Excel, sería algo así:
- AÑO ( HOY() )
Pues bien, el equivalente de estas funciones en M es:
- AÑO → Date.Year
- HOY → DateTime.LocalNow
Por lo tanto, podemos modificar así el código:
Qué tal, no !!!!!
Sin embargo, el tipo de dato anterior es una lista y necesitamos que sea una tabla
Para lograrlo es suficiente con pulsar en el comando: A la tabla en la pestaña Transformar:
«!»: Los tipos de datos en Power Query, la profundización en lista puedes verlas en nuestro libro: El ADN de Power Query
Función Personalizada y Consolidación
Es importante asignarle el tipo de dato Texto a la columna que contiene los años consecutivos
Vamos a la pestaña Agregar Columna y pulsamos clic en el comando Columna Personalizada
En el cuadro de diálogo llamamos a nuestra función personalizada recién creada: FnObtenerFestivos, y en su único argumento le asignamos la columna: Column1. (Para seleccionarla basta con pulsar dos veces clic en su nombre en la parte derecha del cuadro de diálogo en la parte: Columnas disponibles)
Al pulsar clic en el botón Aceptar obtendremos una nueva columna con todas las tablas (cada una en su casilla) con los días festivos
Aquí lo único que hace falta es consolidar todas esas tablas en una única, para ello pulsamos clic en el botón expandir en la parte superior derecha de la columna con nombre Personalizada, recordemos deshabilitar: Usar el nombre de la columna original como prefijo, y, Aceptar:
Ajustes para Relacionar con la Tabla de Calendario
A pocos «metros» nos encontramos de finalizar
A pesar de ello, para que se así procederemos a sensibilizar el escenario en este punto
Suposición Días Asignados
Vamos a suponer que nuestra compañía no tiene días asignados, es decir, no existen fechas adicionales establecidas por la empresa que no sean laborales
Porque recordemos que los días no laborales totales se componen así
Al asumir que no existen días asignados en nuestro escenario el problema se simplifica, y no por un factor desdeñable.
A razón de que, si existiera una taba con dichas fechas, deberíamos entrar a combinarlas utilizando alguno de los tipos de joins y evaluar si hay fechas coincidentes, si es así, mirar el tratamiento de valores úncios con columnas condicionales y demás.
Merecería su propio artículo
Por eso asumiremos que no existe dicha tabla (días asignados).
Ajustes para el Modelo de Datos
Nos hace falta una columna con la fecha completa, es decir, algo del estilo: 1/01/1999
Dado que en nuestra tabla, el día, mes y año se encuentran en columnas diferente
Para solucionar esto creamos una nueva columna personalizada, así:
Después le asignamos formato fecha la nueva columna creada
Eliminamos las columnas: Column1, Column1.1 y Column2
Para reordenar y renombrar:
Eso es todo por ahora, ya tenemos la tabla lista, en la segunda parte la relacionaremos y hablaremos de cómo se debe crear las medidas para tener en cuenta los días laborales y los días no laborales.
Por favor, comenta y regálame tus comentarios, la verdad no me quiero sentir sólo en esta serie de artículos y tu feedback para evaluar si es de interés el debate que acabamos de iniciar sería de gran ayuda. Estaré leyendo y respondiendo todos los comentarios activamente.
— ¿Quieres que sigamos hablando de este tema en Power BI?
Nos leemos en los comentarios !!!
— Miguel Caballero