POWERQUERY :: Ajout d'une colonne avec calculs multiples & TCD à l'issue des calculs

[oguruma]

Bonjour,
Toujours suite à un projet récent concernant une certification d'applications, pour l'exemple du post on suppose que l'on reçoive un fichier texte csv comportant des résultats de traitements comme suit
DOMAINE;APPLI;CERTIF_1;CERTIF_2;CERTIF_3;OK
Domaine_1;Appli_1_1;16;21;25;57
Domaine_1;Appli_1_2;20;29;21;56
Domaine_1;Appli_1_3;16;20;20;49
Domaine_1;Appli_1_4;21;30;16;54
Domaine_1;Appli_1_5;22;17;15;45
Domaine_1;Appli_1_6;16;17;29;56
Domaine_1;Appli_1_7;29;18;17;59
Domaine_1;Appli_1_8;27;20;18;57
Domaine_1;Appli_1_9;22;29;27;64
Domaine_1;Appli_1_10;19;25;24;57
Domaine_2;Appli_2_1;17;21;29;66
Domaine_2;Appli_2_2;15;26;15;48
Domaine_2;Appli_2_3;25;23;17;51
Domaine_2;Appli_2_4;16;15;23;41
Domaine_2;Appli_2_5;27;28;16;64
Domaine_2;Appli_2_6;25;24;20;64
Domaine_2;Appli_2_7;26;27;22;66
Domaine_2;Appli_2_8;25;21;26;69
Domaine_2;Appli_2_9;19;25;20;54
Domaine_3;Appli_3_1;26;18;22;63
Domaine_3;Appli_3_2;17;26;15;48

Nous avons :
une colonne domaine
une colonne applications du domaine
une colonne nbr de traitements par phase de certification
une colonne nbr de traitement ok pour toute la période de certification

C'est juste un scénario simplifié permettant de mettre en oeuvre les calculs multiples pendant l'ajout d'une colonne.

Cible : à l'issue de l'importation du fichiers données on souhaite obtenir ceci

Soit pour une application :
- le total des traitements
- en déduire le total des traitements ko
- le % des ok
- le % des ko

Méthode 1
On importe le fichier via powerquery puis on le charge dans une feuille Excel et on effectue les calculs via des formules Excel classique
Avantage : rapide, traitements légers dans powerquery
Inconvénient : les formules sont sous Excel - à voir les performances en cas de forte volumétrie - feuille lourde

Méthode 2
On importe le fichier via powerquery on effectue les calculs dans powerquery en ajoutant les colonnes nécessaires puis on charge la requête dans une feuille Excel
Avantage : la feuille Excel est dépourvue de formules
Inconvénient : un peu plus compliqué en powerquery il faut maîtriser les ajouts de colonnes et les calculs en powerquery - une étape à chaque ajout de colonne ce qui peut ralentir les traitements

Méthode 3
On importe le fichier via powerquery on effectue les calculs dans powerquery en ajoutant les colonnes nécessaires puis on charge la requête dans une feuille Excel MAIS les calculs sont réalisés en une seule étape
Avantage : cas de la solution 2
Inconvénient : un peu plus compliqué en powerquery il faut maîtriser les ajouts de colonnes et les calculs en powerquery - cependant avec l'avantage que les calculs sont réalisés en une seule étape ce qui va améliorer les performances

Environnement PowerQuery

Importation du fichier de données
Les données sont enregistrées dans la table TBL_SRC_DATA_Traitements

let
    Source = Csv.Document(File.Contents(PRM_STR_FichierTxt),[Delimiter=";", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    TBL_Promote = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TBL_ModifTypeEntier = Table.TransformColumnTypes(TBL_Promote,{{"CERTIF_1", Int64.Type}, {"CERTIF_2", Int64.Type}, {"CERTIF_3", Int64.Type}, {"OK", Int64.Type}})
in
    TBL_ModifTypeEntier

Méthode 1 détaillée
On charge la table via la requête

let
    Source = TBL_SRC_DATA_Traitements
in
    Source

La table des données une fois chargée on écrits les formules dans Excel
TOTAL=SOMME(QRY_Traitements_V1[@[CERTIF_1]:[CERTIF_3]])
KO=[@TOTAL]-[@OK]
PCT_OK=[@OK]/[@TOTAL]
PCT_KO=[@KO]/[@TOTAL]
ALERTE==SI([@[PCT_KO]]>=0.1;"OUI";"NON")

Méthode 2 détaillée

let
    Source = TBL_SRC_DATA_Traitements,
    TBl_AddColumnTotal = Table.AddColumn(Source, "TOTAL", each [CERTIF_1] + [CERTIF_2] + [CERTIF_3]),
    TBl_AddColumnKO = Table.AddColumn(TBl_AddColumnTotal, "KO", each [TOTAL] - [OK]),
    TBl_AddColumnPCTOK = Table.AddColumn(TBl_AddColumnKO, "PCT_OK", each [OK]/[TOTAL]),
    TBl_AddColumnPCTKO = Table.AddColumn(TBl_AddColumnPCTOK, "PCT_KO", each [KO]/[TOTAL]),
    TBl_AddColumnAlerte = Table.AddColumn(TBl_AddColumnPCTKO, "ALERTE", each if [PCT_KO] >= 0.1 then "OUI" else "NON"),
    TBL_ReorderColumns = Table.ReorderColumns(TBl_AddColumnAlerte,{"DOMAINE", "APPLI", "CERTIF_1", "CERTIF_2", "CERTIF_3", "TOTAL", "OK", "KO", "PCT_OK", "PCT_KO", "ALERTE"})
in
    TBL_ReorderColumns

Jusque-là rien de compliqué. Il suffit d'utiliser les assistants.

Méthode 3 détaillée

let
    Source = TBL_SRC_DATA_Traitements,
    TBL_AddCalculs=Table.AddColumn(Source, "ComputeColumns",
                        each [
                                TOTAL=[CERTIF_1] + [CERTIF_2] + [CERTIF_3],
                                KO= TOTAL - [OK],
                                PCT_OK=[OK]/TOTAL,
                                PCT_KO=KO/TOTAL,
                                ALERTE=if PCT_KO>=0.1 then "OUI" else "NON"
                        ]
                    ),
    TBL_ComputeColumns = Table.ExpandRecordColumn(TBL_AddCalculs, "ComputeColumns", {"TOTAL", "KO", "PCT_OK", "PCT_KO", "ALERTE"}, {"TOTAL", "KO", "PCT_OK", "PCT_KO", "ALERTE"}),
    TBL_ReorderColumns = Table.ReorderColumns(TBL_ComputeColumns,{"DOMAINE", "APPLI", "CERTIF_1", "CERTIF_2", "CERTIF_3", "TOTAL", "OK", "KO", "PCT_OK", "PCT_KO", "ALERTE"})
in
    TBL_ReorderColumns

La difficulté se trouve ici

each [

                                TOTAL=[CERTIF_1] + [CERTIF_2] + [CERTIF_3],

                                KO= TOTAL - [OK],

                                PCT_OK=[OK]/TOTAL,

                                PCT_KO=KO/TOTAL,

                                ALERTE=if PCT_KO>=0.1 then "OUI" else "NON"

                        ]

Voici les étapes

L'étape TBL_AddCalculs va créer en fait un RECORD pour chaque enregistrement.

Détail du RECORD

Il suffit à l'issue de développer les RECORDs de chaque enregistrement. C'est l'étape TBL_ComputeColumns
Résultat

Et il ne reste plus qu'à charge la table dans une feuille de calculs.

MISE EN OEUVRE DES TCD

3 méthodes pour y parvenir.

Méthode 1
La cible étant d'obtenir cette présentation

let
    Source = QRY_Traitements_V3,
   // #"Lignes groupées" = Table.Group(Source, {"DOMAINE"}, {{"Nombre", each Table.RowCount(_), Int64.Type}}),
    TBL_TCCD=Table.Group(Source,{"DOMAINE"},
                {
                    {"Total traitements", each List.Sum([TOTAL]), type nullable number},
                    {"Total OK", each List.Sum([OK]), type nullable number},
                    {"Total KO", each List.Sum([KO]), type nullable number},
                    {"Pct OK", each List.Sum([OK])/List.Sum([TOTAL]), type nullable number},
                    {"Pct KO", each List.Sum([KO])/List.Sum([TOTAL]), type nullable number},
                    {"Applications", each Text.Combine(List.Distinct([APPLI]), "#(cr)#(lf)")}
                }
 
    ),
    TBL_AddColumnAlerte = Table.AddColumn(TBL_TCCD, "Alerte", each if [Pct KO] >= 0.1 then "OUI" else "NON")

in
    TBL_AddColumnAlerte

Cette solution comporte un inconvénient majeur : List.Sum([TOTAL]) est effectué 3 fois - ce qui peut avoir un impact sur les performances.
Notez au passage la manière de provoquer un CR/LF (Windows) "#(cr)#(lf)". Attention pour cela il faut activer cette option sous Excel :

Méthode 2
A travers cette méthode on va utiliser l'astuce des calculs multiples dans une colonne.

let
    Source = QRY_Traitements_V3,
    TBL_TCCD=Table.Group(Source,{"DOMAINE"},
            {
                {"Total traitements", each List.Sum([TOTAL]), type nullable number},
                {"Total OK", each List.Sum([OK]), type nullable number},
                {"Total KO", each List.Sum([KO]), type nullable number},
                {"Applications", each Text.Combine(List.Distinct([APPLI]), "#(cr)#(lf)")}
            }
 
    ),
    TBL_AddCalculs=Table.AddColumn(TBL_TCCD, "ComputeColumns",
            each [
                    PCT_OK=[Total OK]/[Total traitements],
                    PCT_KO=[Total KO]/[Total traitements],
                    ALERTE=if PCT_KO>=0.1 then "OUI" else "NON"
            ]

    ),
    TBL_ExpandRecord = Table.ExpandRecordColumn(TBL_AddCalculs, "ComputeColumns", {"PCT_OK", "PCT_KO", "ALERTE"}, {"PCT_OK", "PCT_KO", "ALERTE"})
in
    TBL_ExpandRecord

Les calculs multiples sont ici

            each [

                    PCT_OK=[Total OK]/[Total traitements],

                    PCT_KO=[Total KO]/[Total traitements],

                    ALERTE=if PCT_KO>=0.1 then "OUI" else "NON"

            ]

On réutilise [Total OK] calculé à l'étape précédente !

Et enfin si on souhaite avoir une synthèse par domaine

let
    Source = QRY_Traitements_V3,
    TBL_TCCD=Table.Group(Source,{"DOMAINE"},
            {
                {"Total traitements", each List.Sum([TOTAL]), type nullable number},
                {"Total OK", each List.Sum([OK]), type nullable number},
                {"Total KO", each List.Sum([KO]), type nullable number}
            }
 
    ),
    TBL_AddCalculs=Table.AddColumn(TBL_TCCD, "ComputeColumns",
            each [
                    PCT_OK=[Total OK]/[Total traitements],
                    PCT_KO=[Total KO]/[Total traitements],
                    ALERTE=if PCT_KO>=0.1 then "OUI" else "NON"
            ]

    ),
    TBL_ExpandRecord = Table.ExpandRecordColumn(TBL_AddCalculs, "ComputeColumns", {"PCT_OK", "PCT_KO", "ALERTE"}, {"PCT_OK", "PCT_KO", "ALERTE"})
in
    TBL_ExpandRecord

Pour conclure, à vous selon cet exemple d'adapter vos requêtes PWQ pour obtenir ces résultats.