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

M

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
M
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...
Trier par date Trier par votes
J

JFL_XLD

XLDnaute Junior
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é.....
 

Pièces jointes

  • Mongo - XLD - PQ - Optimisation requête - v1.xlsx
    299.8 KB · Affichages: 7
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
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

  • Mongo - XLD - PQ - Optimisation requête - v2.xlsx
    279.4 KB · Affichages: 1
  • PQ Fichier source1.xlsx
    301.6 KB · Affichages: 2
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
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
 
Vote positif 0 Vote négatif
Solution
J

JFL_XLD

XLDnaute Junior
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.
 
Vote positif 0 Vote négatif

Discussions similaires

Staple1600
  • Question
Power Query Simplification code M (issu de l'assistant PQ) ou autre syntaxe pour ce résultat
2 3
Réponses
33
Affichages
1 K
Amilo
A
D
  • Question
Microsoft 365 Chemin accès PowerQuery
Réponses
2
Affichages
326
Derrdio
D
L
  • Question
Microsoft 365 POWER QUERY : problème "error" sur chiffre plus grand que 999
Réponses
1
Affichages
399
Cousinhub
Cousinhub
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
306
oguruma
oguruma
oguruma
POWERQUERY :: Combine vertical et totalisation des colonnes et des lignes avec application du SelectCase (ou SWITCH)
Réponses
0
Affichages
200
oguruma
oguruma
oguruma
POWERQUERY :: List.Accumulate - Cas d'exemples concrets
Réponses
0
Affichages
152
oguruma
oguruma
W
  • Question
Power Query Plusieurs chemins d'accès power query
Réponses
11
Affichages
2 K
Cousinhub
Cousinhub

Membres actuellement en ligne

15 membres et 87 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 492
Messages
2 088 925
Membres
103 984
dernier inscrit
maliko67