M365Con 2027 – conférence en ligne gratuite · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Power Query dans Excel : importer, nettoyer, fusionner et actualiser des données

Par · · Mis à jour

Power Query dans Excel importe et nettoie des données sans formules. Découvrez où le trouver, « À partir d’un tableau ou d’une plage », les « Étapes appliquées », la différence entre fusion et ajout, le chargement et l’actualisation.

Traduit automatiquement par IA à partir de l'article original. Lire l'original en anglais

Power Query dans Excel est l’outil de l’onglet Données, dans le groupe Obtenir et transformer des données, qui se connecte aux données, les nettoie, les restructure et charge le résultat dans une feuille de calcul. Les étapes que vous effectuez sont enregistrées et rejouées à chaque actualisation : un nettoyage mensuel ne demande donc plus qu’un clic. Cet article s’adresse aux utilisateurs d’Excel qui copient, collent et corrigent chaque semaine les mêmes exports, et qui veulent arrêter. Vous y trouverez par où commencer, comment nettoyer un tableau, comment ajouter et fusionner, comment charger et actualiser, ainsi que les limites sur Mac et sur le web.

Points clés

  • Power Query dans Excel se trouve dans l’onglet Données, dans le groupe Obtenir et transformer des données. Il se connecte aux données, les met en forme et les charge dans une feuille de calcul ou dans le modèle de données.
  • Chaque modification que vous effectuez est enregistrée comme une étape dans « Étapes appliquées ». La source d’origine n’est pas modifiée, et les étapes sont rejouées à chaque actualisation.
  • L’ajout empile les lignes de tableaux aux colonnes identiques. La fusion joint deux tables sur des colonnes de clé correspondantes, et les colonnes correspondantes doivent avoir le même type de données.
  • Utilisez Données, puis « Actualiser tout » pour réexécuter les requêtes. Dans « Propriétés de connexion », vous pouvez actualiser à intervalles réguliers ou à l’ouverture du fichier.
  • Excel pour Mac et Excel pour le web prennent en charge une partie de Power Query, et les pages de Microsoft diffèrent quant à ce que chacun permet de créer : vérifiez donc avec votre propre version.

Microsoft décrit quatre phases : se connecter aux données, les transformer, les combiner à d’autres sources, puis les charger. La source d’origine reste inchangée tout au long du processus. C’est ce dernier point qui rend les essais sans risque : vous ne pouvez pas casser l’export dont vous êtes parti.

Ce qu’est Power Query et où le trouver

Power Query est un moteur de préparation des données doté d’un éditeur graphique. Selon la page de Microsoft consacrée à ce qu’est Power Query, il assure les opérations d’extraction, de transformation et de chargement, ne dispose d’aucun stockage propre et laisse la destination au produit hôte. Excel est l’un de ces hôtes. Power BI Desktop en est un autre, et les deux partagent le même éditeur de base. Les connecteurs et les destinations peuvent varier selon l’hôte : ne partez pas du principe qu’une requête vue dans un produit sera identique dans l’autre.

L’emplacement de Power Query dépend de la version d’Excel, selon la présentation de Power Query dans Excel publiée par Microsoft :

  • Excel 2016, 2019 et Microsoft 365 pour Windows. Intégré. Allez dans Données et regardez le groupe Obtenir et transformer des données.
  • Excel 2010 et 2013 pour Windows. Power Query était un complément gratuit doté de son propre onglet de ruban. Microsoft indique qu’il est obsolète, même s’il peut encore fonctionner.
  • Excel pour Mac. Prend en charge l’actualisation à partir de plusieurs sources. Il n’est pas disponible dans Excel 2016 ou 2019 pour Mac.
  • Excel pour le web. Pris en charge en partie. Plus de détails ci-dessous.

Sur Windows, Microsoft indique aussi comme prérequis .NET Framework 4.7.2 ou une version ultérieure, ainsi que le WebView2 Runtime pour le connecteur web. Si les commandes sont absentes sur un poste de travail professionnel, demandez à votre service informatique si la version du produit est à jour avant de supposer que la fonctionnalité a été supprimée.

Comment utiliser Power Query dans Excel : votre première requête

Le point de départ le plus simple est constitué des données que vous avez déjà dans un classeur.

  1. Cliquez sur n'importe quelle cellule de votre tableau Excel. Si vos données forment une plage simple, appuyez d'abord sur Ctrl+T pour la transformer en tableau.
  2. Accédez à Données > Obtenir et transformer des données > À partir d'un tableau ou d'une plage.
  3. L'Éditeur Power Query s'ouvre avec un aperçu de vos données.
  4. Apportez vos modifications (voir la section suivante).
  5. Sélectionnez Accueil > Fermer et charger.

La page de Microsoft consacrée à l'importation depuis un tableau Excel indique que le résultat est placé par défaut dans une nouvelle feuille de calcul et que la feuille d'origine n'est pas modifiée. Utilisez Charger dans si vous souhaitez une autre destination. Renommez la nouvelle feuille afin que personne ne la confonde avec la source.

Pour vous connecter à une autre source, utilisez Données > Obtenir des données et choisissez une source : un fichier, une base de données, un service en ligne ou le web. Après l'avoir sélectionnée, vous obtenez un aperçu et le choix entre Charger (directement dans Excel) et Transformer les données (ouvrir d'abord l'éditeur). Mon conseil est de choisir Transformer les données presque systématiquement, même lorsque vous pensez que les données sont propres. C'est dans l'éditeur que vous définissez les types de colonnes, et des types incorrects sont à l'origine de la plupart des erreurs d'actualisation par la suite.

Si vous souhaitez partir d'une requête vierge, la même page indique Données > Obtenir des données > À partir d'autres sources > Requête vide, ou Lancer l'éditeur Power Query sous Obtenir des données.

L'éditeur en termes simples

  • Le aperçu des données affiche votre tableau et se met à jour après chaque modification.
  • Le volet Paramètres de requête répertorie les ÉTAPES APPLIQUÉES. Chaque action que vous effectuez ajoute une étape, comme Source ou Type modifié. Vous pouvez sélectionner une étape pour voir les données à ce stade, modifier une étape ou la supprimer.
  • Le volet Requêtes répertorie toutes les requêtes du classeur. Ouvrez-le avec la flèche située à côté de l'aperçu.
  • L'Éditeur avancé vous permet de consulter et de modifier le script sous-jacent dans le langage Power Query M. Microsoft précise que chaque étape créée dans l'interface est écrite en M. Vous n'avez pas besoin d'y toucher pour tirer profit de l'outil.

Nettoyer et restructurer les données avec des étapes

Les tâches de nettoyage courantes dans l'éditeur : filtrer des lignes, supprimer des colonnes, modifier les types de données, fractionner une colonne, remplacer des valeurs. L'éditeur enregistre chacune d'elles sous forme d'étape. Les commandes se trouvent dans le ruban de l'éditeur et dans le menu qui apparaît lorsque vous faites un clic droit sur l'en-tête d'une colonne ; celles qui sont disponibles dépendent du type de colonne. Vérifiez donc le menu de l'en-tête avant de chercher dans le ruban.

Une routine qui fonctionne pour la plupart des exports :

  1. Définissez d'abord les types. Vérifiez l'icône de type à côté de chaque en-tête de colonne. Les dates, les nombres et le texte se comportent différemment dans les étapes suivantes et lors des fusions.
  2. Supprimez ce dont vous n'avez pas besoin. Moins de colonnes signifient un résultat plus léger, plus rapide et moins de surprises lorsque la source évolue.
  3. Filtrez les lignes. Utilisez la flèche de l'en-tête de colonne pour supprimer les cellules vides ou les valeurs indésirables.
  4. Corrigez le texte. Remplacez les coquilles et les anciens codes, supprimez les espaces superflus, ou fractionnez une colonne qui contient deux informations.
  5. Renommez la requête dans le volet Paramètres de requête afin de la retrouver facilement plus tard.

