oguruma
XLDnaute Occasionnel
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 :
(
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 :
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.....
(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
(
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)
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.....