Magazine Informatique

En réponse à une question sur notre forum: Créer une table avec le nombre de jours actifs par mois et par item dans l’éditeur Power Query

Publié le 13 juillet 2018 par Sopmar01 @mon_cher_watson

Récemment, un lecteur a posé une question sur notre forum concernant l’éditeur de requêtes Power Query qui, je le rappelle, peut être utilisé via Excel ou via Power BI. Cet article vise à répondre à cette question.

Question du lecteur

Voici la question posée par notre lecteur sur notre forum:

Question forum nb jours

Données originales

Voici les données originales proposées par notre lecteur, une fois que nous les avons importées dans l’éditeur de requêtes de Power BI:

Power Query Importation

Nous avons nommé cette requête : Staging.

Résultat recherché

À partir de ces données, notre lecteur cherche à créer le tableau suivant:

Résultat final nb jours

Ce tableau présente le nombre de jours actifs dans le mois, selon les dates de début et de fin spécifiées pour chacun des items en ligne.

Création de paramètres

Nous devrons tôt ou tard créer une fonction qui permettra de calculer le nombre de jours écoulés dans chaque mois. Pour ce faire, nous aurons besoin d’une date de début et d’une date de fin. Ce faisant, nous avons d’abord créé 2 paramètres, DateDébut et DateFin, auxquels nous avons attribué des valeurs temporaires.

Paramètres date début et date fin

Création de requête qui sera convertie en fonction

Nous avons créé une nouvelle requête qui prend sa source dans la requête Staging. Le but de cette requête sera de construire une fonction qui pourra être utilisée pour tous les items placés en ligne dans le tableau final.

Power Query Importation

Nous avons d’abord cherché à récupérer la DateMin de notre échantillon de données, par le biais de la fonction “Regrouper par”.

Lignes groupées

Une fois la DateMin récupérée, nous l’avons transformée en date de début du mois. Nous avons obtenu 2017-11-01.

Début mois

Nous avons ensuite effectué un clic droit de souris sur la date et cliqué sur Drill-Down. C’est ce qui permet d’en extraire la valeur. Ce faisant, lorsque nous référerons ensuite au mot DateMin, celui-ci retournera 2017-11-01.

Drill Down

Afin d’extraire la DateMax, nous avons d’abord cliqué sur le fx de la barre de formules et nous avons inscrit = Source, afin de retourner à l’étape Source.

Power Query

Nous avons procédé comme avec la DateMin afin d’extraire la DateMax (voir les étapes Lignes groupées 1, Fin du mois calculée, DateMax).

DateMax

À nouveau, nous avons cliqué sur fx dans la barre de formules mais cette fois-ci, nous ne sommes pas retournés à l’étape Source, nous avons plutôt introduit une fonction de liste en langage M. La fonction suivante permet de  créer une liste de dates, qui débute à la date DateMin, qui comprend un nombre d’items correspondant au nombre de jours entre la DateMax et la DateMin + 1, et présenté avec un pas d’une journée.

Power Query

Nous avons ensuite converti notre liste en table et nous avons ajouté une colonne à l’aide du menu Extraire. Nous avons choisi l’option “Extraire les premiers caractères” et nous avons inséré 7. Ce faisant, nous avons obtenu une colonne avec l’année et le mois de chacune des dates de notre liste de dates.

Premiers caractères insérés

Nous avons ensuite supprimé les doublons, ce qui nous a permis d’avoir toutes les combinaisons AAAA-MM sous analyse.

Doublons supprimés

Nous avons ensuite dupliqué notre première colonne et nous l’avons transformée en date de fin de mois.

Fin du mois calculée 1

Nous avons permuté nos colonnes afin de voir l’ensemble des mois sous analyse, suivis de la date de début et de la date de fin de chacun de ces mois. Par la suite, pour avons ajouté une colonne DateDébut et DateFin, qui sont simplement liées aux paramètres DateDébut et DateFin.

Personnalisée ajoutée

Ceci nous a permis de construire la requête suivante:

Personnalisée ajoutée 1

À partir de cette étape, l’idée est maintenant de créer une colonne qui retournera le nombre de jours actifs dans chacune des périodes en ligne, i.e. débutant par DébutMois et se terminant par FinMois. On veut donc savoir pour une période qui débute en 2018-01-10 et qui se termine en 2018-01-25, il y a combien de jours actifs en novembre 2017, en décembre 2017, en janvier 2018 et ainsi de suite… Pour cela, nous avons ajouté une colonne personnalisée avec une fonction if, then, else, permettant de valider toutes les possibilités. Vous pouvez apercevoir un extrait de ce code ci-dessous. Notez que vous pouvez récupérer le fichier avec la solution sur notre forum pour voir le code au complet. Nous ne prétendons pas qu’il s’agisse de la fonction la plus effective (le but du forum est de fournir des pistes de solutions et non de réaliser tout le travail pour nos lecteurs ;)), mais il s’agit d’un code qui retourne définitivement les bons résultats.

Personnalisée ajoutée 2

Notre colonne personnalisée retourne donc 16 jours actifs en janvier 2018, ce qui est cohérent avec notre DateDébut et DateFin.

Étape finale Sophie - Dates début fin

Après cette étape, nous avons pu convertir notre requête en fonction, que nous avons nommée FnDélai.

Nous avons créé une autre requête liée à notre requête Staging, celle-ci nommée Résultat final et nous y avons ajouté une colonne pour invoquer notre fonction FnDélai.

Appeler la fonction

Il ne nous restait plus qu’à extraire le contenu des tables générées par notre fonction.

Déployer la fonction

Nous avons finalement créé une colonne de tableau croisé dynamique à partir de nos colonnes Mois et Nbjours.

Colonne de tableau croisé dynamique

Ceci nous a permis de recréer le tableau suivant dans une visualisation de table.

Résultat final nb jours

Conclusion

Il est de plus en plus facile dans Power Query, de créer des requêtes performantes sans faire intervenir beaucoup de code M. J’essaie de résoudre les problématiques qui me sont soumises en utilisant le moins possible de code M, pour démontrer aux utilisateurs que Power Query est un outil qu’ils peuvent programmer, sans avoir à être des “programmeurs” chevronnés.


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Analytique d’affaires et Finance corporative. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel. De plus, nos formations peuvent mener à l’obtention d’une accréditation.

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com


Vous pourriez être intéressé par :

Retour à La Une de Logo Paperblog

Ces articles peuvent vous intéresser :

A propos de l’auteur


Sopmar01 3934 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte