oguruma
XLDnaute Impliqué
Bonjour Le Forum,
Ce post boucle la boucle à celui-ci.
Pour cela on complète le paramétrage comme suit avec l'ajout de deux paramètres :
APPLIQUER_FILTRES : qui va actionner la fonction chargée de filtrer les données
TABLE_FILTRES : Table comportant la description des filtres
La table des filtres se présente comme suit :
Les opérateurs sont
Remarque : la liste est composée de cette manière
Egal|=;Différent|<>;Commence par|Text.StartsWith;Termine par|Text.EndsWith;Contient|Text.Contains
La fonction va récupérer les opérateurs avec ce contenu composé de deux parties : opérateur syntaxe française, séparateur "|", opérateur syntaxe PowerQuery
Cette chaine est découpée en deux pour n'en retenir que l'opérateur syntaxe PowerQuery
Egal|=
Commence par|Text.StartsWith
Là encore le nom des colonnes de la table de filtres n'a aucune importance. Il faut juste respecter l'ordre des colonnes. On travaille sur leur position relative.
ATTENTION : Pour des raisons de simplicité le filtrage ne sera possible que sur des champs de type texte. Une prochaine version devrait traiter tous types de colonnes.
ENVIRONNEMENT POWERQUERY
RQ_IMPORT_DONNEES_01 : Requête qui a permis de construire en mode pas à pas la fonction fnImportMultipleFoldersFilters. En effet avant de rédiger le code d'une fonction il est intéressant de l'écrire en mode requête. Cela rend son débuggage plus facile. TB_FINALE c'est la requête résultat de la requête .RQ_IMPORT_DONNEES_01
RQ_IMPORT_DONNEES_03 : Requête appelant la fonction fnImportMultipleFoldersFilters
Voici son code
Et enfin le code de la fonction fnImportMultipleFoldersFilters
ICI en détail
Le code est largement documenté. Il est construit sous la même logique que les autres fonctions.
Juste un point particulier sur cette partie de code qui représente le moteur du filtrage
En effet on doit construire au départ le filtrage des données de cette manière quand on applique les signes "=" ou "<>"
StrEval="each Record.Field(_, " & """" & StrColumFilter & """" & ")" & " " & StrOperFilterPWQ & " " & """" & StrValueFilter & """",
pour ensuite l'évaluer et le compiler en code PowerQuery
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field]),
et dans le cas d'une fonction Text.*
StrEval="each " & StrOperFilterPWQ & "(" & "Record.Field(_," & """" & StrColumFilter & """" & "), " & """" & StrValueFilter & """" & ")",
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field,
Text.StartsWith=Text.StartsWith,
Text.EndsWith=Text.EndsWith,
Text.Contains=Text.Contains]),
Rappel : Expression.Evaluate c'est un peu l'équivalent de la fonction INDIRECT sous Excel
REMARQUE : Toutes lignes incomplètes dans la table des critères de filtrage seront ignorées
QUELQUES EXEMPLES
Ce post boucle la boucle à celui-ci.
POWERQUERY :: Combinaison de dossiers multiples non adjacents (non hiérarchiques) - Tables - Feuilles
A l'issue de la combinaison des différents dossiers il peut être intéressant et nécessaire de procéder à un filtrage des données à traiter.Pour cela on complète le paramétrage comme suit avec l'ajout de deux paramètres :
APPLIQUER_FILTRES : qui va actionner la fonction chargée de filtrer les données
TABLE_FILTRES : Table comportant la description des filtres
La table des filtres se présente comme suit :
Les opérateurs sont
Remarque : la liste est composée de cette manière
Egal|=;Différent|<>;Commence par|Text.StartsWith;Termine par|Text.EndsWith;Contient|Text.Contains
La fonction va récupérer les opérateurs avec ce contenu composé de deux parties : opérateur syntaxe française, séparateur "|", opérateur syntaxe PowerQuery
Cette chaine est découpée en deux pour n'en retenir que l'opérateur syntaxe PowerQuery
Egal|=
Commence par|Text.StartsWith
Là encore le nom des colonnes de la table de filtres n'a aucune importance. Il faut juste respecter l'ordre des colonnes. On travaille sur leur position relative.
ATTENTION : Pour des raisons de simplicité le filtrage ne sera possible que sur des champs de type texte. Une prochaine version devrait traiter tous types de colonnes.
ENVIRONNEMENT POWERQUERY
RQ_IMPORT_DONNEES_01 : Requête qui a permis de construire en mode pas à pas la fonction fnImportMultipleFoldersFilters. En effet avant de rédiger le code d'une fonction il est intéressant de l'écrire en mode requête. Cela rend son débuggage plus facile. TB_FINALE c'est la requête résultat de la requête .RQ_IMPORT_DONNEES_01
RQ_IMPORT_DONNEES_03 : Requête appelant la fonction fnImportMultipleFoldersFilters
Voici son code
PowerQuery:
let
//******************************************************************************
// Identification des paramètres
//******************************************************************************
STR_TYPE=fnGetParameter("TB_PARAMS", "TYPE"),
STR_PROMOTE=Text.Upper(fnGetParameter("TB_PARAMS", "PROMOTE")),
STR_TBL_COMBINE=fnGetParameter("TB_PARAMS", "TABLE_LIST_COMBINE"),
STR_TAG_PREM=fnGetParameter("TB_PARAMS", "TAG_PREMIERE_COLONNE"),
STR_TAG_COL_DOSSIER=fnGetParameter("TB_PARAMS", "TAG_COL_DOSSIER"),
STR_AJOUT_DU_CHEMIN_COMPLET=fnGetParameter("TB_PARAMS", "AJOUT_DU_CHEMIN_COMPLET"),
STR_APPLIQUER_FILTRES=fnGetParameter("TB_PARAMS", "APPLIQUER_FILTRES"),
STR_TABLE_FILERS=Text.Upper(fnGetParameter("TB_PARAMS", "TABLE_FILTRES")),
TBL=fnImportMultipleFoldersFilters(STR_TYPE, STR_PROMOTE, STR_TBL_COMBINE, STR_TAG_PREM, STR_TAG_COL_DOSSIER, STR_AJOUT_DU_CHEMIN_COMPLET, STR_APPLIQUER_FILTRES, STR_TABLE_FILERS)
in
TBLEt enfin le code de la fonction fnImportMultipleFoldersFilters
PowerQuery:
let fnImportMultipleFoldersFilters = (
pType as text,
pPromote as text,
pTableCombine as text,
pTag as text,
pFolder as text,
pPath as text,
pFilter as text,
pTableFilter as text
) =>
let
//******************************************************************************
// FONCTIONS SECTION
//******************************************************************************
//******************************************************************************
// Combinaison des dossiers
//******************************************************************************
COMBINE_FOLDERS = () as any =>
// Dans cette fonction on balayer la liste des dossiers à importer contenus dans TBL_COMBINE_FOLDERS_TO_RECORDS
let
//***********************************************************************************************
// On va construire la liste des fichiers à combiner
//***********************************************************************************************
ListAccumlateCombineFolders = List.Accumulate(
LST_COMBINE_FOLDERS_TO_RECORDS,
{}, //#table({},{}),
(state, current) =>
let
Folder=Record.Field(current,LIST_COLUMNS_TBL_COMBINE{0}), // On récupère le dossier en cours de lecture
// Si le nom du dossier n'est pas renseigné on passe au suivant
ListCombineFolders=if Folder <> null
then
let
TblSourceFolder=Folder.Files(Folder), // Table contenant la liste des fichiers dans le dossier
TblRemoveFields = Table.RemoveColumns(TblSourceFolder, {"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
// On récupère les noms des nouvelles colonnes qui seront ajoutées
CurrentTag=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{1}), // Tag fichier
CurrentItem=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{2}), // Item
CurrentFolder=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{3}), // Tag dossier
// Ajout des nouvelles colonnes
TblAddTagColumn=Table.AddColumn(TblRemoveFields, LIST_COLUMNS_TBL_COMBINE{1}, each CurrentTag), // Ajout colonne tag fichier
TblAddItemColumn=Table.AddColumn(TblAddTagColumn, LIST_COLUMNS_TBL_COMBINE{2}, each CurrentItem), // Ajout colonne Item
TblAddTagFolder=Table.AddColumn(TblAddItemColumn, LIST_COLUMNS_TBL_COMBINE{3}, each CurrentFolder), // Ajout colonne tag dossier
TblAddTagPath=if STR_AJOUT_DU_CHEMIN_COMPLET = "OUI" then Table.AddColumn(TblAddTagFolder, "CHEMIN_COMPLET", each Folder) else TblAddTagFolder,
// On construit la liste des fichiers qui seront combinés après exploration de tous les dossiers
ListFilesToRecords=Table.ToRecords(TblAddTagPath), // Liste des enregistrements - fichiers de la table
ListCombineRecords=List.Combine({state, ListFilesToRecords}) // Liste finale des fichiers à traiter et à combiner entre-eux
in
ListCombineRecords
else state // on passe au dossier suivant
in
ListCombineFolders
),
//***********************************************************************************************
// On va combiner la liste des fichiers contruite au prélable
//***********************************************************************************************
TableAccumlateCombineFiles = List.Accumulate(
ListAccumlateCombineFolders,
#table({},{}),
(state,current) =>
let
PathName=current[Folder Path] & current[Name],
// Récupération des valeurs Tag Colonne, Table(Item), Dossier
TagColumn=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{1}),
CurrentItem=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{2}),
TagFolder=Record.Field(current, LIST_COLUMNS_TBL_COMBINE{3}),
Path=if STR_AJOUT_DU_CHEMIN_COMPLET = "OUI" then PathName else null,
// Test existence de l'Item
isExistsIem = if CurrentItem is null then false else fnIsExistsTableSheet(PathName,CurrentItem, STR_TYPE),
TableCombine=if isExistsIem
then
let
wbExcelTmp=try Excel.Workbook(File.Contents(PathName), null, true){[Item=CurrentItem,Kind=STR_TYPE]}[Data] otherwise state,
wbExcel=if STR_PROMOTE ="OUI" then Table.PromoteHeaders(wbExcelTmp, [PromoteAllScalars=true]) else wbExcelTmp,
TblAddTagColumn=Table.AddColumn(wbExcel, LIST_COLUMNS_TBL_COMBINE{1}, each TagColumn), // Ajout colonne tag fichier
TblAddTagFolder=Table.AddColumn(TblAddTagColumn, LIST_COLUMNS_TBL_COMBINE{3}, each TagFolder), // Ajout colonne tag dossier
TblAddPath=if STR_AJOUT_DU_CHEMIN_COMPLET = "OUI" then Table.AddColumn(TblAddTagFolder, "CHEMIN_COMPLET", each Path) else TblAddTagFolder, // Ajout colonne tag dossier
TableCombine=Table.Combine({state,TblAddPath})
in
TableCombine
else state, // Si l'Item n'existe pas on passe au fichier suivant
v= TableCombine
in
v
)
in
TableAccumlateCombineFiles,
//******************************************************************************
// Déplacement de l'étiquette à la 1ère colonne
//******************************************************************************
MOVE_COLUMN_TAG = (pTable as table, pCol as number) as any =>
let
LstColumns=Table.ColumnNames(pTable),
NbrColumnsTotal=List.Count(LstColumns),
NbColumns=if STR_AJOUT_DU_CHEMIN_COMPLET = "OUI" then NbrColumnsTotal - 3 else NbrColumnsTotal - 2,
L1={LIST_COLUMNS_TBL_COMBINE{pCol}},
L2=List.FirstN(LstColumns,NbColumns),
LstCombine=List.Combine({L1,L2}),
TblReorder=Table.ReorderColumns(pTable,LstCombine)
in
TblReorder, //TblReorder,
//******************************************************************************
// Application des filtres
//******************************************************************************
// Ordre des colonnes de la table filtres
// COLONNE_A_FILTRER OPERATEUR FILTRE
SET_FILTERS = () as any =>
let
// Une nouvelle fois on passe par une boucle pour lire les critères de filtres
TableAccumulateFilters = List.Accumulate(
LIST_COLUMNS_FILTERS_TO_RECORDS, // Liste des critères de filtrage
TBL_TRY_02, // On démarre avec la table issue de la fusion des dossiers
(state, current) => // state initialisé avec la table issue de la fusion des dossiers, content critère de recherche en cours de traitement
let
// On récupère les valeurs des filtres - Colonne - Opérateur - Valeur filtrée
StrColumFilter=Record.Field(current, LIST_COLUMNS_FILTERS{0}),
// On vérifie si la colonne existe dans la table des données issue de la combinaison des dossiers
IsColumnExists=List.Contains(LIST_COLUMNS_TRY_02,StrColumFilter),
StrOperFilter=Record.Field(current, LIST_COLUMNS_FILTERS{1}), // on récupère l'opératuer du filtre
StrValueFilter=Text.From(Record.Field(current, LIST_COLUMNS_FILTERS{2})), // On force au format text pour éviter les plantages
StrOperFilterPWQ=if StrOperFilter <> null then Text.Split(StrOperFilter,"|"){1} else "<N/A>", // pour éviter un plantage si l'opérateur est incomplet
// Si la colonne existe on peut filtrer les données
// On vérifie aussi si la ligne est complète
TableEvalSelectRow=if IsColumnExists and StrOperFilter <> null and StrValueFilter <> null and StrOperFilterPWQ <> "<N/A>"
then
if StrOperFilterPWQ = "=" or StrOperFilterPWQ = "<>"
// Cas des opérateurs qui ne font pas appel à une fonction Text de recherche
then
let
// on construit la fonction de recherche de manière indirecte
StrEval="each Record.Field(_, " & """" & StrColumFilter & """" & ")" & " " & StrOperFilterPWQ & " " & """" & StrValueFilter & """",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field]),
// Filtre selon les critères
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRows
// Cas des opérateurs qui font pas appel à une fonction Text de recherche
else
let
// on construit la fonction de recherche de manière indirecte
StrEval="each " & StrOperFilterPWQ & "(" & "Record.Field(_," & """" & StrColumFilter & """" & "), " & """" & StrValueFilter & """" & ")",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field,
Text.StartsWith=Text.StartsWith,
Text.EndsWith=Text.EndsWith,
Text.Contains=Text.Contains]),
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRows
else state // si la colonne n'exite pas on passe au critère de filtre suivant
in
TableEvalSelectRow
)
in
TableAccumulateFilters,
//******************************************************************************
// PRINCIPALE SECTION
//******************************************************************************
//******************************************************************************
// Identification des paramètres
//******************************************************************************
STR_TYPE=pType,
STR_PROMOTE=pPromote,
STR_TBL_COMBINE=pTableCombine,
STR_TAG_PREM=pTag,
STR_TAG_COL_DOSSIER=pFolder,
STR_AJOUT_DU_CHEMIN_COMPLET=pPath,
//******************************************************************************
// On prépare les éléments pour la fusion
//******************************************************************************
// Message d'erreur si plantage en fin de traitement
TBL_MSG_ERROR=#table(type table [STATUT=text, MSG=text, ERRNUM=Int64.Type],{{"ERREUR DE TRAITEMENT", "Vérifier le chemin de vos dossiers et votre paramétrage",99}}),
TBL_MSG_ERROR_FILTERS=#table(type table [STATUT=text, MSG=text, ERRNUM=Int64.Type],{{"ERREUR DE TRAITEMENT FILTRES", "Vérifier vos filtres - uniquement sur colonnes texte",99}}),
// Ordre des colonnes de la table comportant la liste des dossiers à traiter
// FICHIERS_EXCEL_COMBINE ENSEIGNE SHEET_COMBINE DOSSIER
TBL_COMBINE = Excel.CurrentWorkbook(){[Name=STR_TBL_COMBINE]}[Content],
LIST_COLUMNS_TBL_COMBINE=Table.ColumnNames(TBL_COMBINE),
STR_NOM_COLONNE_COMBINE=LIST_COLUMNS_TBL_COMBINE{0}, // Colonne comportant le nom du dossier à importer
LST_COMBINE_FOLDERS_TO_RECORDS=Table.ToRecords(TBL_COMBINE), // Transformation de la table en liste de Records
//******************************************************************************
// Fusion des fichiers
//******************************************************************************
TBL_DATA_COMBINE_FOLDERS=COMBINE_FOLDERS(),
TBL_DATA_FINAL_01=if STR_TAG_PREM = "OUI" then MOVE_COLUMN_TAG(TBL_DATA_COMBINE_FOLDERS, 1) else TBL_DATA_COMBINE_FOLDERS,
TBL_DATA_FINAL_02=try if STR_TAG_COL_DOSSIER = "OUI" then MOVE_COLUMN_TAG(TBL_DATA_FINAL_01, 3) else TBL_DATA_FINAL_01,
HAS_ERROR=TBL_DATA_FINAL_02[HasError],
TBL_TRY_02=if HAS_ERROR then TBL_MSG_ERROR else TBL_DATA_FINAL_02[Value],
//***********************************************************************************
// On va appliquer les filtres si nécessaire en fonction des options de paramétrage
//***********************************************************************************
LIST_COLUMNS_TRY_02=Table.ColumnNames(TBL_TRY_02),
STR_APPLIQUER_FILTRES=pFilter,
STR_TABLE_FILERS=pTableFilter,
TBL_FILTERS = Excel.CurrentWorkbook(){[Name=STR_TABLE_FILERS]}[Content],
LIST_COLUMNS_FILTERS=Table.ColumnNames(TBL_FILTERS),
LIST_COLUMNS_FILTERS_TO_RECORDS=Table.ToRecords(TBL_FILTERS),
STR_NOM_COLONNE_FILTRE=LIST_COLUMNS_FILTERS{0},
TBL_FILTREE= if STR_APPLIQUER_FILTRES ="OUI" then SET_FILTERS() else TBL_TRY_02
in
TBL_FILTREE
in
fnImportMultipleFoldersFiltersICI en détail
PowerQuery:
SET_FILTERS = () as any =>
let
// Une nouvelle fois on passe par une boucle pour lire les critères de filtres
TableAccumulateFilters = List.Accumulate(
LIST_COLUMNS_FILTERS_TO_RECORDS, // Liste des critères de filtrage
TBL_TRY_02, // On démarre avec la table issue de la fusion des dossiers
(state, current) => // state initialisé avec la table issue de la fusion des dossiers, content critère de recherche en cours de traitement
let
// On récupère les valeurs des filtres - Colonne - Opérateur - Valeur filtrée
StrColumFilter=Record.Field(current, LIST_COLUMNS_FILTERS{0}),
// On vérifie si la colonne existe dans la table des données issue de la combinaison des dossiers
IsColumnExists=List.Contains(LIST_COLUMNS_TRY_02,StrColumFilter),
StrOperFilter=Record.Field(current, LIST_COLUMNS_FILTERS{1}), // on récupère l'opératuer du filtre
StrValueFilter=Text.From(Record.Field(current, LIST_COLUMNS_FILTERS{2})), // On force au format text pour éviter les plantages
StrOperFilterPWQ=if StrOperFilter <> null then Text.Split(StrOperFilter,"|"){1} else "<N/A>", // pour éviter un plantage si l'opérateur est incomplet
// Si la colonne existe on peut filtrer les données
// On vérifie aussi si la ligne est complète
TableEvalSelectRow=if IsColumnExists and StrOperFilter <> null and StrValueFilter <> null and StrOperFilterPWQ <> "<N/A>"
then
if StrOperFilterPWQ = "=" or StrOperFilterPWQ = "<>"
// Cas des opérateurs qui ne font pas appel à une fonction Text de recherche
then
let
// on construit la fonction de recherche de manière indirecte
StrEval="each Record.Field(_, " & """" & StrColumFilter & """" & ")" & " " & StrOperFilterPWQ & " " & """" & StrValueFilter & """",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field]),
// Filtre selon les critères
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRows
// Cas des opérateurs qui font pas appel à une fonction Text de recherche
else
let
// on construit la fonction de recherche de manière indirecte
StrEval="each " & StrOperFilterPWQ & "(" & "Record.Field(_," & """" & StrColumFilter & """" & "), " & """" & StrValueFilter & """" & ")",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field,
Text.StartsWith=Text.StartsWith,
Text.EndsWith=Text.EndsWith,
Text.Contains=Text.Contains]),
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRows
else state // si la colonne n'exite pas on passe au critère de filtre suivant
in
TableEvalSelectRow
)
in
TableAccumulateFilters,Le code est largement documenté. Il est construit sous la même logique que les autres fonctions.
Juste un point particulier sur cette partie de code qui représente le moteur du filtrage
PowerQuery:
then
let
// on construit la fonction de recherche de manière indirecte
StrEval="each Record.Field(_, " & """" & StrColumFilter & """" & ")" & " " & StrOperFilterPWQ & " " & """" & StrValueFilter & """",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field]),
// Filtre selon les critères
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRows
// Cas des opérateurs qui font pas appel à une fonction Text de recherche
else
let
// on construit la fonction de recherche de manière indirecte
StrEval="each " & StrOperFilterPWQ & "(" & "Record.Field(_," & """" & StrColumFilter & """" & "), " & """" & StrValueFilter & """" & ")",
// Traduction de l'expression de recherche en langage M
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field,
Text.StartsWith=Text.StartsWith,
Text.EndsWith=Text.EndsWith,
Text.Contains=Text.Contains]),
TableSelectRows=Table.SelectRows(state, EvaluateEach)
in
TableSelectRowsEn effet on doit construire au départ le filtrage des données de cette manière quand on applique les signes "=" ou "<>"
StrEval="each Record.Field(_, " & """" & StrColumFilter & """" & ")" & " " & StrOperFilterPWQ & " " & """" & StrValueFilter & """",
pour ensuite l'évaluer et le compiler en code PowerQuery
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field]),
et dans le cas d'une fonction Text.*
StrEval="each " & StrOperFilterPWQ & "(" & "Record.Field(_," & """" & StrColumFilter & """" & "), " & """" & StrValueFilter & """" & ")",
EvaluateEach=Expression.Evaluate(StrEval, [Record.Field=Record.Field,
Text.StartsWith=Text.StartsWith,
Text.EndsWith=Text.EndsWith,
Text.Contains=Text.Contains]),
Rappel : Expression.Evaluate c'est un peu l'équivalent de la fonction INDIRECT sous Excel
REMARQUE : Toutes lignes incomplètes dans la table des critères de filtrage seront ignorées
QUELQUES EXEMPLES