Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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
M

Mongo

XLDnaute Junior
Bonjour alexga78,

Je vous joins le fichier vierge "PQ lenteur ListSum" incluant la requête ainsi que le fichier source contenant les données. Il reste à adapter le chemin du fichier source lorsque vous les aurez récupérés chez vous.

Merci d'avance,
 

Pièces jointes

  • PQ Fichier source.xlsx
    298.7 KB · Affichages: 3
  • PQ lenteur ListSum.xlsx
    27.4 KB · Affichages: 7
Vote positif 0 Vote négatif
A

alexga78

XLDnaute Occasionnel
Un essai d'optimisation,

PowerQuery: 
let
    Source = Table.RemoveLastN(Excel.Workbook(File.Contents("D:\PQ Fichier source.xlsx), true, true)[Data]{0},3),
    ColName = Table.ColumnNames(Source),
    #"Selected Columns" = Table.SelectColumns(Source, {ColName{0}, ColName{7}}),
    #"Renamed Columns" = Table.RenameColumns(#"Selected Columns",{{ColName{7}, "CA"}}),
    PctVentes = let SumVentes = List.Sum(#"Renamed Columns"[CA]) in Table.Sort(Table.AddColumn(#"Renamed Columns", "% Ventes", each [CA] / SumVentes), {"% Ventes" , Order.Ascending}),
    Pct_Cum = let Pct_Cum= let Pct = List.Buffer(Table.Column(PctVentes, "% Ventes")) in
    List.Generate ( () => [x = Pct{0}, y = 0 ], each [y] < List.Count(Pct), each [x = List.Sum({[x], Pct{[y] + 1}}), y = [y] + 1], each [x]) in Table.FromColumns(Table.ToColumns(PctVentes) & {Pct_Cum}, Table.ColumnNames( PctVentes ) & {"Cumul % Ventes"}),
    Tranche = Table.TransformColumnTypes(Table.AddColumn(Pct_Cum, "Tranche", each let Tranche = {[#"Cumul % Ventes"] <0.20, [#"Cumul % Ventes"]>= 0.20 and [#"Cumul % Ventes"]<= 0.80, [#"Cumul % Ventes"]>0.80}, Result = {"<20%", "20-80%", ">80%"} in Result{List.PositionOf(Tranche, true)}), List.Transform({"% Ventes", "Cumul % Ventes" }, each {_, Percentage.Type}))
in
    Tranche

Bonne soirée
 
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
alexga78,
C'est incomparable.
Là où ma requête chargeait les lignes 30 par 30 jusqu'à atteindre la 4877ème ligne, ta requête est instantanée. J'ai à peine cliqué pour charger les lignes qu'elles le sont déjà.
je vais tenter de m'en inspirer pour mes prochaines requêtes.
Un grand merci à toi.
 
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
Bonjour,
Je reviens sur ce fil car mon besoin ayant évolué, j'ai modifié la requête pour classer le % de CA par ordre croissant et n'attribuer que 2 libellés dans la colonne Tranche.

let
Source = Table.RemoveLastN(Excel.Workbook(File.Contents("F:\Commerce\Data\1-FICHIERS EXPORTÉS\PBI PALMARES VENTES ARTICLES.xlsx"), true, true)[Data]{0},3),
ColName = Table.ColumnNames(Source),
#"Selected Columns" = Table.SelectColumns(Source, {ColName{0}, ColName{7}}),
#"Renamed Columns" = Table.RenameColumns(#"Selected Columns",{{ColName{7}, "CA"}}),
PctVentes = let SumVentes = List.Sum(#"Renamed Columns"[CA]) in Table.Sort(Table.AddColumn(#"Renamed Columns", "% Ventes", each [CA] / SumVentes), {"% Ventes" , Order.Descending}),
Pct_Cum = let Pct_Cum= let Pct = List.Buffer(Table.Column(PctVentes, "% Ventes")) in
List.Generate ( () => [x = Pct{0}, y = 0 ], each [y] < List.Count(Pct), each [x = List.Sum({[x], Pct{[y] + 1}}), y = [y] + 1], each [x]) in Table.FromColumns(Table.ToColumns(PctVentes) & {Pct_Cum}, Table.ColumnNames( PctVentes ) & {"Cumul % Ventes"}),
Tranche = Table.TransformColumnTypes(Table.AddColumn(Pct_Cum, "Tranche", each let Tranche = {[#"Cumul % Ventes"] <=0.80, [#"Cumul % Ventes"]>0.80}, Result = {"20-80", "00"} in Result{List.PositionOf(Tranche, true)}), List.Transform({"% Ventes", "Cumul % Ventes" }, each {_, Percentage.Type})),
Arrondi = Table.TransformColumns(Tranche,{{"CA", each Number.Round(_, 0), type number}}),
#"Colonnes renommées" = Table.RenameColumns(Arrondi,{{"CA", "CA N-2 N-1"}})
in
#"Colonnes renommées"

Problème, la requête finale plante systématiquement lorsque je la joins à la requête modifiée ci-dessus. Le message d'erreur est le suivant : "Expression.Error : L'évaluation a provoqué un dépassement de capacité de la pile et ne peut pas continuer."
Quelqu'un (alexga78 ?) saurait ce qui provoque ce type d'erreur ?

Merci d'avance
 
Vote positif 0 Vote négatif
A

alexga78

XLDnaute Occasionnel
Bonjour Mongo,
Ce genre d'erreur est généralement provoqué par un trop grand nombre d’opérations utilisant beaucoup de mémoire.
Cependant difficile de répondre sans voir le code de la requête finale...
 
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
Sans aller jusqu'à la jointure, le même plantage se produit lorsque je trie la colonne article à la fin de la requête modifiée.
Je joins la requête et le fichier source (chemins à modifier)

let
Source = Table.RemoveLastN(Excel.Workbook(File.Contents("F:\Commerce\Data\1-FICHIERS EXPORTÉS\PBI PALMARES VENTES ARTICLES.xlsx"), true, true)[Data]{0},3),
ColName = Table.ColumnNames(Source),
#"Selected Columns" = Table.SelectColumns(Source, {ColName{0}, ColName{7}}),
#"Renamed Columns" = Table.RenameColumns(#"Selected Columns",{{ColName{7}, "CA"}}),
PctVentes = let SumVentes = List.Sum(#"Renamed Columns"[CA]) in Table.Sort(Table.AddColumn(#"Renamed Columns", "% Ventes", each [CA] / SumVentes), {"% Ventes" , Order.Descending}),
Pct_Cum = let Pct_Cum= let Pct = List.Buffer(Table.Column(PctVentes, "% Ventes")) in
List.Generate ( () => [x = Pct{0}, y = 0 ], each [y] < List.Count(Pct), each [x = List.Sum({[x], Pct{[y] + 1}}), y = [y] + 1], each [x]) in Table.FromColumns(Table.ToColumns(PctVentes) & {Pct_Cum}, Table.ColumnNames( PctVentes ) & {"Cumul % Ventes"}),
Tranche = Table.TransformColumnTypes(Table.AddColumn(Pct_Cum, "Tranche", each let Tranche = {[#"Cumul % Ventes"] <=0.80, [#"Cumul % Ventes"]>0.80}, Result = {"20-80", "Hors 20-80"} in Result{List.PositionOf(Tranche, true)}), List.Transform({"% Ventes", "Cumul % Ventes" }, each {_, Percentage.Type})),
Arrondi = Table.TransformColumns(Tranche,{{"CA", each Number.Round(_, 2), type number}}),
#"Colonnes renommées" = Table.RenameColumns(Arrondi,{{"CA", "CA N-2 N-1"}}),
#"Lignes triées" = Table.Sort(#"Colonnes renommées",{{"Code article", Order.Ascending}})
in
#"Lignes triées"
 

Pièces jointes

  • PQ lenteur ListSum Tri Ascendant.xlsx
    26.9 KB · Affichages: 1
  • PQ Fichier source.xlsx
    298.8 KB · Affichages: 2
Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc
Inactif
alexga78 à dit:
Re,
l'étape de tri demande trop de ressource.
Pourquoi ne pas trier une fois la requête chargée dans Excel ?
Cliquez pour agrandir...
Re-,
Je confirme
J'ai même essayé de Buffériser avant le tri, niet...
J'ai fait l'essai en chargeant la requête dans un onglet :
- Tri par Excel, immédiat
- Import de ce tableau dans une nouvelle requête, tri dans PQ -> immédiat
Un peu surbooké....
Bonne apm
 
Vote positif 0 Vote négatif
M

Mongo

XLDnaute Junior
alexga78 à dit:
Re,
l'étape de tri demande trop de ressource.
Pourquoi ne pas trier une fois la requête chargée dans Excel ?
Cliquez pour agrandir...
Merci déjà !
Je voulais trier avant le chargement dans Excel car le fichier est destiné à des personnes qui ne maîtrisent pas forcément très bien Excel et qui doivent utiliser un fichier prêt à l'emploi! Le fait de le charger déjà tout prêt leur permettait de travailler directement dessus.
En revanche, je ne comprends pas pourquoi cela fonctionne bien en triant l'étape PctVentes dans l'ordre Ascendant alors qu'en triant en Descendant, ça plante.
 

Pièces jointes

  • PQ lenteur ListSum Tri Ascendant.xlsx
    292.3 KB · Affichages: 0
  • PQ Fichier source.xlsx
    298.8 KB · Affichages: 2
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

S
  • Question
XL 2019 power query
2
Réponses
16
Affichages
894
st007
S
  • Question
Power Query Simplification code M (issu de l'assistant PQ) ou autre syntaxe pour ce résultat
2 3
Réponses
33
Affichages
2 K
Amilo
A
D
  • Question
Microsoft 365 Chemin accès PowerQuery
Réponses
2
Affichages
506
Derrdio
D
L
  • Question
Microsoft 365 POWER QUERY : problème "error" sur chiffre plus grand que 999
Réponses
1
Affichages
569
Cousinhub
POWERQUERY :: Ranking par catégorie :: TopN variable d'une liste
Réponses
0
Affichages
152
oguruma
POWERQUERY :: Combinaisons (Table.Combine) de dossiers contenant des fichiers .CSV ou des fichiers Excel - V2
Réponses
0
Affichages
479
oguruma
POWERQUERY :: Combine vertical et totalisation des colonnes et des lignes avec application du SelectCase (ou SWITCH)
Réponses
0
Affichages
266
oguruma
POWERQUERY :: List.Accumulate - Cas d'exemples concrets
Réponses
0
Affichages
266
oguruma
W
  • Question
Power Query Plusieurs chemins d'accès power query
Réponses
11
Affichages
3 K
Cousinhub
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…