Importer une table pivot dans Power BI

L’objectif est d’apprendre à Importer une table pivot dans Power BI. Une table pivot s’appelle aussi un tableau croisé dynamique Excel.

Plus précisément, l’objectif est de retrouver la structure « à plat » sous-jacente du tableau croisé dynamique. En effet, un tableau croisé dynamique Excel n’est généralement pas adapté pour une exploitation simple et immédiate dans Power BI Desktop.

Il sera nécessaire d’appliquer de nombreuses transformations pour retrouver la structure à plat sous-jacente. C’est la raison pour laquelle chaque étape est décomposée et expliquée.

De plus, outre la découverte de nouvelles manipulations dans Power BI, l’intérêt de cet exercice est aussi de fournir une méthode générale de résolution pour l’import des tableaux croisés dynamiques Excel. En effet, cette méthode est applicable et généralisable à vos propres tableaux croisés dynamiques métiers, y compris ceux qui sont complexes.

Les manipulations se font essentiellement dans l’éditeur Power Query.

Gardez à l’esprit que l’interface a pu évoluer fortement depuis que cet article a été rédigé.

Comprendre les données

Avec Microsoft Excel, ouvrir le fichier XLS_Tableau_Croisé_Dynamique.xlsx afin de prendre connaissance de sa structure et de ses données.

Pour information, ce tableau croisé dynamique présente le coût par canal de ventes de chaque fabricant pour des gammes de produits.

Il y a 5 gammes de produits : Automobile, Electricité, Électroménager, Habitation, Mécanique ; 4 canaux de ventes : Catalogue, Magasin, Revendeur, Site web et 25 fabricants.

Se connecter à un fichier Excel

Premièrement, créer un nouveau fichier Power BI Desktop vierge nommé PBI_Lab26.pbix.

Ensuite, dans le ruban Accueil de Power BI Desktop, cliquer directement sur le connecteur Excel (il est facilement reconnaissable) : l’explorateur Windows s’ouvre.

Puis, avec l’explorateur Windows, ouvrir le fichier XLS_Tableau_Croisé_Dynamique.xlsx : le volet navigateur s’ouvre.

Ensuite, cocher la case Ong_Couts, puis, en bas, cliquer sur le bouton Transformer les données (ne pas cliquer sur Charger).

Pour vous aider, regarder la copie d’écran ci-dessous.

Rétrograder les en-têtes

Lors de la connexion, les en-têtes ont été automatiquement promus. Ce qui ne nous arrange pas car il faut d’abord supprimer les lignes inutiles. Vous allez donc annuler cette promotion.

Vous pourriez évidemment supprimer les étapes appliquées à partir de En-têtes promus. Toutefois, vous allez découvrir une nouvelle fonctionnalité. Notamment, vous allez rétrograder les en-têtes.

Pour ce faire, dans le ruban Accueil, cliquer sur Utiliser la première ligne pour les en-têtes, puis dans le sous-menu qui s’affiche, cliquer sur Utiliser les en-têtes comme première ligne (ne cliquer pas sur Utiliser la première ligne pour les en-têtes) : les en-têtes disparaissent.

Cet exemple, purement pédagogique, sert à introduire une nouvelle manipulation qui se révèle utile lorsque vous vous rendez compte, après plusieurs étapes, qu’il aurait été préférable de ne pas promouvoir les en-têtes. En effet, cette manipulation permet de conserver la logique des étapes précédentes sans « tout casser ».

Dans le cas présent, il est évidemment plus simple et plus efficace de supprimer les deux étapes à partir de En-têtes promus. Aussi, appliquez la solution de votre choix. Peu importe.

Supprimer les lignes inutiles

La première ligne (Somme de CUHT, Étiquettes de colonnes, Column3, etc.) est inutile. Vous allez la supprimer.

Pour ce faire, dans le ruban Accueil, cliquer sur Supprimer les lignes, puis dans le sous-menu qui s’affiche, cliquer sur Supprimer les lignes du haut. Dans le pop-up qui s’affiche, indiquer le nombre 1 dans Nombre de lignes puis cliquer sur le bouton OK.

De même, la dernière ligne (Total général, etc.) est inutile. Vous allez la supprimer aussi.

Cliquer à nouveau sur Supprimer les lignes, puis dans le sous-menu qui s’affiche, cliquer sur Supprimer les lignes du bas. Dans le pop-up qui s’affiche, indiquer le nombre 1 dans Nombre de lignes puis cliquer sur le bouton OK.

Pour vous aider, regarder la copie d’écran ci-dessous.

Importer une table pivot dans Power BI : Transposer

L’objectif de ces manipulations est de retrouver la structure « à plat » sous-jacente.

Actuellement, les Canaux de ventes et les Fabricants sont sur 2 lignes différentes. Par exemple, si vous regardez Column2, Column3, etc. il y a une ligne pour les valeurs des Canaux et en-dessous une autre ligne pour les Fabricants, comme ALM Indus.

Concrètement, il faut donc pouvoir afficher un tableau où les Canaux et les Fabricants sont sur la même ligne et dans deux colonnes différentes, ce qui revient à intervertir les lignes et les colonnes. Cette opération s’appelle une transposition.

Pour ce faire, dans le ruban Transformer, cliquer sur Transposer.

Pour vous aider, regarder la copie d’écran ci-dessous.

Remplir vers le bas

Évidemment, le résultat n’est pas encore parfait mais, au moins les Canaux et les Fabricants sont maintenant sur deux colonnes différentes.

Certains Fabricants ont bien leur Canal dans Column1 mais c’est plutôt l’exception, car les autres ont la valeur null. Vous allez généraliser l’association entre Fabricants et Canaux.

Pour ce faire, vérifiez que la colonne Column1 est bien sélectionnée, puis dans le ruban Transformer, cliquer sur Remplir, puis dans le sous-menu qui s’affiche, cliquer sur Vers le bas.

Cela fait apparaître sur chaque ligne les pays, une fois que les totaux intermédiaires auront disparu. Si vous ne voyez pas les totaux intermédiaires, descendez un peu l’ascenseur vertical.

Pour vous aider, regarder la copie d’écran ci-dessous.

Importer une table pivot dans Power BI : Filtrer les lignes

Il faut éliminer ces totaux intermédiaires.

Pour ce faire, cliquer sur la flèche de la Column1, puis dans le pop-up qui s’affiche, indiquer Total dans la zone Rechercher, et cocher la case (Sélectionner tous les résultats de la recherche) pour désélectionner les totaux.

Pour vous aider, regarder la copie d’écran ci-dessous.

Puis cliquer sur le bouton OK pour appliquer votre filtre.

Pour vous aider, regarder la copie d’écran ci-dessous.

Nommer les colonnes

Pour l’instant, vous avez fait le plus simple😊

En effet, les gammes de produits sont sur la première ligne. L’objectif est de les récupérer sur la même ligne que les couples Canaux et Fabricants.

Pour ce faire, dans le ruban Accueil, cliquer sur Utiliser la première ligne pour les en-têtes, puis dans le sous-menu qui s’affiche, cliquer sur Utiliser la première ligne pour les en-têtes.

Cette manipulation permet de nommer proprement chaque colonne des gammes. Autrement dit, le tableau est à nouveau croisé mais dans l’autre sens par rapport au tableau d’origine.

Pour vous aider, regarder la copie d’écran ci-dessous.

Importer une table pivot dans Power BI : Dépivoter

L’opération Dépivoter va permettre de convertir toutes les colonnes, sauf celles qui sont sélectionnées, en paires Attribut-Valeur. Plutôt que d’expliquer avec des mots son fonctionnement, il est préférable que vous puissiez voir le résultat pour comprendre.

Pour ce faire, sélectionner les deux premières colonnes (Column1, Étiquettes de lignes) puis dans le ruban Transformer, cliquer sur Dépivoter les colonnes, puis dans le sous-menu qui s’affiche, cliquer sur Dépivoter les autres colonnes.

Cela permet de convertir toutes les colonnes, sauf celles qui sont sélectionnées, en paires Attribut-Valeur.

Pour vous aider, regarder la copie d’écran ci-dessous.

Renommer les colonnes

Le résultat est presque parfait. Il reste à renommer correctement chaque colonne.

Double-cliquer dans Column1 puis nommer la colonne Canal.

Double-cliquer dans Étiquettes de lignes puis nommer la colonne Fabricants.

Double-cliquer dans Attribut puis nommer la colonne Gamme.

Double-cliquer dans Valeur puis nommer la colonne Coût.

Pour vous aider, regarder la copie d’écran ci-dessous.

C’est terminé. Vous avez réussi à remettre « à plat » le tableau croisé dynamique d’origine.

Bien qu’ils puissent y avoir quelques variantes, notez que ces transformations sont valables quel que soit le tableau croisé dynamique.

Pour prendre une analogie, c’est comme pour le jeu Rubik’s Cube®. En effet, il existe des méthodes qui expliquent comment le résoudre, quel que soit la position de départ. Ces méthodes s’appuient sur des transformations précises pour le faire.

😊

Importer une table pivot dans Power BI : Enregistrer son travail

Appliquer les transformations puis enregistrer et fermer PBI_Lab26.pbix.

Dans cet exercice, vous avez appris à importer un tableau croisé dynamique Excel en suivant un ensemble de transformations.

Ces transformations sont toujours les mêmes, à quelques variantes près. Cependant, même si votre tableau croisé dynamique est plus complexe que cet exemple, il est toujours possible de le ramener à une structure simple.

Par exemple, si votre tableau croisé dynamique possède plusieurs en-têtes, il faut les fusionner pour n’avoir qu’une ligne d’en-tête et appliquer ces transformations. Une fois que les transformations sont appliquées, il est facile de décomposer la ligne d’en-tête.

La manipulation est terminée.