POWERQUERY :: Totaliser les colonnes d'un tableau - 3 méthodes

[oguruma]

Bonjour à tous,
j'avais déjà publié dans le passé une petite fonction. Je reviens sur ce sujet en apportant quelques variantes.

Une fois de plus ces fonctions ont aussi un intérêt pédagogique pour ceux et celles qui souhaitent franchir le pas sur PowerQuery

Fonction 1 : fnTotalizeAllColumnsByMonths
Elle permet de totaliser les colonnes d'un tableau du style

Donc une colonne libellé puis les autres sont les mois. Quelque soit la casse dans le noms des mois... le code s'y retrouve car les titres des colonnes sont tous transformés en majuscules afin d'établir la bonne correspondance entre la ligne titre et la ligne total qui est construite dans une table annexe fusionnée à la table des données à l'issue.

(Nous avons régulièrement ce genre de tableau)

pour obtenir ceci comme suit

let
   Tbl = "TB_DATA_21",
   Totalize=fnTotalizeAllColumnsByMonths(Tbl,"Total " & Tbl)
in
    Totalize

/*
Cette fonction permet d'effectuer les totaux des colonnes de janvier à décembre par exemple
 Libellé | Mois1 | Mois2 | Mois3 | Mois4 | Mois5 | Mois6 | Mois7 |... etc. ...
*/

let TotalizeAllColumns = (
   pSource as any,
   optional pTitleTotal as text,
   optional pBuff as logical
   ) =>

   let

      //----------------------------------------------------------------------------------------------------
      // On ajuste les paramètres
      //----------------------------------------------------------------------------------------------------
      TitleTotal =  if pTitleTotal is null then "Total" else pTitleTotal,
      bBuff = if pBuff is null then false else pBuff,
      START_COLUMN=0,
      START_MONTH=0,
      END_MONTH=11,
   //   StartColumn=0,

      //----------------------------------------------------------------------------------------------------
      // Table à totaliser
      // Structure : Colonne libellé, et une colonne pour chaque mois
      // Libellé | Janvier | Février | Mars | Avril | Mai | Juin | Juillet |... etc. ...
      //----------------------------------------------------------------------------------------------------
      Source = if pSource is text
               then Excel.CurrentWorkbook(){[Name = pSource]}[Content]
               else pSource,

      //----------------------------------------------------------------------------------------------------
      // On va créer une liste standard des mois que l'on va forcer en majuscules
      // De janvier à décembre {0..11}
      //----------------------------------------------------------------------------------------------------
      MonthsNameList = List.Transform(
         {START_MONTH..END_MONTH},
         each Text.Upper(Date.ToText(#date(1900, 1, 1) + Duration.From(_ * 31), "MMMM", "fr-FR"))
      ),

      //----------------------------------------------------------------------------------------------------
      // On récupère la liste des colonnes, noms des mois téls qui sont écrits (libellés d'origine)
      //----------------------------------------------------------------------------------------------------
      ColumnNamesSource=Table.ColumnNames(Source),

      //----------------------------------------------------------------------------------------------------
      // On va transformer la liste des noms colonnes en majuscules et les mémoriser dans une liste dédiée
      // les noms des colonnes d'origine sont transformés en majuscule
      //----------------------------------------------------------------------------------------------------
      ColumnNamesUpper=List.Buffer(List.Transform(ColumnNamesSource, each Text.Upper(_))),

      //----------------------------------------------------------------------------------------------------
      // On renome dans une table temporaire les noms de colonnes en majuscules ainsi on est cohérent
      //----------------------------------------------------------------------------------------------------
      Source2= if bBuff
               then Table.Buffer(Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})) )
               else Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})),

      //----------------------------------------------------------------------------------------------------
      //On effectue l'intersection des noms de colonne à totaliser
      //----------------------------------------------------------------------------------------------------
      IntersectHeaders = List.Buffer(List.Intersect({ColumnNamesUpper, MonthsNameList})),

      //----------------------------------------------------------------------------------------------------
      // On récupère le nom de la 1ère colonne qui sert de titre pour chaque ligne
      //----------------------------------------------------------------------------------------------------
      FirstColumn = Table.FromColumns({{TitleTotal}}, {ColumnNamesUpper{START_COLUMN}}),
     
      //----------------------------------------------------------------------------------------------------
      // On effectue le total de chaque colonne via une boucle
      // A l'issue une table est créée avec uniquement la ligne total
      //----------------------------------------------------------------------------------------------------
      AccumulateTable = List.Accumulate(
         IntersectHeaders,
         FirstColumn,
         (state, current) =>
         Table.AddColumn(state, current, each List.Sum(Table.Column(Source2, current)), type number)
      ),

      //--------------------------------------------------------------------------------------------------------
      // On fusionne la table temporaire avec ses noms de colonnes renimmés à la table contenant la ligne total
      // puis remet les noms de colonne d'origine
      //--------------------------------------------------------------------------------------------------------
      ToTablesFinal = Table.RenameColumns(Table.Combine({Source2, AccumulateTable}), List.Zip({ColumnNamesUpper,ColumnNamesSource}))

   in
      ToTablesFinal
in
   TotalizeAllColumns

ou ceci

let
   Tbl = "TB_DATA_23",
   Totalize=fnTotalizeAllColumnsByMonths(Tbl,"Total " & Tbl,true)
in
    Totalize

On remarque que l'on est pas contraint d'avoir tous les mois de l'année, le code s'ajuste automatiquement - voir les commentaires à l'intérieur

A propos des paramètres
pSource as any, ==> tableau à traiter
optional pTitleTotal as text, ==> titre de la ligne total (par défaut Total)
optional pBuff as logical ==> souhaite-t-on traiter la table dans un buffer... selon la volumétrie et la mémoire ?

Voir le fichier exemple qui sera joint.

Fonction 2 : fnTotalizeAllColumnsByAny

Cette fonction permet de traiter n'importe quel type de tableau y compris un tableau comme celui ci-dessus

let TotalizeAllColumnByAny = (
   pSource as any,
   optional pTitleTotal as text,
   optional pShiftColumnNumber as number,
   optional pBuff as logical
   ) =>

   let
 
      TitleTotal =  if pTitleTotal is null then "Total" else pTitleTotal,
      ShiftColumnNumber = if pShiftColumnNumber is null then 1 else pShiftColumnNumber,
      bBuff = if pBuff is null then false else pBuff,
      START_COLUMN=0,

      //----------------------------------------------------------------------------------------------------
      // Table à totaliser
      //----------------------------------------------------------------------------------------------------
      Source = if pSource is text
               then Excel.CurrentWorkbook(){[Name = pSource]}[Content]
               else pSource,

      //----------------------------------------------------------------------------------------------------
      // On récupère la liste des colonnes, noms des mois téls qui sont écrits
      //----------------------------------------------------------------------------------------------------
      ColumnNamesSource=Table.ColumnNames(Source),          
      NbColumns=List.Count(ColumnNamesSource),
      EndColumnsList=List.Transform(
                     List.LastN(ColumnNamesSource,NbColumns - ShiftColumnNumber), each Text.Upper(_)),      

      //----------------------------------------------------------------------------------------------------
      // On va transformer la liste des noms colonnes en majuscules et les mémoriser dans une liste dédiée
      //----------------------------------------------------------------------------------------------------
      ColumnNamesUpper=List.Buffer(List.Transform(ColumnNamesSource, each Text.Upper(_))),

      //----------------------------------------------------------------------------------------------------
      // On renome dans une table temporaire les noms de colonnes en majuscules ainsi on est cohérent
      //----------------------------------------------------------------------------------------------------
      Source2= if bBuff
               then Table.Buffer(Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})))
               else Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})),

      //----------------------------------------------------------------------------------------------------
      //On effectue l'intersection des noms de colonne
      //----------------------------------------------------------------------------------------------------
      IntersectHeaders = List.Buffer(List.Intersect({ColumnNamesUpper, EndColumnsList})),

      //----------------------------------------------------------------------------------------------------
      // On récupère le nom de la 1ère colonne qui sert de titre pour chaque ligne
      //----------------------------------------------------------------------------------------------------
      FirstColumn = Table.FromColumns({{TitleTotal}}, {ColumnNamesUpper{START_COLUMN}}),
     
      //----------------------------------------------------------------------------------------------------
      // On effectue le total de chaque colonne via une boucle
      // A l'issue une table est créée avec uniquement la ligne total
      //----------------------------------------------------------------------------------------------------
      AccumulateTable = List.Accumulate(
         IntersectHeaders,
         FirstColumn,
         (state, current) =>
         Table.AddColumn(state, current, each List.Sum(Table.Column(Source2, current)), type number)
      ),

      //--------------------------------------------------------------------------------------------------------
      // On fusionne la table temporaire avec ses noms de colonnes renimmés à la table contenant la ligne total
      // puis remet les noms de colonne d'origine
      //--------------------------------------------------------------------------------------------------------
      ToTablesFinal = Table.RenameColumns(Table.Combine({Source2, AccumulateTable}), List.Zip({ColumnNamesUpper,ColumnNamesSource}))

   in
      ToTablesFinal
in
   TotalizeAllColumnByAny

Les différents appels de cette fonction

let
   Tbl = "TB_DATA_2",
   Totalize=fnTotalizeAllColumnsByAny(Tbl,"Total " & Tbl)
in
    Totalize
   

let
   Tbl = "TB_DATA_21",
   Totalize=fnTotalizeAllColumnsByAny(Tbl,"Total " & Tbl,2,true)
