LuckyTemplates entre fechas solución de horas de trabajo

En el blog de hoy, demostraré un enfoque del Desafío Start Stop de Access Analytic donde, usando LuckyTemplates, se calculará el total de horas de trabajo entre fechas. Puedes ver el video completo de este tutorial en la parte inferior de este blog .

La tarea es calcular el número total de horas de un miembro del personal en cada mes y día. Presenté fechas de inicio y finalización que pueden cruzar meses y tener en cuenta el estado para tratar posibles errores tipográficos. 

Tabla de contenido

• Conjunto de datos para las horas de trabajo entre fechas de LuckyTemplates
• Filtrar las filas
• Agregar una columna personalizada
• Horas de trabajo entre fechas de LuckyTemplates: las variables
• Horas de trabajo entre fechas de LuckyTemplates: Proyección
• Horas de trabajo entre fechas de LuckyTemplates: valores agregados
• Conclusión

Conjunto de datos para las horas de trabajo entre fechas de LuckyTemplates

Vayamos al editor de Power Query. 

Aquí están nuestros datos para este desafío. Tenemos una tabla con columnas para el nombre del personal, la fecha de inicio, la hora de inicio, la fecha de finalización, la hora de finalización y el estado.

Filtrar las filas

Comencemos seleccionando las filas en función de su estado, y el editor de consultas puede ayudarnos con esta lógica. Haga clic en la flecha desplegable junto a Estado. Seleccione Filtros de texto y elija No comienza con. 

En la ventana Filas de filtro que aparecerá, escriba "e". Luego haga clic en Aceptar .

No pasará nada todavía y eso está bien porque escribimos una "e" minúscula y el "Excluir" en nuestros datos que comienza con una "E" mayúscula.

Para arreglar eso, usaremos el tercer parámetro opcional de Text.StartsWith . En la barra de fórmulas, vaya al final de la función Text.StartsWith. Agregue una coma e ingresemos nuestro Comparer.OrdinalIgnoreCase. Haga clic en la marca de verificación y eso debería eliminar las filas Excluir .

Por supuesto, podríamos tener a alguien que haya escrito erróneamente "Excluir" sin una "E" inicial. En ese caso, podemos crear alguna lógica adicional para acompañar la situación. 

Podemos probar si la palabra no incluye una "X" extendiendo nuestra función y usando Text.Contains . Para hacer eso, agregue o Text.Contains ([Estado], "x", Comparer.OrdinalIgnoreCase .

Tenga en cuenta que, en comparación con la primera cláusula, reemplazamos "e" con una "x". Seguimos ignorando el caso y colocamos un conjunto de paréntesis alrededor de nuestras dos cláusulas. Haga clic en la marca de verificación y deberíamos obtener una tabla sin filas que contengan el estado Excluir o Xcluir .

Agregar una columna personalizada

Vayamos ahora a la parte principal de este desafío. Agregaremos una columna personalizada usando la interfaz de usuario y luego cambiaremos al editor. 

Comience con el ícono de la mini mesa en la esquina superior izquierda de la mesa. Haga clic en él y en el menú desplegable, seleccione Agregar columna personalizada . 

En este punto, solo nos interesa traer los datos que necesitaremos más adelante. Y debido a que trabajaremos con múltiples campos, creemos un registro utilizando los inicializadores de registros, representados entre corchetes. 

Vamos a crear variables y asignar las columnas disponibles. Comience escribiendo SD , que igualaremos a la Fecha de inicio. Podemos hacerlo haciendo clic en Fecha de inicio en la columna de la derecha.

Haremos lo mismo para las variables ST, ED y ET que se asignarán a Hora de inicio, Fecha de finalización y Hora de finalización , respectivamente. Luego, cierre el registro con un corchete de cierre. Haga clic en Aceptar.

Se agregará a la tabla una columna personalizada con los registros. Haga clic en el espacio en blanco junto a Grabar y se abrirá un panel de vista previa. Esto traerá todos los valores de esta fila.

Horas de trabajo entre fechas de LuckyTemplates: las variables

Luego podemos formatear nuestras variables a través de la ventana del Editor avanzado . Así es como debería verse la ventana del Editor avanzado. 

Luego, coloquemos nuestras variables en nuevas líneas separadas.

Ahora, podemos expandir la lógica que necesitamos. Entonces, en lugar de un registro, también podríamos usar una expresión let anidada aquí. Tenga en cuenta que cualquiera de ellos funcionará porque tenemos múltiples variables o nombres de campo con valores asignados a ellos.

Nuestra fecha de inicio está escrita actualmente como un valor de texto y necesitamos transformarla en un formato de fecha adecuado. Para hacer eso, envuelva la función Date.FromText a su alrededor. 

Para la hora de inicio , que también tiene formato de cadena, debemos convertirla en un número. Luego, podemos dividirlo por cien y redondearlo para eliminar cualquier lugar decimal. 

Usando la función Number.From , divida nuestro valor de Fecha de inicio por 100 . Luego, envuélvalos dentro de la siguiente función, Number.Round , y agregue , 0 al final para redondear sin lugares decimales. 

Para Fecha de finalización y Hora de finalización, simplemente copiaremos las mismas funciones que teníamos en Fecha de inicio y Hora de inicio respectivamente, y las reemplazaremos con las variables apropiadas.

Una vez que las variables estén formateadas correctamente, cree otro campo en nuestro registro para alguna lógica adicional. Cree una nueva línea presionando Enter. 

Necesitamos construir una lista de fechas desde la primera fecha hasta la fecha de finalización. Llamaremos a esas fechas LD y usaremos la función List.Dates . 

El primer parámetro de esta función es el inicio como fecha que sería nuestra SD. Luego, quiere un conteo como número o la diferencia entre la fecha de inicio y la fecha de finalización. Podemos obtener eso usando la función Number.From seguida de ( ED – SD) + 1 . Tenga en cuenta que el "+1" no se muestra en los siguientes conjuntos de capturas de pantalla, pero debería haber un +1 .

El tercer parámetro se llama el paso como duración y queremos un incremento de un día. Podemos obtener eso usando Duration.From (1). 

Hemos creado una lista con fechas desde la primera fecha de inicio hasta la fecha de finalización. Lo que queremos a continuación es crear una lista de tiempos que vaya junto con eso. 

Llamemos a este LT para tiempos de lista. LT puede tener un período de un solo día, lo que requiere una lógica ligeramente diferente que si abarca varios días. Así que eso es algo para lo que tenemos que crear una condición. 

Queremos que la condición diga "si nuestra fecha de inicio es la misma que nuestra fecha de finalización, entonces la hora de finalización menos la hora de inicio". Los resultados deben estar en una lista de formato, por lo que usamos el inicializador de lista representado por { } . 

Ahora, si tenemos un intervalo de fechas que abarca varios días, la primera fecha será 24 menos la hora de inicio. Agregaremos esto a nuestra condición como la declaración else . Nuevamente, formatee eso como una lista usando las llaves.

En este punto, no hemos tenido en cuenta los otros días completos, por lo que usaremos el símbolo de ampersand (&) para agregar varias listas. Para cada día completo, cree una lista con el valor 24 que representa las 24 horas que tenemos en un día. Usaremos List.Repeat para eso. 

Con la función List.Repeat , cree una lista que contenga 24 y repítala varias veces contando la cantidad de días en LD . Para lograr eso, use List.Count (LD)   y luego reste 2 porque tenemos una lista separada para nuestra fecha de inicio y crearemos otra lista para la hora de finalización. 

En esencia, esto creará una lista con solo 24 horas para cada día completo. 

Para el tiempo final, podemos agregarlo nuevamente como una lista usando el signo Y, luego, usando los inicializadores de la lista, llamemos al ET. 

Hasta ahora, tenemos dos grandes listas, una lista de fechas y una lista de horas, cuyas longitudes son iguales entre sí. A partir de esas dos listas, podemos construir una sola tabla. 

En una nueva línea, cree otra variable para la tabla a la que llamaremos t y use la función Table.FromColumns . Esta función requiere listas como lista y usaremos nuestro LD. 

Nuestro LD contiene fechas individuales desde la fecha de inicio hasta la fecha de finalización. Podemos transformar esa lista de fechas al valor de fecha de fin de mes pasando LD y llamando a la función Date.EndOfMonth . Luego, en la siguiente línea, obtengamos también  nuestro LT .

Presione Enter para crear otra línea y establecer nuestra tabla como tabla de tipo . Nuestra tabla tendrá dos columnas, la primera columna será una columna de fecha. Por lo tanto, cree una columna de fecha y llame a la función Date.Type .

La segunda columna será nuestra columna Horas y será un número entero. Por lo tanto, crearemos una columna Hrs y llamaremos a Int8.Type.

Tenemos el corchete de cierre para cerrar nuestro registro y un paréntesis de cierre para cerrar nuestra función Table.AddColumn . Haga clic en Listo y obtendremos una lista de registros en una columna personalizada. 

Nuevamente, haga clic en el espacio en blanco al lado de cada Registro y la tabla debería abrirse en la parte inferior de la pantalla. 

Observe que tenemos nuestra tabla t en la última fila. Intentemos profundizar en uno de los Registros. Haga clic derecho en el primer resultado y Agregar como nueva consulta . 

Obtendremos el mismo resumen que antes y si hacemos clic en la Tabla , deberíamos obtener nuestros valores de Fecha y Hora .

Pero estamos interesados ​​principalmente en la tabla de salida final. Para centrarnos en eso, eliminemos la consulta de ayuda haciendo clic con el botón derecho en Personalizar y eligiendo Eliminar . Confirme haciendo clic en Eliminar en la ventana emergente.

Una vez eliminado, podemos volver a nuestra consulta inicial y nuevamente hacer clic en el espacio en blanco al lado de Grabar. Mostrará los mismos resultados que antes. Pero esta vez, expandamos la barra de fórmulas y llamemos [t] junto a nuestros corchetes de cierre.

En la columna Personalizado, podemos ver que cada Registro se cambia a Tabla . 

Horas de trabajo entre fechas de LuckyTemplates: Proyección

De la tabla que estamos viendo, solo hay dos columnas de interés: el nombre del personal y nuestra columna personalizada. Podemos usar la proyección para retener esas dos columnas solas de esta tabla. 

Vaya a la barra de fórmulas y, junto a nuestro paréntesis de cierre, seleccione los campos que deseamos conservar colocándolos entre corchetes. Luego presione la marca de verificación.

Deberíamos obtener este resultado.

Podemos expandir la tabla anidada haciendo clic en los iconos de flecha  al lado de Personalizado . Desmarque Usar el nombre de la columna original como prefijo y haga clic en Aceptar.

Nuestra tabla debería aparecer así.

Horas de trabajo entre fechas de LuckyTemplates: valores agregados

Todo lo que queda por hacer ahora es agregar estos valores. Para hacerlo, seleccione la columna Nombre del personal y vaya a Transformar. 

En la ventana emergente, elija Horas en la columna Valores porque es lo que queremos agregar. Luego, seleccione Suma en Función de valor agregado . Luego haga clic en Aceptar.

Nuestro resultado final se verá así. 

Creación de una tabla de fechas en LuckyTemplates La forma más rápida posible
Comparación de tiempo para tablas de fechas no estándar en LuckyTemplates
Valor de fecha y hora: cómo quitar segundos

Conclusión

En este blog, ha visto un enfoque del Desafío Start Stop de Access Analytic. Aprendió cómo calcular el número total de horas trabajadas por cada miembro del personal en un mes y cómo lidiar con posibles errores tipográficos en sus datos. 

Con esta técnica y LuckyTemplates, las horas de trabajo entre fechas se pueden calcular fácilmente en su propia organización, o como una práctica para profundizar sus conocimientos y habilidades de LuckyTemplates.