Table des matières
Si vous êtes au moins un peu familiarisé avec les expressions régulières, vous n'avez pas besoin de les publier. Si vous n'êtes pas tout à fait dans le sujet, alors les expressions régulières (Regular Expressions = RegExp = "regexps" = "regulars") sont un langage où, à l'aide de caractères spéciaux et de règles, les sous-chaînes nécessaires sont recherchées dans le texte, elles sont extraites ou remplacé par un autre texte . C'est un outil très puissant et magnifique, d'un ordre de grandeur supérieur à toutes les autres façons de travailler avec du texte.
J'ai déjà décrit en détail et avec un tas d'exemples de la vie comment vous pouvez ajouter le support des expressions régulières à Excel en utilisant des macros simples - si vous n'avez pas lu cet article, je vous recommande fortement de le lire avant de continuer. Vous découvrirez plein de nouveautés, je vous garantis 🙂
Cependant, la question reste ouverte - comment ajouter la possibilité d'utiliser des expressions régulières dans Power Query ? Power Query, bien sûr, est bon en lui-même et peut faire beaucoup avec du texte (couper, coller, nettoyer, etc.), mais si vous pouviez le croiser avec la puissance des expressions régulières, ce ne serait qu'une bombe.
Malheureusement, il n'y a pas de fonctions intégrées pour travailler avec RegExps dans Power Query, et l'aide et le support technique officiels de Microsoft répondent à cette question par la négative. Cependant, il existe un moyen de contourner cette limitation 🙂
L'essence de la méthode
L'idée principale est simple à déshonorer.
Dans la liste des fonctionnalités intégrées de Power Query, il existe une fonction Page web. La description de cette fonction sur le site d'aide officiel de Microsoft est extrêmement concise :
Traduit, cela donnerait : "Renvoie le contenu du document HTML décomposé en ses structures de composants, ainsi qu'une représentation du document complet et de son corps après la suppression des balises." Description moyenne, franchement.
Habituellement, cette fonction est utilisée lors de l'importation de données à partir du Web et est automatiquement remplacée, par exemple, lorsque nous sélectionnons dans l'onglet Données Command À partir d'Internet (Données – À partir du Web). On donne à la fonction une page web en argument, et elle nous renvoie son contenu sous forme de tableaux, en ayant préalablement effacé toutes les balises.
Ce que l'aide ne dit PAS, c'est qu'en plus du langage de balisage HTML fonction Page web prend en charge les scripts JavaScript, qui est maintenant omniprésent sur les sites Web sur Internet. Et JavaScript, à son tour, a toujours été capable de travailler avec des expressions régulières et a des fonctions intégrées pour RegExps ! Donc, pour implémenter des expressions régulières dans Power Query, nous devrons alimenter les fonctions Web.Page en tant qu'argument d'un petit programme JavaScript qui fera tout le travail pour Power Query.
À quoi ça ressemble en JavaScript pur
Il existe de nombreux tutoriels détaillés sur l'utilisation des expressions régulières en JavaScript sur Internet (par exemple, un, deux).
En bref et simplifié, le code JavaScript ressemblera à ceci :
Ici:
- var str = 'Payer les factures 123 et 789 pour les saucisses'; – créer une variable str et assignez-lui le texte source que nous allons analyser.
- modèle var = /d+/gi ; – créer une expression régulière et la mettre dans une variable modèle.
L'expression commence par une barre oblique (/).
L'expression elle-même ici, par exemple, est d+ représente n'importe quelle séquence de chiffres.
Grâce à la fraction après l'expression, il existe des paramètres de recherche supplémentaires (modificateurs) - ils peuvent être spécifiés dans n'importe quel ordre :
- g – signifie recherche globale, c'est-à-dire qu'après avoir trouvé une correspondance, vous ne devez pas vous arrêter, mais continuer la recherche jusqu'à la fin du texte. Si ce modificateur n'est pas défini, notre script ne renverra que la première correspondance (123)
- i – rechercher sans tenir compte de la casse des lettres
- m – recherche multi-lignes (utilisée lorsque le texte source est divisé en plusieurs lignes)
- var result = str.match(motif).join(';'); – effectuer une recherche dans le texte source (str) par l'expression régulière donnée (modèle) et mettre les résultats dans une variable résultat, en les concaténant avec un point-virgule à l'aide de la commande rejoindre
- document.write(résultat); – afficher le contenu de la variable résultat
Notez également que les chaînes de texte (à l'exception des expressions régulières) en JavaScript sont entourées d'apostrophes, et non de guillemets comme dans Power Query ou VBA.
En sortie, ce script nous donnera comme résultat tous les nombres trouvés dans le texte source :
123, 789
Le petit cours JavaScript est terminé, merci à tous. J'espère que vous comprenez la logique 🙂
Il reste à transférer cette construction vers Power Query.
Fonction de recherche et d'extraction de texte par expression régulière dans Power Query
Nous faisons ce qui suit :
1. Ouvrez Excel et créez une nouvelle Power Query vide dans l'onglet Données – Obtenir des données / Créer une demande – À partir d'autres sources – Demande vide (Données - Obtenir des données / Nouvelle requête - À partir d'autres sources - Requête vide). Si vous avez une ancienne version d'Excel 2010-2013 et de Power Query, vous n'avez pas intégré, mais a été installé en tant que complément séparé, alors tout cela sera sur l'onglet Requête d'alimentationEt pas Données.
2. Dans la fenêtre vide de l'éditeur de requêtes qui s'ouvre, dans le panneau de droite, saisissez immédiatement le nom de notre future fonction (par exemple, fxRegExpExtrait)
3. Passons à l'onglet Affichage – Éditeur avancé (Affichage — Éditeur avancé), on efface tout le M-code de la requête vide et on y colle le code de notre superfonction :
Attention à vos mains :
Dans la première ligne, nous disons que notre fonction aura trois arguments textuels : txt – le texte original en cours d'analyse, regex – modèle d'expression régulière, délimiter — caractère délimiteur pour l'affichage des résultats.
On appelle ensuite la fonction Page web, formant le code JavaScript décrit ci-dessus dans son argument. Nous collons et remplaçons nos arguments variables dans le code.
Fragment:
[Données]{0}[Enfants]{0}[Enfants]{1}[Texte]{0}
… est nécessaire pour « tomber » dans le tableau avec les résultats dont nous avons besoin. Le fait est que la fonction Page web en conséquence, il produit plusieurs tableaux imbriqués qui répètent la structure d'une page Web. Sans ce morceau de code M, notre fonction afficherait ceci :
… et il faudrait cliquer plusieurs fois sur le mot lampe de table, successivement "tombant" dans des tables imbriquées enfants dans des colonnes Enfants:
Au lieu de toute cette citation, nous indiquons immédiatement dans le code de notre fonction quelle table et quelle colonne imbriquées (Texte) nous avons besoin.
Voici, en fait, tous les secrets. Il reste à appuyer sur le bouton Finition dans la fenêtre éditeur avancé, où nous avons inséré notre code, et vous pouvez passer au plus délicieux - essayez notre fonction au travail.
Voici quelques exemples de graines.
Exemple 1. Récupérer le numéro de compte et la date de la description du paiement
Nous avons un relevé bancaire avec une description (objet) des paiements, où vous devez extraire les numéros et les dates des factures payées dans des colonnes séparées :
Nous chargeons la table dans Power Query de la manière standard via Données – À partir du tableau/de la plage (Données - De Tcapable/Range).
Ensuite, nous ajoutons une colonne calculée avec notre fonction via Ajouter une colonne - Appeler une fonction personnalisée (Ajouter une colonne - Appeler une fonction personnalisée) et saisissez ses arguments :
En tant qu'expression régulière (argument regex) modèle que nous utilisons :
(j{3,5}|d{2}.d{2}.d{4})
… traduit en langage humain signifiant :
numéros de 3 à 5 chiffres (numéros de compte)
or
fragments de la forme « nombre 2 bits – point – nombre 2 bits – point – nombre 4 bits », c'est-à-dire des dates au format JJ.MM.AAAA.
En tant que caractère délimiteur (argument délimiter) entrez un point-virgule.
Après avoir cliqué sur OK notre fonction magique analyse toutes les données initiales selon notre expression régulière et nous forme une colonne avec les numéros et dates trouvés des factures :
Il reste à le séparer par un point virgule à l'aide de la commande Accueil — Colonne divisée — Par délimiteur (Accueil — Scinder la colonne — Par délimiteur) et on obtient ce qu'on veut :
Beauté!
Exemple 2 : Extraire des adresses e-mail d'un texte
Supposons que nous ayons le tableau suivant comme données initiales :
… d'où nous devons extraire les adresses e-mail qui s'y trouvent (pour plus de clarté, je les ai surlignées en rouge dans le texte).
Comme dans l'exemple précédent, nous chargeons la table dans Power Query de manière standard via Données – À partir du tableau/de la plage (Données - De Tcapable/Range).
Ensuite, nous ajoutons une colonne calculée avec notre fonction via Ajouter une colonne - Appeler une fonction personnalisée (Ajouter une colonne - Appeler une fonction personnalisée) et saisissez ses arguments :
L'analyse des adresses e-mail est une tâche plus difficile et il existe un tas d'expressions régulières à des degrés divers de cauchemar pour le résoudre. J'ai utilisé l'une des options simples - pas idéale, mais qui fonctionne assez bien dans la plupart des cas :
[w|.|-]*@w*.[w|.]*
Comme séparateur (délimiter), vous pouvez saisir un point-virgule et un espace.
Cliquez sur OK et nous obtenons une colonne avec des adresses e-mail extraites du texte original "bouillie":
La magie!
PS
Comme le dit le proverbe : "Il n'y a pas une si bonne chose qui ne puisse être encore améliorée." Power Query est cool en soi, et lorsqu'il est combiné avec des expressions régulières, il nous donne une puissance et une flexibilité complètement irréalistes dans le traitement de toutes les données textuelles. J'espère que Microsoft ajoutera un jour la prise en charge de RegExp dans les mises à jour de Power Query et de Power BI et que toutes les danses ci-dessus avec un tambourin appartiendront au passé. Eh bien, pour l'instant, oui.
Je tiens également à ajouter qu'il est pratique de jouer avec les expressions régulières sur le site https://regexr.com/ – directement dans l'éditeur en ligne. Là dans la rubrique Modèles communautaires Il existe un grand nombre de saisons régulières prêtes à l'emploi pour toutes les occasions. Expérimentez - toute la puissance des expressions régulières est désormais à votre service dans Power Query !
- Que sont les expressions régulières (RegExp) et comment les utiliser dans Excel
- Recherche de texte flou dans Power Query
- Assemblage de tables à partir de différents fichiers à l'aide de Power Query