Comment transformer du texte brut en tableau structuré sur Excel avec Power Query

Dans ce tutoriel, je vais vous montrer comment transformer automatiquement du texte brut désorganisé en un tableau parfaitement structuré grâce à Power Query.

Cette technique va nous permettre de gagner un temps considérable lorsque nous recevons des fichiers texte, des rapports PDF copiés-collés, ou tout autre document contenant des informations mélangées et difficiles à exploiter.

Au lieu de passer des heures à copier-coller manuellement chaque information dans les bonnes cellules, nous allons voir comment Power Query peut automatiser tout ce processus en quelques étapes simples.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser le tableau suivant dans lequel nous retrouvons les rapports de livraison d'une entreprise de logistique.

Ces rapports arrivent chaque jour sous forme de texte brut, directement copiés depuis un système informatique qui n'exporte pas correctement les données vers Excel.

En effet, dans ce texte, nous trouvons des numéros de colis (qui commencent par « COLIS- » suivi d'une lettre et de chiffres), des informations de destination, et surtout les codes des transporteurs assignés à chaque colis (qui commencent par « TRANSP- » suivi de deux chiffres).

Chaque ligne contient un mélange de texte inutile, de codes colis (COLIS-…) et de transporteurs (TRANSP-…). Impossible d’en tirer quoi que ce soit directement.

Notre objectif est donc d’obtenir un tableau clair avec deux colonnes :

• Colis
• Transporteur

Chaque ligne devra représenter l’association d’un colis avec l’un de ses transporteurs, sans doublons.

Nous pourrions le faire à la main ou à l’aide de formules Excel, mais ces méthodes sont longues et non réutilisables.

Power Query, lui, permet de créer une requête automatique que l’on pourra réutiliser à l’infini, simplement en actualisant les données.

2. Préparation des données et chargement dans Power Query

Commençons par transformer cette simple base brute en un tableau structuré.

Pour cela, nous sélectionnons les cellules qui le composent, avec la ligne d’en-tête et le transformons-la en tableau Excel via le raccourci [Ctrl]+[L].

Excel nous affiche une boîte de dialogue dans laquelle il détecte automatiquement la plage des cellules, que nous devons vérifier.

Nous devons également cocher « Mon tableau comporte des en-têtes », puis valider en appuyant sur « OK » :

Le tableau est maintenant formaté et coloré.

Dans l’onglet Création de tableau, nous pouvons son nom : « Tableau1 » par défaut.

Maintenant, nous allons le charger dans un outil très puissant intégré directement dans Excel : Power Query.

Il s’agit d’un véritable moteur de transformation de données, qui permet de nettoyer, filtrer, fusionner, découper et restructurer des données issues de n’importe quelle source — que ce soit un fichier Excel, un texte brut, un PDF, une base de données ou même un site web.

L’un de ses plus grands atouts, c’est qu’il automatise tout le processus : chaque transformation que nous effectuons est enregistrée sous forme d’étapes successives, un peu comme un “historique intelligent”.

Ainsi, si demain nous recevons un nouveau rapport ayant la même structure, il nous suffira de cliquer sur Actualiser pour que Power Query répète toutes les opérations automatiquement, sans que nous ayons à refaire le travail.

Pour l’ouvrir et y intégrer notre tableau, nous commençons par sélectionner l’une des cellules de celui-ci.

Puis, nous nous rendons dans l’onglet Données du ruban Excel et dans la section « Récupérer et transformer les données », nous cliquons sur « À partir d’un tableau/plage ».

Une nouvelle fenêtre s’ouvre alors : c’est l’éditeur Power Query.

Son apparence diffère de celle d’Excel : c’est une interface à part entière, spécialement conçue pour manipuler les données.

Dans cette fenêtre, nous retrouvons :

• Notre tableau affiché sous forme de grille, avec ses en-têtes de colonnes ;
• Sur la droite, un panneau appelé Paramètres de la requête, qui affiche le nom de la requête (souvent le même que celui du tableau, comme “Tableau1”) et surtout la liste des étapes appliquées — c’est ici que Power Query enregistre chacune de nos actions ;
• En haut, un ruban contenant plusieurs onglets (“Accueil”, “Transformer”, “Ajouter une colonne”, “Affichage”, etc.), qui regroupent tous les outils nécessaires à nos futures manipulations.

Chaque action effectuée — qu’il s’agisse d’un filtrage, d’un remplacement de texte, d’un fractionnement ou d’une suppression de doublons — sera automatiquement ajoutée dans la liste des étapes appliquées.

Cette approche offre une traçabilité complète : nous pouvons revenir en arrière, désactiver ou modifier une étape, ou même en insérer une nouvelle au milieu du processus sans tout casser.

3. Extraction et nettoyage des données

   3.1. Séparer le code colis du reste du texte

Maintenant, nous allons pouvoir retraiter nos données.

Si nous les observons attentivement, nous voyons que les lignes utiles contiennent un deux-points (:) juste après le code colis.

Nous allons donc fractionner notre colonne à ce niveau, en effectuant un clic droit sur l’en-tête « Rapport », puis « Fractionner la colonne » > « Par délimiteur » :

Dans la fenêtre qui s’affiche, nous choisissons les « Deux-points » comme délimiteur, et dans les options de fractionnement, nous choisissons l’option « Délimiteur le plus à gauche », afin d’éviter que les deux-points éventuels plus loin dans la phrase ne fractionne en d’autres colonnes.

Lorsque nous appuyons sur le bouton « OK », la colonne se divise automatiquement en deux :

• Rapport.1 : partie avant le deux-points (nos COLIS-…)
• Rapport.2 : partie après le deux-points (texte + transporteurs)

Les lignes sans deux-points affichent null dans la colonne 2 : parfait, cela nous permettra de les filtrer.

  3.2. Supprimer les lignes inutiles

Pour masquer les lignes correspondantes, nous cliquons sur la flèche de la colonne « Rapport.2 », décochons (null), et validons.

Seules les lignes avec des colis restent.

   3.3. Nettoyer les caractères parasites

Nous devons maintenant débarrasser le texte de tous les symboles qui gênent (parenthèses, virgules, mot « et »…).

Pour cela, nous sélectionnons la colonne « Rapport.2 », puis nous nous rendons dans le menu « Transformer » pour cliquer sur le bouton « Remplacer les valeurs » :

Nous effectuons ensuite les remplacement successifs :

• « ( » par une espace
• « ) » par une espace
• « , » par une espace
• « et » par une espace

Comme nous l’avons vu un peu plus tôt, la puissance de Power Query est qu’il enregistrera chaque étape automatiquement, que nous retrouvons sur la droite dans la liste des « Étapes appliquées » :

Le texte est maintenant “propre”, et nous n’y retrouvons plus que des mots séparés par des espaces.

4. Transformation en lignes et filtrage

   4.1. Fractionner en lignes

Nous voulons maintenant éclater le texte pour que chaque mot devienne une ligne distincte.

Pour cela, nous effectuons un nouveau clic droit sur l’en-tête de la colonne « Rapport.2 » afin de relancer un fractionnement de la colonne (« Fractionner la colonne » > « Par délimiteur »).

Nous choisissons alors l’Espace comme délimiteur, un fractionnement sur chaque occurrence du délimiteur et avant de valider, nous déployons le menu des options avancées, afin de sélectionner un fractionnement sour la forme de « Lignes ».

Nous pouvons maintenant valider en appuyant sur « OK ».

Power Query va alors dupliquer automatiquement les valeurs de la première colonne pour chaque mot de la seconde :

Nous avons donc toutes les combinaisons possibles, il ne nous reste qu’à filtrer les lignes utiles.

   4.2. Filtrer les codes utiles

Maintenant, notre but c’est de ne garder que les lignes où la deuxième colonne commence par « TRANSP- » (transporteurs).

Nous cliquons donc sur la flèche de « Rapport.2 » et dans « Filtres textuels », nous choisissons le filtre « Commence par » :

Afin de saisir « TRANSP- » :

Cela garde les transporteurs.

Résultat : chaque ligne représente un colis + un transporteur.

   4.3. Renommer les colonnes

Pour finir, il ne reste plus qu’à renommer les colonnes pour indiquer les informations présentées.

Nous double-cliquons sur les en-têtes pour les renommer proprement :

• « Rapport.1 » devient « Colis »
• « Rapport.2 » devient « Transporteur »

Notre tableau est maintenant prêt :

   4.4. Charger le résultat dans Excel

Pour intégrer notre tableau retraité dans notre classeur Excel, nous nous rendons dans l’onglet « Accueil » et nous cliquons sur « Fermer et charger ».

Power Query renvoie le résultat dans une nouvelle feuille Excel, liée à la requête, que nous pouvons simplement Couper/Coller à côté de notre table d’origine.

Désormais, pour traiter un nouveau rapport : il suffit de coller le texte brut dans la source et d’actualiser.

Pour nous en rendre compte, nous ajoutons une nouvelle ligne en bas du tableau :

Puis, nous nous rendons dans le menu « Données » pour cliquer sur le bouton « Actualiser tout » :

La nouvelle ligne est ajouté automatiquement à la suite de notre base :

5. Bonnes pratiques et conseils

Avant de conclure, prenons un moment pour voir comment exploiter pleinement la puissance de Power Query tout en gardant des requêtes claires, robustes et faciles à maintenir.

Ces bonnes pratiques sont le fruit de l’expérience et vous permettront d’éviter bien des erreurs au fil du temps.

• Toujours travailler à partir d’un tableau Excel : Power Query préfère travailler avec des tableaux structurés plutôt qu’avec de simples plages de cellules. Comme nous venons de le voir, un tableau s’adapte automatiquement aux nouvelles données, sans aucune modification manuelle.
• Nettoyer progressivement : Quand on découvre Power Query, on a souvent tendance à vouloir tout faire en une seule étape. Ce n’est pas forcément une très bonne idée, car chaque transformation (remplacement, fractionnement, filtrage, suppression, etc.) doit rester simple et compréhensible. Mieux vaut dix petites étapes lisibles qu’une seule ultra-complexe qu’on ne saura plus déchiffrer dans trois mois. C’est l’un des grands principes de Power Query : la clarté avant la performance.
• Pourquoi préférer Power Query aux formules Excel ? On pourrait bien sûr réaliser certaines de ces transformations avec des formules Excel classiques comme : STXT() pour extraire une sous-chaîne, CHERCHE() pour localiser un mot, SUBSTITUE() pour remplacer des caractères,… Mais ces solutions deviennent rapidement très lourdes dès que les textes sont un peu irréguliers ou variables. La moindre différence de format casse les formules, et il faut tout réécrire à chaque nouveau cas et surtout, elles ne sont pas dynamiques : si le texte change de structure, le travail doit être refait à zéro. Power Query, au contraire, est conçu pour s’adapter automatiquement à la structure du texte tant que les grands principes (délimiteurs, motifs, etc.) restent les mêmes.