Lire en anglais

Partager via


Regroupement ou résumant les lignes

Dans Power Query, vous pouvez regrouper les valeurs figurant dans différentes lignes dans une seule valeur, en regroupant les lignes en fonction des valeurs figurant dans une ou plusieurs colonnes. Vous avez le choix entre deux types d’opérations de regroupement :

  • Regroupements de colonnes.

  • Regroupements de lignes.

Pour ce tutoriel, vous utiliserez l’exemple de table suivant.

Échantillon de table initiale.

Capture d’écran d’une table avec les colonnes Année (2020), Pays (USA, Panama ou Canada), Produit (Chemise ou Shorts), Canal de vente (En ligne ou Revendeur) et Unités (différentes valeurs comprises entre 55 et 7500)

Où trouver le bouton Grouper par

Vous pouvez trouver le bouton Grouper par dans trois emplacements :

  • Sous l’onglet Accueil, dans le groupe Transformer.

    Capture d’écran du ruban Power Query avec l’option Grouper par mise en évidence dans l’onglet Accueil.

  • Sous l’onglet Transformer, dans le groupe Table.

    Capture d’écran du ruban Power Query avec l’option Grouper par mise en évidence dans l’onglet Transformer.

  • Dans le menu contextuel, lorsque vous cliquez avec le bouton droit pour sélectionner des colonnes.

    Capture d’écran d’une table avec l’option Grouper par mise en évidence dans le menu contextuel.

Utiliser une fonction d’agrégation pour grouper par une ou plusieurs colonnes

Dans cet exemple, votre objectif est de résumer le total des unités vendues aux niveaux d’un pays et d’un canal de vente. Pour effectuer l’opération Grouper par, vous utilisez les colonnes Pays et Canal de vente.

  1. Sélectionnez Grouper par sous l’onglet Accueil.
  2. Sélectionnez l’option Avancé afin de pouvoir sélectionner plusieurs colonnes à regrouper.
  3. Sélectionnez la colonne Country.
  4. Sélectionnez Ajouter un regroupement.
  5. Sélectionnez la colonne Sales Channel.
  6. Dans Nouveau nom de colonne, entrez Total d’unités, dans Opération, sélectionnez Somme, et dans Colonne, sélectionnez Units.
  7. Sélectionnez OK.

Capture d’écran de la boîte de dialogue Grouper par avec les colonnes agrégées remplies.

Cette opération donne le tableau suivant.

Capture d’écran de l’exemple de table de sortie avec les colonnes Pays, Canal de vente et Nombre total d’unités.

Opérations disponibles

La fonctionnalité Grouper par permet de classer les opérations de deux manières :

  • Opération au niveau des lignes
  • Opération au niveau des colonnes

Le tableau suivant décrit chacune de ces onglets.

Nom de l’opération Category Description
Sum Opération de colonne Additionne toutes les valeurs d’une colonne
Moyenne Opération de colonne Calcule la valeur moyenne d’une colonne
Median Opération de colonne Calcule la valeur médiane d’une colonne
Min Opération de colonne Calcule la valeur minimale d’une colonne
Max Opération de colonne Calcule la valeur maximale d’une colonne
Centile Opération de colonne Calcule le centile à l’aide d’une valeur d’entrée comprise entre 0 et 100, à partir d’une colonne
Compter les valeurs distinctes Opération de colonne Calcule le nombre de valeurs distinctes dans une colonne
Count : compter les lignes Opération de ligne Calcule le nombre total de lignes dans un groupe donné
Compter les lignes distinctes Opération de ligne Calcule le nombre de lignes distinctes dans un groupe donné
Toutes les lignes Opération de ligne Affiche toutes les lignes groupées dans une valeur de table sans agrégations

Notes

Les opérations Compter les valeurs distinctes et Centile sont disponibles uniquement dans Power Query Online.

Effectuer une opération pour regrouper par une ou plusieurs colonnes

À partir de l’exemple d’origine, dans cet exemple, vous créez une colonne contenant le nombre total d’unités et deux autres colonnes qui vous donnent le nom et les unités vendues pour le produit le plus performant, résumés au niveau du pays et du canal de vente.

Capture d’écran de l’exemple de table de sortie avec des opérations.

  1. Utilisez les colonnes suivantes comme Regrouper par :

    • Pays ou région
    • Canal de vente
  2. Créez deux nouvelles colonnes en en suivant ces étapes :

    1. Agrégez la colonne Units à l’aide de l’opération Somme. Nommez cette colonne Total d’unités.
    2. Ajoutez une nouvelle colonne Produits à l’aide de l’opération Toutes les lignes.

    Capture d’écran de la boîte de dialogue Grouper par avec une colonne non agrégée.

Une fois cette opération terminée, vous constatez que la colonne Produits contient des valeurs [Table] à l’intérieur de chaque cellule. Chaque valeur [Table] contient toutes les lignes regroupées par les colonnes Country et Sales Channel de votre table d’origine. Vous pouvez sélectionner l’espace blanc à l’intérieur de la cellule pour afficher un aperçu du contenu de la table en bas de la boîte de dialogue.

Capture d’écran du volet de visualisation des détails de la table.

Notes

Il se peut que le volet d’aperçu des détails n’affiche pas toutes les lignes utilisées pour l’opération Grouper par. Vous pouvez sélectionner la valeur [Table] pour afficher toutes les lignes associées à l’opération Grouper par correspondante.

Ensuite, vous devez extraire la ligne contenant la valeur la plus élevée dans la colonne Units des tables à l’intérieur de la nouvelle colonne Produits, et appeler cette nouvelle colonne Produit le plus performant.

Extraire les informations du produit le plus performant

Avec la nouvelle colonne Produits contenant les valeurs [Table], vous créez une colonne personnalisée en accédant à l’onglet Ajouter une colonne dans le ruban et en sélectionnant Colonne personnalisée dans le groupe Général.

Capture d’écran du ruban Power Query avec l’option Colonne personnalisée mise en évidence dans l’onglet Ajouter une colonne.

Nommez votre nouvelle colonne Produit le plus performant. Saisir la formule Table.Max([Products], "Units" ) sous Formule de colonne personnalisée.

Capture d’écran de la boîte de dialogue Colonne personnalisée avec la formule pour Table.Max saisie.

Le résultat de cette formule crée une colonne avec des valeurs [Enregistrement]. Ces valeurs d’enregistrement sont essentiellement une table avec une seule ligne. Ces enregistrements contiennent la ligne avec la valeur maximale de la colonne Units de chaque valeur [Table] dans la colonne Produits.

Capture d’écran du résultat de la formule de colonne personnalisée avec Table.Max.

Avec cette nouvelle colonne Produit le plus performant qui contient des valeurs [Enregistrement], vous pouvez sélectionner l’icône de développement, sélectionner les champs Produit et Unités, et fields, puis sélectionnez OK.

Capture d’écran de l’opération de développement pour la valeur d’enregistrement dans la colonne Produit le plus performant.

Après que vous avez supprimé votre colonne Produits et défini le type de données pour les deux colonnes nouvellement développées, le résultat ressemble à l’image suivante.

Capture d’écran de la table finale avec toutes les transformations.

Regroupement probable

Notes

La fonctionnalité suivante est disponible uniquement dans Power Query Online.

Pour montrer comment effectuer un « regroupement probable », considérez l’échantillon de table illustré dans l’image suivante.

Capture d’écran d’une table avec neuf lignes d’entrées qui contiennent diverses orthographes et emplois des majuscules pour les noms Miguel et William.

L’objectif d’un regroupement probable est d’effectuer une opération Grouper par qui utilise un algorithme de correspondance approximative pour les chaînes de texte. Power Query utilise l’algorithme de similarité Jaccard pour mesurer la similarité entre des paires d’instances. Ensuite, il applique un clustering hiérarchique agglomératif pour regrouper des instances. L’image suivante montre la sortie attendue, où la table est regroupée par la colonne Personne.

Capture d’écran d’une table montrant les entrées Miguel et Mike pour la colonne Personne et la Fréquence 3 et 2, respectivement.

Pour opérer le regroupement probable, vous effectuez les mêmes étapes que celles décrites précédemment dans cet article. La seule différence est que, cette fois, dans la boîte de dialogue Grouper par, vous activez la case à cocher Utiliser le regroupement probable.

