Transformer une colonne en ligne dans Excel avec Power Query dépivotage repose sur une logique précise : chaque valeur répartie sur plusieurs colonnes devient une ligne distincte, avec son attribut associé. Cette opération, appelée « Annuler le pivot des colonnes », est accessible depuis l’onglet Transformation de l’éditeur Power Query. Ce qui distingue Power Query d’une simple transposition manuelle, c’est que la requête se réapplique à chaque actualisation – à condition que cette actualisation se passe sans accroc.
Dépivotage dans Power Query : les trois modes de sélection des colonnes
L’éditeur Power Query propose trois variantes du dépivotage, et le choix entre elles détermine directement la robustesse de la transformation face à des données sources qui évoluent.
| Mode | Comportement | Usage recommandé |
|---|---|---|
| Annuler le pivot des colonnes sélectionnées | Transforme en lignes uniquement les colonnes choisies | Structure source stable, colonnes de valeurs fixes |
| Annuler le pivot des autres colonnes | Transforme toutes les colonnes non sélectionnées comme identifiants | Nouvelles colonnes de mesures attendues à l’actualisation |
| Annuler le pivot uniquement les colonnes sélectionnées | Similaire au premier, mais exclut les colonnes non sélectionnées du résultat | Nettoyage ciblé sans résidu de colonnes |
Le deuxième mode, « Annuler le pivot des autres colonnes », est souvent le plus adapté aux sources dynamiques. En sélectionnant les colonnes d’identification (nom de produit, identifiant client, date de référence), toute nouvelle colonne de mesure ajoutée à la source sera automatiquement dépivotée à la prochaine actualisation. L’inverse pose un problème : si une nouvelle colonne d’identification apparaît dans la source, elle sera dépivotée par erreur et créera des lignes parasites dans le tableau de sortie.
Étapes concrètes pour transformer une colonne en ligne avec Power Query
La procédure suit un ordre fixe dans l’éditeur. Voici les étapes à respecter pour obtenir un résultat fiable :
- Charger la table source dans Power Query via l’onglet Données, puis « À partir d’un tableau/plage » ou « Obtenir des données » selon la version d’Excel utilisée (disponible depuis Excel 2016).
- Identifier les colonnes d’identification à préserver comme lignes, par exemple un identifiant produit ou une colonne de région, en les sélectionnant avec Ctrl+clic.
- Dans l’onglet Transformation, cliquer sur « Annuler le pivot des colonnes » et choisir le mode « Annuler le pivot des autres colonnes » pour couvrir les ajouts futurs dans la source.
- Renommer les colonnes « Attribut » et « Valeur » générées automatiquement pour refléter le contenu réel (ex. : « Mois » et « Chiffre de vente »).
- Charger le résultat dans la feuille Excel via « Fermer et charger », puis vérifier la structure obtenue avant d’enchaîner avec un tableau croisé dynamique si nécessaire.
Actualisation Power Query : quand le tableau dépivoté affiche des données périmées
Un résultat affiché dans Excel après un dépivotage Power Query n’est pas nécessairement un résultat fraîchement calculé. Une actualisation peut échouer sans déclencher de message d’erreur visible au niveau du classeur, ce qui rend la détection du problème non triviale.
Le premier signal à surveiller est le volet « Requêtes et connexions » (onglet Données). Chaque requête y affiche son état : une icône d’avertissement ou la mention « Erreur » indique un échec d’actualisation, même si la feuille de destination continue d’afficher les anciennes données sans modification apparente.
Le deuxième signal concerne l’horodatage. Dans les propriétés de la connexion (clic droit sur la requête, puis « Propriétés »), la date et l’heure de la dernière actualisation réussie sont enregistrées. Si cette date ne correspond pas à l’actualisation attendue, la requête a soit échoué, soit ne s’est pas déclenchée.
Causes fréquentes d’échec silencieux
Plusieurs configurations produisent ce comportement. Un fichier source déplacé ou renommé génère une erreur de chemin que Power Query enregistre dans le journal de la requête, sans interrompre l’affichage des données précédentes. De même, une modification de structure dans la source – suppression d’une colonne d’identification, changement de type – peut provoquer un échec partiel : certaines étapes de la requête se terminent, d’autres non, et le résultat intermédiaire chargé correspond à une étape antérieure.
Pour forcer la détection, l’outil le plus direct est la combinaison Ctrl+Alt+F5 (ou « Actualiser tout » depuis l’onglet Données), suivie d’une vérification immédiate du volet « Requêtes et connexions ». Si une requête affiche une erreur après cette manipulation, le message associé précise l’étape en défaut dans le pipeline de transformation.
Limites de l’actualisation automatique à l’ouverture
L’option « Actualiser les données lors de l’ouverture du fichier », accessible dans les propriétés de connexion, ne garantit pas une mise à jour lorsque le classeur est fermé. L’actualisation planifiée sans intervention humaine nécessite un mécanisme externe (Power Automate, tâche planifiée Windows, ou pipeline de données côté serveur). Sans ce mécanisme, l’ouverture du fichier déclenche bien une actualisation, mais uniquement si les sources de données sont accessibles au moment de l’ouverture.
Power Query vs transposition manuelle vs tableau croisé dynamique
Ces trois méthodes répondent à des besoins distincts. La transposition manuelle (Collage spécial > Transposer) produit un résultat statique, sans lien avec la source. Le tableau croisé dynamique agrège les données mais ne modifie pas la structure de la table sous-jacente. Power Query est la seule option qui enregistre la transformation comme une requête rejouable, applicable à des sources évolutives.
La limite de Power Query sur ce point est précise : les modifications de structure de la source exigent un test d’actualisation explicite, pas une hypothèse de compatibilité. Ajouter une colonne de mesure dans la source est géré automatiquement avec le mode « Annuler le pivot des autres colonnes » ; ajouter une colonne d’identification, en revanche, casse la logique de dépivotage si elle n’a pas été anticipée dans la sélection initiale.
Le choix du mode de dépivotage au moment de la configuration de la requête détermine donc directement la maintenance à prévoir. Une requête construite sur le mode « autres colonnes » avec des identifiants bien définis absorbe les évolutions de la source sans intervention, à condition que la structure des colonnes d’identification reste stable.