Comme Étapes appliquées est une liste, vous pouvez corriger une erreur antérieure sans tout recommencer. Si une étape échoue après une modification de la source, examinez la première étape en erreur. Dans la plupart des cas, elle désigne une colonne dont le nom ou le type a changé.

Une mise en garde concernant les colonnes renommées : la page de Microsoft sur l'actualisation indique que les nouvelles colonnes source n'apparaissent dans Power Query qu'après avoir vérifié l'étape Source, et qu'une barre jaune signalant que l'aperçu risque d'être obsolète signifie que vous devez actualiser le cache local. Si une nouvelle colonne n'apparaît pas, vérifiez d'abord ces deux points.

Combiner des données : ajouter et fusionner

Ces deux commandes ont des fonctions différentes, et on les confond souvent.

Ajouter empile des lignes. Utilisez-le lorsque vous disposez de nombreuses tables de même structure, par exemple un export par mois. Microsoft décrit un ajout comme l’empilement des lignes d’une requête à la suite d’une autre. Il existe deux variantes : un ajout en ligne s’appuie sur la requête existante, tandis qu’un ajout intermédiaire crée une nouvelle requête à chaque fois.

Fusionner joint deux tables côte à côte en fonction de valeurs correspondantes, comme une recherche. La page de Microsoft sur la fusion de requêtes détaille les étapes :

  1. Vérifiez que vous disposez d’au moins deux requêtes comportant une colonne de correspondance. Les colonnes correspondantes doivent avoir le même type de données, par exemple Texte ou Nombre.
  2. Dans l’éditeur, allez dans Accueil > Fusionner les requêtes. Le résultat devient une nouvelle étape de la requête en cours. Si vous préférez une requête distincte, ouvrez la flèche à côté de Fusionner les requêtes et choisissez Fusionner les requêtes en tant que nouvelle.
  3. Choisissez la table principale et sa colonne clé, puis la table associée et sa colonne clé. Pour plusieurs colonnes clés, sélectionnez-les dans le même ordre dans les deux tables.
  4. Vérifiez le nombre de correspondances affiché dans la boîte de dialogue. S’il est inférieur à ce que vous attendez, les colonnes clés diffèrent probablement par l’orthographe, les espaces ou le type.
  5. Choisissez un Type de jointure (ci-dessous), puis sélectionnez OK.
  6. Une nouvelle colonne apparaît. Sélectionnez l’icône Développer à côté de son en-tête, puis cochez les colonnes associées que vous souhaitez ajouter.

Les types de jointure, selon Microsoft :

  • Externe gauche conserve toutes les lignes de la table principale ainsi que les correspondances de la table associée.
  • Externe droite conserve toutes les lignes de la table associée ainsi que les correspondances de la table principale.
  • Externe complète conserve toutes les lignes des deux tables.
  • Interne ne conserve que les lignes qui correspondent dans les deux tables.
  • Anti-gauche conserve les lignes de la table principale qui n’ont aucune correspondance. Anti-droite conserve les lignes de la table associée qui n’ont aucune correspondance.
  • Croisée associe chaque ligne de la table principale à chaque ligne de la table associée.

L’ordre des tables compte, car les notions de gauche et de droite dépendent de la table choisie en premier. La case Utiliser la correspondance approximative pour effectuer la fusion rapproche des valeurs approximatives, et Microsoft précise qu’elle ne fonctionne que sur les colonnes de texte. Si vous utilisiez auparavant une recherche, une fusion avec une jointure externe gauche est l’équivalent le plus proche. Une jointure anti-gauche permet en outre de repérer rapidement les lignes sans correspondance, par exemple des commandes sans fiche client.

Microsoft mentionne également les niveaux de confidentialité. Power Query classe chaque source comme Public, Organisationnel ou Privé, et s’en sert pour empêcher la combinaison involontaire de données susceptible d’exposer des informations privées à une autre source. Si une fusion vous demande de vous prononcer sur les niveaux de confidentialité, lisez le message avant de poursuivre.

Charger une requête et l’actualiser

Fermez l’éditeur avec Accueil > Fermer et charger. La page de Microsoft sur la création, le chargement et la modification d’une requête explique ces destinations :

  • Fermer et charger envoie le résultat vers une feuille de calcul.
  • Fermer et charger dans vous permet de choisir une autre destination, comme le modèle de données. Dans la boîte de dialogue Importer des données, cochez Ajouter ces données au modèle de données si vous prévoyez de créer des tableaux croisés dynamiques à partir de plusieurs tables.
  • Si Charger dans est désactivé, utilisez Fermer et charger, puis allez dans Données > Requêtes et connexions, faites un clic droit sur la requête dans l’onglet Requêtes et choisissez Charger dans.

Pour modifier les valeurs par défaut, allez dans Fichier > Options et paramètres > Options de requête > Chargement des données. Vous pouvez utiliser les paramètres de chargement standard ou définir vos propres valeurs par défaut, pour charger vers une feuille de calcul ou vers le modèle de données.

Pour modifier une requête ultérieurement, sélectionnez une cellule de ses données et choisissez Requête > Modifier, ou faites un clic droit sur la requête dans Requêtes et connexions et choisissez Modifier.

Actualisation

L’actualisation réexécute chaque étape sur la source actuelle. Les différentes méthodes, selon la page d’actualisation de Microsoft, sont les suivantes :

  • Données > Tout actualiser met à jour l’ensemble du classeur (Ctrl+Alt+F5). La flèche à côté propose Actualiser pour l’élément sélectionné (Alt+F5). Échap interrompt une actualisation.
  • Pour une actualisation automatique, allez dans Données > Requêtes et connexions, ouvrez l’onglet Connexions, faites un clic droit sur une connexion et choisissez Propriétés. Dans l’onglet Utilisation, vous pouvez définir Actualiser toutes les (en minutes) et Actualiser les données à l’ouverture du fichier.
  • L’option Activer l’actualisation en arrière-plan, dans le même onglet, vous permet de continuer à travailler pendant l’exécution de la requête. Elle n’est pas disponible pour les requêtes OLAP ni pour les connexions alimentant le modèle de données.

Les mots de passe enregistrés ne sont pas chiffrés, et Microsoft déconseille de les enregistrer. Décochez Enregistrer le mot de passe dans l’onglet Définition : Excel ne demande alors le mot de passe qu’une fois par session.

Si vous envoyez le classeur à quelqu’un, cette personne peut avoir besoin d’accéder à la source. Une requête qui lit un fichier sur votre disque, ou un site auquel elle n’a pas accès, échouera sur son poste. Placez la source de données dans un emplacement partagé, ou envoyez les résultats chargés sous forme de valeurs.

Power Query sur Mac et dans Excel pour le web

Les deux plateformes prennent en charge une partie de l’outil, mais les pages de Microsoft ne concordent pas entièrement.

Excel pour le web. La page de Microsoft indique que vous pouvez importer depuis plusieurs sources, notamment un classeur Excel, du texte/CSV, XML, JSON, SQL Server, une liste SharePoint Online et OData, ainsi qu’un tableau ou une requête vierge. Vous pouvez ouvrir l’Éditeur Power Query (Données > Obtenir des données > Lancer l’éditeur Power Query), charger avec Fermer et charger, et actualiser avec Données > Tout actualiser. L’affichage et l’actualisation des requêtes sont disponibles pour tous les abonnés Microsoft 365, tandis que les fonctionnalités supplémentaires nécessitent un forfait Business ou un forfait Entreprise (E3 et supérieur). Certaines fonctionnalités exigent que les cookies tiers soient activés dans le navigateur.

Excel pour Mac. Une page de Microsoft indique qu’il faut commencer par Données > Obtenir des données > Obtenir des données (Power Query), que la mise en forme nécessite Microsoft 365 et la version 16.69 ou ultérieure, et que Transformer des données ouvre l’éditeur. Sur cette même page, une section distincte précise que la création de requêtes dans l’éditeur n’est pas disponible dans Excel pour Mac, et décrit la rédaction de requêtes en VBA sous Windows ainsi que l’importation du module sur Mac. Je ne parviens pas à concilier ces deux affirmations : testez donc sur votre propre Mac avant de bâtir un processus dessus. L’importation depuis SQL Server est décrite comme réservée à la version bêta des Insiders.

Quand Power Query ne suffit plus

Soyons directs : Power Query sert à préparer les données, pas à les stocker. Microsoft indique que le produit hôte détermine où vont les résultats, ce qui, dans Excel, signifie une feuille de calcul ou le modèle de données. Une feuille de calcul a un nombre maximal de lignes, si bien qu’un résultat de requête très volumineux ne tiendra pas sur une feuille. Chargez-le alors dans le modèle de données, ou déplacez le traitement vers une base de données ou un pipeline dédié.

Autres limites à connaître :

  • Les requêtes lisent la source au moment de leur exécution. Elles ne diffusent pas les modifications en temps réel dans le classeur.
  • La collaboration est délicate. Deux personnes modifiant la même requête dans un classeur partagé, c’est une mauvaise idée. Désignez un seul responsable et donnez aux requêtes des noms clairs.
  • Si vous manipulez des données réglementées, gardez à l’esprit qu’un classeur se copie facilement. Respectez les règles de votre organisation concernant les emplacements de stockage autorisés pour ce type de fichiers.

Erreurs courantes

  • Ignorer les types de données, puis s’étonner qu’une fusion ne trouve aucune correspondance.
  • Fusionner sur des colonnes de types différents ou contenant des espaces en fin de chaîne.
  • Utiliser la fusion alors que l’ajout était nécessaire, ou l’inverse.
  • Charger toutes les données dans une feuille de calcul alors qu’une requête en mode connexion uniquement ou le modèle de données conviendrait mieux.
  • Enregistrer le mot de passe de la source dans la connexion.
  • Oublier d’actualiser avant d’envoyer un rapport.
  • Déplacer le fichier source, puis ne pas comprendre pourquoi la requête échoue.

Sujets connexes à explorer

  • Supprimer les doublons dans l’éditeur. Power Query peut dédoublonner une table sous forme d’étape, ce qui est reproductible, contrairement à la commande ponctuelle du ruban.
  • Dépivoter des colonnes. Transformez les colonnes de mois en lignes avant de créer un tableau croisé dynamique. Microsoft mentionne précisément Power Query pour cette opération.
  • Paramètres. Microsoft propose une page sur les requêtes paramétrées, pour réutiliser une requête avec différentes entrées.
  • Le langage M. Apprenez à lire le script dans l’Éditeur avancé, en commençant par la page Power Query M.
  • Power BI. Découvrez comment fonctionne le même éditeur dans Power BI Desktop lorsque vos données dépassent les capacités d’Excel.

Prochaine étape

Choisissez un export que vous nettoyez à la main chaque semaine. Transformez-le en tableau, passez-le par « À partir d’un tableau/d’une plage », définissez les types de colonnes et chargez-le dans une nouvelle feuille. La semaine suivante, collez le nouvel export dans le tableau et cliquez sur Actualiser tout. C’est là que Power Query dans Excel fait son travail.

Sources

Suivre le podcastSpotify

Entités dans cet article

Microsoft Excel Power Query Power Query M formula language Power BI Desktop

Articles associés

RSS