POWERQUERY :: Importer un fichier .csv avec construction de critères dynamiques - V3.0 - Table.NestedJoin

oguruma · 9 Juillet 2024

Bonjour,
ce post fait suite à celui-ci https://excel-downloads.com/threads...tion-entre-deux-dates-possible-v2-0.20083458/

Il démontre une autre manière de poser des filtres au moment de l'importation d'un fichier .csv et cela encore une fois sans aucune ligne de VBA.

Spécification des critères

Le tableau des critères reconnu par PowerQuery est celui-ci

Il est possible d'ajouter d'autres colonnes selon vos besoins mais 5 semblent être suffisants.
Cette ligne présente en fait une liste déroulante avec les colonnes connues dans le fichier .csv définie comme suit

Rappel la liste des colonnes est déterminée par le bouton donnant pour résultat

Pour ceux qui ont suivi "cette saga" vous remarquerez l'apparition d'une colonne "AFFICHAGE" qui va permettre de retenir les colonnes à afficher à l'issue de l'importation.
Cela est aussi géré en PowerQuery via une requête TB_LISTE_DES_COLONNES_AFFICHE

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="TB_LISTE_DES_COLONNES"]}[Content],
    TbUppercase = Table.TransformColumns(Source,{{"AFFICHAGE", Text.Upper, type text}}),
    TbFilter = Table.SelectRows(TbUppercase, each ([AFFICHAGE] <> null)),
    ListColumns=Table.ColumnNames(Source),
    NbRows=Table.RowCount(TbFilter),
    ListDisplay=if NbRows = 0
                then Table.Column(TbUppercase,ListColumns{0})
                else Table.Column(TbFilter,ListColumns{0})
in
     ListDisplay

Si aucune colonne n'est sélectionnée toutes les colonnes seront affichées :
NbRows=Table.RowCount(TbFilter), ==> cette ligne de code permet de vérifier si des colonnes ont été sélectionnées ; on peut les retenir en les marquant de n'importe quel signe. La ligne de filtre ne retient que les "non-null".
ListDisplay=if NbRows = 0
then Table.Column(TbUppercase,ListColumns{0})
else Table.Column(TbFilter,ListColumns{0})

Gestion des critères : TB_CRIT_COL
Les critères sont récupérés par cette requête. Je vous invite à bien lire ce code ainsi que les commentaires. La démarche est largement détaillée.

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="TB_CRIT_COL"]}[Content],
    ListColumns=Table.ColumnNames(Source),
 
    //***************************************************************************************************************
    // On regarde quelles sont les colonnes qui ont des critères et on élimine celles qui en ont pas
    //***************************************************************************************************************
    LoopToDeleteColumns=List.Accumulate(
            ListColumns,
            Source,
            (state,current)=>
                            let
                                t1=Table.Column(state,current),
                                t2=Table.FromList(t1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                                t3 = Table.SelectRows(t2, each ([Column1] <> null)),
                                t4=Table.RowCount(t3),
                                t99=if t4<=1 then Table.RemoveColumns(state,{current}) else state
                            in
                                t99
    ),
    //***************************************************************************************************************
    // Les noms de colonnes en 2ème ligne deviennent titre des colonnes pour les critères de jointure Inner.Join
    //***************************************************************************************************************
    Promote = Table.PromoteHeaders(LoopToDeleteColumns, [PromoteAllScalars=true]),
 
    NbrColumns=Table.ColumnCount(Promote),
    TbFilter=if NbrColumns<>0
             then
                let
                    //***************************************************************************************************************
                    // ON récupère le nom de la 1ère colonne
                    //***************************************************************************************************************
                    FirstColumn=Table.ColumnNames(Promote){0},

                    //***************************************************************************************************************
                    // On évalue son nom au sens PowerQuery
                    //***************************************************************************************************************
                    Str="each [" & FirstColumn & "] <> null",
                    EvStr=Expression.Evaluate(Str,#shared),

                    //***************************************************************************************************************
                    // On écarte les lignes non renseignées en fixant le remplissage de la 1ère colonne
                    //***************************************************************************************************************
                    LinesFilter = Table.SelectRows(Promote,  EvStr)
                in
                    LinesFilter
             else
                    Promote
in
    TbFilter

Et on obtient cette table

Vous remarquerez que les colonnes et les lignes vides ont été écartées.

PowerQuery:

let
    Source = TB_DATA_SOURCE,
    ListColumnsFilters=Table.ColumnNames(TB_CRIT_COL),
    MergedQueries = Table.NestedJoin(Source,ListColumnsFilters,TB_CRIT_COL,ListColumnsFilters,"ColumnsFilter",JoinKind.Inner),
    RemovedColumns = Table.RemoveColumns(MergedQueries,{"ColumnsFilter"},MissingField.Ignore),
    TbToDisplay = Table.SelectColumns(RemovedColumns,TB_LISTE_DES_COLONNES_AFFICHE),
    //*********************************************************************************************************************************
    // Protection si la table des critères est vide on renvoie la source complète
    //*********************************************************************************************************************************
    TbToDisplayFinal=if Table.RowCount(TB_CRIT_COL) <> 0 or Table.ColumnCount(TB_CRIT_COL) <> 0 then TbToDisplay else Source
in
    TbToDisplayFinal

La sélection passe tout simple par cette ligne après récupération des colonnes critères de la table TB_CRIT_COL
MergedQueries = Table.NestedJoin(Source,ListColumnsFilters,TB_CRIT_COL,ListColumnsFilters,"ColumnsFilter",JoinKind.Inner),

Voir les fichiers joints

Faire tout cela en VBA représente un sérieux paquet de lignes de code à maintenir

POWERQUERY :: Importer un fichier .csv avec construction de critères dynamiques - V3.0 - Table.NestedJoin

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires