PowerQuery :: Effectuer un cumul sur une colonne

[oguruma]

Cette fonction a pour but de faire le cumul d'une colonne (ou deux colonnes - option). Bien que l'on puisse aisément le coder sous Excel avec quelques précautions pour prendre en compte les suppressions et les insertions de lignes cela nous évite d'embarquer des formules sous Excel qui pourraient alourdir les traitements. Voir le fichier Excel pour son fonctionnement.

List.Accumulate nous permet en fait de boucler sur les valeurs à cumuler en tenant compte du cumul précédent pour ajouter la valeur courante.

Attention pour des raisons de rapidité les valeurs sont stockées dans des Buffers. Cependant cela a des limites en termes de volumétrie. Mes tests passent allégrement la barre des 5000 lignes. A tester si besoin d'aller plus loin selon vos besoins.

On peut y voir dans cette fonction l'utilisation de l'instruction Expression.Evaluate qui est très puissante. Cependant elle est assez complexe à manipuler bien qu'elle ne comporte que deux arguments. Pour faire référence à Excel c'est l'équivalent de INDIRECT. Sur le NET elle est largement documentée. A lire.

La fonction

let fnCumulTotal =
(
pSrcData as any,
pFieldValue as text,
pCumulField as text,
optional pFieldValue2 as text,
optional pCumulField2 as text
) as table =>
let
SrcData=pSrcData,
FieldValue=pFieldValue,
CumulField=pCumulField,
FieldValue2=pFieldValue2,
CumulField2=pCumulField2,
SourceData = if SrcData is text then Excel.CurrentWorkbook(){[Name=SrcData]}[Content] else SrcData,

ToStringBuffer_1="List.Buffer(SourceData[" & FieldValue & "])", ==> cela permet de gérer le nom de champ dynamiquement
TmpBuffer=Expression.Evaluate(ToStringBuffer_1, [List.Buffer=List.Buffer,SourceData=SourceData]), ===> ici comme le Indirect sous Excel
ToStringBuffer_2="List.Buffer(SourceData[" & FieldValue2 & "])",
TmpBuffer_2=Expression.Evaluate(ToStringBuffer_2, [List.Buffer=List.Buffer,SourceData=SourceData]),

//************************************************************
// Fonction pour cumuler
//************************************************************
fnCumul = (pBuf as any) =>
let
// On va bufferiser les valeurs
SkipBuff=List.Skip( pBuf, 1),
StartBuffer=pBuf{0},
Begin=SkipBuff & {0},
ComputeTotal = List.Accumulate(Begin,
// Initialisation des compteurs
[Total = {}, Value = StartBuffer],
( status, currentValToSum ) =>
// On récupère les paramètres pour les cumuls
[Value = List.Sum( {status[Value] , currentValToSum } ) ,
// Et le cumul est réalisé en fait ici
Total = status[Total] & { status[Value ] } ] )[Total]
in
ComputeTotal,
//************************************************************
// On fusionne les résultats
//************************************************************
Result =
if pFieldValue2 is null then
Table.FromColumns(
Table.ToColumns( SourceData ) & { Value.ReplaceType( fnCumul(TmpBuffer), type {number} ) } ,
Table.ColumnNames( SourceData ) & {CumulField}
)
else
Table.FromColumns(
Table.ToColumns( SourceData ) & { Value.ReplaceType( fnCumul(TmpBuffer), type {number} ) } & { Value.ReplaceType( fnCumul(TmpBuffer_2), type {number} ) } ,
Table.ColumnNames( SourceData ) & {CumulField} & {CumulField2}
)
in
Result
in
fnCumulTotal