Table des matières
L'une des vidéos les plus vues sur ma chaîne YouTube est une vidéo sur Flash Fill dans Microsoft Excel. L'essence de cet outil est que si vous avez besoin de transformer d'une manière ou d'une autre vos données source, il vous suffit de commencer à taper le résultat que vous souhaitez obtenir dans la colonne adjacente. Après plusieurs cellules tapées manuellement (généralement 2-3 suffisent), Excel « comprendra » la logique des transformations dont vous avez besoin et continuera automatiquement ce que vous avez tapé, complétant tout le travail monotone pour vous :
La quintessence de l'efficacité. Le bouton magique "faites-le bien" que nous aimons tous tant, n'est-ce pas ?
En fait, il existe un analogue d'un tel outil dans Power Query - là, il s'appelle Colonne d'exemples (Colonne des exemples). En fait, il s'agit d'une petite intelligence artificielle intégrée à Power Query qui peut rapidement apprendre de vos données, puis les transformer. Examinons de plus près ses capacités dans plusieurs scénarios pratiques afin de comprendre où il peut nous être utile dans des tâches réelles.
Exemple 1. Coller/découper du texte
Disons que nous avons un tel tableau "intelligent" dans Excel avec des données sur les employés :
Chargez-le dans Power Query de manière standard - avec le bouton À partir du tableau/plage languette Données (Données – À partir du tableau/de la plage).
Supposons que nous devions ajouter une colonne avec les noms de famille et les initiales de chaque employé (Ivanov SV pour le premier employé, etc.). Pour résoudre ce problème, vous pouvez utiliser l'une des deux méthodes suivantes :
- cliquez avec le bouton droit sur l'en-tête de colonne avec les données source et sélectionnez la commande Ajouter une colonne à partir d'exemples (Ajouter une colonne à partir d'exemples);
- sélectionnez une ou plusieurs colonnes avec des données et sur l'onglet Ajout d'une colonne choisir une équipe Colonne d'exemples. Ici, dans la liste déroulante, vous pouvez spécifier si toutes ou seulement les colonnes sélectionnées doivent être analysées.
Ensuite, tout est simple - dans la colonne qui apparaît à droite, nous commençons à saisir des exemples de résultats souhaités, et l'intelligence artificielle intégrée à Power Query tente de comprendre notre logique de transformation et de continuer par elle-même :
Au fait, vous pouvez entrer les options correctes dans toutes les cellules de cette colonne, c'est-à-dire pas nécessairement de haut en bas et en ligne. En outre, vous pouvez facilement ajouter ou supprimer ultérieurement des colonnes de l'analyse à l'aide des cases à cocher de la barre de titre.
Faites attention à la formule en haut de la fenêtre - c'est ce que Smart Query crée pour obtenir les résultats dont nous avons besoin. C'est d'ailleurs la différence fondamentale entre cet outil et Remplissage instantané dans Excel. Le remplissage instantané fonctionne comme une «boîte noire» - ils ne nous montrent pas la logique des transformations, mais donnent simplement des résultats prêts à l'emploi et nous les tenons pour acquis. Ici, tout est transparent et vous pouvez toujours comprendre parfaitement ce qui se passe exactement avec les données.
Si vous voyez que Power Query "a saisi l'idée", vous pouvez appuyer en toute sécurité sur le bouton OK ou raccourci clavier Ctrl+Entrer – une colonne personnalisée avec une formule inventée par Power Query sera créée. D'ailleurs, il peut être facilement modifié plus tard comme une colonne régulière créée manuellement (avec la commande Ajout d'une colonne - Colonne personnalisée) en cliquant sur l'icône d'engrenage à droite du nom de l'étape :
Exemple 2 : Cas comme dans les phrases
Si vous faites un clic droit sur l'en-tête de colonne avec du texte et sélectionnez la commande De La Carrosserie
(Transformer), alors vous pouvez voir trois commandes responsables du changement de registre :
Pratique et cool, mais dans cette liste, par exemple, j'ai personnellement toujours manqué d'une option de plus - cas comme dans les phrases, lorsque la majuscule (majuscule) ne devient pas la première lettre de chaque mot, mais seulement la première lettre de la cellule, et le reste du texte lorsque Ceci est affiché en lettres minuscules.
Cette fonctionnalité manquante est facile à mettre en œuvre avec l'intelligence artificielle Colonnes d'exemples – entrez simplement quelques options pour que Power Query continue dans le même esprit :
En tant que formule ici, Power Query utilise un tas de fonctions Texte.Supérieur и Texte.Inférieur, conversion de texte en majuscules et minuscules, respectivement, et fonctions Texte.Démarrer и Texte.Mid – analogues des fonctions Excel LEFT et PSTR, capables d'extraire une sous-chaîne du texte de la gauche et du milieu.
Exemple 3. Permutation de mots
Parfois, lors du traitement des données reçues, il devient nécessaire de réorganiser les mots dans les cellules dans une séquence donnée. Bien sûr, vous pouvez diviser la colonne en colonnes de mots séparées par le séparateur, puis la recoller dans l'ordre spécifié (n'oubliez pas d'ajouter des espaces), mais à l'aide de l'outil Colonne d'exemples tout sera beaucoup plus simple :
Exemple 4 : Seuls les chiffres
Une autre tâche très vitale consiste à extraire uniquement des nombres (nombres) du contenu de la cellule. Comme auparavant, après avoir chargé les données dans Power Query, accédez à l'onglet Ajout d'une colonne – Colonne à partir d'exemples et remplissez manuellement quelques cellules pour que le programme comprenne exactement ce que nous voulons obtenir :
Bingo!
Encore une fois, cela vaut la peine de regarder en haut de la fenêtre pour s'assurer que Query a correctement généré la formule - dans ce cas, elle contient une fonction Texte. Sélectionner, qui, comme vous pouvez le deviner, extrait les caractères donnés du texte source selon la liste. Par la suite, cette liste, bien sûr, peut être facilement modifiée dans la barre de formule si nécessaire.
Exemple 5 : Texte uniquement
Comme dans l'exemple précédent, vous pouvez extraire et vice versa - uniquement le texte, supprimer tous les chiffres, les signes de ponctuation, etc.
Dans ce cas, une fonction qui a déjà une signification opposée est utilisée - Text.Remove, qui supprime les caractères de la chaîne d'origine selon une liste donnée.
Exemple 6 : Extraire des données d'un porridge alphanumérique
Power Query peut également aider dans les cas plus difficiles, lorsque vous devez extraire des informations utiles de la bouillie alphanumérique dans une cellule, par exemple, obtenir le numéro de compte à partir de la description de l'objet du paiement sur un relevé bancaire :
Notez que la formule de conversion générée par Power Query peut être assez complexe :
Pour faciliter la lecture et la compréhension, il peut être converti en une forme beaucoup plus saine à l'aide d'un service en ligne gratuit. Formateur de requête de puissance:
Chose très pratique – respect aux créateurs!
Exemple 7 : Conversion de dates
Outil Colonne d'exemples peut également être appliqué aux colonnes date ou datetime. Lorsque vous saisissez les premiers chiffres d'une date, Power Query affiche utilement une liste de toutes les options de conversion possibles :
Ainsi, vous pouvez facilement convertir la date d'origine dans n'importe quel format exotique, tel que "année-mois-jour":
Exemple 8 : Catégorisation
Si nous utilisons l'outil Colonne d'exemples à une colonne avec des données numériques, cela fonctionne différemment. Supposons que les résultats des tests des employés soient chargés dans Power Query (scores conditionnels compris entre 0 et 100) et que nous utilisions la gradation conditionnelle suivante :
- Maîtres - ceux qui ont marqué plus de 90
- Experts – notés de 70 à 90
- Utilisateurs – de 30 à 70
- Débutants – ceux qui ont obtenu moins de 30
Si nous ajoutons une colonne des exemples à la liste et commençons à organiser ces gradations manuellement, alors très bientôt Power Query reprendra notre idée et ajoutera une colonne avec une formule, où les opérateurs s'imbriquent les uns dans les autres if une logique sera implémentée, très similaire à ce dont nous avons besoin :
Encore une fois, vous ne pouvez pas appuyer sur la situation jusqu'au bout, mais cliquez sur OK puis corrigez les valeurs de seuil déjà dans la formule – c'est plus rapide ainsi :
Conclusions
certainement un outil Colonne d'exemples n'est pas une "pilule magique" et, tôt ou tard, il y aura des situations non standard ou des cas particulièrement négligés d'une "ferme collective" dans les données, lorsque Power Query échouera et ne pourra pas déterminer ce que nous voulons correctement pour nous. Cependant, en tant qu'outil auxiliaire, il est très bon. De plus, en étudiant les formules qu'il a générées, vous pouvez approfondir vos connaissances sur les fonctions du langage M, ce qui vous sera toujours utile à l'avenir.
- Analyse de texte avec des expressions régulières (RegExp) dans Power Query
- Recherche de texte flou dans Power Query
- Remplissage Flash dans Microsoft Excel