oguruma
XLDnaute Occasionnel
Bonjour le Forum,
A travers ce mini tuto comment importer un fichier Excel en optimisant les étapes de transformations à la volée directement dans les colonnes sans créer de colonnes temporaires additionnelles.
Exemple : Importer ce fichier et procéder à des traitements pendant l'importation
Exemple 1
Nous allons importer ce fichier et au passage transformer la colonne MLE en préfixant par un zéro de manière à avoir 6 caractères puis les séparer par groupe de deux avec le séparateur "-" afin d'avoir ceci
Nous allons procéder à l'importation en passant par les assistants. Cela nous donne la requête suivante
Nous sommes donc passés par une colonne intermédiaire temporaire afin de pouvoir transformer la colonne MLE.
Puis il a été nécessaire de supprimer la colonne initiale et de renommer la colonne temporaire par le nom de la colonne initiale.
Ces 3 étapes peuvent être regroupées en une seule avec la fonction Table.TransformColumns
Exemple 2
Ci-dessous un exemple avancé de transformation sur plusieurs colonnes
Nous intervenons ici sur 3 colonnes.
Exemple 3
Ces transformations multiples en direct dans les colonnes sont aussi possibles avec la fonction Table.TransformRows
Celle-ci offre l'avantage de pouvoir récupérer les valeurs des autres colonnes étant donné que l'on travaille sur le RECORD ce qui n'est pas le cas de la fonction Table.TransformColumns qui elle travaille uniquement sur la colonne. Le RECORD n'est donc pas accessible dans totalité.
Nous obtenons ceci
On remarque les multiples transformations dont certaines assez complexes.
Pour l'exemple la colonne MLE est combinée avec la date sous forme AAAAMMJJ.
Les colonnes NOM et PRENOM ont été renommées.
Les colonnes CONTRAT et SERVICE converties en ProperCase
Création des colonnes spécifiques DATE_AAAAMMJJ, PCT_AUGMENT, MONTANT_AUGMENT, NOUVEAU_SALAIRE
Tous ces traitements auraient réalisables via les assistants au détriment d'opérations multiples qui auraient ralenti les traitement en cas de forte volumétrie.
La requête est certes un peu complexe car elle demande déjà une bonne maîtrise du langage M. C'est cependant un bon exemple pour vos futurs traitements. Il suffit de vous en inspirer comme modèle de syntaxe à employer.
Autre exemple dans le même style
Exemple 4
Dans cet exemple on souhaite uniquement créer une table d'index à l'issue de l'importation pour obtenir ceci
On remarque à l'issue de l'importation on ne retient que deux colonnes que l'on a transformer pendant les traitements.
Méthode en passant par les assistants
Méthode optimisée
A travers ce mini tuto comment importer un fichier Excel en optimisant les étapes de transformations à la volée directement dans les colonnes sans créer de colonnes temporaires additionnelles.
Exemple : Importer ce fichier et procéder à des traitements pendant l'importation
Exemple 1
Nous allons importer ce fichier et au passage transformer la colonne MLE en préfixant par un zéro de manière à avoir 6 caractères puis les séparer par groupe de deux avec le séparateur "-" afin d'avoir ceci
Nous allons procéder à l'importation en passant par les assistants. Cela nous donne la requête suivante
Nous sommes donc passés par une colonne intermédiaire temporaire afin de pouvoir transformer la colonne MLE.
Puis il a été nécessaire de supprimer la colonne initiale et de renommer la colonne temporaire par le nom de la colonne initiale.
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// LES OPERATIONS CI-DESSOUS SERONT REMPLACEES PAR UNE SEULE ETAPE PAR LA FONCTION
// Table.TransformColumns
//*****************************************************************************************************
// Qui consiste à :
// -Transformation directement dans la colonne
// -Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// -Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// -sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// -on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
//*****************************************************************************************************
#"Personnalisée ajoutée" = Table.AddColumn(TBL_PROMOTE, "MLE2", each let
s1=Text.PadStart([MLE],6,"0"),
s2=Text.Start(s1,2),
s3=Text.Middle(s1,3,2),
s4=Text.End(s1,2),
S=s2 & "-" & s3 & "-" & s4
in
S),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"MLE"}),
#"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"MLE2", "MLE"}}),
#"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"MLE", "NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"})
in
#"Colonnes permutées"Ces 3 étapes peuvent être regroupées en une seule avec la fonction Table.TransformColumns
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// Transformation directement dans la colonne
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
//*****************************************************************************************************
TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
TBL_PROMOTE,
{
//***********************************************************************************************
// On fixe le Matricule, on complète par des zéros à droite pour avoir le même nbr de caractères
// ensuite pour l'exemple on le décompose en 3 parties
//***********************************************************************************************
{"MLE",
each
let
strPad=Text.PadStart(_,6,"0"),
strS1=Text.Start(strPad,2),
strS2=Text.Middle(strPad,3,2),
strS3=Text.End(strPad,2),
strMleFinal="ID-" & strS1 & "-" & strS2 & "-" & strS3
in
strMleFinal,
type text}
}
)
in
TBL_TRANSFORM_COLUMNSExemple 2
Ci-dessous un exemple avancé de transformation sur plusieurs colonnes
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// Transformation directement dans les colonnes
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
// Toutes ces opérations auraient nécessité des colonnes intermédiaires à renommer après suppression
// des colonnes d'origine
//*****************************************************************************************************
TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
TBL_PROMOTE,
{
{"MLE",
(xMLE)=>
let
strPad=Text.PadStart(xMLE,6,"0"),
strS1=Text.Start(strPad,2),
strS2=Text.Middle(strPad,3,2),
strS3=Text.End(strPad,2),
strMleFinal= strS1 & strS2 & strS3
in
strMleFinal,
type text},
//*********************************************
// Conversion de la date en AAAAMMJJ
//*********************************************
{"DATE ENTREE",
(xDATE)=>
let
strDay=Text.PadStart(Number.ToText(Date.Day(xDATE)),2,"0"),
strYear=Number.ToText(Date.Year(xDATE)),
strMonth=Text.PadStart(Number.ToText(Date.Month(xDATE)),2,"0"),
strDate=strYear & strMonth & strDay
in
strDate,
type text},
//*********************************************
// On préfixe le type de contrat
//*********************************************
{"CONTRAT",
(xCONTRAT)=>
let
code=if xCONTRAT="CDI" then "01"
else if xCONTRAT="CDD" then "02"
else "99"
in
code & "-" & xCONTRAT
}
}
)
in
TBL_TRANSFORM_COLUMNSNous intervenons ici sur 3 colonnes.
Exemple 3
Ces transformations multiples en direct dans les colonnes sont aussi possibles avec la fonction Table.TransformRows
Celle-ci offre l'avantage de pouvoir récupérer les valeurs des autres colonnes étant donné que l'on travaille sur le RECORD ce qui n'est pas le cas de la fonction Table.TransformColumns qui elle travaille uniquement sur la colonne. Le RECORD n'est donc pas accessible dans totalité.
Nous obtenons ceci
On remarque les multiples transformations dont certaines assez complexes.
Pour l'exemple la colonne MLE est combinée avec la date sous forme AAAAMMJJ.
Les colonnes NOM et PRENOM ont été renommées.
Les colonnes CONTRAT et SERVICE converties en ProperCase
Création des colonnes spécifiques DATE_AAAAMMJJ, PCT_AUGMENT, MONTANT_AUGMENT, NOUVEAU_SALAIRE
Tous ces traitements auraient réalisables via les assistants au détriment d'opérations multiples qui auraient ralenti les traitement en cas de forte volumétrie.
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
//*****************************************************************************************************
// On récupère les taux d'augmentations
//*****************************************************************************************************
pct_Cpt=fnGetParameter("TB_PARAMS","COMPTABILITE","PARAM", "VALEUR"),
pct_Comm=fnGetParameter("TB_PARAMS","COMMERCIAL","PARAM", "VALEUR"),
pct_Ach=fnGetParameter("TB_PARAMS","ACHATS","PARAM", "VALEUR"),
pct_Paie=fnGetParameter("TB_PARAMS","PAIE","PARAM", "VALEUR"),
pct_Info=fnGetParameter("TB_PARAMS","INFORMATIQUE","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// Transformation directement dans les colonnes
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
//*****************************************************************************************************
TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
TBL_PROMOTE,
{
{"MLE",
(xMLE)=>
let
strPad=Text.PadStart(xMLE,6,"0"),
strS1=Text.Start(strPad,2),
strS2=Text.Middle(strPad,3,2),
strS3=Text.End(strPad,2),
strMleFinal= strS1 & strS2 & strS3
in
strMleFinal,
type text}
}
),
//*****************************************************************************************************
// Transformation directement dans les enregistrements RECORDS
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
//*****************************************************************************************************
// Le fait de passer par Table.TransformRows permet d'utiliser tous les champs du RECORD pour
// transformmer le champ(colonne) d'un RECORD
//*****************************************************************************************************
TBL_TRANSFORM_ROWS=Table.TransformRows(
//*********************************************************************************************
// De la même manière que Table.TransformColumns on peut adresse directement un champ/colonne
// pour y faire des transformation en direct sans devoir passer par une colonne intermédiaire
// C'est aussi l'un des moyens pour ajouter des nouvelles colonnes pendant les opérations
// de transformation
//*********************************************************************************************
TBL_TRANSFORM_COLUMNS,
each let
//************************************************************************************
// Construction de la nouvelle colonne DATE_AAAAMMJJ - elle sera ajoutée au RECORD
//************************************************************************************
DATE_AAAAMMJJ=
let
strDay=Text.PadStart(Number.ToText(Date.Day([DATE ENTREE])),2,"0"),
strYear=Number.ToText(Date.Year([DATE ENTREE])),
strMonth=Text.PadStart(Number.ToText(Date.Month([DATE ENTREE])),2,"0"),
strDate=strYear & strMonth & strDay
in
strDate,
//************************************************************************************
// Construction de la nouvelle colonne CODE_CONTRAT - elle sera ajoutée au RECORD
//************************************************************************************
CODE_CONTRAT=
let
code=if [CONTRAT]="CDI" then "01"
else if [CONTRAT]="CDD" then "02"
else "99"
in
code,
//************************************************************************************
// Construction de la nouvelle colonne TAUX_AUGMENT - elle sera ajoutée au RECORD
//************************************************************************************
TAUX_AUGMENT=
let
taux=if [SERVICE]="COMPTABILITE" then pct_Cpt
else if [SERVICE]="COMMERCIAL" then pct_Comm
else if [SERVICE]="ACHATS" then pct_Ach
else if [SERVICE]="PAIE" then pct_Paie
else if [SERVICE]="INFORMATIQUE" then pct_Info
else 0
in
taux,
//************************************************************************************
// Construction de la nouvelle colonne AUGMENT - elle sera ajoutée au RECORD
//************************************************************************************
AUGMENT=
let
augment=Number.RoundUp([SALAIRE] * TAUX_AUGMENT,2)
in
augment,
//************************************************************************************
// Construction de la nouvelle colonne NEW_SALAIRE - elle sera ajoutée au RECORD
//************************************************************************************
NEW_SALAIRE=
let
newSal=[SALAIRE] + AUGMENT
in
newSal,
//************************************************************************************
// On ajoute à l'enregistrement les nouvelles colonnes calulées ci-dessus
//************************************************************************************
FLD_DATE_AAAAMMJJ=Record.AddField(_, "DATE_AAAAMMJJ",DATE_AAAAMMJJ),
FLD_CODE_CONTRAT=Record.AddField(FLD_DATE_AAAAMMJJ, "CODE_CONTRAT",CODE_CONTRAT),
FLD_TAUX=Record.AddField(FLD_DATE_AAAAMMJJ, "PCT_AUGMENT",TAUX_AUGMENT),
FLD_AUGMENT=Record.AddField(FLD_TAUX, "MONTANT_AUGMENT",AUGMENT),
FLD_SALAIRE_2=Record.AddField(FLD_AUGMENT, "NOUVEAU SALAIRE",NEW_SALAIRE),
//************************************************************************************
// On profite de cette opération de transformation pour recommer des colonnes
//************************************************************************************
FLD_RENAME=Record.RenameFields(FLD_SALAIRE_2,{
{"NOM","NOM_SALARIE"},
{"PRENOM","PREN_SALARIE"}
}),
//************************************************************************************
// Fonction permettant d'intervenir directement sur le contenu d'un champ (colonne)
// On procède à de nouvelles transformation en direct dans les colonnes
//************************************************************************************
FLD_TRANSFORM=Record.TransformFields(
FLD_RENAME,
//*************************************************************************
// Transformation du type
//*************************************************************************
{
{"CONTRAT", Text.Proper},
{"NOM_SALARIE", Text.Upper},
{"PREN_SALARIE", Text.Proper},
{"SERVICE", Text.Proper},
//*********************************************************************
// On peut effectuer des traitements conditionnels en direct
// dans une colonne - (xSEXE)=> équivaut à each avec la variable
// - qui représente la colonne en cours de traitement
//*********************************************************************
{"SEXE", (xSEXE)=> // seconde forme du each et _
if xSEXE = "M" then "Masculin"
else if xSEXE ="F" then "Féminin"
else "Inconnu"
},
//*********************************************************************
// Intervention directement dans la colonne MLE pour un formatage
// avec la date AAAAMMJJ
//*********************************************************************
//************************************************************************************************
// Les deux syntaxes fonctionnent parfaitement
// FLD_RENAME représente le RECORD en cours de lecture
// La second a l'avantage de pouvoir adresser un nom de colonne dynamiquement via une variable
//************************************************************************************************
// {"MLE", each _ & "-" & FLD_RENAME[DATE_AAAAMMJJ]}
{"MLE", each _ & "-" & Record.Field(FLD_RENAME,"DATE_AAAAMMJJ")}
}
)
in
//***************************************************************************************
// A l'issue des transformation Rows on obtient une liste de RECORDS
//***************************************************************************************
FLD_TRANSFORM
),
//***************************************************************************************
// Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
//***************************************************************************************
TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)
in
TBLLa requête est certes un peu complexe car elle demande déjà une bonne maîtrise du langage M. C'est cependant un bon exemple pour vos futurs traitements. Il suffit de vous en inspirer comme modèle de syntaxe à employer.
Autre exemple dans le même style
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// Transformation directement dans les enregistrements RECORDS
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
//*****************************************************************************************************
// Le fait de passer par Table.TransformRows permet d'utiliser tous les champs du RECORD pour
// transformmer le champ(colonne) d'un RECORD
//*****************************************************************************************************
TBL_TRANSFORM_ROWS=Table.TransformRows(
TBL_PROMOTE,
each let
DATE_AAAAMMJJ=
let
strDay=Text.PadStart(Number.ToText(Date.Day([DATE ENTREE])),2,"0"),
strYear=Number.ToText(Date.Year([DATE ENTREE])),
strMonth=Text.PadStart(Number.ToText(Date.Month([DATE ENTREE])),2,"0"),
strDate=strYear & strMonth & strDay
in
strDate,
CODE_CONTRAT=
let
code=if [CONTRAT]="CDI" then "01"
else if [CONTRAT]="CDD" then "02"
else "99"
in
code,
FLD_DATE_AAAAMMJJ=Record.AddField(_, "DATE_AAAAMMJJ",DATE_AAAAMMJJ),
FLD_CODE_CONTRAT=Record.AddField(FLD_DATE_AAAAMMJJ, "CODE_CONTRAT",CODE_CONTRAT),
FLD_RENAME=Record.RenameFields(FLD_CODE_CONTRAT,{
{"NOM","NOM_SALARIE"},
{"PRENOM","PREN_SALARIE"}
}),
//***********************************************************************************
// Fonction permettant d'intervenir directement sur le contenu d'un champ (colonne)
//***********************************************************************************
FLD_TRANSFORM=Record.TransformFields(
FLD_RENAME,
{
//********************************************************
// On intervention directement sur le type de la colonne
//********************************************************
{"CONTRAT", Text.Proper},
{"NOM_SALARIE", Text.Upper},
{"PREN_SALARIE", Text.Proper},
{"SERVICE", Text.Proper},
//************************************************************************
// Traitement conditionnel sur la colonne SEXE directement sur la colonne
//************************************************************************
{"SEXE", (xSEXE)=>
if xSEXE = "M" then "Masculin"
else if xSEXE ="F" then "Féminin"
else "Inconnu"
},
//********************************************************
// Formtage spécifique de la colonne MLE
//********************************************************
{"MLE", (FLD_MLE)=> // seconde forme du each suivi de _
let
strPad=Text.PadStart(FLD_MLE,6,"0"),
strS1=Text.Start(strPad,2),
strS2=Text.Middle(strPad,3,2),
strS3=Text.End(strPad,2),
strMleFinal= strS1 & strS2 & strS3,
newMLE=strMleFinal & "-" & FLD_RENAME[DATE_AAAAMMJJ]
in
newMLE
}
}
)
in
FLD_TRANSFORM
),
//***************************************************************************************
// Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
//***************************************************************************************
TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)
in
TBLExemple 4
Dans cet exemple on souhaite uniquement créer une table d'index à l'issue de l'importation pour obtenir ceci
On remarque à l'issue de l'importation on ne retient que deux colonnes que l'on a transformer pendant les traitements.
Méthode en passant par les assistants
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
#"Personnalisée ajoutée" = Table.AddColumn(TBL_PROMOTE, "MLE2", each let
s1=Text.PadStart([MLE],6,"0"),
s2=Text.Start(s1,2),
s3=Text.Middle(s1,3,2),
s4=Text.End(s1,2),
S=s2 & s3 & s4
in
S),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"MLE"}),
#"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"MLE2", "MLE"}}),
#"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées",{"MLE", "NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"}),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Colonnes permutées", "Personnalisé", each [NOM] & ", " & [PRENOM]),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"NOM", "PRENOM", "SEXE", "DEPT", "AGE", "CONTRAT", "DATE ENTREE", "SERVICE", "SALAIRE"}),
#"Colonnes renommées1" = Table.RenameColumns(#"Colonnes supprimées1",{{"Personnalisé", "SALARIE"}, {"MLE", "CLEF_INDEX"}})
in
#"Colonnes renommées1"Méthode optimisée
PowerQuery:
let
//*****************************************************************************************************
// On récupère les paramètres permettant d'importer le fichier
//*****************************************************************************************************
STR_Tbl2Import=fnGetParameter("TB_PARAMS","FICHIER_RH_2_IMPORT","PARAM", "VALEUR"),
STR_Item=fnGetParameter("TB_PARAMS","ITEM_TABLE","PARAM", "VALEUR"),
STR_Kind=fnGetParameter("TB_PARAMS","ITEM_KIND","PARAM", "VALEUR"),
Source = Excel.Workbook(File.Contents(STR_Tbl2Import), null, true),
TBL_BDD_SHEET = Source{[Item=STR_Item,Kind=STR_Kind]}[Data],
TBL_PROMOTE = Table.PromoteHeaders(TBL_BDD_SHEET, [PromoteAllScalars=true]),
//*****************************************************************************************************
// Transformation directement dans les colonnes
// Ca évite de passer par une colonne temporaire afin de formater le MLE comme souhaité
// Ca évite aussi de supprimer l'ancienne colonne et de renommer la nouvelle par le nom de l'ancienne
// sachant qu'il aurait fallu replacer la nouvelle colonne en tête de la table
// on gagne ainsi 3 étapes ce qui peut être un gain en cas de forte volumétrie
// Toutes ces opérations auraient nécessité des colonnes intermédiaires à renommer après suppression
// des colonnes d'origine
//*****************************************************************************************************
TBL_TRANSFORM_COLUMNS=Table.TransformColumns(
TBL_PROMOTE,
{
{"MLE",
(xMLE)=>
let
strPad=Text.PadStart(xMLE,6,"0"),
strS1=Text.Start(strPad,2),
strS2=Text.Middle(strPad,3,2),
strS3=Text.End(strPad,2),
strMleFinal=strS1 & strS2 & strS3
in
strMleFinal,
type text},
{"DATE ENTREE",
(xDATE)=>
let
strDay=Text.PadStart(Number.ToText(Date.Day(xDATE)),2,"0"),
strYear=Number.ToText(Date.Year(xDATE)),
strMonth=Text.PadStart(Number.ToText(Date.Month(xDATE)),2,"0"),
strDate=strYear & strMonth & strDay
in
strDate,
type text},
{"CONTRAT",
(xCONTRAT)=>
let
code=if xCONTRAT="CDI" then "01"
else if xCONTRAT="CDD" then "02"
else "99"
in
code
}
}
),
//**********************************************************************************************************
// Par ce procédé on ne retient que les colonnes que l'on souhaite conserver
// à l'issue des transformations ci-dessus
// Ca éviter des étapes de suppressions de colonnes
//**********************************************************************************************************
TBL_TRANSFORM_ROWS=Table.TransformRows(TBL_TRANSFORM_COLUMNS,
each [
//**********************************************************************************************
// Création des colonnes CLEF_INDEX et SALARIE en créant directement un RECORD via le each
//**********************************************************************************************
CLEF_INDEX = [MLE] & "-" & [DATE ENTREE] & "-" & [CONTRAT],
SALARIE=[NOM] & ", " & [PRENOM]
]),
//***************************************************************************************
// Il faut transformer cette liste de RECORDS en table pour obtenir le résultat final
//***************************************************************************************
TBL=Table.FromRecords(TBL_TRANSFORM_ROWS)
in
TBL
Dernière édition:
