SOMMEPROD dans Power Query

Bonjour,

J'ai un tableau de ce genre :

Et un autre avec des coefficients :

Mon but est d'obtenir avec Power Query une somme pondérée dans une nouvelle colonne genre SOMMEPROD(ligneScorenom1; TRANSPOSE(coeff))

Dans la réalité, le tableau de score peut comporter plusieurs centaines de lignes et surtout 20 à 25 colonnes.
Il me faudrait donc une solution clean pour ne pas être trop lente.

À vous de jouer !

Bonjour à tous,

Une approche via les Records car apparemment ils seraient l'outil le plus performant pour ce type de tache. Unpivot + join sur de gros tableaux c'est vrai que ça explose vite.

NB1 : solution IA "tweakée".

NB2 : si les tableaux sont typés en amont, pas besoin des étapes 1 et 5.

let
    fnPondereTable = (t_base as table, t_coefs as table) as table =>
    let
        // 1. Nettoyage et typage des coefficients
        CoeffsTyped = Table.TransformColumnTypes(t_coefs, {{"Score", type text}, {"Coeff", type number}}),

        // 2. Création d'un dictionnaire (Record) pour un accès ultra-rapide
        // On transforme le tableau Coeff en [Score1 = 1, Score2 = 2, ...]
        CoeffRecord = Record.FromTable(
            Table.RenameColumns(
                Table.SelectColumns(CoeffsTyped, {"Score", "Coeff"}), 
                {{"Score", "Name"}, {"Coeff", "Value"}}
            )
        ),

        // 3. Identification des colonnes présentes dans les deux tables
        BaseCols   = Table.ColumnNames(t_base),
        ScoreCols  = List.Intersect({BaseCols, Record.FieldNames(CoeffRecord)}),

        // 4. Création de la liste des coefficients alignée sur ScoreCols
        // Buffer pour éviter réévalutation dans 6. Produit scalaire
        L_Coeffs = List.Buffer(
            List.Transform(
                ScoreCols, 
                each Record.FieldOrDefault(CoeffRecord, _, 0)
            )
        ),

        // 5. Typage de la table de base (essentiel pour la performance du calcul)
        BaseTyped = Table.TransformColumnTypes(t_base, List.Transform(ScoreCols, each {_, type number})),

        // 6. Logique de calcul par ligne
        CalculSommePonderee = (r as record) as number =>
            let
                // On extrait les valeurs de la ligne pour les colonnes de score uniquement
                ValeursLigne = Record.ToList(Record.SelectFields(r, ScoreCols)),
                // Remplacement des nulls par 0
                ValeursNettes = List.Transform(ValeursLigne, each if _ = null then 0 else _),
                // Produit scalaire : Somme de (Valeur * Coeff)
                Produits = List.Zip({ValeursNettes, L_Coeffs}),
                Somme    = List.Sum(List.Transform(Produits, each _{0} * _{1}))
            in
                Somme,

        // 7. Ajout de la colonne de résultat
        Resultat = Table.AddColumn(
            BaseTyped, 
            "Somme pondérée", 
            each CalculSommePonderee(_), 
            type number
        )
    in
        Resultat
in
    fnPondereTable

Exemple d'appel :

let
    res = fnPondereTable(
        Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
    )
in
    res

Bonjour

Je confirme que dépivot/fusion n'est pas adapté s'il y a beaucoup de colonnes/lignes

Une remarque sur la proposition de saboh12617 :

l'étape ValeursNettes = List.Transform(ValeursLigne, each if _ = null then 0 else _), n'est pas nécessaire si ensuite on utilise each _{0} * _{1} dans le List.Transform de l'étape Somme car null * une valeur = null

elle aurait été nécessaire si on avait utilisé List.Product à la place de each _{0} * _{1}

Sinon, sur le principe je trouve ce type de code très bien mais pas adapté à la grande majorité des utilisateurs qui ne vont pas comprendre le principe

Si vos données Score1 à 20 ou 25 sont toujours à partir de la colonne 2 (en gros qu'il n'y a que la colonne "Nom" en premier") et que l'ordre des scores est bien le même dans le Tableau2, cela revient à

= Table.AddColumn(Source, "SommeProd",
 each List.Sum(
    List.Transform(
        List.Zip({
           List.RemoveFirstN(
             Record.ToList(_),
             1),
           Tableau2[Coeff]}),
     each _{0}*_{1})), 
type number)

• Record.ToList pour transformer la ligne actuelle (un record) en liste
• List.RemoveFirstN pour retirer le 1er élément (le nom) - à adapter au besoin
• List.Zip pour créer une liste de liste avec à chaque fois le score et le coefficient correspondant dans le Tableau2 (mettre Tableau2[Coeff] en buffer avec List.Buffer)
• List.Transform pour multiplier le score et le coeff de toutes les sous-listes du List.Zip
• List.Sum pour additionner le tout

Stéphane

Bonjour à tous !

Une proposition ( esprit identique avec les propositions précédentes) :

let
    fnTbl = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
    tCoef = List.Buffer(Table.ToRows(fnTbl("tCoeff"))), 
    Record = Table.AddColumn(
        fnTbl("tScores"), 
        "Total", 
        each 
            let
                r = Record.RemoveFields(_, "Nom"), 
                z = List.Zip(
                    {
                        List.ReplaceMatchingItems(Record.FieldNames(r), tCoef), 
                        List.Transform(Record.ToList(r), each _ ?? 0)
                    }
                )
            in
                List.Sum(List.Transform(z, List.Product))
    )
in
    Record

Sur la base de 2 tableaux structurés (tScores et tCoeff).

reBonjour à tous,

Merci pour ton retour Stéphane, effectivement puisque Sum ignore les nulls l'étape est un peu redondante mais attention au cas (très extreme il est vrai) d'une colonne de null auquel cas la somme reste null. Je pense qu'un bon compromis serait (_{0} ?? 0) * _{1}(dans l'idée de JFL) pour gérer ce cas de bord.

Sur ta proposition, en termes de performances, il me semble que puisque Tableau2[Coeff] se trouve dans le each, il sera réévalué pour chaque ligne de la Source. Ou bien PQ est assez intelligent pour le garder en mémoire ?

En tout cas belle synthèse si on se concentre sur l'efficacité en se fiant à la mise en page des tableaux.

(un petit mot pour) JFL, je trouve toujours tes approches élégantes… et originales. Bravo

ReBonjour,

Si vos données Score1 à 20 ou 25 sont toujours à partir de la colonne 2 (en gros qu'il n'y a que la colonne "Nom" en premier") et que l'ordre des scores est bien le même dans le Tableau2

C'est effectivement le cas. Les coefficients sont dans l'ordre des colonnes.

Sur ta proposition, en termes de performances, il me semble que puisque Tableau2[Coeff] se trouve dans le each, il sera réévalué pour chaque ligne de la Source

Je vais "bufferiser" la liste des coefficients en amont et utiliser le résultat de cette opération dans le each.

Merci à vous trois, je vais utiliser la proposition de Stéphane pour l'instant.
Ma table est minuscule pour l'instant, mais appelée à grossir par la suite, donc je n'ai pas testé la vitesse des différentes propositions.

A+

Re-

En effet, la "bufferisation" ou "mise en mémoire" pour éviter l'anglicisme est nécessaire si on commence à avoir beaucoup de lignes pour ne pas réévaluer la liste des coefficients à chaque fois.

Précision pour ceux qui souhaite apprendre les subtilités de Power Query : concernant l'opérateur coalesce ??( a ?? b est équivalent à if a = null then b else a), la question ici est de savoir si on remplace les null par des 0 pour les multiplier par le coeff avant de les additionner (ce qui est nécessaire avec le List.Product proposé par JFL car null * 3 = null mais List.Product({null,3}) = 3)

Enfin, si la table est très volumineuse (vraiment beaucoup de lignes) mais très "creuse" (c'est à dire qu'il y a beaucoup de null et peu de valeur), le dépivot/fusion/pivot peut s'essayer en terme de performance pour éviter de faire un SOMMEPROD sur des milliers/millions de null - A tester !

Stéphane

Bonjour à tous !

@OOoiste : Merci de ce retour !

@saboh12617 : Que voilà de bien sympathiques mots ... Pas certain de les mériter. Merci merci !