Calendrier perpétuel des vacances – POTW #12 (Solution Power Query)

Dans cet article de blog, nous allons explorer ma solution au . Pour ce défi, nous avons revisité le premier défi Problème de la semaine de LuckyTemplates, où nous avons créé un calendrier de vacances perpétuel à partir d'un mélange de dates spécifiques et relatives. Mais cette fois-ci, nous n'utiliserons que Power Query . Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Si vous êtes bloqué sur la répartition du problème lui-même, vous pouvez revoir la pour voir s'il y a des éléments dans l'une des approches qui peuvent vous aider à relever ce défi.

En lisant les soumissions, j'ai vu un mélange de personnes tirer parti de l'interface utilisateur et écrire des fonctions personnalisées pour créer un calendrier perpétuel. Cela a été agréable d'analyser chaque entrée, et si vous voulez vous inspirer, je vous encourage à faire de même.

Lorsque je travaillais moi-même sur ce scénario, j'ai fini par créer plusieurs solutions. Celui que j'ai choisi illustre le mieux comment vous pouvez non seulement décomposer un problème en plus petits morceaux , mais aussi les rendre visibles . Ceci est très utile si vous rencontrez une erreur ou des résultats inattendus.

Table des matières

• Le point de départ pour créer un calendrier perpétuel
• Récupération des années à partir du tableau des dates
• Obtenir le chiffre de la colonne du jour
• Appel de la fonction personnalisée pour le calendrier perpétuel des vacances
• Création d'une liste de dates
• Application de la logique conditionnelle
• Examen du code M pour le calendrier perpétuel des vacances
• Conclusion

Le point de départ pour créer un calendrier perpétuel

Cette table de fête brute est notre point de départ. Pour reconstituer une date, nous avons besoin d'une année, d'un mois et d'un jour pour des dates précises. Il ne manque que l'année.

Mais pour les dates relatives, nous devons faire un peu plus de travail. Pour commencer, nous devons diviser cette colonne de jour en chiffre et en jour de semaine. Passons en revue ces étapes.

Récupération des années à partir du tableau des dates

Pour rendre cette solution dynamique, je vais récupérer les années de la table dates . Dans la barre de formule, vous pouvez voir que j'ai utilisé une projection pour obtenir une table à une seule colonne à partir de ma table de dates. Ceci, bien sûr, contiendra des doublons. Pour les supprimer, j'ai ajouté Table.Distinct .

Lorsque je clique sur le côté dans l'espace blanc à côté de l'un de ces tableaux, vous verrez également une liste avec les années distinctes présentes dans ma requête de table de dates.

On nous a donné un nom de mois , mais nous aurons en fait besoin d' un numéro de mois . Désormais, lorsque nous concaténons une année à un nom de mois, nous pouvons utiliser la fonction Date.From pour renvoyer le premier de ce mois en tant que valeur de date.

En ajoutant Date.Month , il ne reste que le numéro du mois pour cette date spécifique. Pour janvier, il renvoie un 1. Pour février, il renvoie un 2, et ainsi de suite.

Obtenir le chiffre de la colonne du jour

Pour obtenir le chiffre de la colonne du jour, nous avons décidé de ne pas diviser la colonne et d'extraire la valeur à la place. Comme vous pouvez le voir dans la barre de formule, nous avons utilisé Text.BeforeDelimiter et utilisé cet espace pour identifier les parties droite et gauche de cette chaîne de texte.

S'il n'y a pas d'espace, nous voulons retourner ce qui reste dans la colonne du jour. Ainsi, pour le premier enregistrement, cela renvoie un 1. Pour le deuxième enregistrement qui contient cet espace, il renvoie la première partie de la chaîne de texte, le mot Third .

Nous utiliserons une approche similaire pour obtenir le nom de la date, mais utilisez la fonction List.PositionOf . Cela renvoie le décalage de base zéro de la valeur dans la liste passée en premier argument, et il renvoie -1 si la valeur n'apparaît pas dans cette liste.

Pour le premier enregistrement, il passe la valeur 1, qui n'est pas présente dans notre liste. C'est pourquoi il renvoie la valeur de -1. Pour le deuxième enregistrement, il renvoie la valeur Monday .

Il le transmet à notre fonction et renvoie un 1 car il s'agit du deuxième élément de notre liste. Ensuite, nous remplacerons toutes les valeurs -1 par un null .

À ce stade, nous avons créé tous les blocs de construction dont nous avons besoin pour appeler la fonction personnalisée.

Appel de la fonction personnalisée pour le calendrier perpétuel des vacances

Dans la barre de formule, nous pouvons voir que la fonction prend quatre arguments : une année , un mois , un chiffre et un jour de la semaine .

Mais comment détermine-t-il cette valeur de date ? Nous utiliserons à nouveau List.PositionOf pour obtenir une valeur numérique pour le chiffre. Nous avons une liste contenant le premier, le deuxième, le troisième et le quatrième. Lorsque nous passons le chiffre à List.PositionOf , il renvoie le décalage de base zéro.

Encore une fois, si une valeur n'est pas présente, elle renvoie un -1. Donc, pour le premier enregistrement, nous passons la valeur 1. 1 n'est pas présent dans cette liste, c'est pourquoi elle renvoie la valeur de -1. Pour le deuxième enregistrement, nous transmettons la valeur Third, qui est le troisième élément de notre liste. Cela renvoie la valeur de 2.

Création d'une liste de dates

Créons maintenant une liste de dates. Nous allons déterminer la date de début de ce mois, déterminer le nombre de jours de ce mois et utiliser List.Dates pour créer une liste contenant tous les jours de ce mois.

Ensuite, nous utiliserons List.Select pour ne conserver que les dates correspondant à un jour de semaine spécifique.

Lorsque nous cliquons sur le côté à côté de l'une de ces listes, nous pouvons voir qu'elle ne contient que ces quatre éléments.

Application de la logique conditionnelle

Enfin, nous appliquerons une logique conditionnelle pour construire ou récupérer une date. Si le jour de la semaine est null , indiquant que nous avons une date spécifique, nous pouvons utiliser le #date intrinsèque pour construire une valeur de date.

Si le chiffre est égal à last, nous pouvons utiliser la fonction List.Reverse pour inverser l'ordre dans la liste et extraire la première valeur.

Si nous voulons changer cet ordre croissant en décroissant, nous pouvons toujours extraire la dernière date de cette plage de dates. Peu importe si nous avons quatre ou cinq éléments dans la liste. En inversant la commande et en extrayant le premier article, nous avons toujours la dernière date.

Pour tous les autres cas, nous utilisons List.Skip .

Nous pouvons extraire la valeur en fonction de cette position sur la liste. Pour le jour de Thanksgiving, le chiffre est le quatrième et le quatrième renvoie un 3. Cela ignorera les trois premières valeurs de la liste et renverra toujours le quatrième élément.

Mettons tout cela ensemble et passons à la requête de solution. Pour l'étape source, nous appelons la table de dates de vacances RAW. Ensuite, nous allons ajouter une colonne qui renvoie une liste avec des dates.

Examen du code M pour le calendrier perpétuel des vacances

Vérifions le code M de la colonne. Pour chaque ligne du tableau, nous avons imbriqué un tableau contenant les années distinctes du tableau des dates. Ensuite, nous avons transformé les valeurs de cette colonne d'année en appelant la fonction personnalisée.

Bien sûr, pour pouvoir invoquer la fonction personnalisée, nous devons d'abord effectuer une configuration. Nous devons obtenir le numéro du mois , la valeur numérique et bien sûr le jour de la semaine . Ensuite, à partir de cette table imbriquée, nous avons conservé la liste avec les dates.

Lorsque nous cliquons sur le côté dans l'espace blanc, nous pouvons voir un aperçu de cette liste ci-dessous.

L'étape suivante consiste à développer cette liste et à ajouter le CelebratedOnDate .

C'est la logique que nous avons utilisée dans ce calcul : si la date est un samedi, alors on va soustraire un jour pour qu'on atterrisse un vendredi. Si c'est un dimanche, alors nous allons ajouter un jour pour atterrir un lundi. Si ce n'est pas un samedi ou un dimanche, alors nous voulons une valeur nulle.

Enfin, la dernière étape consiste à changer les types.

Conclusion

Juste un petit rappel que est une série en cours qui se déroule tous les 1er et 3e mercredis sur le forum LuckyTemplates. Le problème publié la 1ère semaine nécessite une solution basée sur DAX, tandis que le problème de la 3ème semaine demande une solution basée sur Power Query.

Si vous avez apprécié le contenu couvert dans ce POTW, veuillez vous abonner à la chaîne de télévision LuckyTemplates pour en savoir plus. Nous avons une énorme quantité de contenu qui sort tout le temps de moi-même et d'une gamme de créateurs de contenu, tous dédiés à l'amélioration de la façon dont vous utilisez LuckyTemplates et Power Platform.

Mélisse