L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Power Query

Transposer un tableau empilé

2 février 2015
Imprimer E-mail

Il arrive qu'après importation de données, on se retrouve avec non pas un tableau contenant les titres et les divers enregistrements situés dessous, mais un tableau d'une seule colonne comme celui de ce classeur.

Si nous avons peu de données comme dans ce classeur, le 1er geste est d'effectuer des copier-coller ou glisser-deplacer pour remettre sous forme d'un tableau bien ordonné. Mais si le nombre de données à repositionner est important cela devient franchement fastidieux. Je vous propose donc d'utiliser Power Query pour effectuer cette transposition.

 Charger les données dans Power Query

Sur l'onglet POWER QUERY, cliquez sur la commande A partir du Tableau.

Attention ! Sélectionnez vos données plus une cellule vide au dessus du Tableau.

Requête Power Query depuis un tableau Excel

 Nous récupérons donc nos données dans une colonne de Power Query. Vous pouvez bien sûr renommer la colonne obtenue, par Exemple "A Transposer".

Ajoutons une colonne d'index : Sur l'onglet Ajouter une colonne, sélectionnez la commande Ajouter une colonne d'index. Au passage, vous remarquerez que vous avez le choix de début pour votre index numérique (1).

Power Query - Ajouter une colonne d'index

Pour faciliter la lecture de ce qui suit je renomme mon étape (2) AjoutIndex :

  • Clic-droit sur le nom de l'étape dans le volet situé à droite
  • Clic sur Renommer

Transposer les données empilées

 Ajouter une colonne personnalisée, celle-ci va simplement lire l'enregistrement situé à la ligne égale à l'index +1

Le nom de cette colonne sera Col1 et la formule : AjoutIndex{[Index]+1}[A Transposer]

Que signifie cette formule :

AjoutIndex => Etape précédente

AjoutIndex{0} => 1ère ligne du tableau résultant de l'étape précédente ; AjoutIndex{1} correspond à la 2e ligne. Donc AjoutIndex{[Index]+1} renvoie toujours la ligne suivante

AjoutIndex{[Index]+1}[A Transposer] => Lit la données du champ [A Transposer] de la ligne suivante du tableau résultant de l'étape précédente.

Power Query - Lire la ligne suivante

Recommençons cette étape 2 fois en ajoutant 2, puis 3 à l'index, ce qui nous donne le tableau suivant où chaque colonne lit l'enregistrement du 1er champ, 1 puis 2 puis 3 lignes suivantes.

Power Query - Lecture des lignes suivantes

Supprimer les lignes inutiles

 Vous constatez que certes nous avons transposé mais nous avons besoin :

  1. de conserver 1 ligne ;
  2. de supprimer 3 lignes ;
  3. répéter les étapes 1 et 2.

Pour cela utilisons la commande Supprimer les autres lignes du menu Supprimer les lignes sur l'onglet Dossier racine. Il suffit d'indiquer la 1ère ligne à supprimer, le nombre de lignes à supprimer et le nombre de lignes à conserver.

Power Query - Supprimer des lignes

Finalisation du tableau transposé

 Nous y sommes presque, mais la 1ère ligne correspond en fait aux titres du tableau final et nous n'avons plus besoin de la colonne d'index.

  • onglet Transformer> Utiliser la première ligne pour les en-têtes
  • clic-droit sur la colonne "0" et Supprimer
  • clic-droit sur la colonne "Date" et Modifier le type > Date
  • clic-droit sur la colonne "NB" et Modifier le type > Nombre entier
  • clic-droit sur la colonne "PU" et Modifier le type > Nombre décimal

Il ne vous reste plus qu'à fermer Power Query pour charger le résultat dans un nouveau tableau d'Excel

 J'ai trouvé cette astuce sur le blog Excelguru.ca de Ken Puls, MVP Excel canadien. Ce blog est d'ailleurs une excellente référence pour Power Query et Power Pivot.