IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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 à 05h23 (7191 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 : 4725
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 : 4578
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 : 4619
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 : 4555
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 : 4537
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 : 4566
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 : 4547
Taille : 52,4 Ko

Nom : 2019-09-17_213837.png
Affichages : 4570
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 : 4541
Taille : 22,2 Ko

Nom : 2019-09-17_214157.png
Affichages : 4555
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.

  4. Avatar de elclor
    • |
    • permalink
    Bonjour
    Je ne connais rien du PQ mais tu m'as donné envie de commencer et pour cela j'essaye de refaire ton exemple.
    Mais dès le début en ajoutant une colonne avec la condition identique à la tienne=[@PAYS]="AU" je reçois le message d'erreur suivant : la syntaxe de ce nom est incorrecte et ce qui est dans les crochets est mis en surbrillance. Une colonne PAYS existe bien dans ma BD et AU comme valeur d'une cellule de cette colonne.
    Merci de ton aide
    elclor
  5. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonjour.

    Comme tu as aussi posé la question sur le forum, j'ai répondu dans cette discussion, notamment parce que je ne comprends pas bien le contexte dans lequel tu évolues... N'hésite pas à réagir dans la discussion...
  6. Avatar de duplums
    • |
    • permalink
    Bonjour,
    D'abord grand merci pour vos nombreux travaux souvent très intéressants et vos grandes qualités de pédagogue !
    Je suis un vieil "amateur éclairé" d'Excel et de VBA et je suis avec intérêt les évolutions actuelles d'Excel et VBA. Je travaille sous Windows 7 (hé oui !) avec la dernière version de Microsoft 365.
    Je constate les rapides évolutions et j'avoue être parfois dépassé par les orientations récentes et actuelles prises par Microsoft. Par exemple, j'ai mis du temps à comprendre que Power Query et Power Pivot sont deux choses différentes, et je ne parle pas de l'apparition récente de Power Apps et de la mise à disposition gratuite d'Office en version allégée ...
    J'ai bien noté aussi que vous vous désoliez du peu d'utilisation d'une technique vieille de 13 ans....

    Dans ces conditions, je trouverais déjà utile que vous actualisiez votre billet (qui n'a pourtant guère qu'un an d'âge !).
    Mais j'aimerais bien aussi que vous donniez (dans un autre billet ou sur le forum ?) votre jugement d'expert sur toutes ces évolutions concernant Office et Excel et d'apporter des réponses à des questions telles que :
    - est-il réaliste de laisser penser que ces nouvelles évolutions ne nécessitent soi-disant pas de développement VBA (sous-entendu de personnels spécialisés ?) sous prétexte que "tout peut se faire à la main" ?
    - quand on constate le grand décalage entre la publication de nouvelles fonctionnalités et leur utilisation réelle, ne peut-on penser que l'on va ajouter un peu plus de confusion dans les esprits avec la débauche actuelle de nouveautés ?
    - quid des performances par rapport aux solutions actuellement utilisées ?
    - nécessité d'évolutions des matériels et logiciels de connexion, de sécurité, de connexion au "cloud", ...

    Merci d'avance de votre intérêt aux questions que je pose !
    YD
  7. Avatar de canou974
    • |
    • permalink
    Bonjour,
    Merci pour vos réponses, elles me font progresser dans mon raisonnement. Pour préciser cela, ce que je voudrais faire, c'est charger les deux tables(les données, les données pour le filtre) dans power query et ensuite trouver le code en langage M, pour filtrer la 1ère table à l'aide de la seconde(comprenant dix lignes au moins), sans repasser par Excel et que toujours dans la 1ère table, il y ait une 1 étape appliquée supplémentaire avec le résultat du filtre. En fait, je voudrais récupérer tout ce qui n'est pas filtré. Je fais cela pour conserver la dynamique dans une succession de traitements comprenant plusieurs fichiers, sans devoir retourner dans Excel, si je peux m'exprimer ainsi.
    La 2ème table(celle pour le filtre comporte plusieurs colonnes et lignes):
    - Métiers
    - date de début , critère > date de début
    - date de fin, critère < date de fin
    - Jour
    - Qté de produits vendus-objectif
    Pour finir, je n'arrive pas à écrire le code en langage M, permettant de tenir compte des conditions avec l'opérateur "ET", je bute notamment sur la reconnaissance du nom de l'autre table(celle contenant les filtres) dans la première en utilisant un filtre basique.
    En vous remerciant pour tout concours.
  8. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonjour

    Je ne suis pas certain d'avoir compris votre besoin. Je crois comprendre qu'il s'agit d'un problème de jointure pour exclure les données qui sont dans le tableau de filtre. Il faut pour cela choisir la jointure "Gauche opposée" qui reprend les lignes de la table de gauche qui ne sont pas dans celle de droite.

    Désolé, mais sur ce système stupide de blogs, on ne sais pas mettre une image ou une réponse en commentaire. C'est d'un pénible!

    Mon billet sur les jointures pourrait vous être utile
    Mis à jour 19/02/2021 à 06h32 par Pierre Fauconnier