Power Query : Importez et transformez vos données comme un pro

Si vous passez des heures chaque semaine à télécharger des fichiers CSV, à supprimer des lignes vides, à formater des dates et à écrire des formules imbriquées complexes juste pour préparer vos données pour l'analyse, vous travaillez plus dur que nécessaire. Bienvenue dans Power Query — l'outil d'automatisation de données le plus puissant intégré directement dans Microsoft Excel.

Souvent appelé « Obtenir et transformer des données », Power Query vous permet de vous connecter à presque n'importe quelle source de données, de nettoyer et de remodeler les informations, puis de les charger dans votre feuille de calcul. Le meilleur dans tout ça ? Il enregistre vos étapes. La prochaine fois que vous recevrez de nouvelles données, vous n'aurez pas à répéter le travail manuel ; il vous suffira de cliquer sur Actualiser.

Dans ce guide complet, nous allons explorer ce qu'est Power Query, comment naviguer dans son interface et parcourir un exemple pratique pour transformer un jeu de données désordonné en informations propres et prêtes à être analysées.

Qu'est-ce que Power Query ?

Power Query est un moteur de connexion et de préparation de données. Dans le monde de la gestion de bases de données, ce processus est connu sous le nom d'ETL : Extract, Transform, and Load (Extraire, Transformer et Charger).

• Extraire : Récupérer des données de diverses sources (classeurs Excel, fichiers CSV et texte, pages web, bases de données SQL ou même des services cloud).
• Transformer : Nettoyer et remodeler les données. Cela inclut le filtrage des lignes, la modification des types de données, le fractionnement du texte, la fusion de tables et la création de colonnes calculées.
• Charger : Restituer les données polies directement dans un tableau Excel, un tableau croisé dynamique ou le modèle de données Power Pivot.

Traditionnellement, les utilisateurs d'Excel s'appuyaient sur une combinaison de fonctions telles que TRIM, PROPER, SUBSTITUTE et VLOOKUP associées à des copier-coller manuels pour gérer ces tâches. Power Query remplace ce flux de travail fastidieux par une interface visuelle et conviviale.

Pourquoi vous devriez apprendre Power Query

Si vous hésitez encore à apprendre un nouvel outil Excel, voici pourquoi la maîtrise de Power Query va révolutionner votre productivité :

• Aucun codage requis : Bien que Power Query fonctionne sur un langage de formule appelé « M », vous pouvez effectuer 95 % des transformations de données en utilisant uniquement les boutons du ruban et les menus contextuels.
• Automatisation sans VBA : Vous n'avez pas besoin de savoir comment écrire des macros pour automatiser les tâches répétitives. Power Query mémorise vos « Étapes appliquées » et les applique automatiquement aux nouvelles données.
• Gère le Big Data : Excel a une limite physique de 1 048 576 lignes. Power Query peut se connecter à des jeux de données de millions de lignes, agréger les données en arrière-plan et ne charger que les résultats résumés dans votre feuille de calcul.
• Édition non destructive : Power Query n'altère jamais votre source de données d'origine. Il crée une connexion, lit les données et génère une copie fraîchement nettoyée.

Premiers pas : L'éditeur Power Query

Pour accéder à Power Query, ouvrez un classeur Excel vierge et accédez à l'onglet Données sur le ruban. Cherchez le groupe Obtenir et transformer des données tout à gauche.

À partir de là, vous pouvez cliquer sur Obtenir des données pour voir un menu déroulant des sources de données disponibles. Une fois que vous sélectionnez un fichier et cliquez sur « Transformer les données », Excel ouvre l'Éditeur Power Query dans une nouvelle fenêtre. Cette interface se compose de quatre zones principales :

1. Le ruban : Situé en haut, il contient des onglets comme Accueil, Transformer, Ajouter une colonne et Affichage. C'est là que se trouvent tous vos outils.
2. Le volet Requêtes : Situé à gauche, il répertorie toutes les connexions de données actives dans votre classeur.
3. L'aperçu des données : La grille centrale affichant un aperçu en direct de ce à quoi ressemblent vos données à l'étape actuellement sélectionnée.
4. Le volet Paramètres d'une requête (Étapes appliquées) : Situé à droite. C'est la partie la plus importante de l'éditeur. Chaque fois que vous modifiez quelque chose, Power Query l'enregistre ici sous forme d'étape séquentielle. Vous pouvez supprimer, réorganiser ou modifier ces étapes à tout moment.

Guide étape par étape : Nettoyer des données de vente désordonnées

Regardons un exemple pratique du monde réel. Imaginez que vous exportiez un rapport de ventes hebdomadaire depuis le CRM de votre entreprise. L'exportation brute est désordonnée, contenant des en-têtes inutiles, des chaînes de texte combinées et un formatage incohérent.

Voici un échantillon de nos données brutes et désordonnées :

