oguruma
XLDnaute Occasionnel
Bonjour,
ce post est un peu dans la "même veine" que ce post https://excel-downloads.com/threads...-multiples-tcd-a-lissue-des-calculs.20083900/ sur la manière finale de procéder.
Il arrive bien souvent quand on reçoit des données d'un site central alias mainframe (eg. IBM 4381, 3090) [un de mes passages dans l'informatique Mainframe COBOL/DB2/CICS/JCL/TSO et j'en passe) que la structure des enregistrements soit de cette nature (nous restons ici dans le contexte d'une certification d'applications):
Domaine_1Appli_1_1484017055
Domaine_1Appli_1_2356065438
Domaine_1Appli_1_3029382493
Domaine_1Appli_1_4257214471
Domaine_1Appli_1_5385133010
Cette structure d'enregistrement dans le monde mainframe s'appelle encore "Fixe Bloqué" (pour les anciens
L'idée est donc d'obtenir après importation et transformation ceci :
A noter que le total dans l'enregistrement n'est pas fourni. Il faudra donc créer la colonne et le calculer.
Je passe volontairement la faisabilité sous Excel. Ce n'est pas l'objet de ce post.
Pour cela on prévoit côté Excel les paramètres suivants :
l'emplacement du fichier à importer
La définition de l'enregistrement
Cette table sera récupérée dans PowerQuery.
Environnement PowerQuery
La structure de l'enregistrement est chargée dans les listes paramètres
Pour cela une petite fonction fnGetManyParameters chargée de lire cette table pour chaque rubrique
Exemple pour un paramètre
Pour PRM_LST_APPLI tout comme les autres on a donc une liste :
1er élément (indice {0}) : la longueur de la zone
2ème élément (indice {1}) : le début de la zone
3ème élément (indice {2}) : la fin de la zone
Pour parvenir au résultat souhaité 3 méthodes :
- méthode 1 : totalement via les assistants en ajoutant autant de colonnes que de rubriques à créer puis transformer chaque colonne ajouter en découpant la colonne "Column1" contenant l'enregistrement. Je passe volontairement sur celle-ci. Il suffit de suivre pas à pas l'assistant.
- méthode 2 : cette méthode revient à ajouter d'une manière plus propre les colonnes nécessaires. On éviter l'ajout de colonnes et la nécessité de les retravailler une à une. On gagne ainsi des étapes
- méthode 3 : on applique le procédé des traitements multiples au moment de l'ajout d'une colonne (cf. lien en introduction de ce post) - une autre manière de le mettre en pratique
ce post est un peu dans la "même veine" que ce post https://excel-downloads.com/threads...-multiples-tcd-a-lissue-des-calculs.20083900/ sur la manière finale de procéder.
Il arrive bien souvent quand on reçoit des données d'un site central alias mainframe (eg. IBM 4381, 3090) [un de mes passages dans l'informatique Mainframe COBOL/DB2/CICS/JCL/TSO et j'en passe) que la structure des enregistrements soit de cette nature (nous restons ici dans le contexte d'une certification d'applications):
Domaine_1Appli_1_1484017055
Domaine_1Appli_1_2356065438
Domaine_1Appli_1_3029382493
Domaine_1Appli_1_4257214471
Domaine_1Appli_1_5385133010
Cette structure d'enregistrement dans le monde mainframe s'appelle encore "Fixe Bloqué" (pour les anciens
L'idée est donc d'obtenir après importation et transformation ceci :
A noter que le total dans l'enregistrement n'est pas fourni. Il faudra donc créer la colonne et le calculer.
Je passe volontairement la faisabilité sous Excel. Ce n'est pas l'objet de ce post.
Pour cela on prévoit côté Excel les paramètres suivants :
l'emplacement du fichier à importer
La définition de l'enregistrement
Cette table sera récupérée dans PowerQuery.
Environnement PowerQuery
La structure de l'enregistrement est chargée dans les listes paramètres
Pour cela une petite fonction fnGetManyParameters chargée de lire cette table pour chaque rubrique
PowerQuery:
let fnGetManyParameters =
(
pTable as any,
pName as text,
optional pParam as text,
optional pVal1 as text,
optional pVal2 as text,
optional pVal3 as text
) as any =>
let
ParamSource = if pTable is text then
Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else pTable,
Parametre = if pParam <> null then
pParam
else "PARAMETRE",
Valeur1 = if pVal1 <> null then
pVal1
else "VALEUR1",
Valeur2 = if pVal2 <> null then
pVal2
else "VALEUR2",
Valeur3 = if pVal2 <> null then
pVal3
else "VALEUR3",
StrParam="each ([" & Parametre & "]) = " & """" & pName & """",
EvalParam=Expression.Evaluate(StrParam,[ParamSource=ParamSource]),
ParamRow = Table.SelectRows(ParamSource, EvalParam),
Value1= if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},Valeur1),
Value2= if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},Valeur2),
Value3= if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},Valeur3),
LST_Values={Value1, Value2, Value3}
in
LST_Values
in
fnGetManyParametersExemple pour un paramètre
Pour PRM_LST_APPLI tout comme les autres on a donc une liste :
1er élément (indice {0}) : la longueur de la zone
2ème élément (indice {1}) : le début de la zone
3ème élément (indice {2}) : la fin de la zone
Pour parvenir au résultat souhaité 3 méthodes :
- méthode 1 : totalement via les assistants en ajoutant autant de colonnes que de rubriques à créer puis transformer chaque colonne ajouter en découpant la colonne "Column1" contenant l'enregistrement. Je passe volontairement sur celle-ci. Il suffit de suivre pas à pas l'assistant.
- méthode 2 : cette méthode revient à ajouter d'une manière plus propre les colonnes nécessaires. On éviter l'ajout de colonnes et la nécessité de les retravailler une à une. On gagne ainsi des étapes
PowerQuery:
let
Source = TBL_SRC_DATA_TraitementsSEQ,
//***************************************************************************************************************************
// Structure de l'enregistrement à découper
//***************************************************************************************************************************
P_LST_DOMAINE=PRM_LST_DOMAINE,
P_LST_APPLI=PRM_LST_APPLI,
P_LST_CERTIF1=PRM_LST_CERTIF1,
P_LST_CERTIF2=PRM_LST_CERTIF2,
P_LST_CERTIF3=PRM_LST_CERTIF3,
//***************************************************************************************************************************
// On découpe l'enregistrement en ajoutant les colonnes une à une
//***************************************************************************************************************************
TBL_AddColumnDomaine=Table.AddColumn(Source, "DOMAINE", each Text.Start([Column1],P_LST_DOMAINE{0})),
TBL_AddColumnAppli=Table.AddColumn(TBL_AddColumnDomaine, "APPLI", each Text.Range([Column1],P_LST_APPLI{1}-1,P_LST_APPLI{0})),
TBL_AddColumnCert1=Table.AddColumn(TBL_AddColumnAppli, "CERTIF_1", each Number.FromText(Text.Range([Column1],P_LST_CERTIF1{1}-1,P_LST_CERTIF1{0})), Int64.Type),
TBL_AddColumnCert2=Table.AddColumn(TBL_AddColumnCert1, "CERTIF_2", each Number.FromText(Text.Range([Column1],P_LST_CERTIF2{1}-1,P_LST_CERTIF2{0})), Int64.Type),
TBL_AddColumnCert3=Table.AddColumn(TBL_AddColumnCert2, "CERTIF_3", each Number.FromText(Text.End([Column1],P_LST_CERTIF3{0})), Int64.Type),
TBL_AddColumnTotal=Table.AddColumn(TBL_AddColumnCert3, "TOTAL", each [CERTIF_1] + [CERTIF_2] +[CERTIF_3], Int64.Type),
//***************************************************************************************************************************
// La colonne source à découper n'est plus nécessaire
//***************************************************************************************************************************
TBL_SupprColumnEnregi = Table.RemoveColumns(TBL_AddColumnTotal,{"Column1"})
in
TBL_SupprColumnEnregi- méthode 3 : on applique le procédé des traitements multiples au moment de l'ajout d'une colonne (cf. lien en introduction de ce post) - une autre manière de le mettre en pratique
PowerQuery:
let
Source = TBL_SRC_DATA_TraitementsSEQ,
//***************************************************************************************************************************
// On récupère les paramètres qui permettent de découper l'enregistrement
//***************************************************************************************************************************
P_LST_DOMAINE=PRM_LST_DOMAINE,
P_LST_APPLI=PRM_LST_APPLI,
P_LST_CERTIF1=PRM_LST_CERTIF1,
P_LST_CERTIF2=PRM_LST_CERTIF2,
P_LST_CERTIF3=PRM_LST_CERTIF3,
//***************************************************************************************************************************
// On découpe l'enregistrement
//***************************************************************************************************************************
TBL_AddSplitEnregi=Table.AddColumn(Source, "tmp_SPLI_ENREGI",
each
[
DOMAINE = Text.Start([Column1],P_LST_DOMAINE{0}),
APPLI = Text.Range([Column1],P_LST_APPLI{1}-1,P_LST_APPLI{0}),
CERTIF_1 = Number.FromText(Text.Range([Column1],P_LST_CERTIF1{1}-1,P_LST_CERTIF1{0})),
CERTIF_2 = Number.FromText(Text.Range([Column1],P_LST_CERTIF2{1}-1,P_LST_CERTIF2{0})),
CERTIF_3 = Number.FromText(Text.End([Column1],P_LST_CERTIF3{0})),
TOTAL = CERTIF_1 + CERTIF_2 + CERTIF_3
]
),
//***************************************************************************************************************************
// On découpe chaque record en colonnes
//***************************************************************************************************************************
TBL_ExpandColumns = Table.ExpandRecordColumn(TBL_AddSplitEnregi, "tmp_SPLI_ENREGI",
{"DOMAINE", "APPLI", "CERTIF_1", "CERTIF_2", "CERTIF_3", "TOTAL"},
{"DOMAINE", "APPLI", "CERTIF_1", "CERTIF_2", "CERTIF_3", "TOTAL"}),
//***************************************************************************************************************************
// La colonne contenant l'enregistrement à découper n'est plus utile
//***************************************************************************************************************************
TBL_SupprColumnEnregi = Table.RemoveColumns(TBL_ExpandColumns,{"Column1"})
in
TBL_SupprColumnEnregi