oguruma
XLDnaute Impliqué
Bonjour le Forum,
Des problèmes de santés +/- importants m'ont contraint de marquer une pause. La bataille n'est pas pour autant encore gagnée.
Pour revenir à la problématique de PowerQuery et de ses immenses capacités, il peut être nécessaire dans un tableau de devoir effectuer plusieurs remplacements de données dans une ou plusieurs colonnes. Il y a bien certes les fonctions de base d'Excel mais il y a aussi PowerQuery et toute sa puissance.
Supposons le tableau de données suivant :
Nous devons effectuer des remplacements de données pour les colonnes Client, Produit et Vendeur.
Bien entendu on peut passer par les assistants et tout coder en "dur" dans la requête. Cette méthode n'offre aucune souplesse dans le cas où il faut modifier les colonnes à remplacer et les critères de remplacement.
Autre point négatif via les assistants : on va générer autant d'étapes que de remplacements à effectuer ce qui risque d'alourdir la requêtes en nombre de ligne et en temps de traitement.
Afin de rendre ces remplacements dynamiques, nous allons créer une table de critères de remplacement comme ci-dessous :
Les colonnes de la tables sont assez significatives.
Nous avons dans [COLONNE] le nom de la colonne à traiter.
Dans la [COLONNE] à traiter on renseigne l'ancienne valeur et la nouvelle valeur.
On peut renseigner plusieurs colonnes (une par ligne) et dans n'importe quel ordre.
La requête PowerQuery développée va balayer chaque enregistrement de la table des critères de remplacement et appliquer les modifications.
Afin d'éviter tout plantage la requête prévoit les cas suivants et aucun traitement ne sera appliqué :
* une colonne inconnue comme celle-ci
* un vendeur inconnu Gean (Jean) :
* une ancienne valeur non renseignée :
Il est aussi possible de remplacer une valeur par des 'blancs' comme celle-ci
Voici la requête avec ses commentaires
On voit ici l'utilisation d'une fonction très puissante de PowerQuery et aussi assez complexe à maîtriser : List.Accumulate qui permet en fait d'effectuer une boucle de lecture sur la table des paramètres afin de les traiter un à un. Ici les enregistrements sont lus en fonction de leur position relative dans la table. Il y bien entendu d'autres manière de s'y prendre. Je ne m'étends pas sur le sujet pour l'instant.
Des problèmes de santés +/- importants m'ont contraint de marquer une pause. La bataille n'est pas pour autant encore gagnée.
Pour revenir à la problématique de PowerQuery et de ses immenses capacités, il peut être nécessaire dans un tableau de devoir effectuer plusieurs remplacements de données dans une ou plusieurs colonnes. Il y a bien certes les fonctions de base d'Excel mais il y a aussi PowerQuery et toute sa puissance.
Supposons le tableau de données suivant :
Nous devons effectuer des remplacements de données pour les colonnes Client, Produit et Vendeur.
Bien entendu on peut passer par les assistants et tout coder en "dur" dans la requête. Cette méthode n'offre aucune souplesse dans le cas où il faut modifier les colonnes à remplacer et les critères de remplacement.
Autre point négatif via les assistants : on va générer autant d'étapes que de remplacements à effectuer ce qui risque d'alourdir la requêtes en nombre de ligne et en temps de traitement.
Afin de rendre ces remplacements dynamiques, nous allons créer une table de critères de remplacement comme ci-dessous :
Les colonnes de la tables sont assez significatives.
Nous avons dans [COLONNE] le nom de la colonne à traiter.
Dans la [COLONNE] à traiter on renseigne l'ancienne valeur et la nouvelle valeur.
On peut renseigner plusieurs colonnes (une par ligne) et dans n'importe quel ordre.
La requête PowerQuery développée va balayer chaque enregistrement de la table des critères de remplacement et appliquer les modifications.
Afin d'éviter tout plantage la requête prévoit les cas suivants et aucun traitement ne sera appliqué :
* une colonne inconnue comme celle-ci
* un vendeur inconnu Gean (Jean) :
* une ancienne valeur non renseignée :
Il est aussi possible de remplacer une valeur par des 'blancs' comme celle-ci
Voici la requête avec ses commentaires
PowerQuery:
let
//******************************************************************************
// Identification des paramètres
//******************************************************************************
STR_TBL_DATA=fnGetParameter("TB_PARAMS", "TABLE_DONNES"),
STR_TBL_CRITERES=fnGetParameter("TB_PARAMS", "TABLE_CRITERES"),
TBL_DATA = Excel.CurrentWorkbook(){[Name=STR_TBL_DATA]}[Content],
//******************************************************************************
// Table des critères, table des données à traiter identifiés par les paramètres
//******************************************************************************
TBL_CRITERES = Excel.CurrentWorkbook(){[Name=STR_TBL_CRITERES]}[Content],
TBL_CRITERES_TO_RECORDS=Table.ToRecords(TB_CRITERES),
//******************************************************************************
//* On identifie les noms de colonnes dans la table des critères
//******************************************************************************
LST_COLUMNS=Table.ColumnNames(TBL_CRITERES),
//******************************************************************************
// On récupère les noms des colonnes de la table des critères
// ceci ne fige donc pas les noms de colonnes à titre de la table des critères
//*******************************************************************************
STR_NOM_COLONNE=LST_COLUMNS{0}, // Nom de la colonne comportant le nom des colonnes à traiter
STR_ANC_VALEUR=LST_COLUMNS{1}, // Nom de la colonne comportant les anciennes valeurs
STR_NOUV_VALEUR=LST_COLUMNS{2}, // Nom de la colonne comportant les nouvelles valeurs
//
// Ainsi par cette méthode on est libre du nom des colonnes dans la table des critères de remplacement
// Il faut juste respecter dans l'ordre : colonne à traiter, ancienne valeur, nouvelle valeur
//
INT_NB_CRITERES=Table.RowCount(TBL_CRITERES), // On détermine le nombre de critères (nbr d'enregistrements de la table critères)
LST_COMPTEUR={0..INT_NB_CRITERES - 1 }, // L'index démarre à zéro - il faut donc retirer 1 au nombre d'enregistement pour ne pas avoir un débordement dans la boucle
TB_REPLACE=List.Accumulate(
LST_COMPTEUR, // Pointeur sur le nombre d'enregistrements critères à traiter
TBL_DATA, // Table des données dans laquelle il faut effectuer les remplacement
(state,current) => // Variables itératives - current: n° enregistrement en cours --- state : table des données
let
Colonne=Record.Field(TBL_CRITERES_TO_RECORDS{current},STR_NOM_COLONNE), // nom de la colonne dans laquelle il faut effectuer un remplacement
IsExists=Table.HasColumns(TBL_DATA,Colonne), // Booléen pour tester l'existence de la colonne
TblNew=if IsExists // Pour éviter tout plantage si la colonne n'est pas correctement renseignéé
then
let
AncValeur=Record.Field(TBL_CRITERES_TO_RECORDS{current}, STR_ANC_VALEUR), // ancienne valeur à remplacer
IsNullAncValeur=if AncValeur is null then true else false, // SI la colonne à remplacer n'est pas renseignée
NouvValeur=Record.Field(TBL_CRITERES_TO_RECORDS{current}, STR_NOUV_VALEUR), // nouvelle valeur
NouvValeur2=if NouvValeur is null then "" else NouvValeur, // Si la valeur de remplacement n'est pas renseignée
TblReplace=if not (IsNullAncValeur) then
Table.TransformColumns(state,{{Colonne, each Text.Replace(_,AncValeur,NouvValeur2), type text}}) // on procède au remplacement des valeurs dans la colonne
else
state
in
TblReplace
else
state
in
//******************************************************************************
// Table transformée
//******************************************************************************
TblNew // On récupère ici la nouvelle table avec les valeurs remplacées
)
in
TB_REPLACEOn voit ici l'utilisation d'une fonction très puissante de PowerQuery et aussi assez complexe à maîtriser : List.Accumulate qui permet en fait d'effectuer une boucle de lecture sur la table des paramètres afin de les traiter un à un. Ici les enregistrements sont lus en fonction de leur position relative dans la table. Il y bien entendu d'autres manière de s'y prendre. Je ne m'étends pas sur le sujet pour l'instant.
Dernière édition: