POWERQUERY :: Cumuls par trimestre ou par période groupées

oguruma · 12 Juin 2024

Bonjour,
ce post fait suite à celui-ci : https://excel-downloads.com/threads...les-colonnes-dun-tableau-3-methodes.20083284/
Il semblait normal donc de faire une totalisation par trimestre ou par période.

Exemples de tableau

Environnement PowerQuery

Fonction 1 : fnSplitByQuarterV1

Ici nous sommes dans un cas très simple - un tableau avec 12 mois

PowerQuery:

let
    Source = fnSplitByQuarterV1 ("VENTES_1")
in
    Source

PowerQuery:

let
    Source = fnSplitByQuarterV1 ("VENTES_1","Produits","Trimestre_",true,"Total Trimestres")
in
    Source

"Produits", ==> titre de la colonne libellé
"Trimestre_", ==> titre des colonnes trimestres
true, ==> on active la somme des trimestres
"Total Trimestres" ==> libellé de la colonne total des trimestres

PowerQuery:

let
    Source = fnSplitByQuarterV1 ("VENTES_1",null,null,true,null)
in
    Source

On accepte les valeurs par défaut et on fait la somme des trimestres

Code de la fonction

PowerQuery:

let fnSplitByQuarterV1 = (
            pTableToQuarter as any,           
            optional pTitleColumnsPivot as text,
            optional pTitleColumnsQuarters as text,
            optional pSumQuaters as logical,
            optional pTitleSumQuaters as text
        ) =>

        let

            //******************************************************************************
            // Gestion des paramètres
            //******************************************************************************           
            TableToQuarter=pTableToQuarter,
            TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
            TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
            TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
            bSumQuaters=if pSumQuaters is null then false else pSumQuaters, 

            //******************************************************************************
            // Table pour laquelle il faut faire le découpage en trimestres
            //******************************************************************************
            Source = if pTableToQuarter is text
                     then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
                     else pTableToQuarter,

            //******************************************************************************
            // Alimentation des constantes
            //******************************************************************************
            BASE3=3,
            BASE1=1,

            //**************************************************************************************************
            // Dans cette liste on ne tient pas compte qu'une série de mois peut se suivre sur deux années
            // On considère pour simplifier le découpage que l'on est dans la même année
            // On pourrait donc avoir un découpage qui n'aurait pas de sens si deux années se suivent
            // Cette liste est utilisée pour numéroter les trimestres dans l'ordre
            //**************************************************************************************************
            QUATER_LIST= {1,2,3,4},

            //******************************************************************************
            // Titres des colonnes avec ou sans la colonne total des trimestres
            // On va boucler pour numéroter les trimestres
            //******************************************************************************
            TitleTrim= if bSumQuaters
                    then {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)) & {TitleSumQuaters}
                    else {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)),

            //******************************************************************************
            // Regroupement par trimestre ligne à ligne {}
            // Doit-on faire la somme totale des trimestres
            //******************************************************************************
            ComputeTrim= if bSumQuaters
                        then
                            //----------------------------------------------------------------------
                            // Dans ce cas il faut tenir compte de la colonne total des trimestres 
                            //----------------------------------------------------------------------   
                            List.Transform( List.Skip(Table.ToRows(Source),1),
                                (RecordLine) =>
                                    {RecordLine{0}} & // Découpage modulo BASE3 (3 mois pour un trimestre) et il faut tenir compte des trimestres incomplets
                                    List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * BASE3 + BASE1,BASE3))) & {List.Sum( List.Skip(RecordLine,1))})                           
                        else
                            //----------------------------------------------------------------------
                            // On ne prend pas en compte la colonne total des trimestres
                            //----------------------------------------------------------------------
                            List.Transform( List.Skip(Table.ToRows(Source),1),
                                (RecordLine) =>
                                    {RecordLine{0}} &
                                    List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * BASE3 + BASE1,BASE3)))),

            //******************************************************************************
            // On construit dynamiquement la table avec son constructeur
            //******************************************************************************
            // Construction de la table finale avec les trimestres
            //******************************************************************************
            ToTableTrim=#table(TitleTrim,ComputeTrim)

        in
            ToTableTrim
in
    fnSplitByQuarterV1

Cette fonction comporte néanmoins un inconvénient quand deux années se suivent la notion de trimestre perd son sens. La période doit être uniquement sur l'année afin que le calcul et la répartition par trimestre soit cohérente.

Afin palier à cette incohérence voici la fonction fnSplitByAnyV1

PowerQuery:

let
    Source = fnSplitByAnyV1 ("VENTES_1")
in
    Source

Nous obtenons ici le même résultat que la fonction précédente

PowerQuery:

let Source = fnSplitByAnyV1 ("VENTES_1",4,"1;2;3","Art","Periode") in Source

Nous avons ici le choix du nombre de colonnes à totaliser (4) et du nombre de colonne à présenter "1;2;3" [transformer en liste afin de boucler pour construire les colonnes]

Nous avons ici un découpage en semestre

PowerQuery:

let Source = fnSplitByAnyV1 ("VENTES_1",6,"1;2",null,"Semestre_") in Source

Autre possibilité de regroupement

PowerQuery:

let Source = fnSplitByAnyV1 ("VENTES_1",3,"1;2;3;4","Libellé","Cadence") in Source

Il n'est pas obligatoire d'avoir des dates dans le titre des colonnes

Code:

let /* pTableToQuarter as any, optional pTitleColumnsPivot as text, optional pTitleColumnsQuarters as text, optional pSumQuaters as logical, optional pTitleSumQuaters as text, optional pYyyy as number, optional pSeparator as text */ Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trim",false,null,4,"-") in Source

Code de la fonction

PowerQuery:

let fnSplitByAnyV1 = (
            pTableToQuarter as any,
            optional pBase as number,
            optional pAnyList as text,           
            optional pTitleColumnsPivot as text,
            optional pTitleColumnsQuarters as text,
            optional pSumQuaters as logical,
            optional pTitleSumQuaters as text
        ) =>

        let

            //******************************************************************************
            // Gestion des paramètres
            //******************************************************************************           
            TableToQuarter=pTableToQuarter,
            Base = if pBase is null then 3 else pBase,
            AnyNumberList = if pAnyList is null then {1,2,3,4} else List.Transform(Text.Split(pAnyList,";"), each Number.FromText(_)),
            TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
            TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
            TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
            bSumQuaters=if pSumQuaters is null then false else pSumQuaters, 

            //******************************************************************************
            // Table pour laquelle il faut faire le découpage en trimestres
            //******************************************************************************
            Source = if pTableToQuarter is text
                     then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
                     else pTableToQuarter,

            //******************************************************************************
            // Alimentation des constantes
            //******************************************************************************
            
            BASE1=1,

            //**************************************************************************************************
            // Dans cette liste on ne tient pas compte qu'une série de mois peut se suivre sur deux années
            // On considère pour simplifier le découpage que l'on est dans la même année
            // On pourrait donc avoir un découpage qui n'aurait pas de sens si deux années se suivent
            // Cette liste est utilisée pour numéroter les trimestres dans l'ordre
            //**************************************************************************************************
            QUATER_LIST= AnyNumberList,

            //******************************************************************************
            // Titres des colonnes avec ou sans la colonne total des trimestres
            // On va boucler pour numéroter les trimestres
            //******************************************************************************
            TitleTrim= if bSumQuaters
                    then {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)) & {TitleSumQuaters}
                    else {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)),

            //******************************************************************************
            // Regroupement par trimestre ligne à ligne {}
            // Doit-on faire la somme totale des trimestres
            //******************************************************************************
            ComputeTrim= if bSumQuaters
                        then
                            //----------------------------------------------------------------------
                            // Dans ce cas il faut tenir compte de la colonne total des trimestres 
                            //----------------------------------------------------------------------   
                            List.Transform( List.Skip(Table.ToRows(Source),1),
                                (RecordLine) =>
                                    {RecordLine{0}} & // Découpage modulo BASE3 (3 mois pour un trimestre) et il faut tenir compte des trimestres incomplets
                                    List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * Base + BASE1,Base))) & {List.Sum( List.Skip(RecordLine,1))})                           
                        else
                            //----------------------------------------------------------------------
                            // On ne prend pas en compte la colonne total des trimestres
                            //----------------------------------------------------------------------
                            List.Transform( List.Skip(Table.ToRows(Source),1),
                                (RecordLine) =>
                                    {RecordLine{0}} &
                                    List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * Base + BASE1,Base)))),

            //******************************************************************************
            // On construit dynamiquement la table avec son constructeur
            //******************************************************************************
            // Construction de la table finale avec les trimestres
            //******************************************************************************
            ToTableTrim=#table(TitleTrim,ComputeTrim)

        in
            // QUATER_LIST
            ToTableTrim
