Cumul Progressif Power Query List.Generate
Bonsoir à tous de nouveau !
Version intégrant une requête (avec List.Accumulate) pour le calcul des valeurs mensuelles à partir d'un cumul :
Bonsoir jfl,
Merci pour votre aide et cette proposition !
Je vous fais un retour demain matin 😉
Bonne soirée à vous,
Cordialement.
Bonjour à tous,
Après avoir adapter sur ma volumétrie de données, je réitère que la solution la plus rapide pour :
- déterminer les valeurs mensuelles (onglet MENSUEL FDM), c'est ni List.Generate, ni List.Alternate mais bien une solution proposée par JFL sur un autre post qui est la plus rapide (temps divisé par 3),
- déterminer les valeurs cumulées (onglet CUMUL FDM), les deux propositions List.Generate et List.Alternate sont équivalentes, et satisfaisantes en actualisation !
Mais ma question est de savoir s'il est possible pour les valeurs cumulées d'avoir une approche (sans fonction List.) équivalente à la solution proposée par JFL sur l'onglet Mensuel FDM,
Merci d'avance pour votre aide,
Cordialement,
Bonjour à tous !
L'opiniâtreté finira par payer......
Version...."x" du projet :
Bonjour à tous,
Merci JFL pour cette ultime version, je pense qu'on a fait le toru des différentes silutions possible,
Cette dernière version est similaire en vélocié par rapport à List.Generate ou List.Agregat,
En conclusion avec ma volumétrie de données puisées sur des fichiers extérieurs, pour :
- déterminer les valeurs mensuelles (onglet MENSUEL FDM), c'est ni List.Generate, ni List.Alternate mais bien une solution proposée par JFL sur un autre post qui est la plus rapide (temps divisé par 3),
- déterminer les valeurs cumulées (onglet CUMUL FDM), les Trois propositions List.Generate, List.Alternate et la version ultime de JFL sont équivalentes, et satisfaisantes en actualisation !
Merci à tous pour votre aide sur ce sujet clos,
Cordialement,
Bonjour à tous !
Merci JFL pour cette ultime version, je pense qu'on a fait le toru des différentes silutions possible,
Cette dernière version est similaire en vélocié par rapport à List.Generate ou List.Agregat,
C'est étonnant...
Sur 25000 lignes, le temps de réponse est +/- 5 secondes. Ce qui est, compte tenu de la triple contrainte (libellés, raison1 et date) très véloce me semble-t-il.
Ce test a été réalisé avec la table de 25000 lignes accompagnée de la requête concernée uniquement.
Bonjour JFL,
Les méthodes sont équivalentes chez moi, sur mon fichier de travail, cela met effectivement entre 5 à 7 secondes, que ce soit avec List.generate, List Alternate ou votre dernière version,
La grosse différence de temps est pour le calcul afin de :
- déterminer les valeurs mensuelles (onglet MENSUEL FDM), c'est ni List.Generate, ni List.Alternate mais bien une solution proposée par JFL
sur un autre post qui est la plus rapide (temps divisé par 3),
Cordialement,
Bonsoir à tous !
Suite à des tests, en situation comparable à ce que je pense être votre environnement ( lecture de sources externes puis retraitements), mon ressenti sur les trois méthodes (25.000 lignes et 4 champs) :
- List.Generate : de loin la plus véloce. Cette option, qui confirme mon idée première, est à privilégier.
- Méthode jointure sur index : moins rapide mais "acceptable".
- List.Accumulate : J'ai stoppé au bout de.....30 minutes !
Concernant la méthode visant à déterminer les valeurs mensuelles à partir de valeurs cumulées, la solution (dont vous m'imputez la paternité) est véloce mais nécessite de n'avoir que deux libellés dans le jeu de données. La rapidité trouve son origine dans le pivot qui divise par deux le nombre de lignes à traiter.
Remarque : Le double ajout de champs (Qtés mensuelles et Pertes mensuelles) pourrait être remplacé par une approche de type "record" qui permettrait, en une itération unique, de traiter les deux champs. Une itération unique versus deux = gain de temps.
Bonsoir à tous,
Bonsoir JFL,
Je rejoins votre ressenti sauf pour List.Accumulate mais comme le plus véloce reste List.Generate, j'ai choisi cette solution pour des données mensuelles afin d' obtenir des données cumulées.
Pour la situation inverse, (où l'on détermine des valeurs mensuelles à partir des valeurs cumulées), après mes tests la solution dont je vous attribue la paternité
Seulement je ne savais l'expliquer sans votre retour, néanmoins si il y a plus de deux libellés dans le jeu de données ce sera peut être moins vrai ? (vs List.Generate).
Votre remarque sous entend qu'il y aurait une autre approche encore plus véloce de type "Record" pour le calcul du cumul des Quantités mensuelles et des pertes mensuelles via une itération unique, mais je ne pense pas que je puisse arriver à réaliser cette solution.
Merci d'avance,
Cordialement,
Bonsoir à tous de nouveau !
Je n'ai pas testé cette méthode sur un jeu de données conséquent.
Proposition à tester :
let
Source = Excel.CurrentWorkbook(){[Name="TEST23"]}[Content],
TypeMod = Table.TransformColumnTypes(Source,{{"Date", type date}}),
ColDyn = Table.Pivot(TypeMod, List.Distinct(TypeMod[Libellés]), "Libellés", "Valeurs", List.Sum),
AjtAnnée = Table.AddColumn(ColDyn, "Année", each Date.Year([Date])),
Tri = Table.Buffer(Table.Sort(AjtAnnée,{{"Raison1", 0}, {"Date", 0}})),
GroupByMensuel = Table.Group(Tri, {"Année", "Raison1"}, {{"All", each Table.AddIndexColumn(Table.AddIndexColumn(_,"Idx0",0),"Idx1",1)}}),
Combine = Table.Combine(GroupByMensuel[All]),
Jointure = Table.NestedJoin(Combine, {"Année", "Raison1", "Idx0"}, Combine, {"Année", "Raison1", "Idx1"}, "All", JoinKind.LeftOuter),
Record = Table.AddColumn(Jointure, "Rec", each [
Quantités mensuelles = try [Quantités]-[All][Quantités]{0} otherwise [Quantités],
Pertes mensuelles = try [Pertes]-[All][Pertes]{0} otherwise [Pertes]
]),
ExpandRecord = Table.ExpandRecordColumn(Record, "Rec", {"Quantités mensuelles", "Pertes mensuelles"}),
SuppCol = Table.RemoveColumns(ExpandRecord,{"Quantités", "Pertes", "Année", "Idx0", "Idx1","All"}),
Unpivot = Table.UnpivotOtherColumns(SuppCol, {"Raison1", "Date"}, "Libellés", "Valeur"),
TriFinal = Table.Sort(Unpivot,{{"Libellés", 1}, {"Raison1", 0}, {"Date", 0}}),
Types = Table.TransformColumnTypes(TriFinal,{{"Raison1", type text}, {"Date", type date}, {"Libellés", type text}, {"Valeur", Int64.Type}})
in
TypesA voir si cette modification a une influence positive sur la rapidité d'exécution.
Bonsoir à tous,
Après essais, je ne vois pas de différence significative de temps d'exécution entre les deux méthodes (avec ou sans record),
Merci pour votre dernière proposition JFL que je retiendrais
Cordialement,
Bonsoir à tous !
C'est noté !
Je vous remercie de ce retour.