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

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

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

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
 

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.
 

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
 

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: 2
  • PQ Fichier source.xlsx
    298.8 KB · Affichages: 3

Cousinhub

XLDnaute Barbatruc
Inactif
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
 

Mongo

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

  • PQ lenteur ListSum Tri Ascendant.xlsx
    292.3 KB · Affichages: 0
  • PQ Fichier source.xlsx
    298.8 KB · Affichages: 2

Discussions similaires

Statistiques des forums

Discussions
314 709
Messages
2 112 103
Membres
111 418
dernier inscrit
mlds