oguruma
XLDnaute Impliqué
Bonjour Le Forum,
dans la précédente publication ICI j'avais évoqué qu'il était possible d'appliquer des traitements particuliers sur les colonnes. C'est donc chose faite dans cette nouvelle version.
Cette nouvelle version permet aussi soit d'importer un fichier Excel soit un fichier .CSV.
IMPORTATION D'UN FICHIER .CSV
On passe toujours par un tableau de paramètres comme celui-ci
Cette fois nous avons le choix sur le type de fichier
Les paramètres ITEM et KIND sont eux utilisés pour les fichiers Excel que nous verrons plus bas.
Puis nous avons les deux tables de traitements et nettoyage
L'organisation de PowerQuery est la suivante :
RQ_IMPORT_DONNEES est une requête de connexion qui va se charger de produire la table finale chargée dans TB_FINALE.
Selon les besoins les évolutions se feront dans cette table TB_FINALE.
Le fait de découper les traitements PowerQuery permet d'alléger le code. RQ_IMPORT_DONNEES est une requête de préparation des données.
Préalable : afin de connaître le nom des colonnes il faudra ouvrir le fichier .CSV et identifier les noms des colonnes dans la 1ère ligne.
Le fait de passer par une table de nettoyage offre beaucoup de souplesse pour la maintenance. Ceci évite d'ouvrir le code de la requête pour ajouter ou supprimer des colonnes à nettoyer.
La logique est la même si on a besoin d'effectuer des remplacements de valeurs dans les colonnes. On passera par la table des critères de remplacement.
Voici le code qui est aussi identique pour les importations de fichiers EXCEL. C'est juste le paramétrage qui diffère.
Je vous invite à bien lire les commentaires.
Remarque : même si on trouve que cet utilitaire apporte peu d'intérêt il peut être très pédagogique dans l'apprentissage du langage M de PowerQuery.
IMPORTATION D'UN FICHIER EXCEL
Ceci est déterminé via le tableau
Via la requête
Il suffit de rafraîchir le tableau par le boutonj Actualiser qui va renvoyer automatiquement le liste des ITEM.
Selon le type de source de données il faudra choisir
Installation
Pour vos tests :
1- créer un dossier
2- dans ce dossier déposer les deux fichiers Excel
3- déposer le fichier Excel Ventes_2022_2
4- déposer et dézipper diplomes-professionnels.zip
5- effectuer le paramétrage nécessaire correspondant au dossier où vous avez déposé les fichiers
dans la précédente publication ICI j'avais évoqué qu'il était possible d'appliquer des traitements particuliers sur les colonnes. C'est donc chose faite dans cette nouvelle version.
Cette nouvelle version permet aussi soit d'importer un fichier Excel soit un fichier .CSV.
IMPORTATION D'UN FICHIER .CSV
On passe toujours par un tableau de paramètres comme celui-ci
Cette fois nous avons le choix sur le type de fichier
Les paramètres ITEM et KIND sont eux utilisés pour les fichiers Excel que nous verrons plus bas.
Puis nous avons les deux tables de traitements et nettoyage
L'organisation de PowerQuery est la suivante :
RQ_IMPORT_DONNEES est une requête de connexion qui va se charger de produire la table finale chargée dans TB_FINALE.
PowerQuery:
let
Source = RQ_IMPORT_DONNEES
in
SourceSelon les besoins les évolutions se feront dans cette table TB_FINALE.
Le fait de découper les traitements PowerQuery permet d'alléger le code. RQ_IMPORT_DONNEES est une requête de préparation des données.
Préalable : afin de connaître le nom des colonnes il faudra ouvrir le fichier .CSV et identifier les noms des colonnes dans la 1ère ligne.
Le fait de passer par une table de nettoyage offre beaucoup de souplesse pour la maintenance. Ceci évite d'ouvrir le code de la requête pour ajouter ou supprimer des colonnes à nettoyer.
La logique est la même si on a besoin d'effectuer des remplacements de valeurs dans les colonnes. On passera par la table des critères de remplacement.
Voici le code qui est aussi identique pour les importations de fichiers EXCEL. C'est juste le paramétrage qui diffère.
Je vous invite à bien lire les commentaires.
PowerQuery:
let
//******************************************************************************
// FONCTIONS SECTION
//******************************************************************************
//*******************************************************************************
// Fonction permettant d'importer le fichier mentionné dans les paramètres
//*******************************************************************************
GET_DATA_SOURCE = () as any =>
let
//Csv.Document(File.Contents(STR_DOSSIER & "\" & STR_FICHIER ),[Delimiter=STR_SEPARATEUR, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
TblSource=if STR_TYPE="CSV" or STR_TYPE is null then
Csv.Document(File.Contents(STR_DOSSIER & "\" & STR_FICHIER ),[Delimiter=STR_SEPARATEUR, Encoding=65001, QuoteStyle=QuoteStyle.Csv])
else
let
Source = Excel.Workbook(File.Contents(STR_DOSSIER & "\" & STR_FICHIER ), null, true),
SourceExcel = Source{[Item=STR_ITEM,Kind=STR_KIND]}[Data]
in
SourceExcel
in
TblSource,
//*******************************************************************************
// Opération de nettoyage
//*******************************************************************************
TRAITEMENT_NETTOYAGE = () as any =>
let
// NETTOYAGE SUPPR_ESPACE TEXTE CONVERSION
// Récupération des opérations de nettoyage - identification des colonnes et peu importe le nom
// il faut juste respecter l'ordre préciser ci-dessus dans les commentaires
STR_COL_NETTOYAGE = LST_COLUMNS_TRAITEMENTS{1}, // option de nettoyage
STR_COL_SUPPR_ESPACE = LST_COLUMNS_TRAITEMENTS{2}, // option de suppression des espaces
STR_COL_CONV_TEXTE = LST_COLUMNS_TRAITEMENTS{3}, // optin de conversion en texte
STR_COL_CONVERSION=LST_COLUMNS_TRAITEMENTS{4}, // option de conversion de la casse
// Boucle de traitement sur les opérations de nettoyage à effectuer
//------------------------------------------------------------------
LST_ACCUMULATE=List.Accumulate(
TBL_TRAITEMENTS_TO_RECORDS, // Liste des enregistrements à traiter
TBL_DATA, // On récupère le fichier à l'issue de l'importation
(state,current)=> // variables de boucle - state = accumulateur au fil des traitement -- current = record en cours de traitement
let
// On récupère les options de nettoyage et de conversion si elles sont mentionnées
//--------------------------------------------------------------------------------
IsNettoyage=Record.Field(current, STR_COL_NETTOYAGE),
IsSupprEspace=Record.Field(current, STR_COL_SUPPR_ESPACE),
ISConvTexte=Record.Field(current, STR_COL_CONV_TEXTE),
TypeConversion=Record.Field(current, STR_COL_CONVERSION),
// phase de nettoyage
//-------------------
TblNettoyage=if IsNettoyage = "NON" or IsNettoyage is null or Record.Field(current, STR_NOM_COLONNE_TRAITEMENT) is null then TBL_DATA
else Table.TransformColumns(state,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), Text.Clean, type text}}),
TblSpprEspace=if IsSupprEspace ="NON" or IsSupprEspace is null or Record.Field(current, STR_NOM_COLONNE_TRAITEMENT) is null then TblNettoyage
else Table.TransformColumns(TblNettoyage,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), Text.Trim, type text}}),
TblText=if ISConvTexte = "NON" or ISConvTexte is null or Record.Field(current, STR_NOM_COLONNE_TRAITEMENT) is null then TblSpprEspace
else Table.TransformColumnTypes(TblSpprEspace,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), type text}}),
// phase de conversion majuscule, minuscule, nom propre
//-------------------------------------------------------
TblConversion=if TypeConversion is null or Record.Field(current, STR_NOM_COLONNE_TRAITEMENT) is null then TblText
else if TypeConversion = "MINUSCULE"
then Table.TransformColumns(TblText,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), Text.Lower, type text}})
else if TypeConversion = "MAJUSCULE"
then Table.TransformColumns(TblText,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), Text.Upper, type text}})
else if TypeConversion = "NONPROPRE"
then Table.TransformColumns(TblText,{{Record.Field(current, STR_NOM_COLONNE_TRAITEMENT), Text.Proper, type text}})
else TblText
in
TblConversion // nouvelle table à l'issue des opérations de traitements
)
in
LST_ACCUMULATE, // fin de la boucle
TRAITEMENTS_REMPLACEMENTS = () as any =>
let
STR_COL_ANC_VALEUR=LST_COLUMNS_CRITERES{1}, // Nom de la colonne comportant les anciennes valeurs
STR_COL_NOUV_VALEUR=LST_COLUMNS_CRITERES{2}, // Nom de la colonne comportant les nouvelles valeurs
// boucle de traitement sur les critères de remplacement
//--------------------------------------------------------
LST_ACCUMULATE=List.Accumulate (
TBL_CRITERES_TO_RECORDS, // Liste des enregistrements de remplacement à traiter
TBL_NETTOYAGE, // Table de données à l'issue du nettoyage
(state,current) =>
let
Colonne=Record.Field(current,STR_NOM_COLONNE), // nom de la colonne à traiter
IsExists=if Colonne is null then false else Table.HasColumns(TBL_NETTOYAGE,Colonne), // on va vérifier si elle existe pour éviter des plantages - si inconnue on passe à la suivante
TblNew = if IsExists then
let
AncValeur=Record.Field(current, STR_COL_ANC_VALEUR),
NouvValeur=Record.Field(current, STR_COL_NOUV_VALEUR),
NouvValeur2=if NouvValeur is null then "" else NouvValeur, // pour éviter un plantage si aucune valeur est renseignée et remise à blanc
// remplacement des données dans la colonne en cours
//--------------------------------------------------
TblReplace=if AncValeur is null then state
else
Table.TransformColumns(state,{{Colonne, each Text.Replace(_,AncValeur,NouvValeur2), type text}}) // on procède au remplacement des valeurs dans la colonne
in
TblReplace
else state
in
TblNew // nouvelle table à l'issue dees remplacements
)
in
LST_ACCUMULATE,
//******************************************************************************
// PRINCIPALE SECTION
//******************************************************************************
//******************************************************************************
// Identification des paramètres
//******************************************************************************
STR_DOSSIER=fnGetParameter("TB_PARAMS", "DOSSIER"),
STR_FICHIER=fnGetParameter("TB_PARAMS", "FICHIER"),
STR_TYPE=fnGetParameter("TB_PARAMS", "TYPE"),
STR_ITEM=fnGetParameter("TB_PARAMS", "ITEM"),
STR_KIND=fnGetParameter("TB_PARAMS", "KIND"),
STR_SEPARATEUR=fnGetParameter("TB_PARAMS", "SEPARATEUR"),
STR_PROMOTE=Text.Upper(fnGetParameter("TB_PARAMS", "PROMOTE")),
// STR_DONNES=fnGetParameter("TB_PARAMS", "TABLE_DONNES_IMPORT"),
STR_CRITERES=fnGetParameter("TB_PARAMS", "TABLE_CRITERES_IMPORT"),
STR_TRAITEMENTS=fnGetParameter("TB_PARAMS", "TABLE_TRAITEMENTS_IMPORT"),
//******************************************************************************
// Chargement du fichier .csv
//******************************************************************************
TBL_DATA_SOURCE = GET_DATA_SOURCE(),
//Csv.Document(File.Contents(STR_DOSSIER & "\" & STR_FICHIER ),[Delimiter=STR_SEPARATEUR, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
TBL_DATA = if STR_PROMOTE is null or STR_PROMOTE = "OUI" then Table.PromoteHeaders(TBL_DATA_SOURCE, [PromoteAllScalars=true]) else TBL_DATA_SOURCE,
//******************************************************************************
// Table des critères de remplacement - table des traitements
//******************************************************************************
TBL_CRITERES = Excel.CurrentWorkbook(){[Name=STR_CRITERES]}[Content],
TBL_TRAITEMENTS = Excel.CurrentWorkbook(){[Name=STR_TRAITEMENTS]}[Content],
//******************************************************************************
// On identifie les colonnes des tableaux
//******************************************************************************
LST_COLUMNS_CRITERES=Table.ColumnNames(TBL_CRITERES),
LST_COLUMNS_TRAITEMENTS=Table.ColumnNames(TBL_TRAITEMENTS),
//*******************************************************************************
// Respecter l'ordre des colonnes peu importe le nom des colonnses
// Colonnes critères
//*******************************************************************************
STR_NOM_COLONNE=LST_COLUMNS_CRITERES{0}, // Nom de la colonne comportant le nom des colonnes à traiter
//*******************************************************************************
// Peu importe le nom des colonnes dans le tableau des traitement cet ordre doit être respecté
// NETTOYAGE SUPPR_ESPACE TEXTE CONVERSION
//*******************************************************************************
STR_NOM_COLONNE_TRAITEMENT=LST_COLUMNS_TRAITEMENTS{0}, // Nom de la colonne à traiter
//*******************************************************************************
// Nombre de critères de remplacements - nombre de traitement
//*******************************************************************************
// INT_NB_CRITERES=Table.RowCount(TBL_CRITERES),
// INT_NB_TRAITEMENTS=Table.RowCount(TBL_TRAITEMENTS),
//*******************************************************************************
// Transformation des critères et des traitements en liste de records
//*******************************************************************************
TBL_CRITERES_TO_RECORDS=Table.ToRecords(TBL_CRITERES),
TBL_TRAITEMENTS_TO_RECORDS=Table.ToRecords(TBL_TRAITEMENTS),
//*******************************************************************************
// Pour vérifier si des traitements sont à effectuer
//*******************************************************************************
IsTraitementNettoyage=Record.Field(TBL_TRAITEMENTS_TO_RECORDS{0},STR_NOM_COLONNE_TRAITEMENT),
IsCritere=Record.Field(TBL_CRITERES_TO_RECORDS{0},STR_NOM_COLONNE),
//*******************************************************************************
// Traitements de nettoyage et de remplacement des données selon les critères fournis
//*******************************************************************************
TBL_NETTOYAGE=if IsTraitementNettoyage is null then TBL_DATA else TRAITEMENT_NETTOYAGE(),
TBL_FINALE=if IsCritere is null then TBL_NETTOYAGE else TRAITEMENTS_REMPLACEMENTS()
in
TBL_FINALERemarque : même si on trouve que cet utilitaire apporte peu d'intérêt il peut être très pédagogique dans l'apprentissage du langage M de PowerQuery.
IMPORTATION D'UN FICHIER EXCEL
Ceci est déterminé via le tableau
Via la requête
PowerQuery:
let
STR_DOSSIER=fnGetParameter("TB_PARAMS", "DOSSIER"),
STR_FICHIER=fnGetParameter("TB_PARAMS", "FICHIER"),
STR_TYPE=fnGetParameter("TB_PARAMS", "TYPE"),
Source = if STR_TYPE ="EXCEL" then
let
WB_EXCEL=Excel.Workbook(File.Contents(STR_DOSSIER & "\" & STR_FICHIER), null, true),
SUPPR_COLONNES = Table.RemoveColumns(WB_EXCEL,{"Data", "Item", "Kind", "Hidden"})
in
SUPPR_COLONNES
else
#table(
{ "Name" }, // defines column names
{
{ "" }
}
)
in
SourceIl suffit de rafraîchir le tableau par le boutonj Actualiser qui va renvoyer automatiquement le liste des ITEM.
Selon le type de source de données il faudra choisir
Installation
Pour vos tests :
1- créer un dossier
2- dans ce dossier déposer les deux fichiers Excel
3- déposer le fichier Excel Ventes_2022_2
4- déposer et dézipper diplomes-professionnels.zip
5- effectuer le paramétrage nécessaire correspondant au dossier où vous avez déposé les fichiers
