POWERQUERY :: Importation d'un fichier comportant des enregistrements de longueur fixe et les découper selon une structure définie

[oguruma]

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

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
    fnGetManyParameters

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

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

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