Ce n’est pas de la magie, c’est Power Query!

Publié le 07 janvier 2019
par Sophie Marchand M.Sc.

Il y a quelques jours, Paul a posé une question sur notre forum et cet article vise à répondre à cette question. En somme, Paul voulait savoir comment faire pour transformer la première table ci-dessous en la deuxième table, par le biais de Power Query, dans Excel. Nous lui fournissions ici une réponse basée sur la fonction List.Dates en langage M.

Ci-dessous, je réponds donc à cette question avec un tutoriel détaillé. Mon but est toujours le même, soit celui d’utiliser le moins de code M possible afin d’arriver au résultat. Tout cela pour vous démontrer qu’en utilisant les fonctionnalités principales de Power Query, on peut transformer à peu près n’importe quelle source de données. N’oubliez pas qu’une fois les transformations effectuées, elles sont enregistrées à l’intérieur d’une requête et donc, du moment que des données seront ajoutées dans la première table et que l’on actualisera la requête, la deuxième table s’ajustera automatiquement, selon les règles établies.

 

1 – Importer les données

En guise de première étape, évidemment, j’ai importé la première table dans Power Query.

 

2 – Ajout de colonne personnalisée avec la fonction List.Dates

J’ai ensuite ajouté une colonne personnalisée dans laquelle j’ai inséré une fonction List.Dates afin de générer une ligne pour chacune des dates de publication. L’idée est de pouvoir ensuite identifier les dates où les publications se chevauchent.

Fonction List.Dates

 

Le résultat obtenu a été le suivant:

 

Et une fois les listes développées, j’ai obtenu une table comprenant une ligne par date de publication, par publication. À noter que l’image ci-dessous ne montre qu’un extrait de cette table.

 

3 – Regrouper par

J’ai donc ensuite regroupé les données par date (colonne “Personnalisé”) et j’ai demandé de compter les lignes et d’obtenir l’ensemble des lignes.

 

Ceci m’a permis d’obtenir une colonne “Nombre” et une colonne de tables avec les données précisées ci-dessous:

 

Voici un extrait du résultat obtenu une fois les tables développées, en extrayant seulement la colonne Publication:

Développement des tables

 

Si on jette un coup d’oeil plus bas dans la table, on voit qu’en novembre, chaque date est répétée puisqu’il y a une publication Appro nov 2018 et Noël 2018. On voit aussi le chiffre 2 dans la colonne Nombre.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

4 – Colonne personnalisée

J’ai ensuite ajouté une colonne pour fusionner le nom de la publication avec le nombre, question d’identifier les dates où il y a chevauchement.

 

Ceci m’a permis d’obtenir ce qui suit:

 

5 – Regrouper par

J’ai ensuite regroupé les données pour obtenir une ligne par fusion “publication et nombre” et j’ai demandé de calculer la date minimum et la date maximum.

 

Ceci m’a permis d’obtenir le résultat suivant:

 

Cette table comprend une ligne pour chacun des intervalles de dates souhaités dans la deuxième table de Paul Toutefois, Paul souhaite que les lignes 2 et 3 soient fusionnées. Il nous reste donc un peu de travail à faire.

 

6 – Colonne personnalisée avec Text.From

J’ai ensuite calculé un champ qui unit les dates minimum et maximum.

 

Ceci m’a permis d’obtenir ce qui suit:

 

J’ai ensuite supprimé les colonnes non nécessaires pour obtenir ce qui suit:

7 – Regrouper par

J’ai ensuite regroupé les données pour chaque intervalle de dates et j’ai demandé d’obtenir toutes les lignes.

 

Ceci m’a permis d’obtenir ce qui suit, soit la liste des intervalles de dates nécessaires à la création du deuxième tableau:

 

8 – Colonne personnalisée avec Table.ToList

De façon à pouvoir obtenir les informations de publication fusionnées, j’ai converti les tables ci-dessus en listes avec la fonction Table.ToList.

 

Ça m’a permis d’obtenir ce qui suit:

 

Et d’extraire ensuite les valeurs des listes:

 

En les fusionnant avec une virgule:

 

Ce qui a généré le résultat suivant:

 

Et après avoir supprimé une colonne et changé les types de données, j’ai obtenu ce qui suit:

 

9 – Remplacer les valeurs

L’étape suivante est plus difficile à réaliser sans s’approprier le moindrement le code M. Ce que l’on veut, c’est remplacer les dates et les guillemets par rien du tout, de façon à obtenir seulement la liste des publications. Pour faciliter les choses, ce que vous pouvez faire, c’est utiliser la fonctionnalité “Remplacer les valeurs” et par exemple, remplacer “ABC” par rien.

 

Ensuite, vous pouvez aller dans le code M pour remplacer le ABC. Dans ce cas-ci, on veut utiliser chacune des valeurs de la colonne Min-Max. Nous devons donc écrire ce qui suit:

 

Et pour remplacer les guillemets:

 

Et pour se débarrasser des doubles virgules (celles qui séparaient les publications et les dates):

 

Ça nous amène au résultat suivant. Il ne reste plus qu’une virgule à la toute fin.

 

On peut s’en débarrasser avec la fonctionnalité suivante:

 

On obtient donc ce qui suit:

 

10 – Résultat final

Il ne reste plus qu’à fractionner notre colonne de dates Min-Max pour obtenir la date de début et la date de fin:

 

Et voici le résultat final:

 

Cette approche est possible uniquement si on maîtrise les listes et les tables dans Power Query, dont notamment la fonction List.Dates et Table.ToList.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Pour une introduction au langage M, comme la fonction List.Dates, suivez la formation Excel – Introduction à Power Query et au langage M.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Voici quelques commentaires d’apprenants ayant suivi la formation - Excel Introduction à Power Query et au langage M

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

4 réflexions sur “Ce n’est pas de la magie, c’est Power Query!”

  1. Bonjour
    J’ai utilisé les 3 lignes suivantes
    Action Début Fin
    Compta 02/01/2019 25/01/2019
    Formation 10/01/2019 15/02/2019
    Consolidation 01/02/2019 28/02/2019

    et cela en fonctionne par correctement

    de même avec
    Action Début Fin
    Compta 01/10/2018 30/11/2018
    Formation 01/11/2018 15/11/2018

    Maque-t-il une étape ?
    Merci

    1. Sophie Marchand

      Bonjour,

      Difficile de répondre sans voir vos données… Vous pourriez déposer votre fichier dans notre forum si vous souhaitez qu’on y jette un coup d’oeil.

      Merci,

      Sophie

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut