Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

oguruma

XLDnaute Occasionnel
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
 

Pièces jointes

  • Consolider2M_V3.014.xlsm
    241.2 KB · Affichages: 0
  • fr-en-liste-diplomes-professionnels.txt
    102.2 KB · Affichages: 0
Dernière édition:

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…