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...
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
 
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.
 
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
 
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

Re,
l'étape de tri demande trop de ressource.
Pourquoi ne pas trier une fois la requête chargée dans Excel ?
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
 
Re,
l'étape de tri demande trop de ressource.
Pourquoi ne pas trier une fois la requête chargée dans Excel ?
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

- 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