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

  • Initiateur de la discussion Initiateur de la discussion Mongo
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

Mongo

XLDnaute Junior
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"
 
Solution
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...
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,
 

Pièces jointes

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
 
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.
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

  • Question Question
Power Query Power Query
Réponses
26
Affichages
412
  • Question Question
Réponses
16
Affichages
1 K
Réponses
2
Affichages
587
Retour