Dépivoter un tableau croisé avec Power Query sur Excel
Un exercice corrigé pour dépivoter un tableau croisé en base plate avec Power Query (Unpivot), prêt pour un TCD ou Power BI, et s'entraîner sur le fichier Excel à télécharger.
Un tableau de ventes avec les mois en colonnes est agréable à lire, mais un TCD ou Power BI n'en tire pas grand-chose, puisque chaque mois y forme une colonne distincte. La commande Dépivoter de Power Query (Unpivot en anglais) règle ce problème en quelques clics. Elle empile les colonnes de mois en lignes et produit une base plate avec une colonne Mois et une colonne Montant !
Dans cet exercice, tu vas nommer et importer un tableau croisé vendeurs/mois, sélectionner la colonne Vendeur, puis dépivoter les autres colonnes pour obtenir une ligne par combinaison vendeur/mois. Le réflexe à retenir, c'est qu'une analyse sérieuse part d'une base plate, et que Dépivoter est l'outil Power Query pour y passer en un clic.
Ce que tu vas construire
- Identifier pourquoi un tableau croisé (mois en colonnes) bloque les analyses TCD.
- Nommer le tableau de départ, puis l'ouvrir dans l'éditeur Power Query via l'onglet Données.
- Dépivoter les autres colonnes pour transformer les colonnes de mois en lignes.
- Renommer les colonnes Attribut et Valeur en Mois et Montant.
- Fixer le type de la colonne Montant après le dépivotage, pour couvrir aussi les mois ajoutés plus tard.
- Charger la base plate résultante et vérifier le nombre de lignes (21 lignes).
À connaître avant de commencer
- Savoir ouvrir l'éditeur Power Query.
- Comprendre la différence entre un tableau croisé (lisible) et une base plate (exploitable).
Voici les données de départ de cet exercice. Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Vendeur | Janvier | Février | Mars |
| 2 | Dupont | 4 500 | 3 200 | 5 100 |
| 3 | Martin | 1 200 | 2 800 | 1 900 |
| 4 | Garcia | 6 000 | 4 100 | 3 300 |
| 5 | Leroy | 3 700 | 3 900 | 4 400 |
| 6 | Petit | 2 600 | 1 800 | 2 200 |
| 7 | Moreau | 5 300 | 6 200 | 5 800 |
| 8 | Lambert | 1 100 | 1 500 | 2 100 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Dépivoter les AUTRES colonnes pour rester robuste
En sélectionnant la colonne Vendeur et en choisissant Dépivoter les autres colonnes, tu rends la requête extensible. Si tu ajoutes Avril ou Mai au tableau source, ils seront dépivotés à l'actualisation sans modifier la requête.
Une base plate alimente un TCD
Après dépivotage, ta base plate est prête à être insérée dans un tableau croisé dynamique. Fais le lien avec l'exercice tableau croisé dynamique pour construire un rapport complet à partir de cette base.
Pour aller plus loin sur Power Query
https://ledojo.club/blog/formation-power-query-excel
S'entraîner sur 3 exercices similaires à dépivoter un tableau croisé avec Power Query
Tableau de bord commercial
Construire un tableau de bord commercial pour suivre le CA, le taux de conversion, le classement et l'atteinte des objectifs de chaque vendeur.
Voir l'exercice
Analyse des ventes par région
Construire un tableau d'analyse des ventes par région pour identifier tes zones fortes, tes zones en retard et prioriser tes efforts commerciaux.
Voir l'exercice
Pipeline commercial CRM
Construire un pipeline commercial dans Excel pour suivre tes opportunités de la prospection à la signature, calculer la valeur pondérée et prévoir ton CA.
Voir l'exercice
Tu as peut-être encore une question
Dans l'éditeur Power Query, sélectionne la ou les colonnes à conserver comme identifiants (ici Vendeur), fais un clic droit sur l'en-tête et choisis « Dépivoter les autres colonnes » (« Supprimer les autres colonnes du tableau croisé dynamique » dans certaines versions). Power Query empile toutes les autres colonnes dans deux colonnes nommées Attribut et Valeur, que tu peux ensuite renommer. Le même menu propose aussi « Dépivoter uniquement les colonnes sélectionnées », qui ne traite que les colonnes désignées.
Un tableau croisé (mois en colonnes) est lisible pour un humain, mais un TCD, un graphique ou Power BI n'y trouvent aucun champ Mois à consolider, puisque chaque mois est une colonne distincte. La base plate (une ligne par vendeur et par mois) est le format attendu par les outils de reporting.
Pivoter transforme des valeurs d'une colonne en en-têtes de colonnes (base plate vers tableau croisé). Dépivoter fait l'inverse, il transforme les en-têtes de colonnes en valeurs d'une colonne (tableau croisé vers base plate). Dans Power Query, Dépivoter sert surtout à préparer des données exportées depuis des outils qui présentent les mois ou les catégories en colonnes.
Sélectionne les colonnes identifiants comme Vendeur et choisis Dépivoter les autres colonnes, plutôt que Dépivoter uniquement les colonnes sélectionnées sur les colonnes de mois. La première écrit dans le code M la liste des colonnes à garder (Table.UnpivotOtherColumns), la seconde la liste des mois à traiter (Table.Unpivot). Un mois Avril ajouté au tableau source est donc dépivoté à l'actualisation avec la première, et reste une colonne à part avec la seconde.
Le dépivotage ne transforme pas les montants en texte. La colonne Montant reprend le type des colonnes de mois, celui que leur a donné l'étape automatique Type modifié si elle est restée après Source (en général Nombre entier, pour des montants sans décimales), aucun type si elle a été supprimée, et les montants chargés dans la feuille restent des nombres que SOMME additionne. Le typage en Nombre décimal après le dépivotage protège surtout la requête quand le tableau grandit, car une étape Type modifié qui cite Janvier, Février et Mars par leur nom laisse sans type un mois ajouté plus tard, et la colonne Montant perd alors son type.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de CRM simple
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule