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:
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:
Nous avons nommé cette requête : Staging.
Résultat recherché
À partir de ces données, notre lecteur cherche à créer le tableau suivant:
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.
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.
Nous avons d’abord cherché à récupérer la DateMin de notre échantillon de données, par le biais de la fonction “Regrouper par”.
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.
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.
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.
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).
À 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.
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.
Nous avons ensuite supprimé les doublons, ce qui nous a permis d’avoir toutes les combinaisons AAAA-MM sous analyse.
Nous avons ensuite dupliqué notre première colonne et nous l’avons transformée en date de fin de mois.
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.
Ceci nous a permis de construire la requête suivante:
À 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.
Notre colonne personnalisée retourne donc 16 jours actifs en janvier 2018, ce qui est cohérent avec notre DateDébut et DateFin.
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.
Il ne nous restait plus qu’à extraire le contenu des tables générées par notre fonction.
Nous avons finalement créé une colonne de tableau croisé dynamique à partir de nos colonnes Mois et Nbjours.
Ceci nous a permis de recréer le tableau suivant dans une visualisation de table.
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
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 [email protected]
Jeu de briques
Snake
Pacman
Bubble