How to use fuzzy matching in Power Query to clean up Excel data

Les recherches Excel standard sont des perfectionnistes qui échouent en raison de fautes de frappe ou d’espaces supplémentaires. Bien que vous puissiez pirater une solution en utilisant les fonctions XLOOKUP, TRIM et LOWER imbriquées, elle est fragile et difficile à maintenir. La correspondance floue Power Query est la méthode de travail la plus intelligente et « suffisamment proche ».

Power Query utilise un algorithme de correspondance floue sophistiqué pour calculer un score de similarité entre deux chaînes, comblant ainsi le fossé entre « Microsoft » et « Mcrosoft » sans une seule modification manuelle. Cela signifie que vous pouvez fusionner des exportations compliquées avec des bases de données principales plus propres en quelques secondes.

Le but : Une histoire de deux tables

Imaginez que vous disposez d’un rapport de ventes (T_Sales, orange) rempli d’entrées manuelles et que vous devez extraire la région de vente d’une table principale (T_Master, vert).

Cependant, en raison de fautes de frappe et de conventions de dénomination différentes dans T_Sales, un XLOOKUP standard renverrait #N/A pour presque toutes les recherches. Nous avons donc besoin d’Excel pour examiner les noms dans T_Sales, trouver la correspondance la plus proche dans T_Master et renvoyer la région, même si l’orthographe est légèrement erronée.

Pour gérer des raccourcis métier spécifiques qu’aucun algorithme ne peut deviner (comme “MSFT”), vous avez également besoin d’une petite table de pont (T_Abbrev, bleu) avec les en-têtes de colonnes From et To. Assurez-vous que ce tableau est prêt avant de commencer.

Étape 1 : Importez vos données à l’aide de connexions

Avant de laisser libre cours à l’algorithme, vous devez intégrer les trois tables dans le moteur Power Query. Commencez par vous assurer que vos plages de données sont formatées sous forme de tableaux Excel (Ctrl+T). Ensuite, importez le premier tableau en sélectionnant n’importe quelle cellule du tableau et en cliquant sur Données > À partir du tableau/plage.

En rapport

Si vous ne renommez pas les tableaux dans Excel, c’est aujourd’hui le jour pour commencer

Qu’y a-t-il dans un nom ? Eh bien, beaucoup.

Pour éviter d’encombrer votre classeur, ne cliquez pas sur le bouton standard « Fermer et charger » après avoir importé chaque table dans Power Query. Au lieu de cela, cliquez sur la flèche vers le bas et sélectionnez « Fermer et charger vers ».

Ensuite, dans la boîte de dialogue Importer des données, sélectionnez « Créer uniquement une connexion » et cliquez sur « OK ».

Après avoir répété ce processus pour les trois tables, vous les verrez sous forme de connexions uniquement dans le volet Requêtes et connexions (qui s’ouvre automatiquement après la création de la première connexion), prêtes pour la fusion.

Étape 2 : Fusionnez vos tables pour commencer le nettoyage

Pour commencer le nettoyage, cliquez avec le bouton droit sur “T_Sales” dans le volet Requêtes et connexions et sélectionnez “Fusionner”.

Ensuite, dans la boîte de dialogue Fusionner, sélectionnez “T_Master” comme deuxième table et dans les deux aperçus de table, sélectionnez la colonne “Nom”. Essentiellement, vous dites à Excel que vous souhaitez écraser les tables T_Sales et T_Master, et la colonne Nom est ce qui les relie.

Maintenant, cochez « Utiliser la correspondance floue pour effectuer la fusion » pour indiquer à Excel que certaines connexions ne sont pas parfaites en raison de fautes de frappe ou de conventions de dénomination incohérentes. Sous le capot, l’option Ignorer la casse est cochée par défaut, ce qui signifie que « MICROSOFT » correspondra correctement à « Microsoft » sans aucune étape supplémentaire. Pour le type de jointure, restez sur « Left Outer » : cela garantit que vous conservez chaque transaction de vente tout en marquant simplement les informations régionales correspondantes.

En rapport

Comment utiliser les caractères génériques dans Microsoft Excel pour affiner votre recherche

Trouvez des correspondances partielles en un instant.

Étape 3 : Composez le seuil de similarité

OK, vous avez donc indiqué à Excel les tableaux que vous fusionnez, la variable de connexion et que les correspondances ne sont pas exactes. Maintenant, vous devez définir le seuil.

Cliquez sur la flèche à côté de “Options de correspondance floue”.

