Pierre Fauconnier

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:




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.





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.





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 ⁽¹⁾. 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}})



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)



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.




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)



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 ⁽³⁾.







⁽¹⁾ 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.

⁽²⁾ Power Query permet d'intégrer des tableaux structurés, des plages nommées ou des feuilles de calcul.

⁽³⁾ 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?