POWERQUERY :: List.Accumulate - Cas d'exemples concrets

[oguruma]

Ce post fait suite à celui-ci https://excel-downloads.com/threads/powerquery-les-boucles-avec-list-accumulate.20080604/
Il manquait cependant un cas d'exemple concret.
L'exemple retenu consiste à calculer un bonus en % selon le montant d'un chiffre d'affaires réalisé.

L'avantage de la solution PowerQuery par rapport à la solution formules : la requête est dynamique. Si les bornes des tranches évoluent ou d'autres tranches viennent se greffer la requête répondra toujours correctement. Dans le cas des formules il faudra intervenir et ajouter les colonnes et conditions nécessaires sauf si on s'embarque dans une formule matricielle compliquée.
Le titres des colonnes ventilées se positionnent automatiquement via PowerQuery. A contrario en mode formule il faut le prévoir.

Une liste de vendeurs fictifs avec leurs CA.

Les bonus par tranche

Version en mode formules - versus O365

La formule

=
@SIERREUR(
SI.CONDITIONS(
ET([@[Total Ventes]]>=1000;[@[Total Ventes]]<=2000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche1");[@[Total Ventes]]*Tranches!$D$2;
ET([@[Total Ventes]]>=2001;[@[Total Ventes]]<=3000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche2");[@[Total Ventes]]*Tranches!$D$3;
ET([@[Total Ventes]]>=3001;[@[Total Ventes]]<=4000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche3");[@[Total Ventes]]*Tranches!$D$4;
ET([@[Total Ventes]]>=4001;[@[Total Ventes]]<=5000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche4");[@[Total Ventes]]*Tranches!$D$5;
ET([@[Total Ventes]]>=5001;[@[Total Ventes]]<=6000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche5");[@[Total Ventes]]*Tranches!$D$6;
ET([@[Total Ventes]]>=6001;[@[Total Ventes]]<=7000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche6");[@[Total Ventes]]*Tranches!$D$7;
ET([@[Total Ventes]]>=7001;[@[Total Ventes]]<=8000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche7");[@[Total Ventes]]*Tranches!$D$8;
ET([@[Total Ventes]]>=8001;[@[Total Ventes]]<=10000;@TB_CALCULS[[#En-têtes];[Tranche1]:[Tranche8]]="Tranche8");[@[Total Ventes]]*Tranches!$D$9);
0)

Sa simplicité se passe d'explications.

Via PowerQuery

On importe la table des tranches de calculs - le code est largement documenté sur la manière de procéder.

Cet exemple peut être adapté à des calculs de remises, taxes, TVA, etc. etc..

let
    Source = Excel.CurrentWorkbook(){[Name="TB_TRANCHE"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"TRANCHE", type text}, {"MIN", Int64.Type}, {"MAX", Int64.Type}, {"TAUX", type number}})
in
    #"Type modifié"

Calcul des bonus

let
    //-----------------------------------------------------------------
    // On récupère les données sources
    //-----------------------------------------------------------------
    Source = TB_DATA,

    //-----------------------------------------------------------------
    // Ventilation des calculs par colonne - une tranche par colonne
    //-----------------------------------------------------------------
    CalculsTranches =
        let

            //----------------------------------------------------------------------
            // On récupère les données pour calculer la prime en fonction du taux
            //----------------------------------------------------------------------
            NB_TRANCHES = Table.RowCount(TB_TRANCHE)-1,
            TRANCHE=TB_TRANCHE[TRANCHE],
            BASE_TAUX = TB_TRANCHE[TAUX],
            BASE_MIN = TB_TRANCHE[MIN],
            BASE_MAX = TB_TRANCHE[MAX],

            //----------------------------------------------------
            // Détail des calculs
            //----------------------------------------------------
            Calculs = List.Accumulate(
                    //--------------------------------------------
                    // Permet de déterminer le nbr de colonnes
                    //--------------------------------------------
                    {0..NB_TRANCHES},

                    //---------------------------------------------------
                    // Initialisation sur la table Source à lire - state
                    //---------------------------------------------------
                    Source,

                    //----------------------------------------------------------
                    // Déclaration des variables nécessaires aux traitements
                    //----------------------------------------------------------
                    (state,current)=>

                        //-----------------------------------------------------------
                        // Ajout de la colonne en fonction de la tranche de calcul
                        //-----------------------------------------------------------
                        Table.AddColumn(
                            state,
                            TRANCHE{current} & " - " &
                            Number.ToText(
                                BASE_TAUX{current},
                                "0.00%"
                            ),

                            //-------------------------
                            // Boucle de traitement
                            //-------------------------
                            each
                                //-------------------------------------------------------------------------------------
                                // Etant donné que plusieurs traitements sont nécessaires on passe par un let imbriqué
                                //-------------------------------------------------------------------------------------
                                let
                                    //--------------------------------------------------------------------------------------
                                    // Record en cours de lecture
                                    // Pour des questions de lisibilité on isole le champ [TotalVentes] dans une variable
                                    //--------------------------------------------------------------------------------------
                                    Ventes = [TotalVentes],                                 
                                    //---------------------------------------------------------------------------------------------------------
                                    // Evaluation selon le montant des ventes pour calculer la prime
                                    // On boucle pour trouver la bonne tranche et on applique le taux selon l'indice contenue dans current
                                    //----------------------------------------------------------------------------------------------------------
                                    EvaluationTranche =  if Ventes >= BASE_MIN{current} and Ventes <= BASE_MAX{current}
                                                then Ventes * BASE_TAUX{current}
                                                else null
                                in
                                    // Résultat de l'évaluation de la tranche
                                    EvaluationTranche,
                              
                                // Format monétaire
                               Currency.Type
                        )
                    )
        in
            Calculs
in
    CalculsTranches