Le seuil de similarité est une échelle de 0,00 à 1,00, où 0,00 correspond à toutes les valeurs avec n’importe quel niveau de similarité et 1,00 correspond uniquement aux valeurs exactes. Notez dans la capture d’écran ci-dessus que trois des six lignes correspondent. En effet, Power Query utilise par défaut un seuil de similarité de 0,80. À ce niveau, le moteur est strict : il détecte facilement « Microsoft Inc » mais échoue sur les fautes de frappe plus agressives comme « Appel » ou « Gogle ».

Dans ce cas, un seuil de 0,50 est suffisamment bas pour détecter « Appel » et « Gogle » sans faire correspondre accidentellement des mots sans rapport. Il trouve cinq lignes sur six, ne laissant que l’abréviation « MSFT » sans correspondance.

Étape 4 : Appliquer la table de transformation pour les abréviations

Aucun algorithme n’est assez intelligent pour savoir que « MSFT » signifie Microsoft. C’est là que la table T_Abbrev entre en jeu : dans le menu déroulant Table de transformation, cliquez sur “T_Abbrev”, ce qui oblige le moteur à traiter vos remplacements manuels comme des correspondances parfaites. Une fois sélectionné, votre nombre de matchs en bas passera à un parfait six sur six.

Étape 5 : Nettoyez et résumez votre rapport final

Lorsque vous cliquez sur « OK », vous êtes redirigé vers l’éditeur Power Query avec une nouvelle colonne remplie de résultats de tableau.

Cliquez sur l’icône “Développer” dans l’en-tête de colonne, décochez “Utiliser le nom de colonne d’origine comme préfixe” pour éviter les en-têtes maladroits comme T_Master.Name, puis cliquez sur “OK”.

Vous pouvez voir que les noms sont désormais standardisés dans une nouvelle colonne Name.1 et que les régions sont ajoutées dans une nouvelle colonne Région.

À partir de là, supprimez la colonne Nom d’origine en désordre (cliquez avec le bouton droit sur l’en-tête de la colonne et sélectionnez “Supprimer”), cliquez et faites glisser la nouvelle colonne Nom vers la gauche afin qu’elle soit la première colonne du tableau, puis renommez-la (double-cliquez sur l’en-tête de la colonne) en quelque chose comme Nom officiel. Pendant que vous y êtes, assurez-vous que les types de données corrects sont attribués à chaque colonne en cliquant sur les icônes dans les en-têtes de colonne.

En rapport

Comment nettoyer et importer des données à l’aide de Power Query dans Excel

Ne négligez pas cet incroyable outil Excel !

Étant donné que ce processus marque chaque transaction individuelle, vous vous retrouvez avec plusieurs lignes pour la même entreprise. Pour transformer cela en un rapport propre, dans l’onglet Transformation, cliquez sur “Regrouper par”.

Ensuite, dans la boîte de dialogue Regrouper par, sélectionnez le bouton radio “Avancé” en haut. Cela vous permet de regrouper plusieurs colonnes à la fois. Dans ce cas, nous souhaitons regrouper selon les colonnes Nom et Région. Pour ce faire, pour le premier regroupement, sélectionnez « Nom officiel ». Ensuite, cliquez sur « Ajouter un regroupement » et sélectionnez « Région ».

Maintenant, définissez le calcul. Dans le champ Nouveau nom de colonne, saisissez Ventes totalesdans le champ Opération, sélectionnez « Somme » et dans le champ Colonne, sélectionnez « Ventes ».

Lorsque vous cliquez sur « OK », toutes les lignes répétées sont regroupées.

Une fois que vous avez examiné les résultats pour confirmer que toutes les données apparaissent comme prévu, cliquez sur « Fermer et charger » dans l’onglet Accueil.

Désormais, vos données joliment résumées et parfaitement orthographiées apparaissent dans une nouvelle feuille Excel. Voici une capture d’écran avant et après qui vous montre les avantages de l’utilisation de la correspondance floue de Power Query pour fusionner et nettoyer des ensembles de données désordonnés dans Excel.

Les recherches standard sont trop rigides pour la réalité des données désordonnées. En utilisant la correspondance floue, vous échangez la saisie manuelle des données contre un système automatisé et évolutif qui comprend l’intention plutôt que la simple syntaxe. Ce n’est qu’une des nombreuses façons d’exploiter les outils Power Query pour ranger les données de feuilles de calcul désordonnées : une fois que vous aurez commencé, vous ne pourrez plus vous arrêter !

Système d’exploitation

Windows, macOS, iPhone, iPad, Android

Essai gratuit

1 mois

Microsoft 365 inclut l’accès aux applications Office telles que Word, Excel et PowerPoint sur jusqu’à cinq appareils, 1 To de stockage OneDrive et bien plus encore.