Performance fonction récursive Power Query
Bonjour
Je suis nouveau ici et sur les techno Microsoft !
J'ai besoin de quelques conseils sur les fonctions récursives Power Query.
je dois faire l'action suivante :
J'ai une table de 300 000 lignes depuis une source SQL.
Je la requète et ajoute une colonne d'index.
Ensuite,
je dois mettre à jour la colonne QTY_NEEDED, ligne après ligne,
selon la valeur NUM_OF de la ligne précédente et aussi selon une variable de boucle calculé chaque tour (= quantitySoFar).
- je met à jour la ligne seulement si la valeur NUM_OF de la ligne précédente est égal à NUM_OF de la ligne en cours
- la nouvelle valeur deQTY_NEEDED est 0 si quantitySoFar est plus grand que QTY_TOSERVE sinon c'est QTY_TOSERVE - quantitySoFar
- quantitySoFar est mis à jour aussi dans chaque tour de récursion, si la valeur précédente de NUM_OF est différente de la valeur NUM_OF en cours, c'est (QTY_STOCK - QTY_TOSERVE), sinon c'est (quantitySoFar - QTY_TOSERVE)
je l'ai codé et ça marche
sauf que c'est très lent à s'exécuter sur toute la table. Ce n'est pas fini après 1h (j'ai arrété le calcul, je ne sais pas si ça finit)
avez vous des idées pour améliorer le code ?
(moins de colonne dans la requete SQL ? utiliser autre chose que "ReplaceValue" ? buffer si possible ?
Merci !
let
Source = dsBigSQL_OLEDB_DEV,
DynSQL = Value.NativeQuery (Source,"
SELECT
...
NUM_OF,
QTY_NEEDED,
QTY_TOSERVE,
QTY_STOCK,
DATE,
...
15 columns
FROM
...
WHERE
...
ORDER BY NUM_OF, DATE
"),
#"AddedIndex" = Table.AddIndexColumn(DynSQL, "Index", 1, 1),
updateTableLineByLine = (numRow, TableSoFar, quantitySoFar) as table =>
let
// table size
rowcount = Table.RowCount(TableSoFar),
// get from the previous row and the current row : NUM_OF, QTY_NEEDED
previousRow = Table.SelectRows( TableSoFar, each [Index] = numRow-1 ),
currentRow = Table.SelectRows( TableSoFar, each [Index] = numRow ),
previousNumOF = previousRow[NUM_OF]{0},
currentQuantityNeeded = currentRow[QTY_NEEDED]{0},
currentQuantityToServe = currentRow[QTY_TOSERVE]{0},
// Compute the new value of QTY_NEEDED and local variable quantitySoFar here
quantitySoFar = quantitySoFar - currentQuantityToServe
newQuantityNeeded = if ... then ... else ...
// Update the column QTY_NEEDED ONLY for the current row (=> column Index = numRow) and only if NUM_OF of previous equal NUM_OF of current row
TableWithNewValue = Table.ReplaceValue(TableSoFar,
each Record.Field(_,"QTY_NEEDED"),
each if Record.Field(_,"Index") = numRow && Record.Field(_,"NUM_OF") = previousNumOF then newQuantityNeeded else Record.Field(_,"QTY_NEEDED") ,
Replacer.ReplaceValue,
{"QTY_NEEDED"}
),
TableRecursive = if numRow +1 > rowCount then TableWithNewValue else @updateTableLineByLine(numRow +1, TableWithNewValue, quantitySoFar)
in
TableRecursive ,
result = updateTableLineByLine(2, AddedIndex, 0)
in
result
bonjour
on dirait bien une lecture de mouvements de stock, avec synthèse
selon moi ça relève d'un TCD et non de calculs ligne à ligne
joins un petit extrait de ta base (format Excel ou texte)
15 lignes suffisent
et le résultat attendu de ces 15 lignes
note : PQuery est un ETL, pas destiné à faire ce genre de manoeuvre (selon moi)
si on n'y arrive pas avec un TCD (ou des formules), on passera à Power Pivot ou Power BI (tous 2 avec DAX qui est plus adapté)
note 2 : quelles sont les performances de ton PC?
à te relire
amitiés
merci pour le premier retour.
Oui c'est bien ça, c'est un mouvement de stock.
je met un exemple en pièce jointe.
Le résultat du calcul est dans un modèle tabulaire SSAS, puis exploité en Power BI en Direct Query,
du coup, on ne peut pas faire de DAX ?
Un TCD ? (tableau croisé ?), j'ai du mal à visualiser pour l'instant comment ça peut être fait
mais mon exemple peut peut être aider.
Côté performance, je ne sais pas trop pour l'instant.
Je travaille sur un Visual Studio / SSAS sur un serveur de Dev de la boite
vite un message pour te dire que DAX est le langage de PBI et il sait faire, mais c'est pas simple
je regarde
je ne comprends pas cette gestion de stock
un OF, c'est un stock de départ, une quantité fabriquée, et éventuellement une quantité expédiée (qui est >= stockdedépart + qtéfabriquée)
la somme de tous les OF est le stock actuel
du coup, je suis perdu
pour etre plus precis, dans l'exemple, il y a un stock par OF et chaque ligne represente une quantité a servir / fabriquer
a chaque ligne servie / fabriquée en quantié demandée, le stock de l'OF est diminuée, jusqu'à 0 (objet du calcul recursif). à ce moment, les prochaines lignes de la meme OF ne peuvent pas etre servie et la quantité manquante est alors calculée.
OF signifie dans l'exemple Ordre de Fabrication
j'espere que ça aide