in
    
    fnSplitByAnyV1

Fonction permettant de corriger le calcul des trimestres quand deux années se suivent

PowerQuery:

let
    Source = fnSplitByQuarterV2 ("VENTES_2","Libellé")
in
    Source

PowerQuery:

let
/*
                    pTableToQuarter as any,
                    optional pTitleColumnsPivot as text,
                    optional pTitleColumnsQuarters as text,
                    optional pSumQuaters as logical,
                    optional pTitleSumQuaters as text,
                    optional pYyyy as number,
                    optional pSeparator as text
*/

    Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trimestre",true,"Total Trim",2,"-")
in
    Source

PowerQuery:

let
/*
                    pTableToQuarter as any,
                    optional pTitleColumnsPivot as text,
                    optional pTitleColumnsQuarters as text,
                    optional pSumQuaters as logical,
                    optional pTitleSumQuaters as text,
                    optional pYyyy as number,
                    optional pSeparator as text
*/

    Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trim",false,null,4,"-")
in
    Source

Code de la fonction

PowerQuery:

let fnSplitByQuarterV2 = (
                    pTableToQuarter as any,
                    optional pTitleColumnsPivot as text,
                    optional pTitleColumnsQuarters as text,
                    optional pSumQuaters as logical,
                    optional pTitleSumQuaters as text,
                    optional pYyyy as number,
                    optional pSeparator as text
        ) =>

    let           

            TableToQuarter=pTableToQuarter,
            TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
            TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
            bSumQuaters=if pSumQuaters is null then false else pSumQuaters,     
            TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
            Yyyy=if pYyyy is null then 2 else pYyyy,
            Separator=if pSeparator is null then "-" else pSeparator,

            IgnoreCount=1,
            Base=3,

            Source = if pTableToQuarter is text
                     then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
                     else pTableToQuarter,

            //**************************************************************************************************
            // Pour chaque date on va déterminer le trimestre et l'année afin de les distinguer
            // Ainsi on résoud le problème quand deux années se suivent
            //**************************************************************************************************   
            QuatersList = List.Transform(
                    List.Skip( Record.FieldValues(Table.First(Source)), IgnoreCount),
                        each TitleColumnsQuarters &
                            Separator &                     
                            Text.From(Date.QuarterOfYear(_)) & 
                            Separator &                     
                            Text.End(Text.From( Date.Year(_)), Yyyy)
            ),

            //************************************************************************************************************
            // Titre des colonnes trimestres
            //************************************************************************************************************   
            TitleListQuarters= if bSumQuaters
                            then {Table.FirstValue(Source)} & List.Distinct(QuatersList) & {TitleSumQuaters}
                            else {Table.FirstValue(Source)} & List.Distinct(QuatersList),

            //************************************************************************************************************
            // Autre méthode pour le découpage en tenant compte de liste des trimestres calculés à l'étape précédente
            //************************************************************************************************************
            ComputeList=if bSumQuaters
                        then
                            List.Transform(List.Skip(Table.ToRows(Source),IgnoreCount),
                                (RecordLine) =>
                                    {RecordLine{0}} &
                                    List.Transform(
                                        // On va répérer les mois les uns après les autres
                                        List.Transform(List.Distinct(QuatersList), each List.PositionOf(QuatersList, _)),
                                        // Cumul des mois détectés
                                        each List.Sum(List.Range(RecordLine, _ + 1, Base))
                                // On ajoute la colonne total des trimestres
                                ) & {List.Sum(List.Skip(RecordLine,IgnoreCount))}) 
                        else
                            List.Transform(List.Skip(Table.ToRows(Source),IgnoreCount),
                                (RecordLine) =>
                                    {RecordLine{0}} &
                                    List.Transform(
                                        // On va répérer les mois les uns après les autres
                                        List.Transform(List.Distinct(QuatersList), each List.PositionOf(QuatersList, _)),
                                        // Cumul des mois détectés
                                        each List.Sum(List.Range(RecordLine, _ + 1, Base))
                                ) ),
                
            //************************************************************************************************************
            // Construction de la table finale avec son constructeur
            //************************************************************************************************************
            TableToQuaters= #table(
                TitleListQuarters,
                ComputeList)

        in
            TableToQuaters
in
    fnSplitByQuarterV2

Pour plus de précisions voir le fichier joint

POWERQUERY :: Cumuls par trimestre ou par période groupées

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires