Transformer des lignes en colonnes dans Excel revient souvent à chercher la fonction TRANSPOSE ou à coller avec transposition. Ces raccourcis fonctionnent sur des tableaux figés. Dès que la source change, il faut tout recommencer. Power Query propose une approche différente : définir la transformation une seule fois, puis actualiser le résultat à chaque mise à jour des données.
Pivoter ou transposer dans Power Query : deux opérations distinctes
La confusion entre pivoter et transposer est fréquente. Les deux convertissent des lignes en colonnes, mais la logique sous-jacente diffère. Un tableau comparatif clarifie les cas d’usage.
A lire aussi : Pourquoi le portage salarial séduit de plus en plus les informaticiens
| Critère | Transposer (Table.Transpose) | Pivoter (Table.Pivot) |
|---|---|---|
| Principe | Rotation complète du tableau : les lignes deviennent des colonnes et inversement | Une colonne de catégories devient plusieurs colonnes, chaque valeur unique crée un en-tête |
| Cas d’usage typique | Tableau à une seule ligne ou petit référentiel à réorienter | Données tabulaires avec répétitions (dates, catégories, produits) |
| Gestion des doublons | Aucune : les valeurs sont simplement déplacées | Nécessite une fonction d’agrégation (somme, moyenne, comptage) si plusieurs valeurs par croisement |
| Nombre de colonnes en sortie | Égal au nombre de lignes d’origine | Égal au nombre de valeurs distinctes dans la colonne pivotée |
| Actualisation automatique | Oui, mais les en-têtes sont perdus (renommage manuel nécessaire) | Oui, y compris les en-têtes, à condition de définir la colonne source correctement |
La transposition convient à des structures simples. Pour des jeux de données volumineux ou évolutifs, le pivotement reste la méthode fiable dans Power Query.
A lire en complément : Tableau en ligne alternatif : créer facilement des tableaux sans utiliser Excel !
Étape par étape : pivoter des lignes en colonnes avec Power Query dans Excel
Plutôt que de décrire l’interface bouton par bouton, concentrons-nous sur les décisions qui conditionnent le résultat. Trois paramètres déterminent la réussite d’un pivot.
Choisir la colonne d’attributs
C’est la colonne dont les valeurs deviendront les futurs en-têtes. Dans un tableau de ventes avec une colonne « Mois » contenant Janvier, Février, Mars, chaque mois deviendra une colonne distincte après pivotement.
Sélectionnez cette colonne, puis dans l’onglet Transformer, cliquez sur « Colonne de tableau croisé dynamique » (ou « Pivot Column » en anglais). Power Query vous demande ensuite quelle colonne contient les valeurs à répartir.
Définir la colonne de valeurs et la fonction d’agrégation
Si chaque croisement ligne/colonne ne correspond qu’à une seule valeur, sélectionnez « Ne pas agréger ». C’est le cas le plus courant quand on restructure un export brut.
Si plusieurs lignes partagent la même combinaison, Power Query renverra une erreur sans agrégation. Il faut alors choisir somme, moyenne ou comptage selon le besoin analytique. L’oubli de l’agrégation est la première cause d’erreur au pivotement.
Vérifier le typage des colonnes après pivot
Après l’opération, les nouvelles colonnes héritent parfois du type « Tout » (Any). Forcez le type numérique, date ou texte selon le contenu. Un typage incorrect empêche les calculs ultérieurs et provoque des erreurs silencieuses lors du chargement dans la feuille Excel.
Dépivotement Power Query : l’opération inverse pour restructurer un tableau croisé
Beaucoup de fichiers Excel arrivent déjà sous forme de tableau croisé avec des mois ou des catégories en colonnes. Pour les exploiter dans un tableau croisé dynamique ou les charger dans Power BI, il faut revenir à un format tabulaire long.
Power Query appelle cette opération « Dépivoter les colonnes » (Unpivot). Elle transforme chaque en-tête de colonne en valeur dans une colonne « Attribut », et chaque cellule correspondante en valeur dans une colonne « Valeur ».
Trois variantes existent dans le menu contextuel :
- Dépivoter les colonnes sélectionnées : seules les colonnes en surbrillance sont converties en lignes. Les autres restent intactes et se répètent pour chaque paire attribut-valeur.
- Dépivoter les autres colonnes : toutes les colonnes sauf celles sélectionnées sont dépivotées. Cette option est plus robuste quand de nouvelles colonnes apparaissent dans la source, car Power Query dépivote automatiquement les ajouts.
- Dépivoter uniquement les colonnes sélectionnées : comportement identique à la première option, mais avec un ancrage strict sur les noms de colonnes choisis.
« Dépivoter les autres colonnes » protège la requête contre les ajouts futurs. Si un nouveau mois apparaît dans le fichier source, il sera automatiquement intégré au dépivotement sans modifier la requête.
Langage M : contrôler le pivot au-delà de l’interface graphique
L’éditeur graphique génère du code M dans la barre de formule. Pour un pivot simple, la ligne ressemble à ceci :
Table.Pivot(Source, List.Distinct(Source[Mois]), "Mois", "Montant", List.Sum)
Cinq arguments structurent cette fonction :
- La table source sur laquelle opérer.
- La liste des valeurs distinctes qui deviendront des en-têtes (ici les mois).
- Le nom de la colonne d’attributs (« Mois »).
- Le nom de la colonne de valeurs (« Montant »).
- La fonction d’agrégation (List.Sum, List.Average, List.Count, ou null pour aucune agrégation).
Modifier directement le code M permet de gérer des cas que l’interface ne couvre pas. Par exemple, remplacer List.Distinct(Source[Mois]) par une liste ordonnée fixe garantit que les colonnes apparaissent dans un ordre précis, indépendamment de l’ordre d’apparition dans les données.
Le code M offre un contrôle sur l’ordre des colonnes pivotées que le menu graphique ne propose pas nativement.
Requête Power Query réutilisable entre Excel, Power BI et Fabric
Un point rarement abordé dans les tutoriels Excel : le moteur M est partagé entre Excel, Power BI Desktop et Microsoft Fabric Dataflows Gen2. Une requête de pivotement écrite dans Excel fonctionne sans modification dans Power BI, et inversement.
Cette compatibilité a une conséquence pratique directe. Un analyste qui construit sa transformation de lignes en colonnes dans Excel peut la copier-coller dans un Dataflow Power BI pour alimenter un rapport partagé, sans réécrire la logique. Le même code M de pivot fonctionne dans Excel, Power BI et Fabric.
Power Query a aussi étendu ses connecteurs vers des sources cloud comme Snowflake ou Google BigQuery. La transposition ou le pivotement peut donc s’appliquer directement sur des données distantes, avant même leur chargement dans une feuille de calcul. Le traitement se fait côté requête, ce qui réduit le volume de données transféré.
La transformation de lignes en colonnes avec Power Query ne se limite pas à un clic sur « Pivoter ». Le choix entre transposition et pivotement, la gestion de l’agrégation et le typage post-pivot conditionnent la fiabilité du résultat. L’option « Dépivoter les autres colonnes » reste la plus résiliente face aux données évolutives. Et le code M, accessible dans la barre de formule, donne la main sur les cas que l’interface graphique laisse de côté.
