Power Query Lenteur requête Power Query avec List.Sum

[Mongo]

Bonjour,
J'ai créé la requête ci-dessous dont le résultat est conforme à mes attentes. Le seul problème et non des moindres est qu'elle est extrêmement lente.
Le chargement des 4877 lignes sur 5 colonnes prend entre 5 et 10 minutes.
Quelqu'un saurait-il si une optimisation est réalisable ?
Par avance merci,

let
Source = Excel.Workbook(File.Contents("F:\Commerce\Data\1-FICHIERS EXPORTÉS\PBI PALMARES VENTES ARTICLES.xlsx"), null, true),
Sheet = Source{[Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Code article", type text}, {"Statut article", type text}, {"Désignation article", type text}, {"Famille stat 1", type text}, {"Famille stat 2", type text}, {"Famille stat 3", type text}, {"Quantité facturée", type number}, {"CA HT (devise société)", type number}, {"Quantité disponible US", type number}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Statut article", "Désignation article", "Famille stat 1", "Famille stat 2", "Famille stat 3", "Quantité facturée", "Quantité disponible US"}),
#"Lignes filtrées" = Table.SelectRows(#"Colonnes supprimées", each not Text.StartsWith([Code article], "Filtre") and [Code article] <> null and [Code article] <> "ZREM"),
DEPARTCALCUL = Table.RenameColumns(#"Lignes filtrées",{{"CA HT (devise société)", "CA"}}),
#"Personnalisée ajoutée" = Table.AddColumn(DEPARTCALCUL, "PourcentageVentes", each [#"CA"]/List.Sum(DEPARTCALCUL[#"CA"])),
#"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"PourcentageVentes", type number}}),
TriCroissant = Table.Sort(#"Type modifié1",{{"PourcentageVentes", Order.Ascending}}),
Index_ajouté = Table.AddIndexColumn(TriCroissant, "Index", 1, 1),
CumulPourcentages = Table.AddColumn(Index_ajouté, "CumulPourcentages", each List.Sum(List.Range(Index_ajouté[PourcentageVentes],0,[Index]))),
#"Personnalisée ajoutée1" = Table.AddColumn(CumulPourcentages, "Tranche", each if [CumulPourcentages] <0.20 then "<20%" else if [CumulPourcentages]>= 0.20 and [CumulPourcentages]<=0.80 then "20-80%" else if [CumulPourcentages]>0.80 then ">80%" else null),
#"Type modifié2" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"CumulPourcentages", type number}}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Type modifié2",{"Index"}),
#"Personnalisée ajoutée2" = Table.AddColumn(#"Colonnes supprimées1", "Cumul % Ventes", each [CumulPourcentages]),
#"Colonnes permutées" = Table.ReorderColumns(#"Personnalisée ajoutée2",{"Code article", "CA", "PourcentageVentes", "CumulPourcentages", "Cumul % Ventes", "Tranche"}),
#"Colonnes supprimées2" = Table.RemoveColumns(#"Colonnes permutées",{"CumulPourcentages"})
in
#"Colonnes supprimées2"

 

[JFL_XLD]

Bonsoir à tous !

Une approche qui semble véloce (sur mon ordinateur portable...😉)
J'ai intégré l'arrondi et le tri sur le code article.
En espérant ne rien avoir oublié.....

 

[Cousinhub]

Bonsoir,
JF
Tu participes à la LDC? (Foot)
Impressionnant!!!
Bon W-E

 

[Mongo]

Bonjour,
Désolé pour le délai de réponse, un peu surbooké :-(

@JFL_XLD : Merci pour ta requête qui ne plante pas, même avec mes données de prod qui sont un peu plus conséquentes !!!
En revanche, le calcul ne ne fait pas comme je le souhaiterais mais je pense avoir corrigé le tir : la colonne CA ou le % du CA doivent être triés par ordre décroissant pour faire le cumul en partant des plus grosses ventes. De cette manière, le cumul % permet d'identifier facilement les valeurs qui composent 80% du CA en partant des plus importantes.
Je pense avoir résolu le problème dans le fichier que je joins.

Il me reste cependant un souci que je ne parviens pas à régler : en Prod, le fichier source est issu de power BI.
Il y a une ligne de total en fin de tableau qui viens polluer les calculs de %. Cette ligne est identifiable par le mot total dans la colonne article. Je ne parviens pas à exclure cette ligne sans mettre le bazar dans ton code.
Un grand merci par avance,

 

[Mongo]

Je viens de trouver la solution :
let
Source = let
c=Excel.CurrentWorkbook(){[Name="CheminClasseur"]}[Content]{0}[Column1],
s=Excel.Workbook(File.Contents(c), true, true)[Data]{0}[[Code article],[#"CA HT (devise société)"]],
l = Table.RemoveLastN(s,each [#"CA HT (devise société)"]=null or [Code article] = "Total")
in Table.Sort(l,{{"CA HT (devise société)", Order.Descending}}),
Traitt = let
DetCa=List.Buffer(Source[#"CA HT (devise société)"]),
NbrCa=List.Count(DetCa),
TotCa=List.Sum(DetCa),
fnTranche=(Val)=>if Val<=.8 then "20-80" else null
in List.Generate(
()=> [c=0,Pct_Ca=DetCa{0}/TotCa,Cum_PctCa=Pct_Ca,Tranche=fnTranche(Cum_PctCa)],
each [c]<NbrCa,
each [c=[c]+1,Pct_Ca=DetCa{c}/TotCa,Cum_PctCa=[Cum_PctCa]+Pct_Ca,Tranche=fnTranche(Cum_PctCa)],
each [[Pct_Ca],[Cum_PctCa],[Tranche]]),
Final = let
p=Table.FromColumns(Table.ToColumns(Source) & {Traitt},{"Code article","CA","Rec"}),
e=Table.ExpandRecordColumn(p, "Rec", Record.FieldNames(Traitt{0})),
t=Table.Sort(e,{{"Code article", Order.Ascending}}),
a=Table.TransformColumns(t,{{"CA", each Number.Round(_, 2), type number}})
in Table.TransformColumnTypes(a,{{"Code article", type text}, {"CA", type number}, {"Pct_Ca", Percentage.Type}, {"Cum_PctCa", Percentage.Type}, {"Tranche", type text}})
in
Final

Merci et bonne journée

 

[JFL_XLD]

Bonjour à tous !
Parfait ! 😉

Remarque : Il est préférable d'opter directement pour la valeur numérique de l'énumération spécifiant l'ordre : 1 au lieu de Order.Descending. Vous éviterez ainsi la transformation lors de l'interprétation du code.