Dépivoter un tableau croisé pour le rendre exploitable

Un client m’envoie régulièrement un export avec un mois par colonne : Janvier, Février, Mars, jusqu’à Décembre, une ligne par produit ou par centre de coût. C’est lisible à l’œil, agréable pour une impression, et complètement inutilisable pour construire un tableau croisé dynamique ou une formule SOMME.SI.ENS dessus. Ces outils veulent une observation par ligne : un produit, un mois, un montant. Pas un produit et douze montants côte à côte.

C’est la structure dite « en tableau croisé » ou « au format large », et c’est le format que produisent presque tous les exports comptables et la plupart des rapports collés depuis un autre tableau croisé dynamique. Power Query règle ce problème en une opération : dépivoter. Je vous montre la version qui survit à un changement de source, pas seulement celle qui marche une fois.

Le point de départ

Prenons un tableau typique, une fois chargé dans Power Query :

Produit    | Janvier | Février | Mars
Casque A   |     120 |     95  |  140
Casque B   |      40 |     55  |   60

L’objectif est d’obtenir :

Produit    | Mois     | Valeur
Casque A   | Janvier  |    120
Casque A   | Février  |     95
Casque A   | Mars     |    140
Casque B   | Janvier  |     40
…

Dans l’éditeur Power Query, sélectionnez la colonne Produit (celle qui doit rester en l’état), clic droit, puis Dépivoter les autres colonnes. Ce choix précis compte : il existe aussi une commande Dépivoter les colonnes, qui fait l’inverse et qui a un effet secondaire à connaître, détaillé plus bas.

Le code M généré

L’opération produit cette ligne, ajoutée à votre requête :

Depivote = Table.UnpivotOtherColumns(
    Source,
    {"Produit"},
    "Attribut",
    "Valeur"
)

Argument par argument :

• Source : la table à transformer, telle qu’elle arrive de l’étape précédente.
• {"Produit"} : la liste des colonnes à garder telles quelles. Toutes les colonnes qui ne figurent pas dans cette liste seront dépivotées. C’est le nom du paramètre qui prête à confusion : il s’appelle pivotColumns alors qu’il désigne les colonnes qu’on ne touche pas, pas celles qu’on dépivote.
• "Attribut" : le nom de la nouvelle colonne qui reçoit les en-têtes d’origine (ici, les noms de mois). Vous pouvez le remplacer directement par "Mois" à cette étape, plutôt que de renommer la colonne ensuite.
• "Valeur" : le nom de la nouvelle colonne qui reçoit les valeurs. Même remarque : nommez-la "Montant" ou ce qui convient tout de suite, c’est gratuit.

Le piège : la mauvaise commande pour la mauvaise raison

Power Query propose deux commandes qui se ressemblent :

• Dépivoter les colonnes : vous sélectionnez les colonnes à dépivoter (Janvier, Février, Mars). Elle génère Table.Unpivot(Source, {"Janvier", "Février", "Mars"}, "Attribut", "Valeur"), une liste figée des colonnes concernées.
• Dépivoter les autres colonnes : vous sélectionnez la ou les colonnes à garder (Produit). Elle génère Table.UnpivotOtherColumns, dont la règle est « tout ce qui n’est pas dans cette liste ».

La différence ne saute pas aux yeux tant que la source ne change pas. Elle devient un vrai problème le mois où votre export gagne une colonne Avril, ce qui arrive à chaque rapport qui s’étoffe au fil de l’année. Avec Table.Unpivot et sa liste figée, la colonne Avril reste une colonne à part entière : la requête ne plante pas, elle produit silencieusement un résultat incomplet, le genre d’erreur qu’on ne remarque qu’en recomptant un total qui ne tombe pas juste. Avec Table.UnpivotOtherColumns, la nouvelle colonne est automatiquement absorbée dans le dépivotage puisqu’elle n’est pas dans la liste des colonnes gardées.

