Si vous avez déjà commencé à travailler avec Power Query, vous savez qu’il s’agit d’un outil puissant quand vient le temps d’importer, de transformer et de charger des grandes quantités de données dans un modèle de données Power Pivot. À mon avis, Power Query est la plus belle avancée des dernières années de Microsoft au niveau d’Excel. Cela dit, il est possible qu’en utilisant ce nouvel add-in, vous ayez rencontré quelques embûches. Le but de cet article est de vous exposer la méthodologie de travail que j’ai développée au fil du temps, afin de m’assurer de la plus grande qualité et de la plus grande robustesse dans mes modèles. Si vous avez d’autres façons de faire qui pourraient améliorer cette méthodologie, je vous invite à partager avec moi vos approches, à partir de la zone de commentaires au bas de cet article.
Donner un nom significatif aux bases de données sous-jacentes
D’abord, j’aime travailler avec des bases de données qui ont des noms significatifs. Cela rend les transformations plus faciles. Par exemple, si vos données d’origine sont dans une table Excel, je recommande de nommer d’abord la table Excel avec un nom siginificatif, comme Ventes_Web ci-bas. Ainsi, lorsque vous vous connecterez à ce fichier, qui possède plusieurs tables, vous saurez immédiament laquelle de ces tables vous intéresse.
Ci-bas, on peut apercevoir que le fichier Ventes possède 4 onglets et 4 tables. Les 4 tables ont des noms significatifs. Il est ainsi beaucoup plus facile de trouver ce que l’on cherche.
S’assurer du bon format des données
Avec le temps, j’ai réalisé qu’il était plus simple de travailler le format des données directement dans Power Query. Ainsi, quand je termine une série de transformations sur une source de données dans Power Query, je m’assure que chaque colonne est sauvegardée dans le bon format avant de sauvegarder ma requête.
Créer des requêtes intermédiaires (connexion seulement)
Au moment où j’écris cet article, il y a un certain « bug » avec Power Query. En effet, lorsque vous faites des imporations de données et que vous insérez des étapes de transformations et que vous enregistrez le tout directement dans le modèle de données Power Pivot, vous vous exposez à certaines problématiques. Par exemple, si votre table est ensuite liée à d’autres tables dans votre modèle de données et que vous avez des colonnes et des champs calculés et que vous souhaitez ensuite modifier les étapes de transformation de votre requête, vous abimerez votre modèle de données (celui-ci pourrait même tomber dans un état permanent de « Read Only »). J’en ai personnellement fait l’expérience et je vous assure que ce fût très douloureux :s. Pour vous éviter de subir le même sort, je partage ici avec vous une méthodologie, influencée de tout ce que j’ai trouvé comme information sur le web, de la part d’utilisateurs ayant été confrontés aux mêmes problèmes.
D’abord, limitez-vous à créer des requêtes « avec connexion seulement » et faites toutes les transformations que vous devez faire dans ces requêtes.
Créer des références aux requêtes intermédiaires
Ensuite, créez une référence à la première requête et cette fois-ci, enregistrez cette requête dans le modèle de données Power Pivot.
Au moment où j’écris cet article, lorsque l’on fait une référence à une autre requête, Power Query enregistre automatiquement la requête dans le fichier Excel (ce n’est pas ce que l’on souhaite). Vous devez donc cliquer sur le bouton de droite sur votre nouvelle requête et décocher l’option « enregistrer dans le fichier Excel » et cocher plutôt l’option « enregistrer la requête dans le modèle de données » Power Pivot.
Cela dit, une autre option est possible. C’est celle que j’utilise. Vous pouvez simplement vous rendre dans la section des sources de données de Power Query et choisir l’option « From Other Sources ». Ensuite, vous écrivez simplement =nomdelarequête et vous êtes libre d’enregistrer le tout directement dans le modèle de données Power Pivot.
Nommer la requête correctement du premier coup
Lorsque vous avez créé votre requête référée, donnez-lui tout de suite un nom significatif avant de l’enregistrer dans le modèle de données Power Pivot. Pourquoi? Parce que c’est ce nom que prendra Power Pivot pour nommer l’onglet correspondant. Au moment où j’écris cet article, lorsque l’on renomme la requête dans Power Query, celle-ci n’est pas renommée dans Power Pivot.
Créer des groupes de requêtes
En suivant la méthodologie décrite ci-haut, vous obtiendrez un nombre important de requêtes. Afin de faciliter l’usage et la compréhension de ces requêtes, je vous recommande fortement d’utiliser l’option « Move to group » pour classer ces différentes requêtes.
Dans mon exemple, je dois d’abord importer une source de données de ventes web, une source de données de ventes de partenaires, une source de données de ventes de services et une source de données de ventes de produits. Ensuite, je dois utiliser l’option de fusion « Append » pour obtenir les ventes internes (services et produits) et les ventes externes (web et partenaires). Finalement, je dois utiliser à nouveau l’option « Append » pour fusionner les ventes internes et les ventes externes, afin d’obtenir les ventes totales. Toutes ces requêtes sont en mode « connexion seulement ». Je retournerai à ces requêtes seulement si je dois éventuellement faire des ajustements aux étapes de transformations sous-jacentes. Autrement, je pourrai grouper le dossier, de façon à voir plus clair dans mes requêtes.
Par exemple, ici, dans mon modèle final, tous mes dossiers sont groupés et il est facile de voir que j’ai 14 requêtes qui servent à importer et transformer des sources de données et 8 requêtes qui servent à importer des données dans mon modèle de données Power Pivot. Dans « Other queries », qui est créé par défaut par Power Query et qui ne peut être renommé, on pourrait par exemple classer toutes les fonctions personnalisées qui sont utilisées dans les requêtes.
Donner des noms significatifs aux étapes de transformation
Il peut s’avérer plus facile de donner des noms significatfs aux étapes de transformation, surtout si plusieurs usagers doivent travailler avec les requêtes et les comprendre individuellement. Pour cela, il s’agit de faire un clic droit de souris sur une étape de transformation et lui donner un nom approprié.
Formation Power BI
Si cela vous intéresse, j’offre une formation Power BI d’une journée qui couvre le volet Power Query, Power Pivot, Power View et Power Map.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et 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.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble