IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

Power Query: Etablir le TOP X d'une série de données (interface graphique)

Noter ce billet
par , 23/11/2020 à 10h42 (467 Affichages)
Salut.

Power Query permet vraiment pas mal de choses et devient pour moi l'outil incontournable de la préparation des données pour Excel.

Dans ce billet, je propose d'établir le TOP X d'une série de données, sur base du tableau suivant:

Nom : 2020-11-23_083632.png
Affichages : 311
Taille : 14,5 Ko


L'idée est de calculer les quantités sorties pour chaque article, et d'établir le TOP 3 des articles les mieux vendus. Bien sûr, on pourrait utiliser un tableau croisé dynamique pour réaliser l'opération.

Nom : 2020-11-23_085201.png
Affichages : 41
Taille : 3,4 Ko



La récupération des données par Power Query au sein d'un tableau structuré permet cependant une exploitation souvent plus simple de ces valeurs. De plus, comme nous le verrons plus loin dans le billet, Power Query permet une variation du X pour établir le Top 5, le Top 10, le Top X des articles vendus.

Nom : 2020-11-23_094424.png
Affichages : 38
Taille : 1,9 Ko



J'illustre la commande Power Query pour chaque étape, mais toutes les étapes sont réalisables au travers du QBE (Query By Example) de Power Query pour vous permettre de réaliser les étapes facilement par l'interface Power Query. Seule la dernière étape pour permettre la variation du X nécessite la modification manuelle de la ligne de commande.

1. La première étape consiste à récupérer le tableau dans Power Query, ce qui est réalisable facilement en sélectionnant le tableau puis, via l'onglet Données > Récupérer et transformer des données > A partir d'un tableau ou d'une plage, on intègre le tableau à la solution Power Query. Deux étapes sont alors créées dans Power Query puisque, lors de la récupération de la source, Power query retype les données par défaut. Nous renommons la requête et nous en profitons ici pour modifier le type des données de date que Power Query met en datetime alors que nous ne souhaitons que les dates (1). Nous renommons nos étapes pour une meilleure compréhension de la solution Power Query mise en place:

= Excel.CurrentWorkbook(){[Name="t_Mouvements"]}[Content]
= Table.TransformColumnTypes(Source,{{"Article", type text}, {"Date", type date}, {"Type", type text}, {"Qté", Int64.Type}})


2. Nous allons filtrer sur les sorties pour ne retenir que les lignes qui nous intéressent. Perso, je prends l'habitude de renommer l'étape de façon systématique.

= Table.SelectRows(AdapterFiltre, each ([Type] = "Sortie"))

3. Nous allons regrouper les valeurs par articles en sommant les quantités. Dans l'onglet Transformer, nous avons à gauche l'outil de regroupement, qui consiste en une boite de dialogue qui normalement se passe de commentaires.

= Table.Group(FiltrerSorties, {"Article"}, {{"Qtés", each List.Sum([Qté]), type nullable number}})
Nom : 2020-11-23_090804.png
Affichages : 34
Taille : 139,0 Ko


4. Nous approchons du but. Il faut maintenant trier les données par ordre décroissant sur les quantités vendues, puis ne reprendre que les 3 premières lignes. Pour isoler ces trois lignes, Accueil > Réduire les lignes > Conserver les lignes pour renseigner le nombre de lignes souhaité. Nous pouvons également agir par clic droit sur l'icone de table, à gauche des colonnes de la requête. Nous remarquons au passage la formule utilisée, qui va nous intéresser pour la suite.

= Table.Sort(#"Lignes groupées",{{"Qtés", Order.Descending}})
= Table.FirstN(TrierQtés,3)
Nom : 2020-11-23_091316.png
Affichages : 33
Taille : 4,4 Ko


5. Il suffit de retourner le tableau dans Excel pour obtenir notre podium. Mission accomplie.



Et si on veut faire varier le TOP3 en TOP 5, Top 10... Bref, établir le Top X de nos articles...

Vous l'aurez compris, l'idée ici est de modifier le 3 aperçu dans la barre de formule lorsque nous avons récupéré les 3 premières lignes triées. Mais cette solution impose d'ouvrir Power Query et de savoir à quelle étape le top x a été établi. Il serait probablement plus intéressant de pouvoir déterminer cela dans le classeur Excel, par exemple au dessus du tableau de résultat récupéré de Power Query.

Nom : 2020-11-23_094708.png
Affichages : 32
Taille : 2,5 Ko


Pour réaliser cela, nous allons nommer la plage B1, par exemple TopNbreLignes, puis, avec B1 sélectionnée, nous allons intégrer cette donnée dans la solution Power Query (2). Il va falloir maintenant pouvoir récupérer la valeur de cette requête "singleton" pour modifier notre dernière étape du classement. Pour récupérer cette valeur, nous disposons d'une "formule" Power Query qui permet de récupérer la première valeur de la première ligne d'une table: = Table.FirstValue(TopNbreLignes)
Nom : 2020-11-23_092411.png
Affichages : 37
Taille : 1,7 Ko


Il va suffire maintenant d'utiliser cette formule pour remplacer le 3 de l'étape d'établissement du Top3.
= Table.FirstN(#"Lignes groupées",Table.FirstValue(TopNbreLignes))

Nous pouvons maintenant faire varier le nombre de lignes de notre Top X en modifiant la valeur de B1 puis en actualisant la requête (3).

Nom : 2020-11-23_093928.png
Affichages : 33
Taille : 3,0 Ko





(1) Cette modification du type de la colonne peut s'effectuer soit par l'ajout d'une étape (clic droit sur l'intitulé de la colonne puis Modifier le type > Date) mais il est possible également de modifier l'étape existante en corrigeant la formule dans la barre de formules. Il est toujours intéressant, à chaque étape de Power Query, de lire la formule réalisant l'étape.

(2) Power Query permet d'intégrer des tableaux structurés, des plages nommées ou des feuilles de calcul.

(3) Cette opération pourrait être réalisée automatiquement lors de la modification de la valeur de la cellule avec une ligne de code VBA.


Ca vous parle, Power Query?
Envisagez-vous de travailler avec Power Query?
Voulez-vous d'autres billets sur cet outil fabuleux?

Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Viadeo Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Twitter Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Google Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Facebook Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Digg Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Delicious Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog MySpace Envoyer le billet « Power Query: Etablir le TOP X d'une série de données (interface graphique) » dans le blog Yahoo

Commentaires

  1. Avatar de Pierre Dumas
    • |
    • permalink
    Bonjour Pierre

    Ce billet de blog est pertinent et illustre bien une possibilité parmi d'autres de PowerQuery.

    Afin de l'améliorer, il pourrait être opportun de préciser un peu comment on fait pour intégrer une plage nommée dans PowerQuery. Cele ne me parait pas très "instinctif" pour un débutant.
    Enfin, il me semble qu'il y a une coquille, certainement à cause d'un copier-coller oublié, dans la dernière ligne de code inscrite :
    = Table.FirstN(TrierQtés,Table.FirstValue(TopNbreLignes))
    au lieu de :
    = Table.FirstN(#"Lignes groupées",Table.FirstValue(TopNbreLignes))
    Bonne journée

    Pierre Dumas
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonjour Pierre,

    Merci pour ton appréciation


    Citation Envoyé par Pierre Dumas
    [...]
    Afin de l'améliorer, il pourrait être opportun de préciser un peu comment on fait pour intégrer une plage nommée dans PowerQuery[...]
    Je viens de détailler cela dans ce billet de blog. J'espère qu'il permettra de clarifier ce point, puisque Power Query se comporte de manière différente selon le contenu de la plage nommée...