PowerQuery :: FFLIST_FilterFromList :: Extraction personnalisée à partir d'une liste de valeurs

[oguruma]

Ce post composé d'un ensemble d'astuces PowerQuery permettant de filtrer un tableau structuré.
(de bons exemples à découvrir pour ceux et celles qui souhaitent franchir le pas de PowerQuery - le code reste assez simple à appréhender)

Cette extraction est pilotée par une fonction dans laquelle on passe en paramètres :

• Un tableau structuré contenant les données
• La colonne dans le tableau structuré à filtrer
• Un tableau structuré dans lequel se trouvent les données à filtrer (à sélectionner)
• Un tableau structuré dans lequel se trouvent les colonnes qui seront produites dans le tableau résultat
• Une option de tri permettant de stipuler si on souhaite trier la colonne filtrée dans le tableau résultat

La fonction attend donc les paramètres ci-dessous :
(
pSrcData as any,
pFilterField as text,
pTableFilterList as any,
pTableListColumns as any,
pTriOpt as text
) =>

Les tableaux filtres et champs seront constitués d'une seule colonne. Le titre des colonnes n'a aucun impact car la fonction les détecte automatiquement et impose ses propres noms arbitraires afin de toujours traiter les mêmes noms de colonnes.

Exemple

Le renommage des champs afin d'imposer les noms de colonnes pour la suite des traitements est calculé comme suit :
// On affecte un champ de travail fixe arbitraire
TmpRenameSourceFilterList = Table.RenameColumns(SourceFilterList,{{ColumnListName,"µ9FieldFilter9µ"}}),

µ9FieldFilter9µ ==> très peu de chance que l'on trouve un nom de colonne comme celui-ci

pSrcData as any,
pTableFilterList as any,
pTableListColumns as any,
Les paramètres concernant les tableaux structurés peuvent être soit :

• le nom du tableau structuré dans la feuille Excel (donc forme texte)
• le nom du tableau importé dans PowerQuery (donc au format table)

Cette distinction est gérée comme suit - exemples
SourceFilterList = if pTableFilterList is text then Excel.CurrentWorkbook(){[Name=TableFilterList]}[Content] else pTableFilterList
SourceColumnList = if pTableListColumns is text then Excel.CurrentWorkbook(){[Name=TableListColumns]}[Content] else pTableListColumns
SourceData = if pSrcData is text then Excel.CurrentWorkbook(){[Name=SrcData]}[Content] else pSrcData

(d'où la nécessité du typage any dans le passage des paramètres)

Le filtrage après renommage des champs est tout simplement réalisé par cette ligne :
FilteredRows = Table.SelectRows(SortData, each List.Contains(SortFilters[µ9FieldFilter9µ], [µ9FielItemd9µ])),

Les colonnes à retenir sont évalués comme ceci :
ListColumnsKeep = TmpSourceColumnList[µ9FieldList9µ],

puis en fin de traitement on applique cette rétention
RemovedOtherColumns = Table.SelectColumns(TriOptionnel,ListColumnsKeep)

Avant de terminer on vérifie si un tri est nécessaire
TriOptionnel=if TriOpt="O" then Table.Sort(TmpRename_2,{{FilterField, Order.Ascending}}) else TmpRename_2,

Exemples d'utilisation - cas d'un passage des noms de TS au format texte
let

Source = fnFilterFromList("TB_EQUIP","ComLib","TB_FILTRE","TB_CHAMPS","o")
in
Source

Exemples d'utilisation - cas d'un passage des noms de TS au format table PowerQuery
let

Source = fnFilterFromList(TB_EQUIP,"ComLib",TB_FILTRE,TB_CHAMPS,"o")
in
Source

Organisation sous PowerQuery

La fonction dans son intégralité

let fnFilterFromList =
(
pSrcData as any,
pFilterField as text,
pTableFilterList as any,
pTableListColumns as any,
pTriOpt as text
) =>
let
SrcData=pSrcData,
FilterField=pFilterField,
TableFilterList=pTableFilterList,
TableListColumns=pTableListColumns,
TriOpt=Text.Upper(pTriOpt),

// Table des filtres
SourceFilterList = if pTableFilterList is text then Excel.CurrentWorkbook(){[Name=TableFilterList]}[Content] else pTableFilterList,
ColumnListName=Table.ColumnNames(SourceFilterList){0},

// On affecte un champ de travail fixe arbitraire
TmpRenameSourceFilterList = Table.RenameColumns(SourceFilterList,{{ColumnListName,"µ9FieldFilter9µ"}}),
SortFilters = Table.Sort(TmpRenameSourceFilterList,{{"µ9FieldFilter9µ", Order.Ascending}}),

// Table des colonnes à conserver
SourceColumnList = if pTableListColumns is text then Excel.CurrentWorkbook(){[Name=TableListColumns]}[Content] else pTableListColumns ,
ColumnSourceListName=Table.ColumnNames(SourceColumnList){0},

// On affecte aussi un champ de travail fixe
TmpSourceColumnList = Table.RenameColumns(SourceColumnList,{{ColumnSourceListName,"µ9FieldList9µ"}}),
ListColumnsKeep = TmpSourceColumnList[µ9FieldList9µ],

// Source de données à traiter
SourceData = if pSrcData is text then Excel.CurrentWorkbook(){[Name=SrcData]}[Content] else pSrcData,

// Rename temporaire afin de définir un champ de travail fixe, on se simplifie la vie par rapport à l'utilisation de Expression.Evaluate
TmpRename_1 = Table.RenameColumns(SourceData,{{FilterField, "µ9FielItemd9µ"}}),
SortData = Table.Sort(TmpRename_1,{{"µ9FielItemd9µ", Order.Ascending}}),

// Applique les filtres
FilteredRows = Table.SelectRows(SortData, each List.Contains(SortFilters[µ9FieldFilter9µ], [µ9FielItemd9µ])),

// On replace le nom d'origine
TmpRename_2 = Table.RenameColumns(FilteredRows,{{"µ9FielItemd9µ",FilterField}}),

// Tri optionnel
TriOptionnel=if TriOpt="O" then Table.Sort(TmpRename_2,{{FilterField, Order.Ascending}}) else TmpRename_2,

// Elimine les colonnes non retenues
RemovedOtherColumns = Table.SelectColumns(TriOptionnel,ListColumnsKeep)
in
RemovedOtherColumns
in
fnFilterFromList

Mise en garde : Cette fonction pourrait être assez consommatrice en temps de traitement si le tableau de données à traiter est volumineux (plus de 1000 lignes). Ceci est lié au filtrage des champs un à un (via List.Contains). Une optimisation est à l'étude.....

 

[oguruma]

Comme annoncé en conclusion du post initial.... difficile d'en rester-là sur les aspects de rapidité d'exécution.
En creusant un peu... bon c'est un peu tiré par les Tifs...

Il y a en fait une relation un peu comme un Vlookup ou Xlookup entre la table des données et la table des données à retenir puisqu'en fait on pointe sur la même colonne.
Donc pourquoi pas tenter une relation entre ces deux tables de manière à ne conserver que les croisements d'enregistrements réalisés par égalité entre la source et la cible.

Pour les connaisseurs on va faire appel à un RightOuter en PowerQuery.

Surprenant mais....
Test effectué avec 3000 lignes dans le fichier joint : le résultat est sans appel !
Voir la requête RQ_TST (3) --> (version 1.1)
La requête RQ_TST (2) --> (version 1.0) pointe sur le tableau de 3000 lignes et c'est bien plus long.
La nouvelle fonction se nomme : fnFilterFromListOuterJoin

Je vous laisse tester et découvrir le code. Sa conception repose sur les mêmes principes que la v1.0

let fnFilterFromListOuterJoin =
(
pSrcData as any,
pFilterField as text,
pTableFilterList as any,
pTableListColumns as any,
pTriOpt as text
) =>

let
SrcData=pSrcData,
FilterField=pFilterField,
TableFilterList=pTableFilterList,
TableListColumns=pTableListColumns,
TriOpt=pTriOpt,

// Source de données à traiter
SourceData = if SrcData is text then Excel.CurrentWorkbook(){[Name=SrcData]}[Content] else SrcData,
TmpRename_1 = Table.RenameColumns(SourceData,{{FilterField, "µ9FielItemd9µ"}}),

// Table des filtres
SourceFilterList = if TableFilterList is text then Excel.CurrentWorkbook(){[Name=TableFilterList]}[Content] else TableFilterList,
ColumnListName=Table.ColumnNames(SourceFilterList){0},
// On affecte un champ de travail fixe arbitraire
TmpRenameSourceFilterList = Table.RenameColumns(SourceFilterList,{{ColumnListName,"µ9FieldFilter9µ"}}),
TmpFilterName=Table.ColumnNames(TmpRenameSourceFilterList){0},

// Table des colonnes à conserver
SourceColumnList = if TableListColumns is text then Excel.CurrentWorkbook(){[Name=TableListColumns]}[Content] else TableListColumns ,
ColumnSourceListName=Table.ColumnNames(SourceColumnList){0},
TmpSourceColumnList = Table.RenameColumns(SourceColumnList,{{ColumnSourceListName,"µ9FieldList9µ"}}),
ListColumnsKeep = TmpSourceColumnList[µ9FieldList9µ],

// Filtrage en appliquant une jointure
MergedQueries = Table.NestedJoin(TmpRename_1, {"µ9FielItemd9µ"}, TmpRenameSourceFilterList, {TmpFilterName}, "µ99FilterName99µ", JoinKind.RightOuter),

// Suppression de la colonne table à déplier est inutile
RemovedColumns = Table.RemoveColumns(MergedQueries,{"µ99FilterName99µ"}),

// On replace le nom d'origine
TmpRename_2 = Table.RenameColumns(RemovedColumns,{{"µ9FielItemd9µ",FilterField}}),

// Tri optionnel
TriOptionnel=if TriOpt="O" then Table.Sort(TmpRename_2,{{FilterField, Order.Ascending}}) else TmpRename_2,

// Elimine les colonnes non retenues
RemovedOtherColumns = Table.SelectColumns(TriOptionnel,ListColumnsKeep)

in
RemovedOtherColumns

in
fnFilterFromListOuterJoin

 

[Cousinhub]

Bonjour,
Toujours dans un souci de simplification (et de rapidité...)
Tout ceci peut être résumé à ces deux lignes :

let
    Source = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="TB_EQUIP2"]}[Content],List.Distinct(TB_CHAMPS[CHAMP])),
    Filtr = Table.SelectRows(Source, each  List.Contains(List.Distinct(TB_FILTRE[FILTRE]),[ComLib]))
in
    Filtr

Pour info, dans ton fichier, avec ton code, 1.6 seconde, le mien 0.6
Pourquoi toujours vouloir insérer des fonctions, quand le besoin n'est pas forcément avéré?

 

[oguruma]

Bonjour,
Toujours dans un souci de simplification (et de rapidité...)
Tout ceci peut être résumé à ces deux lignes :

let
    Source = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="TB_EQUIP2"]}[Content],List.Distinct(TB_CHAMPS[CHAMP])),
    Filtr = Table.SelectRows(Source, each  List.Contains(List.Distinct(TB_FILTRE[FILTRE]),[ComLib]))
in
    Filtr

Pour info, dans ton fichier, avec ton code, 1.6 seconde, le mien 0.6
Pourquoi toujours vouloir insérer des fonctions, quand le besoin n'est pas forcément avéré?

Bonjour,
Oui écrit comme cela on gagne des étapes. Mais dans ta solution tout est "dur". Ma fonction certes un peu plus longue permet de gérer n'importe quelle table de données, champs, etc....
Ma fonction nécessite en effet un passage de paramètres. En revanche quand c'est en dur c'est une intervention qu'il faut faire dans le code pour adapter la table source de données et la table comportant les champs....
Choix donc en effet choix à arbitrer entre la souplesse, la rapidité, et la difficulté de coder en PQ.
Donc ça ce discute
A partir de ta solution il est aussi possible d'en construire une fonction avec Expression.Evaluate afin de gérer les nom de champs et autres afin qu'ils soient variables. C'est en effet faisable également.
Allez comme tu es à la source d'une évolution je te laisse ce privilège de cette optimisation

 

[Cousinhub]

Re-,
Pour moi, vu que les TS "TB_EQUIP2", "TB_CHAMP" et "TB_FILTRE" sont définis dans un onglet, le seul argument à insérer est la colonne "ComLib" qui sert de colonne de filtre.
Et que ce soit dans ta fonction ou dans mon code, il faudra entrer cet argument "à la mimine"...