Aller au contenu
Power Platform Hub Parler à un expertUn expert

Données et connexions

Nettoyer vos données efficacement avec Power Query

Power Query corrige les espaces, les doublons, les dates et les cases vides de vos fichiers, puis rejoue ces corrections en un clic sur le fichier suivant.

Power Query est l’outil de préparation de données intégré à Excel et à Power BI. Il corrige les espaces en trop, les doublons, les dates mal formatées et les cases vides, puis mémorise chaque correction pour la rejouer en un clic sur le fichier suivant. Dans une entreprise, il évite de refaire à la main, chaque semaine ou chaque mois, le même nettoyage d’un export avant de pouvoir s’en servir.

À quoi sert Power Query dans une entreprise

Prenons une situation type. Chaque mois, le contrôle de gestion reçoit trois fichiers : les ventes sorties du logiciel de caisse, les factures de la comptabilité et un tableau Excel tenu par les commerciaux. Les dates n’ont pas le même format, les noms de clients contiennent des espaces en trop, certaines lignes apparaissent deux fois. Refaire ce travail à la main prend du temps à chaque clôture, et une erreur de copier-coller passe facilement inaperçue.

Avec Power Query, ce nettoyage se décrit une seule fois. Chaque correction devient une étape enregistrée dans une requête (le nom donné à ce traitement). Le mois suivant, vous remplacez le fichier source, vous actualisez, et les mêmes corrections s’appliquent aux nouvelles lignes. Le fichier d’origine n’est jamais modifié.

Ce qui change au quotidien : le résultat ne dépend plus de la personne qui fait le travail, et chaque correction reste visible dans une liste que l’on peut relire. Le même moteur se retrouve dans Power BI Desktop, pour préparer les données d’un rapport, et dans les dataflows, qui permettent de partager un nettoyage entre plusieurs rapports. Il sert aussi à importer des données dans Dataverse, la base de données de la Power Platform.

Les défauts à repérer dans vos fichiers

Avant de nettoyer, il faut savoir ce qui ne va pas. Les fichiers que vous recevez présentent presque toujours les mêmes problèmes.

  • Les espaces superflus se cachent au début ou à la fin des cellules. « Dupont » et « Dupont » suivi d’un espace comptent alors pour deux clients, et les regroupements et les recherches échouent.
  • Les formats incohérents : une même colonne de dates mélange « 01/12/2024 » et « 1-déc-24 ».
  • Les doublons faussent les calculs et gonflent les résultats.
  • Les valeurs manquantes : des cellules restent vides alors qu’elles devraient contenir « 0 » ou « Non renseigné ».

L’éditeur aide à les repérer. Dans l’onglet Affichage, cochez Qualité de la colonne, Distribution de la colonne et Profil de la colonne. Un petit bandeau indique alors, en haut de chaque colonne, la part de valeurs valides, d’erreurs et de cases vides. Attention : par défaut, ces indicateurs portent sur les 1 000 premières lignes. La barre d’état permet de les calculer sur tout le jeu de données.

Démarrer avec Power Query dans Excel

Power Query est disponible dans Excel pour Windows, pour Mac et pour le web, avec des différences selon les versions : la page de Microsoft sur Power Query dans Excel précise par exemple qu’il n’est pas pris en charge dans Excel 2016 et 2019 pour Mac.

  1. Cliquez dans votre tableau, puis ouvrez l’onglet Données, groupe Obtenir et transformer des données.
  2. Choisissez la commande qui charge le tableau ou la plage en cours (À partir d’un tableau ou d’une plage ; le libellé varie un peu selon la version). Pour un fichier, un dossier ou une base, passez par Obtenir des données.
  3. L’éditeur Power Query s’ouvre dans une nouvelle fenêtre : vos données au centre, les Étapes appliquées à droite. Chaque correction s’ajoute à cette liste.
  4. Pour revenir en arrière, supprimez une étape avec la croix à sa gauche. Vous pouvez donc essayer sans risque.
  5. Quand le résultat vous convient, cliquez sur Accueil, puis Fermer et charger. Les données nettoyées arrivent dans une feuille Excel, sous forme de tableau.

Les corrections courantes, étape par étape

L’ordre compte : retirez d’abord ce qui est inutile, corrigez le texte et les types, et ne dédoublonnez qu’ensuite. Supprimer des doublons avant d’avoir retiré les espaces laisse passer de fausses différences.

Supprimer les colonnes inutiles

Les colonnes dont vous n’avez pas besoin ralentissent les traitements. Sélectionnez celles à garder en maintenant Ctrl, faites un clic droit et choisissez Supprimer les autres colonnes. Cette méthode est plus sûre que de supprimer les colonnes une par une : si une nouvelle colonne apparaît un jour dans le fichier source, elle sera écartée d’office.

Nettoyer le texte et les espaces

Sélectionnez la colonne, puis Transformer et Format. Vous y trouverez :

  • Supprimer les espaces, pour retirer ceux du début et de la fin ;
  • Nettoyer, pour retirer les caractères non imprimables, fréquents dans les exports de logiciels ;
  • minuscules, MAJUSCULES et Mettre en majuscules à chaque mot.

La correction s’applique à toutes les lignes et vous voyez le résultat aussitôt. Pour une erreur récurrente, comme « Oui » écrit avec une faute dans une partie des lignes, utilisez Remplacer les valeurs.

Corriger les types de données

Power Query devine le type de chaque colonne au chargement, et se trompe parfois. Une colonne de codes postaux reconnue comme un nombre perd ses zéros de tête : « 01000 » devient 1000. Cliquez sur l’icône à gauche du nom de la colonne pour choisir Texte, Nombre entier, Nombre décimal, Date, etc.

Les dates doivent être au format date pour calculer des durées, les montants en nombre décimal pour les additions. Si des dates arrivent dans un format d’un autre pays, passez par Modifier le type et l’option qui utilise les paramètres régionaux, et indiquez la langue du fichier source.

Traiter les valeurs manquantes

Plusieurs réponses existent selon le cas :

  • Transformer, puis Remplir et Vers le bas répète la dernière valeur connue. C’est utile pour un export où le nom du client n’apparaît que sur la première ligne de chaque groupe. Le remplissage ne s’applique qu’aux valeurs nulles : une case qui contient un texte vide reste telle quelle. Remplacez d’abord le vide par null avec Remplacer les valeurs.
  • Remplacer les valeurs transforme les cases vides en « Non renseigné » ou en 0.
  • Accueil, Supprimer les lignes, puis Supprimer les lignes vides élimine les lignes dont toutes les colonnes sont vides.

Gérer les doublons

Sélectionnez les colonnes qui définissent un doublon : pour des clients, l’e-mail ou le numéro de téléphone. Puis Accueil, Supprimer les lignes et Supprimer les doublons. Power Query garde la première ligne rencontrée et distingue les majuscules des minuscules : « A@exemple.fr » et « a@exemple.fr » ne sont pas des doublons pour lui. Passez la colonne en minuscules avant.

Pour comprendre d’où viennent les doublons, utilisez d’abord Conserver les lignes et Conserver les doublons : vous les examinez avant de les supprimer.

Diviser et fusionner des colonnes

Un nom complet dans une seule colonne se sépare avec Fractionner la colonne, Par délimiteur, en choisissant par exemple l’espace ou le point-virgule. L’inverse existe : Fusionner les colonnes assemble un prénom et un nom avec le séparateur de votre choix. Une information par colonne facilite les filtres et les regroupements.

Filtrer les données indésirables

Cliquez sur la flèche à droite du nom de la colonne et décochez les valeurs à exclure. Les filtres de texte gardent les lignes qui commencent par, contiennent ou se terminent par certains caractères. Pour les nombres, filtrez sur des conditions comme « supérieur à 1000 » ou « entre 50 et 100 », ce qui écarte les valeurs aberrantes. Un filtre fondé sur une condition reste juste quand de nouvelles valeurs arrivent, ce qui n’est pas le cas d’une liste de cases décochées une à une.

Créer des colonnes calculées et lire le langage M

Le nettoyage inclut aussi l’enrichissement des données. L’onglet Ajouter une colonne propose trois outils.

  • Colonne à partir d’exemples : vous tapez quelques résultats attendus et Power Query propose la logique. Relisez ce qu’il a déduit, surtout s’il existe des cas particuliers.
  • Colonne conditionnelle : elle crée des catégories sans formule, par exemple « Faible », « Moyen » ou « Élevé » selon des seuils.
  • Colonne personnalisée : vous écrivez la formule en langage M, celui que Power Query génère derrière chaque clic.

Le langage M respecte les majuscules (Text.Trim, pas text.trim). Il utilise toujours la virgule comme séparateur, quelle que soit la langue d’Excel. Voici quelques formules de colonne personnalisée :

Text.Trim(Text.Clean([Nom]))

Cette formule retire les caractères non imprimables puis les espaces de début et de fin de la colonne Nom.

Text.PadStart(Text.From([CP]), 5, "0")

Elle remet les zéros de tête d’un code postal déjà converti en nombre : 1000 redevient « 01000 ».

if [Montant] < 1000 then "Faible" else if [Montant] < 5000 then "Moyen" else "Élevé"

C’est ce que produit une colonne conditionnelle. Les seuils sont à adapter à votre activité.

if [Region] = null then "Non renseigné" else [Region]

Elle remplace les valeurs nulles par un texte, sans toucher aux autres.

Pour garder la ligne la plus récente d’un client plutôt que la première trouvée, triez puis dédoublonnez. Le moteur peut ne pas respecter le tri sans Table.Buffer. Dans l’éditeur avancé (onglet Accueil), Source désigne ici l’étape précédente :

Table.Distinct(Table.Buffer(Table.Sort(Source, {{"Date", Order.Descending}})), {"Email"})

Réutiliser et actualiser vos requêtes

Une fois le nettoyage terminé, les étapes restent mémorisées. Quand vous recevez un nouveau fichier de même structure, placez-le au même endroit et sous le même nom, puis actualisez la requête depuis Données, Actualiser tout.

Trois habitudes rendent une requête durable :

  • Documentez les étapes complexes. Faites un clic droit sur une étape, puis Propriétés : vous pouvez la renommer et lui ajouter une description. Un intitulé comme « Retire les lignes de total » évite de relire le code six mois plus tard.
  • Dupliquez une requête pour créer une variante. Cela accélère le traitement de fichiers proches.
  • Traitez un dossier plutôt qu’un fichier si vous recevez un fichier par mois. Obtenir des données, À partir d’un fichier, puis À partir d’un dossier assemble tous les fichiers de même structure, et un nouveau fichier est pris en compte à l’actualisation.

Si le résultat doit ensuite alimenter un flux automatique, regardez comment automatiser Excel avec Power Automate : l’un prépare les données, l’autre enchaîne les actions.

Les erreurs fréquentes à éviter

  • Renommer une colonne dans le fichier d’origine. Power Query cherche ce nom et affiche une erreur indiquant que la colonne est introuvable. Faites vos renommages dans l’éditeur, et demandez à ceux qui produisent le fichier de garder les mêmes en-têtes.
  • Déplacer ou renommer le fichier source. L’actualisation échoue. Le chemin se corrige dans Accueil, Paramètres de la source de données.
  • Enchaîner les étapes dans le mauvais ordre. Une étape dépend de celles qui la précèdent : en supprimer une au milieu peut casser les suivantes.
  • Se fier à la détection automatique des types. L’étape « Type modifié », ajoutée d’office, cite les colonnes par leur nom et peut casser si le fichier change. Vérifiez-la à chaque nouvelle source.
  • Tester directement sur des millions de lignes. Travaillez sur un échantillon, puis lancez le traitement complet : cela évite de longues attentes si quelque chose ne va pas.

Quand confier la préparation des données à un expert

Pour un export à nettoyer dans un seul service, vous vous en sortirez très bien seul avec les étapes ci-dessus. La question se pose autrement quand le résultat alimente une clôture, une paie ou un reporting de direction, quand plusieurs services modifient les mêmes fichiers, ou quand une actualisation prend plusieurs minutes et que seul son auteur comprend la requête. Ce sont souvent les signes qu’un classeur est devenu une base de données qui ne dit pas son nom : il peut alors être plus sage de ranger ces données au même endroit, dans une base partagée, et de se faire accompagner pour reprendre l’existant sans perdre l’historique. Si l’objectif final est un suivi chiffré, voyez aussi ce qu’apporte un tableau de bord Power BI tenu à jour.

Questions fréquentes

Power Query fonctionne-t-il avec d’autres sources de données qu’Excel ?

Oui. Il se connecte aux fichiers CSV, aux bases de données SQL, aux API web, à des dossiers entiers et à de nombreux services en ligne. Vous pouvez donc centraliser le nettoyage de sources différentes dans un seul outil. Selon la source, une authentification sera demandée à la première connexion.

Peut-on annuler une transformation après avoir fermé l’éditeur ?

Oui. Ouvrez le volet Requêtes et connexions depuis l’onglet Données, puis double-cliquez sur la requête pour revenir dans l’éditeur. Toutes vos étapes apparaissent à droite. Supprimez celle qui pose problème, en vérifiant que les suivantes fonctionnent encore.

Les transformations ralentissent-elles Excel avec de gros fichiers ?

Le traitement se fait au moment de l’actualisation, pas à chaque calcul du classeur. Pour de très gros volumes, choisissez Fermer et charger dans, puis chargez les données uniquement dans le modèle de données plutôt que dans une feuille. Une feuille Excel reste limitée à 1 048 576 lignes et devient lourde bien avant.

Comment partager des requêtes avec des collègues ?

Les requêtes sont enregistrées dans le classeur : en envoyant le fichier, vous partagez aussi les étapes. Vérifiez que vos collègues ont bien accès aux sources, car un chemin vers votre disque local ne fonctionnera pas chez eux. Placez plutôt les sources dans un dossier partagé.

Peut-on programmer une actualisation automatique ?

Dans Excel, les propriétés de la requête permettent d’actualiser à l’ouverture du fichier ou toutes les X minutes, tant que le fichier reste ouvert. Dans le service Power BI, l’actualisation se planifie vraiment : jusqu’à 8 fois par jour sur une capacité partagée et 48 fois sur une capacité Premium, Fabric ou avec Premium par utilisateur. Le détail se trouve dans notre guide pour planifier l’actualisation de vos données Power BI. Si les fichiers ou les bases sont sur vos serveurs, il faudra aussi une passerelle de données.

Parlons de votre besoin

Décrivez ce qui vous fait perdre du temps. Nos experts vous disent quel outil convient.

  1. Vous décrivez votre besoinRéponse sous 48 h ouvrées.
  2. On en parle 40 minutesL’échange est offert.
  3. Vous voyez une maquetteAvant tout engagement.
  4. Votre outil est livréDe 4 à 6 semaines.
Ce qui vous intéresse (plusieurs choix possibles)

Vos informations servent seulement à répondre à votre demande.