Exportation système : Rapport de ventes T3	Colonne2	Colonne3
Généré le : 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Si nous utilisions des formules traditionnelles, nous devrions utiliser LEFT, RIGHT, FIND et VALUE pour extraire les noms des représentants et corriger les nombres. Utilisons plutôt Power Query.

Étape 1 : Importer les données

Enregistrez les données désordonnées sous forme de fichier CSV ou Excel. Ouvrez un nouveau classeur Excel, allez dans Données > Obtenir des données > À partir d'un fichier, et sélectionnez votre fichier. Lorsque la fenêtre d'aperçu apparaît, cliquez sur Transformer les données. L'Éditeur Power Query s'ouvrira.

Étape 2 : Supprimer les premières lignes et utiliser la première ligne comme en-têtes

Les deux premières lignes de nos données sont des métadonnées d'exportation du système, pas de véritables enregistrements de données. Nous devons nous en débarrasser.

• Sur l'onglet Accueil, cliquez sur Supprimer les lignes > Supprimer les premières lignes. Entrez « 2 » et cliquez sur OK.
• Maintenant, nos véritables en-têtes de colonnes se trouvent à la ligne 1. Cliquez sur le bouton Utiliser la première ligne comme en-têtes sur l'onglet Accueil. Instantanément, « Rep_ID_Name », « Order_Date » et « Revenue » deviennent vos titres de colonnes officiels.

Étape 3 : Fractionner la colonne Rep

La colonne « Rep_ID_Name » contient à la fois le numéro d'identification et le nom de l'employé séparés par un tiret.

• Faites un clic droit sur l'en-tête de la colonne « Rep_ID_Name ».
• Sélectionnez Fractionner la colonne > Par délimiteur.
• Choisissez « Personnalisé » et tapez un tiret ( - ). Cliquez sur OK.
• Power Query divise les données en deux colonnes. Vous pouvez maintenant double-cliquer sur les nouveaux en-têtes de colonnes pour les renommer en « Rep_ID » et « Rep_Name ».

Pour nettoyer les traits de soulignement dans le nom de Bob (Bob_Jones), faites un clic droit sur la colonne Rep_Name, choisissez Remplacer les valeurs, tapez un trait de soulignement (_) dans la case « Valeur à rechercher », et laissez « Remplacer par » vide ou ajoutez un espace. Cliquez sur OK.

Étape 4 : Standardiser les types de données

Remarquez comment nos dates et revenus sont dans des formats complètement différents ? Power Query facilite la standardisation de tout cela.

• Cliquez sur la petite icône sur le côté gauche de l'en-tête de la colonne « Order_Date » (il y a probablement écrit « ABC » ou « N'importe lequel »). Remplacez-la par Date. Power Query traduit automatiquement les différents formats de date en une date standard unifiée.
• Cliquez sur l'icône à côté de « Revenue » et changez-la en Devise (ou Nombre décimal). Cela supprime les signes de dollar et formate tout proprement.

Étape 5 : Ajouter une colonne conditionnelle

Supposons que nous voulions catégoriser les ventes supérieures à 1 000 $ comme « Valeur élevée ». Au lieu d'écrire une fonction IF complexe comme =IF(C2>=1000, "High Value", "Standard") dans Excel, nous pouvons utiliser l'interface de Power Query.

Allez dans l'onglet Ajouter une colonne et cliquez sur Colonne conditionnelle. Définissez les règles : Si [Revenue] est supérieur ou égal à 1000, alors afficher « High Value », sinon « Standard ». En coulisses, Power Query génère le code M suivant pour cette étape :

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Fusionner des requêtes : L'alternative moderne à VLOOKUP

L'une des tâches les plus courantes dans l'analyse de données est la combinaison de tables. Si vous avez une table distincte contenant la région pour chaque représentant commercial, vous vous tourneriez normalement vers notre guide complet sur la fonction VLOOKUP pour importer ces données.

Cependant, l'exécution de milliers de formules VLOOKUP ou INDEX et MATCH peut ralentir considérablement votre classeur. Dans Power Query, vous utilisez la fonctionnalité Fusionner des requêtes.

Importez simplement les deux tables dans Power Query, sélectionnez votre table de ventes principale, et cliquez sur Fusionner des requêtes dans l'onglet Accueil. Sélectionnez la deuxième table (la table des Régions), cliquez sur la colonne correspondante dans les deux tables (par exemple, « Rep_ID »), et cliquez sur OK. Power Query effectue l'équivalent d'un VLOOKUP ultra-rapide en quelques secondes, que vous ayez dix lignes ou dix millions.

Dépivoter des données : L'arme secrète de Power Query

Souvent, vous recevez des données qui sont déjà regroupées dans une structure de type tableau croisé dynamique (par exemple, les mois s'étendant sur les colonnes : Jan, Fév, Mar, Avr). Bien que cela soit facile à lire pour les humains, c'est terrible pour créer des graphiques ou des tableaux croisés dynamiques.

Sélectionnez vos colonnes d'identifiants (comme Rep Name), faites un clic droit sur l'en-tête, et choisissez Dépivoter les autres colonnes. Power Query transforme instantanément vos données larges et croisées en une disposition plate et tabulaire avec une nouvelle colonne « Attribut » (Mois) et « Valeur » (Ventes). Faire cela avec des formules Excel standard est presque impossible, faisant de Dépivoter l'une des fonctionnalités les plus célèbres de Power Query.

Charger les données transformées

Une fois que vos données sont parfaitement propres, il est temps de les renvoyer dans Excel.

Sur l'onglet Accueil, cliquez sur Fermer et charger. Par défaut, cela chargera vos données transformées dans un tout nouveau tableau Excel vert sur une nouvelle feuille de calcul. Si vous préférez envoyer les données directement à votre phase d'analyse, vous pouvez cliquer sur la flèche déroulante, choisir Fermer et charger dans..., et sélectionner un Rapport de tableau croisé dynamique à la place. Si vous avez besoin d'un rappel sur la construction de ces résumés, consultez notre tutoriel sur la création de tableaux croisés dynamiques pour les débutants.

La magie du bouton « Actualiser »

La véritable puissance de Power Query devient évidente la semaine suivante lorsque vous recevez une nouvelle exportation de ventes brutes. Ne répétez pas les étapes ci-dessus !

Enregistrez simplement le nouveau fichier CSV sur l'ancien (conservez exactement le même nom de fichier et le même emplacement de dossier). Ensuite, ouvrez votre classeur Excel, faites un clic droit n'importe où dans votre tableau de données propres, et cliquez sur Actualiser.

Power Query accède au fichier, réapplique chaque étape — supprimer des lignes, utiliser la première ligne comme en-têtes, fractionner des colonnes, remplacer du texte, vérifier des conditions et fusionner des tables — et met à jour votre résultat final en une fraction de seconde. Il s'agit d'un composant vital des flux de travail d'automatisation Excel.

Simplifier votre parcours de données avec l'IA

Bien que Power Query gère brillamment les transformations structurelles, vous avez parfois besoin d'une logique conditionnelle spécifique ou d'une analyse de texte complexe qui nécessite des formules Excel avancées ou du code M personnalisé. Plutôt que de parcourir les forums à la recherche de réponses, vous pouvez tirer parti de l'intelligence artificielle.

Si vous avez du mal à écrire la formule de colonne personnalisée parfaite, ExcelGPT est le compagnon idéal. Décrivez simplement ce que vous essayez d'accomplir en langage naturel — par exemple, « J'ai besoin d'une formule pour extraire uniquement les nombres d'une chaîne de texte mixte » — et ExcelGPT générera instantanément la bonne formule ou le bon code M. Combiner Power Query avec l'IA pour le nettoyage de données vous offre une boîte à outils imparable pour l'analyse de données.

Foire aux questions

Power Query modifie-t-il mon fichier de données d'origine ?

Non. Power Query crée une connexion unidirectionnelle vers vos données sources. Il lit les données, applique les transformations en mémoire et génère un nouveau résultat dans Excel. Votre CSV, base de données ou classeur d'origine reste complètement intact et en sécurité.

Puis-je utiliser Power Query sur un Mac ?

Oui, Microsoft a considérablement amélioré la prise en charge de Power Query dans Excel pour Mac. Bien que la version Mac manquait traditionnellement de certains des connecteurs avancés et des fonctionnalités d'interface utilisateur disponibles sur Windows, vous pouvez désormais vous connecter à des fichiers locaux, des bases de données et actualiser les requêtes existantes en toute fluidité dans les versions modernes de Microsoft 365.

Quelle est la différence entre Fusionner et Ajouter dans Power Query ?

Fusionner (Merge) est l'équivalent d'un VLOOKUP ou INDEX/MATCH. Vous l'utilisez pour ajouter de nouvelles colonnes de données en faisant correspondre un identifiant commun entre deux tables. Ajouter (Append) revient à copier-coller des données au bas d'une feuille. Vous l'utilisez pour empiler des tables les unes sur les autres, en ajoutant de nouvelles lignes (par exemple, en combinant les ventes de janvier et de février).

Pourquoi l'actualisation de ma requête Power Query a-t-elle échoué ?

La raison la plus courante de l'échec de l'actualisation d'une requête est que le fichier source a été déplacé, renommé ou supprimé. Un autre problème fréquent est qu'un en-tête de colonne dans les données brutes a changé (par exemple, « Revenue » a été changé en « Total Revenue » par le système). Vous pouvez corriger cela en ouvrant l'Éditeur Power Query, en allant dans le volet Étapes appliquées et en mettant à jour l'étape Source ou en renommant la colonne dans la logique de votre étape.