Microsoft 365 Cumul progressif en PowerQuery

Laurent78 · 29 Décembre 2023

Bonsoir à tous,
J'espère que vous avez passé une excellente première partie des fêtes de fin d'année.

J'ai un petit sujet à traiter.
Il s'agit de "virtuellement" refabriquer des numéros de pièces dans une bases de données comptables (afin de pouvoir par la suite faire des liens/jointures, mais cette suite n'est pas dans le problème).
Sachant que les bases de données comportent plusieurs centaines de milliers des lignes (et dépassent le million de ligne d'une feuille Excel), j'utilise PowerQuery, sauf que, autant il est simple de faire un cumul progressif dans Excel autant c'est un peu plus compliqué dans PQ, et surtout, l'autojointure + regroupement sur des centaines de millier de lignes risque de prendre beaucoup de temps.
Bref si quelqu'un qui passe par là a une idée, ce serait chouette.

Bien évidement, arrivé ici, vous n'avez certainement pas compris grand chose

Donc, je joins une fichier Anonymisé avec 2 Onglets, l'un ("Le but") comprend la base et ce que je souhaite obtenir, le second ("DataBase Exemple") la base (sous forme de tableau structuré) pour faire mumuse dans PQ.

Un grand merci d'avance à ceux qui auront des idées voire la solution !

@+
Laurent

mromain · 29 Décembre 2023

Re,

Du coup, tu peux tester avec cette requête :

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    AddColumn_Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    AddColumn_MontantSigné = Table.AddColumn(AddColumn_Index, "Montant signé", each [Débit]-[Crédit], type number),
    listMontantSignés = List.Buffer(AddColumn_MontantSigné[Montant signé]),
    AddColumn_CumulProgressif = Table.AddColumn(AddColumn_MontantSigné, "Cumul progressif", each List.Sum(List.FirstN(listMontantSignés, [Index])), type number),
    AddColumn_FlagZero = Table.AddColumn(AddColumn_CumulProgressif, "FlagZero", each if [Cumul progressif]=0 then [Index] else null),
    FillUp_FlagZero = Table.FillUp(AddColumn_FlagZero,{"FlagZero"})...

mromain · 29 Décembre 2023

Bonsoir Laurent,

Ci-dessous une possibilité :

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    AddColumn_MontantSigné = Table.AddColumn(Source, "Montant signé", each [Débit]-[Crédit], type number),
    listMontantSignés = List.Buffer(AddColumn_MontantSigné[Montant signé]),
    AddColumn_CumulProgressif = Table.AddColumn(AddColumn_MontantSigné, "Cumul progressif", each List.Sum(List.FirstN(listMontantSignés, [ID])), type number)
in
    AddColumn_CumulProgressif

A+

edit : pour la dernière colonne (A-1, B-1n ...) je t'avoue ne pas trop comprendre...

Laurent78 · 29 Décembre 2023

mromain à dit:

Bonsoir Laurent,

Ci-dessous une possibilité :

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    AddColumn_MontantSigné = Table.AddColumn(Source, "Montant signé", each [Débit]-[Crédit], type number),
    listMontantSignés = List.Buffer(AddColumn_MontantSigné[Montant signé]),
    AddColumn_CumulProgressif = Table.AddColumn(AddColumn_MontantSigné, "Cumul progressif", each List.Sum(List.FirstN(listMontantSignés, [ID])), type number)
in
    AddColumn_CumulProgressif

A+

edit : pour la dernière colonne (A-1, B-1n ...) je t'avoue ne pas trop comprendre...

Bonsoir Romain,
Déjà un grand merci pour ce début de réponse.
évidement, j'ai fait une première boulette, la colonne ID, donc, on ne peut pas s'appuyer dessus, ne devrait pas être là, je l'avais ajouté dans Excel (j'ai modifié mon fichier exemple dans le Post#1); Mais cela ne pose de problème majeur, puisqu'il suffit d'ajouter un colonne Index en PQ.
Ensuite, le but final est en fait de dire, lorsque le cumul progressif passe à 0, alors c'est la dernière ligne de la pièce comptable, qui doit donc avoir le même numéro que la ligne au dessus (A-1), mais ensuite, on passe à un nouveau numéro de pièce (B-1). Je pense que j'aurais dû écrire A1, A2, etc et non A-1 ou le signe "-" porte à confusion. Il fallait lire "A tiret 1" et non pas "A moins 1". Du coup, j'ai supprimé les "-" (tirets)
Suis-je clair ?
Encore merci.
Laurent

mromain · 29 Décembre 2023

Re,

Du coup, vu ton explication, j'ai du mal à comprendre le travail fait à la main :

la cellule I10 ne devrait-elle pas valoir C-2 ?
la cellule I12 ne devrait-elle pas valoir C-3 ?
...

A+

Laurent78 · 29 Décembre 2023

mromain à dit:
Re,

Du coup, vu ton explication, j'ai du mal à comprendre le travail fait à la main :

la cellule I10 ne devrait-elle pas valoir C-2 ?

la cellule I12 ne devrait-elle pas valoir C-3 ?

...

A+

Ben si , tu as parfaitement raison (et donc compris , comme je l'ai fait à la main, je me suis un peu décalé. Merci pour la remarque. Fichier exemple modifié.

mromain · 29 Décembre 2023

Re,

Du coup, tu peux tester avec cette requête :

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    AddColumn_Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    AddColumn_MontantSigné = Table.AddColumn(AddColumn_Index, "Montant signé", each [Débit]-[Crédit], type number),
    listMontantSignés = List.Buffer(AddColumn_MontantSigné[Montant signé]),
    AddColumn_CumulProgressif = Table.AddColumn(AddColumn_MontantSigné, "Cumul progressif", each List.Sum(List.FirstN(listMontantSignés, [Index])), type number),
    AddColumn_FlagZero = Table.AddColumn(AddColumn_CumulProgressif, "FlagZero", each if [Cumul progressif]=0 then [Index] else null),
    FillUp_FlagZero = Table.FillUp(AddColumn_FlagZero,{"FlagZero"}),
    DeleteColumn_Index = Table.RemoveColumns(FillUp_FlagZero,{"Index"}),
    GroupBy_PieceFlagZero = Table.Group(DeleteColumn_Index, {"Pièce", "FlagZero"}, {{"Data", each _, type table [Jour=datetime, Pièce=text, Compte général=any, Débit=number, Crédit=number, Montant signé=number, Cumul progressif=number, FlagZero=number]}}),
    AddColumn_GroupIndex = Table.AddIndexColumn(GroupBy_PieceFlagZero, "GroupIndex", 0, 1, Int64.Type),
    listGroupPièce = List.Buffer(AddColumn_GroupIndex[Pièce]),
    AddColumn_IdPiece = Table.AddColumn(AddColumn_GroupIndex, "ID Pièce", each let piece = [Pièce], idx = [GroupIndex] in piece & "-" & Text.From(List.Count(List.Select(List.FirstN(listGroupPièce, idx), each _ = piece)))),
    SelectColumns = Table.SelectColumns(AddColumn_IdPiece,{"Data", "ID Pièce"}),
    DevelopDataGroup = Table.ExpandTableColumn(SelectColumns, "Data", {"Jour", "Pièce", "Compte général", "Débit", "Crédit", "Montant signé", "Cumul progressif"}, {"Jour", "Pièce", "Compte général", "Débit", "Crédit", "Montant signé", "Cumul progressif"})
in
    DevelopDataGroup

Peut-être y aura-t-il d'autres propositions plus performante, surtout si tu as un gros volume de données.

A+

Laurent78 · 29 Décembre 2023

Re,

Super, je vais tester, ce qu'il me manquait était le Table.FillUp.
Je vais pouvoir dormir plus serein
Bon, il faut tout de même que je vois ce que cela donner sur presque 2M de lignes.

Merci encore

Très bonne fin d'année.
Laurent

oguruma · 29 Décembre 2023

Bonsoir, peut-être un début de piste.... une de mes solutions en PWQ a adapter certainement
Ici : https://excel-downloads.com/threads/powerquery-effectuer-un-cumul-sur-une-colonne.20078758/

Bon attention... je passe par les Buffers.... peut-être qu'avec ta volumétrie ça pourrait coincer

Microsoft 365 Cumul progressif en PowerQuery

Laurent78

XLDnaute Occasionnel

Pièces jointes

mromain

mromain

XLDnaute Barbatruc

Laurent78

XLDnaute Occasionnel

mromain

XLDnaute Barbatruc

Laurent78

XLDnaute Occasionnel

mromain

XLDnaute Barbatruc

Laurent78

XLDnaute Occasionnel

oguruma

XLDnaute Occasionnel

Discussions similaires