in
    Totalize
   
let
   Tbl = "TB_DATA_22",
   Totalize=fnTotalizeAllColumnsByAny(Tbl,"Total " & Tbl,3,true)
in
    Totalize
   

let
   Tbl = "TB_DATA_4",
   Totalize=fnTotalizeAllColumnsByAny(Tbl,"Total " & Tbl,3,false)
in
    Totalize

Les paramètres

pSource as any, ==> table à traiter
optional pTitleTotal as text, ==> ligne titre
optional pShiftColumnNumber as number, ==> nombre de colonne à décaler, à partir de quelle colonne il faut faire les totaux
optional pBuff as logical ==> on bufferise ou pas ?

Fonction 3 : fnTotalizeAllColumnsGroupbyElse
Variante via une agrégation de colonne en passant une fonction afin de réaliser un groupby sur celles-ci... je vous laisse découvrir
elle donne les mêmes résultats que les deux fonctions précédentes.

let
   TotalizeAllColumnsGroupbyElse = (
      pSource as any,
      optional pTitleTotal as text,
      optional pShiftColumnNumber as number,
      optional pBuff as logical
   ) =>

      let
       
            TitleTotal =  if pTitleTotal is null then "Total" else pTitleTotal,
            ShiftColumnNumber = if pShiftColumnNumber is null or pShiftColumnNumber = 0 then 1 else pShiftColumnNumber,
            bBuff = if pBuff is null then false else pBuff,
            StartColumn=0,

            //----------------------------------------------------------------------------------------------------
            // Table à totaliser
            //----------------------------------------------------------------------------------------------------
            Source = if pSource is text
                     then Excel.CurrentWorkbook(){[Name = pSource]}[Content]
                     else pSource,

            //----------------------------------------------------------------------------------------------------
            // On récupère la liste des noms de colonne d'origine
            //----------------------------------------------------------------------------------------------------
            ColumnNamesSource=Table.ColumnNames(Source),          

            //----------------------------------------------------------------------------------------------------
            // On récupère le nombre de colonnes de la source
            //----------------------------------------------------------------------------------------------------
            NbColumns=List.Count(ColumnNamesSource),
           
            //----------------------------------------------------------------------------------------------------
            // On les force les colonnes à totaliser en majuscule afin de matcher correctement
            //----------------------------------------------------------------------------------------------------
            EndColumnsList=List.Transform(
                           List.LastN(ColumnNamesSource,NbColumns - ShiftColumnNumber), each Text.Upper(_)),      

            //----------------------------------------------------------------------------------------------------
            // Tous les noms de colonnes de la source sont forcés en majuscule
            //----------------------------------------------------------------------------------------------------
            ColumnNamesUpper=List.Buffer(List.Transform(ColumnNamesSource, each Text.Upper(_))),

            //----------------------------------------------------------------------------------------------------
            // On renome dans une table temporaire les noms de colonnes en majuscules ainsi on est cohérent
            //----------------------------------------------------------------------------------------------------
            Source2=if bBuff
                    then Table.Buffer(Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})))
                    else Table.RenameColumns(Source, List.Zip({ColumnNamesSource,ColumnNamesUpper})),

            ColumnNamesPivot=Table.ColumnNames(Source2){0},

            //----------------------------------------------------------------------------------------------------
            // On effectue l'intersection des noms de colonne pour identifier les colonnes à cumuler
            //----------------------------------------------------------------------------------------------------
            IntersectHeaders = List.Buffer(List.Intersect({ColumnNamesUpper, EndColumnsList})),

            //----------------------------------------------------------------------------------------------------
            // On agrège une fonction pour les cumuls
            //----------------------------------------------------------------------------------------------------

            ListColumnsAggregate =
            List.Transform(
               //List_MonthCol,
               IntersectHeaders,
               each {_, (x)=> List.Sum(Record.Field(x, _)), type number}
            ),

            //----------------------------------------------------------------------------------------------------
            // On effectue un groupBy sur cette fonction pour cumuler à partir de la colonne pivot
            //----------------------------------------------------------------------------------------------------
            GroupingByColumns =
            Table.AddColumn(
               Table.Group(
                  Source2, {}, ListColumnsAggregate, GroupKind.Local
               ),
               ColumnNamesPivot, each pTitleTotal
            ),


            //----------------------------------------------------------------------------------------------------
            // On restore les noms de colonnes d'origine en associant la table total à la table des données
            //----------------------------------------------------------------------------------------------------
            ToTablesFinal = Table.RenameColumns(Table.Combine({Source2,GroupingByColumns}), List.Zip({ColumnNamesUpper,ColumnNamesSource}))

         in
            ToTablesFinal
in
   TotalizeAllColumnsGroupbyElse