Hi Power BI community !
I need help on a power query script I have created for transforming one of my table in a dataflow.
Context
I have two tables :
- transaction ; this table contains the list of transactions generated by my activity. For each transaction, I have several transaction_items each of them having transaction_item_amount data that contains the price for the item
- lien_transac_remises ; this table contains a list of discounts that have been applied on our products. Each line makes the link between transaction_item_id and the discount (decimal number between 0 and 1). Note that one product can be applied several discounts (no real limit)
What I'm trying to achieve
I want to recalculate the transaction_item_amount with the discount(s) applied on it. So I replace the content with the discounted content.
How I did it
For now, here is the power query M script I did (it also does several other transformations but the most interesting part is the second part of the file) :
let
Source = transactions,
NoIntermag = Table.SelectRows(Source, each [transaction_sub_type] <> "intermagasin"),
OnlyVentes = Table.SelectRows(NoIntermag, each [transaction_type] = "vente"),
Ventes = Table.SelectRows(OnlyVentes, each [shop_go_id] <> "632c5c860078140014ced90e"),
#"Remove columns" = Table.RemoveColumns(
Ventes,
Table.ColumnsOfType(
Ventes, {type table, type record, type list, type nullable binary, type binary, type function}
)
),
#"Inserted conditional column" = Table.AddColumn(
#"Remove columns",
"Client encarté",
each if [customer_go_id] = null then false else if [transaction_sub_type] = "bon_de_commande" then false else true
),
ChangeTypeClientEncarte = Table.TransformColumnTypes(
#"Inserted conditional column", {{"Client encarté", type logical}}
),
CalculMontantRemiseIncluse = Table.FromRecords(
Table.TransformRows(
ChangeTypeClientEncarte,
(row) =>
Record.TransformFields(
row,
{
{
"transaction_item_amount",
each
let
transactionItemId = row[transaction_item_id],
listeRemises = Table.SelectRows(
lien_transac_remises, each [transaction_item_id] = transactionItemId
),
remisesAppliquees = Table.Column(listeRemises, "Pourcentage_de_remise"),
//calculMontantRemise = Number.Round(montantInitial * List.Product(remisesAppliquees), 2),
calculMontantRemise = Number.Round(
List.Accumulate(remisesAppliquees, _, (acc, remise) => acc * (1 - remise)), 2
),
montantRemise = if calculMontantRemise is null then 0 else calculMontantRemise
in
montantRemise
}
}
)
),
Value.Type(ChangeTypeClientEncarte)
),
MontantRemiseIncluse = Table.TransformColumnTypes(
CalculMontantRemiseIncluse, {"transaction_item_amount", type number}
)
in
MontantRemiseIncluse
My issue
This code seems to work. But it's TERRIBLY low. I launched the dataflow update yesterday evening and it hasn't finished yet 😭
I assume the issue lies in the request I do to the lien_transac_remises table from the transaction table (with selectRows). But I'm not sure and I NEED YOUR HELP !
