[EXCEL-POWERQUERY] :: Simuler la fonction FILTRE (simplifiée) via une requête POWERQUERY pour les versions Excel antérieures à Office 365

[oguruma]

Bonjour le Forum,

La fonction FILTRE est apparue sauf erreur à la sortie de Office 365.
Il est donc intéressant de reproduire cette fonction pour les utilisateurs qui sont restés par exemple en version 2016 ou qui ne possèdent pas cette fonction.
Pour cela nous disposons d'un merveilleux outil à ne plus mettre de côté POWERQUERY directement intégré dans la version Excel 2016.
Pour les versions antérieures à 2016 (2013, 2010) PowerQuery existait sous forme de d'add-on à installer avec ses défauts de jeunesse. Oublions !

Le but est de reproduire ceci
A partir d'un tableau de données comme celui-ci

Construire un onglet permettant d'effectuer des recherches filtrées comme le ferait la fonction FILTRE.

On renseigne la colonne à filtrer

puis la valeur recherchée

et la recherche est lancée automatiquement.

Voici donc le montage à effectuer pas à pas.

1. Un tableau de données structuré : T_BDD_VENTES dans notre exemple

2. Une feuille de paramètre appelée ici Param (avec les commentaires dans le cadre de l'exemple)

Ces paramètres seront nécessaires à la construction de la requête PowerQuery.

3. Préparation paramètres pour la fontion FILTRE simulée (Colonne filtrée, valeur cherchée)

Nous avons les deux champs nommés comme ceux-ci. Llibre à vous de choisir d'autres noms mais dans ce cas il faudra adapter le code M qui sera détaillé ci-dessous.

4. La partie POWERQUERY
Elle est composée des éléments suivants :

La requête T_FILTRE simule la fonction FILTRE de Excel.

Voici le code

let

    //******************************************************************
    //  On récupère les paramètres
    //******************************************************************
    STR_Table=fnGetName("NOM_TABLE"),
    STR_TableAff=fnGetName("NOM_TABLE_AFF"), 
    STR_Col=fnGetName("NOM_COL_FILTRE"),
    STR_Val=fnGetName("VALEUR_FILTRE"),   

    //******************************************************************
    //  On récupère la table à filtrer
    //******************************************************************
    Source = Excel.CurrentWorkbook(){[Name=STR_Table]}[Content],     
    
    //******************************************************************
    //  On construit la liste des colonnes à afficher
    //******************************************************************
    LST_COL_AFFICHE=Table.ToList(fnGetList(STR_TableAff)),

    //*****************************************************************************************
    //  On va construire une table vide si aucune valeur
    // pour éviter le plantage erreur PowerQuery si la valeur recherchée n'est pas renseignée
    //*****************************************************************************************
    TB_NULL= #table(LST_COL_AFFICHE,{}),   
    
    //******************************************************************
    //  On pose le filtre
    //  avec la précaution si la valeur recherchée n'est pas renseignée
    //******************************************************************
    TB_FILTRE = if STR_Val is null then TB_NULL else fnFILTREV2016(Source, STR_Col, STR_Val),

    //******************************************************************
    //  On sélectionne les colonnes à afficher
    // Idem même précaution si la valeur recherchée n'est pas renseignée
    //******************************************************************
    TB_SELECT_COLUMN = if STR_Val is null then TB_NULL else Table.SelectColumns(TB_FILTRE,LST_COL_AFFICHE)
in
   TB_SELECT_COLUMN

Elle fait appel aux fonction suivantes

let fnFILTREV2016= (pTable as table, pCol as text, pValue as any) as table =>

//**********************************************************************
// Cette fonction permet de simuler la fonction FILTRE de Excel
// Ici l'opérateur est EGAL pour simmplifier les choses
//**********************************************************************

        let

        //**************************************************************
        // On récupèer les paramètres
        //**************************************************************
            Source = pTable,
            STR_COL_SELECT=pCol,
            STR_COL_VALUE=pValue,

        //**************************************************************
        // Les colonnes à afficher sont transformées en liste
        //**************************************************************
            LST_COL_SELECT= {STR_COL_SELECT},
        
        //**************************************************************
        // On récupère les colonnes de la table des données
        //**************************************************************
            LST_COLUMNS=Table.ColumnNames(Source),

        //******************************************************************
        // Par sécurité on fait l'intersection avec les colonnes à afficher
        //******************************************************************
            LST_INTERSECT_FILTRE=List.Intersect({LST_COLUMNS,LST_COL_SELECT}),
            STR_COL_FILTRE=if List.IsEmpty(LST_INTERSECT_FILTRE) then LST_COLUMNS{0} else LST_INTERSECT_FILTRE{0},

        //**************************************************************
        // On règle le cas où on filtre sur une valeur numérique
        //**************************************************************
            convertType= if STR_COL_VALUE is text then """" & Text.From(STR_COL_VALUE) & """" else Text.From(Number.From(STR_COL_VALUE)) ,

        //**************************************************************
        // Construction du filtre avec un each indirect
        //**************************************************************
            STR_EVAL="each ["& STR_COL_FILTRE & "] = " & convertType,           

        //**************************************************************
        // Expression.Evaluate c'est un peu le INDIRECT de Excel
        //**************************************************************
            EVAL_FILTRE=Expression.Evaluate(STR_EVAL),   

        //**************************************************************
        // Et on renvoie la table filtrée
        //**************************************************************
            TB_FILTRE = Table.SelectRows(Source, EVAL_FILTRE)
        in
            TB_FILTRE
in 
    fnFILTREV2016
    
    
let
    fnGetName = (pName as text) as any =>
        let
            Source = Excel.CurrentWorkbook(){[Name=pName]}[Content],           
            STR_Val = Source{0}[Column1]
        in
            STR_Val
in
    fnGetName
    
let 
    fnGetList = (pName as text) as any =>
        let
            Source = Excel.CurrentWorkbook(){[Name=pName]}[Content],
            LST_COL=Table.ColumnNames(Source),
            TBL_ChangTypeColumn1 = Table.TransformColumnTypes(Source,{{LST_COL{0}, type text}}),
            STR_Val = TBL_ChangTypeColumn1
        in
            STR_Val
in 
    fnGetList

fnGetList : est utilisée pour récupérer la liste des colonnes à afficher précisées dans le tableau des paramètres

fnGetName : Permet de récupérer un paramètre renseigné dans un champ nommé

Autre exemple avec une autre personnalisation des colonnes à afficher

5. Mise à jour automatique à l'issue du choix de la valeur recherchée
On passera par du code VBA en faisant appel aux événements de la feuille (onglet) T_FILTRE (Feuil1)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 If Not Application.Intersect(Target, Range("NOM_COL_FILTRE")) Is Nothing Then
     Range("VALEUR_FILTRE") = ""
     Exit Sub
 End If
 
 If Not Application.Intersect(Target, Range("VALEUR_FILTRE")) Is Nothing Then
     If Range("VALEUR_FILTRE") <> "" Then ActiveWorkbook.Connections("Requête - T_FILTRE").Refresh
 End If
End Sub

bien entendu adapter le champ nommé Range("NOM_COL_FILTRE") et le nom de la requête ActiveWorkbook.Connections("Requête - T_FILTRE").Refresh si vous envisagez d'autres noms