La règle pratique : dès que le nombre de colonnes à dépivoter est susceptible de changer (un mois de plus, un centre de coût de plus, un produit de plus en colonne), préférez systématiquement Dépivoter les autres colonnes. Réservez Table.Unpivot aux cas où c’est l’inverse qui est stable : peu de colonnes fixes à dépivoter, et un nombre de colonnes à garder qui varie, lui, d’un fichier à l’autre.

Deuxième piège : les cellules fusionnées

Un tableau croisé collé depuis Excel a souvent une colonne de catégorie fusionnée visuellement : le nom de la catégorie n’apparaît que sur la première ligne du groupe, les suivantes semblent vides. Une fois importées dans Power Query, ces cellules ne sont pas fusionnées : elles sont simplement null. Dépivoter directement dans cet état associe la valeur à un Attribut correct mais à une catégorie vide sur la majorité des lignes.

Le correctif se fait avant le dépivotage, avec Table.FillDown, qui recopie vers le bas la dernière valeur non vide rencontrée dans la colonne :

Rempli = Table.FillDown(Source, {"Categorie"}),
Depivote = Table.UnpivotOtherColumns(Rempli, {"Categorie", "Produit"}, "Mois", "Montant")

Vérifiez d’abord que le vide correspond bien à une fusion visuelle et non à une donnée manquante légitime : Table.FillDown ne fait pas la différence, il recopie dans tous les cas.

Troisième piège : la ligne ou la colonne de total

Beaucoup d’exports incluent une ligne Total ou une colonne Total en plus des mois ou des produits. Dépivotée sans y prendre garde, cette ligne devient une valeur Attribut = "Total" comme les autres : si la table sert ensuite à une somme par produit, le total s’additionne avec le détail et double le résultat. Filtrez-la avant ou après le dépivotage :

SansTotal = Table.SelectRows(Source, each [Produit] <> "Total")

Si le total est une colonne plutôt qu’une ligne, excluez-la de la liste des colonnes passées à Table.UnpivotOtherColumns en l’ajoutant à la liste des colonnes gardées, puis supprimez-la avec Table.RemoveColumns juste après : elle ne sera alors ni dépivotée à tort, ni conservée dans le résultat final.

Quatrième piège : le type de la colonne Valeur

Avant dépivotage, chaque colonne de mois avait son propre type, détecté indépendamment des autres. Après Table.UnpivotOtherColumns, toutes ces valeurs sont empilées dans une seule colonne Valeur, et Power Query redétecte un type unique sur l’ensemble. Si une seule cellule d’un des mois contenait du texte (un « n.d. » ou une remarque saisie par erreur dans une cellule numérique), le type détecté sur toute la colonne Valeur peut basculer en texte, ou faire remonter des erreurs sur des lignes qui étaient pourtant numériques à l’origine. Fixez le type explicitement en dernière étape plutôt que de garder l’étape Type modifié générée automatiquement :

Types = Table.TransformColumnTypes(Depivote, {{"Valeur", type number}})

Une valeur non numérique remonte alors en erreur visible sur sa seule ligne, au lieu de fausser silencieusement une somme calculée plus loin.

Disponibilité

Power Query est intégré nativement à Excel depuis la version 2016 (sous le nom « Obtenir et transformer les données »), avec un module complémentaire pour Excel 2010 et 2013. Les commandes Dépivoter les colonnes et Dépivoter les autres colonnes, ainsi que les fonctions Table.Unpivot, Table.UnpivotOtherColumns, Table.FillDown et Table.TransformColumnTypes utilisées ici, sont disponibles dans toutes les versions de Power Query, y compris sur Excel 2016 : rien dans cet article n’exige Microsoft 365.

Ce que ça change

Une fois la table dépivotée et ses types fixés, un tableau croisé dynamique classique ou une formule SOMME.SI.ENS sur la colonne Mois et la colonne Produit fonctionnent normalement. C’est aussi le format qu’attend un graphique croisé dynamique, ou une consolidation avec d’autres sources qui n’ont pas forcément les mêmes mois en colonnes. La documentation complète des fonctions utilisées : Table.UnpivotOtherColumns, Table.Unpivot, Table.FillDown et Table.TransformColumnTypes.