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.
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
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.
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:
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.
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
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
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:
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