Solution LuckyTemplates entre les dates des heures de travail

Dans le blog d'aujourd'hui, je vais démontrer une approche du défi Start Stop d'Access Analytic où, à l'aide de LuckyTemplates, les heures de travail totales entre les dates seront calculées. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog .

La tâche consiste à calculer le nombre total d'heures d'un membre du personnel pour chaque mois et date. J'ai présenté des dates de début et de fin qui peuvent s'étendre sur des mois et tenir compte du statut en traitant d'éventuelles fautes de frappe. 

Table des matières

• Jeu de données pour les heures de travail entre les dates de LuckyTemplates
• Filtrage des lignes
• Ajouter une colonne personnalisée
• Heures de travail entre les dates de LuckyTemplates : les variables
• Heures de travail entre les dates de LuckyTemplates : projection
• Heures de travail entre les dates de LuckyTemplates : valeurs agrégées
• Conclusion

Jeu de données pour les heures de travail entre les dates de LuckyTemplates

Passons à l'éditeur Power Query. 

Voici nos données pour ce défi. Nous avons un tableau avec des colonnes pour le nom du personnel, la date de début, l'heure de début, la date d'arrêt, l'heure d'arrêt et le statut.

Filtrage des lignes

Commençons par sélectionner les lignes en fonction de leur statut, et l'éditeur de requête peut nous aider avec cette logique. Cliquez sur la flèche déroulante à côté de Statut. Sélectionnez Filtres de texte et choisissez Ne commence pas par. 

Dans la fenêtre Filtrer les lignes qui apparaîtra, écrivez « e ». Cliquez ensuite sur OK .

Rien ne se passera encore et c'est bien parce que nous écrivons un « e » minuscule et le « Exclure » dans nos données qui commence par un « E » majuscule.

Pour résoudre ce problème, nous utiliserons le troisième paramètre facultatif de Text.StartsWith . Dans la barre de formule, allez à la fin de la fonction Text.StartsWith. Ajoutez une virgule et entrons dans notre Comparer.OrdinalIgnoreCase. Cliquez sur la coche et cela devrait supprimer les lignes Exclure .

Bien sûr, nous pourrions avoir quelqu'un qui a tapé par erreur "Exclure" sans un "E" initial. Dans ce cas, nous pouvons créer une logique supplémentaire pour accompagner la situation. 

Nous pouvons tester si le mot n'inclut pas un "X" en étendant notre fonction et en utilisant Text.Contains . Pour ce faire, ajoutez ou Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Notez que par rapport à la première clause, nous avons remplacé « e » par un « x ». Nous continuons à ignorer la casse et enveloppons un ensemble de parenthèses autour de nos deux clauses. Cliquez sur la coche et nous devrions obtenir un tableau sans les lignes contenant le statut Exclude ou Xclude .

Ajouter une colonne personnalisée

Passons maintenant à la partie principale de ce défi. Nous allons ajouter une colonne personnalisée à l'aide de l'interface utilisateur, puis passer à l'éditeur. 

Commencez par l' icône de la mini table dans le coin supérieur gauche de la table. Cliquez dessus et dans la liste déroulante, sélectionnez Ajouter une colonne personnalisée . 

À ce stade, nous sommes seulement intéressés à apporter les données dont nous aurons besoin plus tard. Et comme nous allons travailler avec plusieurs champs, créons un enregistrement à l'aide des initialiseurs d'enregistrement, représentés par des crochets. 

Créons des variables et affectons les colonnes disponibles. Commencez par taper SD que nous assimilerons à la date de début. Nous pouvons le faire en cliquant sur Date de début dans la colonne de droite.

Nous ferons de même pour les variables ST, ED et ET à affecter respectivement à l'heure de début, à la date d'arrêt et à l'heure d'arrêt . Ensuite, fermez le dossier avec une parenthèse fermante. Cliquez sur OK.

Une colonne personnalisée avec les enregistrements sera ajoutée au tableau. Cliquez sur l'espace blanc à côté de Record et un volet de prévisualisation s'ouvrira. Cela apportera toutes les valeurs de cette ligne.

Heures de travail entre les dates de LuckyTemplates : les variables

Nous pouvons ensuite formater nos variables via la fenêtre de l'éditeur avancé . Voici à quoi devrait ressembler la fenêtre de l'éditeur avancé. 

Ensuite, mettons nos variables dans de nouvelles lignes séparées.

Maintenant, nous pouvons étendre la logique dont nous avons besoin. Ainsi, au lieu d'un enregistrement, nous pourrions également utiliser une expression let imbriquée ici. Notez que l'un ou l'autre fonctionnera car nous avons plusieurs variables ou noms de champs avec des valeurs qui leur sont attribuées.

Notre date de début est actuellement écrite sous forme de valeur textuelle et nous devons la transformer en un format de date approprié. Pour ce faire, enroulez la fonction Date.FromText autour d'elle. 

Pour l' heure de début qui est également formatée en tant que chaîne, nous devons la convertir en nombre. Ensuite, nous pouvons le diviser par cent et l'arrondir pour supprimer les décimales. 

À l'aide de la fonction Number.From , divisez notre valeur Start Date par 100 . Ensuite, enveloppez-les dans la fonction suivante, Number.Round , et ajoutez , 0 à la fin pour arrondir sans décimales. 

Pour la date de fin et l'heure de fin, nous allons simplement copier les mêmes fonctions que nous avions respectivement dans la date de début et l'heure de début , et les remplacer par les variables appropriées.

Une fois les variables correctement formatées, créez un autre champ dans notre enregistrement pour une logique supplémentaire. Créez une nouvelle ligne en appuyant sur Entrée. 

Nous devons construire une liste de dates à partir de la première date jusqu'à la date de fin. Nous appellerons ces dates LD et nous utiliserons la fonction List.Dates . 

Le premier paramètre de cette fonction est la date de début qui serait notre SD. Ensuite, il veut un compte comme nombre ou la différence entre la date de début et la date de fin. Nous pouvons obtenir cela en utilisant la fonction Number.From suivie de ( ED – SD) + 1 . Notez que le "+1" n'est pas affiché dans les prochaines séries de captures d'écran, mais il devrait y avoir un +1 .

Le troisième paramètre s'appelle le pas comme durée et nous voulons un incrément d'un jour. Nous pouvons obtenir cela en utilisant Duration.From (1). 

Nous avons créé une liste avec des dates allant de la toute première date de début jusqu'à la date d'arrêt. Ce que nous voulons ensuite, c'est créer une liste d'heures qui va avec cela. 

Appelons ce LT pour les temps de liste. LT peut avoir une période de jour d'un seul jour qui nécessite une logique légèrement différente que si elle s'étend sur plusieurs jours. C'est donc quelque chose pour lequel nous devons créer une condition. 

Nous voulons que la condition dise "si notre date de début est la même que notre date de fin, alors l'heure de fin moins l'heure de début". Les résultats doivent être dans une liste de format donc nous utilisons l' initialiseur de liste représenté par { } . 

Maintenant, si nous avons une plage de dates qui s'étend sur plusieurs jours, la première date sera 24 moins l'heure de début. Nous ajouterons ceci à notre condition en tant qu'instruction else . Encore une fois, formatez-le sous forme de liste en utilisant les accolades.

À ce stade, nous n'avons pas pris en compte les autres jours entiers, nous utiliserons donc le symbole esperluette (&) pour ajouter plusieurs listes. Pour chaque journée entière, créez une liste avec la valeur 24 représentant les 24 heures que nous avons dans une journée. Nous utiliserons List.Repeat pour cela. 

Avec la fonction List.Repeat , créez une liste qui contient 24 et répétez cela plusieurs fois en comptant le nombre de jours dans LD . Pour y parvenir, utilisez List.Count (LD)   puis soustrayez 2 car nous avons une liste distincte pour notre date de début et nous allons créer une autre liste pour l'heure de fin. 

Essentiellement, cela créera une liste avec seulement 24 heures pour chaque journée entière. 

Pour l'heure de fin, nous pouvons l'ajouter à nouveau sous forme de liste en utilisant l'esperluette, puis, en utilisant les initialiseurs de liste, appelons l'ET. 

Jusqu'à présent, nous avons deux grandes listes - une liste de dates et une liste d'heures - dont la longueur est égale l'une à l'autre. A partir de ces deux listes, nous pouvons construire un seul tableau. 

Dans une nouvelle ligne, créez une autre variable pour la table que nous appellerons t et utilisez la fonction Table.FromColumns . Cette fonction nécessite des listes en tant que liste et nous utiliserons notre LD. 

Notre LD contient des dates individuelles à partir de la date de début jusqu'à la date d'arrêt. Nous pouvons transformer cette liste de dates en valeur de date de fin de mois en passant LD et en appelant la fonction Date.EndOfMonth . Ensuite, dans la ligne suivante, obtenons également  notre LT .

Appuyez sur Entrée pour créer une autre ligne et définir notre table comme type table . Notre tableau aura deux colonnes, la première colonne étant une colonne de date. Créez donc une colonne Date et appelez la fonction Date.Type .

La deuxième colonne sera notre colonne Heures et ce sera un entier. Ainsi, nous allons créer une colonne Hrs et appeler Int8.Type.

Nous avons la parenthèse fermante pour fermer notre enregistrement et une parenthèse fermante pour fermer notre fonction Table.AddColumn . Cliquez sur Terminé et nous obtenons une liste d'enregistrements sous une colonne personnalisée. 

Encore une fois, cliquez sur l'espace blanc à côté de chaque enregistrement et le tableau devrait s'ouvrir dans la partie inférieure de l'écran. 

Notez que nous avons notre table t dans la dernière ligne. Essayons d'explorer l'un des enregistrements. Faites un clic droit sur le premier résultat et Ajouter en tant que nouvelle requête . 

Nous obtiendrons le même résumé que précédemment et si nous cliquons sur le Table , nous devrions obtenir nos valeurs Date et Hrs .

Mais nous sommes principalement intéressés par le tableau de sortie final. Pour nous concentrer sur cela, supprimons la requête d'aide en cliquant avec le bouton droit sur Personnalisé et en choisissant Supprimer . Confirmez en cliquant sur Supprimer dans la fenêtre contextuelle.

Une fois supprimé, nous pouvons revenir à notre requête initiale et cliquer à nouveau sur l'espace blanc à côté de Record. Il affichera les mêmes résultats que précédemment. Mais cette fois, développons la barre de formule et appelons [t] à côté de nos crochets fermants.

Dans la colonne Custom, nous pouvons voir que chaque Record est remplacé par Table . 

Heures de travail entre les dates de LuckyTemplates : projection

Dans le tableau que nous examinons, il n'y a que deux colonnes intéressantes : le nom du personnel et notre colonne personnalisée. Nous pouvons utiliser la projection pour ne retenir que ces deux colonnes de ce tableau. 

Allez dans la barre de formule et à côté de notre parenthèse fermante, sélectionnez les champs que nous voulons conserver en les plaçant entre crochets. Appuyez ensuite sur la coche.

Nous devrions obtenir ce résultat.

Nous pouvons développer le tableau imbriqué en cliquant sur les icônes fléchées  à côté de Custom . Décochez Utiliser le nom de la colonne d'origine comme préfixe et cliquez sur OK.

Notre tableau devrait apparaître comme ceci.

Heures de travail entre les dates de LuckyTemplates : valeurs agrégées

Il ne reste plus qu'à agréger ces valeurs. Pour ce faire, sélectionnez la colonne Nom du personnel et accédez à Transformer. 

Dans la fenêtre contextuelle, choisissez Hrs sous la colonne des valeurs car c'est ce que nous voulons agréger. Ensuite, sélectionnez Somme sous Fonction de valeur agrégée . Cliquez ensuite sur OK.

Notre sortie finale ressemblera à ceci. 

Création d'une table de dates dans LuckyTemplates Le moyen le plus rapide possible
Comparaison de temps pour les tables de dates non standard dans LuckyTemplates
DateTime Value: Comment supprimer les secondes

Conclusion

Dans ce blog, vous avez vu une approche du défi Start Stop d'Access Analytic. Vous avez appris à calculer le nombre total d'heures travaillées par chaque membre du personnel au cours d'un mois et à gérer les éventuelles fautes de frappe dans vos données. 

Avec cette technique et LuckyTemplates, les heures de travail entre les dates peuvent facilement être calculées dans votre propre organisation, ou en tant que pratique pour approfondir vos connaissances et compétences LuckyTemplates.