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

[Mongo]

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"

 

[alexga78]

Bonjour Mongo,

Pouvez-vous nous fournir un fichier exemple avec des données anonymisées. Plus simple pour optimiser le code...

 

[Mongo]

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,

 

[alexga78]

Re,
impossible d'ouvrir le fichier "PQ Fichier source"..

 

[Mongo]

En voici 2 exemplaires...

 

[alexga78]

Un essai d'optimisation,

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]

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]

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

 

[alexga78]

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

 

[Cousinhub]

Bonjour,
Hello alex
Ne serait-ce plutôt une division par zéro? (NaN, dans la requête?)
Peut-être?

 

[alexga78]

Bonjour Cousinhub,

Peut-être, mais je pense plus à trop de calculs. Le mieux est d'attendre le code de la requête finale.

 

[Mongo]

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"

 

[alexga78]

Re,
l'étape de tri demande trop de ressource.
Pourquoi ne pas trier une fois la requête chargée dans Excel ?

 

[Cousinhub]

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]

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.