Voir le flux RSS

Pierre Fauconnier

Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît!

Note : 2 votes pour une moyenne de 5,00.
par , 18/09/2019 à 06h23 (2315 Affichages)
Salut.

Je vois régulièrement passer sur le forum des demandes pour extraire des données d'une table en fonction d'une condition. Il existe depuis très longtemps les filtres avancés sur Excel pour réaliser la manipulation. Mais il faut avouer que l'outil a vieilli, notamment parce qu'il ne prend pas en compte les tables de données ou tableaux structurés.

Lorsque je dis qu'il a vieilli, c'est que l'ergonomie est loin d'être au top, que l'extraction étant régulièrement réalisée vers une autre feuille, il ne faut pas se louper dans la manœuvre et que le résultat de l'extraction n'est pas déposé dans une table de données, mais dans une plage classique. De plus, tout ou presque est à refaire lorsque les données de la source ont été modifiées:
  • condition qui change;
  • ajout, modification ou suppression de données.


EUREKA!! Si vous utilisez une version au moins égale à XL2010, Power Query offre une solution légère, rapide et DYNAMIQUE sans aucune ligne de VBA, sans nécessiter la présence d'une zone de critères, sans besoin de relancer "toute" la mécanique du filtre avancé...

Considérons la table suivante reprenant des données de vente, nommée t_Ventes.

Nom : 2019-09-17_211137.png
Affichages : 1868
Taille : 33,1 Ko



On souhaite extraire de cette table les données concernant la Belgique dans une table qui sera créée sur une autre feuille. On souhaite en plus placer la colonne Pays en début de table et ne pas reprendre la colonne de la zone. On souhaite également que les conditions d'extraction puissent être modifiées par la suite. Bref, du travail pour le filtre avancé... mais aussi et surtout pour Power Query.


On va d'abord ajouter une colonne formulée en exprimant la condition souhaitée (ici, [Pays] = "Belgique")

Nom : 2019-09-17_212409.png
Affichages : 1845
Taille : 19,9 Ko



En se plaçant dans la table, on crée la requête Power Query via l'onglet Données

Nom : 2019-09-17_212507.png
Affichages : 1868
Taille : 36,8 Ko


Power Query s'ouvre et la requête est affichée. Ici comme pour les autres étapes, ne vous préoccupez pas de ce que vous voyez apparaître dans la barre de formule de Power Query. C'est le script enregistré lors de votre manipulation (une sorte d'enregistreur de macro à la sauce Power Query).

Nom : 2019-09-17_212708.png
Affichages : 1824
Taille : 58,5 Ko


Avant toute modification de la table, on remarque que la colonne Date est passée en DateTime et on la remet en Date. Ici comme partout avec Office, le clic droit nous conduit où il faut...

Nous allons filtrer la table comme nous le ferions dans Excel, en choisissant True dans la colonne Condition (Eh oui, Power Query parle anglais). Ce filtre se place comme dans Excel, en cliquant sur l'outil de filtre placé sur les intitulés de colonnes (ici aussi, on remarque au passage la ligne de script Power Query sur la barre de formule)... On en profite pour modifier le nom de la requête par clic droit sur la requête dans le panneau de gauche...

Nom : 2019-09-17_213117.png
Affichages : 1823
Taille : 15,7 Ko


A ce stade, nous avons presque fini notre travail... Nous devons encore réorganiser les colonnes et supprimer les deux que nous ne souhaitons pas récupérer (Zone et Condition). Déplacer une colonne s'effectue par simple glisser-déplacer, la suppression des deux colonnes s'effectuant par clic droit.

Nom : 2019-09-17_213453.png
Affichages : 1822
Taille : 19,5 Ko



Il nous reste à renvoyer le résultat de la requête dans Excel, ce qui s'effectue par le bouton Fermer et Charger. Ici, pour les besoins du billet, je vais effectuer la manipulation en choisissant Fermer et charger dans... tout à gauche du bouton d'accueil, et placer le résultat sur la même feuille que celle des données...

Nom : 2019-09-17_213814.png
Affichages : 1823
Taille : 52,4 Ko

Nom : 2019-09-17_213837.png
Affichages : 1821
Taille : 43,0 Ko



Et voilà le travail. Et après, si la condition est modifiée, que faut-il faire? Pas grand-chose, en fait:
  • changer la formule de la condition;
  • rafraîchir la requête.




Si je veux les ventes du Bénélux supérieures à 20.000 euros, j'adapte la formule de la façon suivante puis j'actualise ma requête (la table verte) par clic droit... Je procéderai aussi en actualisant la table de résultat si j'ai modifié, ajouté ou supprimé des données. De surcroît, Power Query permet que les données de la source ne soient pas dans le même classeur. Elles peuvent même être d'une autre nature qu'Excel (Access, Sql, csv, ...) car Power Query est un puissant intégrateur de données permettant de les préparer pour l'analyse avec notre outil préféré...

Nom : 2019-09-17_214119.png
Affichages : 1813
Taille : 22,2 Ko

Nom : 2019-09-17_214157.png
Affichages : 1824
Taille : 57,5 Ko



Conclusions


Power Query est un outil puissant aux ressources multiples. Il est l'allié idéal des tables de données (tableaux structurés) pour plus d'efficacité à moindre coût avec Excel...

Et vous, vous connaissiez cette technique?
Vous avez essayé cette technique? Partagez votre expérience en commentaires à ce billet
Envie d'en savoir plus sur Power Query?

Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Viadeo Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Twitter Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Google Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Facebook Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Digg Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Delicious Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog MySpace Envoyer le billet « Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît! » dans le blog Yahoo

Commentaires

  1. Avatar de 78chris
    • |
    • permalink
    Bonjour

    Bonne idée ce billet. Pour compléter :

    On peut aussi utiliser une cellule nommée Pays contenant Belgique ou Benelux ou... (avec si on le souhaite une liste déroulante des pays) et récupérer le contenu de cette cellule comme variable.
    Cette variable est utilisée pour filtrer dans PowerQuery

    Créer une requête vide nommée Pays et dans la barre de formule de PowerQuery taper
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Pays"]}[Content])
    Filtrer la requête issue du tableau t_Ventes avec une des valeurs et dans la barre de Formule remplacer cette valeur (y compris les "") par le nom de variable Pays

    On garde le même dynamisme en évitant une colonne...
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Chris

    Merci d'être passé par ici!


    Citation Envoyé par 78chris
    [...]

    On garde le même dynamisme en évitant une colonne...
    Oui, mais on retrouve alors la notion de la "zone de critères" du filtre élaboré. Cela dit, je l'ai dans mon exemple simplement déplacée au sein même du tableau...

    L'astuce de la plage nommée est intéressante, mais elle fige la notion du critère à un pays donné. L'idée de la colonne formulée renvoyant VRAI ou FAUX permet de formuler n'importe quel critère, comme l'illustre l'étape où le critère porte sur la zone et le montant et non plus seulement sur le pays.

    Dans les faits, je suis néanmoins d'accord avec toi pour dire que l'extraction est rarement faite "à la volée"...
  3. Avatar de Pierre Fauconnier
    • |
    • permalink
    Citation Envoyé par 78chris
    [...]
    Créer une requête vide nommée Pays et dans la barre de formule de PowerQuery taper
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Pays"]}[Content])
    Filtrer la requête issue du tableau t_Ventes avec une des valeurs et dans la barre de Formule remplacer cette valeur (y compris les "") par le nom de variable Pays[...]
    Pour compléter ma réponse précédente, dans le cas où je voudrais filtrer uniquement sur un pays (et non comme, dans ma solution initiale, sur un critère quel qu'il soit), je préfèrerais alors utiliser une table ne contenant qu'une donnée plutôt qu'une plage nommée. Il suffirait alors de monter la table dans Power Query puis de fusionner avec la table principale en inner join... Ca éviterait la manipulation que tu décris sur le filtre dans Power Query car je peux alors utiliser les outils PQ prévus pour, et ça permettrait de renseigner éventuellement plusieurs pays dans la table de critères. Tant qu'à faire, qui peut le plus peut le moins.