Capture d’écran de la case à cocher Regroupement probable mise en évidence dans la zone de dialogue Grouper par.

Pour chaque groupe de lignes, Power Query choisit l’instance la plus fréquente comme instance « canonique ». Si plusieurs instances se produisent avec la même fréquence, Power Query choisit la première. Une fois que vous avez sélectionné OK dans la boîte de dialogue Grouper par, vous obtenez le résultat attendu.

Capture d’écran de l’exemple de table finale Regroupement probable, pas de table de transformation.

Toutefois, vous avez davantage de contrôle sur l’opération de regroupement probable en développant les Options de groupe probable.

Capture d’écran de la boîte de dialogue Grouper par avec les options de Regroupement probable mises en évidence.

Les options suivantes sont disponibles pour le regroupement probable :

  • Seuil de similarité (facultatif) : cette option indique comment deux valeurs similaires doivent être regroupées. La valeur minimale de zéro (0) entraîne le regroupement de toutes les valeurs. La valeur maximale de 1 permet de regrouper uniquement les valeurs qui correspondent exactement. La valeur par défaut est 0.8.
  • Ignorer la casse : lors de la comparaison de chaînes de caractères, la casse est ignorée. Cette option est activée par défaut.
  • Regroupement par combinaison de parties de texte : l’algorithme tente de combiner des parties de texte (par exemple, en combinant Micro et soft en Microsoft) pour regrouper les valeurs.
  • Afficher les scores de similarité : afficher les scores de similarité entre les valeurs d’entrée et les valeurs représentatives calculées après le regroupement probable. Nécessite l’ajout d’une opération telle que Toutes les lignes pour présenter ces informations ligne par ligne.
  • Table de transformation (facultatif) : vous pouvez sélectionner une table de transformation qui mappe les valeurs (par exemple, mapper MSFT à Microsoft) pour les regrouper.

Pour cet exemple, une table de transformation est utilisée pour montrer comment des valeurs peuvent être mappées. La table de transformation comporte deux colonnes :

  • De : La chaîne de texte à rechercher dans votre tableau.
  • To : chaîne de texte à utiliser pour remplacer la chaîne de texte entrée dans la colonne From.

L’image suivante montre la table de transformation utilisée dans cet exemple.

Capture d’écran de la table montrant Des valeurs Mike et William et Aux valeurs Miguel et Bill.

Important

Il est important que la table de transformation ait les mêmes colonnes et noms de colonnes que ceux indiqués dans l’image précédente (ils doivent être étiquetés « De » et « À »). Sinon, Power Query ne reconnaît pas la table en tant que table de transformation.

Revenez à la boîte de dialogue Grouper par, développez les Options de groupe probable, modifiez l’opération Compter les lignes en Toutes les lignes, activez l’option Afficher les scores de similarité, puis sélectionnez le menu déroulant Table de transformation.

Capture d’écran du menu déroulant de l’exemple de table de transformation de regroupement probable.

Après que vous avez sélectionné la table de, sélectionnez OK. Le résultat de cette opération vous offre les informations suivantes :

Capture d’écran de l’exemple de table finale de regroupement probable avec une table de transformation.

Dans cet exemple, l’option Ignorer la casse a été activée, de sorte que les valeurs figurant dans la colonne From de la Table Transformation sont utilisées pour rechercher la chaîne de texte sans tenir compte de la casse de celle-ci. Cette opération de transformation se produit en premier, suivie de l’opération de regroupement probable.

Le score de similarité est également affiché dans la valeur de table en regard de la colonne Person, reflétant exactement la façon dont les valeurs ont été regroupées et leurs scores de similarité respectifs. Vous pouvez développer cette colonne au besoin ou utiliser les valeurs des nouvelles colonnes Fréquence pour d’autres types de transformations.

Notes

Lors d’un regroupement par plusieurs colonnes, la table de transformation effectue l’opération de remplacement dans toutes les colonnes si le remplacement de la valeur augmente le score de similarité.

Pour plus d’informations sur le fonctionnement des tables de transformation, accédez aux Principes pour les tables de transformation.

Voir aussi

Ajouter une colonne personnalisée
Supprimer les doublons