Translation SUM Excel formula to Power Query / M

[Damyan_]

Hey guys,
it's a bit of a follow up but:
I am trying to translate Excel formulas into Power Query. I have the following:
=IF(AND(OR(B2="410",B2="411",B2="415"),X2="210"),IFERROR(SUMIFS(Step5_Biovet!M:M,Step5_Biovet!A:A,E2,Step5_Biovet!D:D,G2)/SUMIFS(Step5_Biovet!E:E,Step5_Biovet!A:A,E2,Step5_Biovet!D:D,G2)*IF(LEFT(U2,6)="Biovet",J2,0),0),IF(C2="Purchase order",IF(OR(B2="310",B2="610"),IFERROR(SUMIFS(Step4_CIL_auto_filter!$AI:$AI,Step4_CIL_auto_filter!$E:$E,Q2,Step4_CIL_auto_filter!$R:$R,G2)/SUMIFS($J:$J,$Q:$Q,Q2,$G:$G,G2,$B:$B,B2,$C:$C,C2)*J2,0),IF(B2="160",IFERROR(SUMIFS(Step4_CIL_auto_filter!$AI:$AI,Step4_CIL_auto_filter!$C:$C,B2,Step4_CIL_auto_filter!$P:$P,O2,Step4_CIL_auto_filter!$R:$R,G2)/SUMIFS(Step4_CIL_auto_filter!$T:$T,Step4_CIL_auto_filter!$C:$C,B2,Step4_CIL_auto_filter!$P:$P,O2,Step4_CIL_auto_filter!$R:$R,G2)*J2,0),IFERROR(SUMIFS(Step4_CIL_auto_filter!$AI:$AI,Step4_CIL_auto_filter!$C:$C,B2,Step4_CIL_auto_filter!$P:$P,E2,Step4_CIL_auto_filter!$R:$R,G2,Step4_CIL_auto_filter!$R:$R,G2,Step4_CIL_auto_filter!$A:$A,X2)/SUMIFS(Step4_CIL_auto_filter!$V:$V,Step4_CIL_auto_filter!$C:$C,B2,Step4_CIL_auto_filter!$P:$P,E2,Step4_CIL_auto_filter!$R:$R,G2,Step4_CIL_auto_filter!$A:$A,X2)*J2,0))),0))

This is the Power Query, which I've come to:
Add_ProfitPurchase = Table.AddColumn(profitOB, "profit purchase", each
let
Profit =
if List.Contains({410, 411, 415}, [Company]) and [Related Company] = 210 then
let
Numerator = List.Sum(
Table.SelectRows(Step5_Biovet_filtered, each _[Item Number] = [ItemID] and _[Batch number] = [Batch])[#"profit/line"]
),
Denominator =
if Text.StartsWith([Account name], "Biovet") then
List.Sum(
Table.SelectRows(Step5_Biovet_filtered, each _[Item Number] = [ItemID] and _[Batch number] = [Batch])[Quantity]
) * [newQty]
else 0
in
try Numerator / Denominator
otherwise 0

else if [Reference] = "Purchase order" then
if List.Contains({310, 610}, [Company]) then
let
Numerator = List.Sum(Table.SelectRows(Step4_CIL, each _[newInvoice] = [Invoice] and _[newBatch] = [Batch])[Profit]),
Denominator = List.Sum(Table.SelectRows(profitOB, each _[Invoice] = [Invoice] and _[Batch] = [Batch] and _[Company] = [Company] and _[Reference] = [Reference])[newQty]) * [newQty]
in
try Numerator / Denominator otherwise 0

else if [Company] = 160 then
let
Numerator = List.Sum(Table.SelectRows(Step4_CIL, each _[Organ N] = [Company] and _[newItem ID] = [Item ID] and _[newBatch] = [Batch])[Profit]),
Denominator = List.Sum(Table.SelectRows(Step4_CIL, each _[Organ N] = [Company] and _[newItem ID] = [Item ID] and _[newBatch] = [Batch])[#"Invent Qty"]) * [newQty]
in
try Numerator / Denominator otherwise 0

else
let
Numerator = List.Sum(
Table.SelectRows(Step4_CIL, each _[Organ N] = [Company] and _[newItem ID] = [Item ID] and _[newBatch] = [Batch] and _[Sell Com] = [Related Company])[Profit]
),
Denominator = List.Sum(
Table.SelectRows(Step4_CIL, each _[Organ N] = [Company] and _[newItem ID] = [Item ID] and _[newBatch] = [Batch] and _[Sell Com] = [Related Company])[#"new Sales Qty"]
) * [newQty]
in
try Numerator / Denominator otherwise 0

else 0
in
Profit
)
in
Add_ProfitPurchase


Assuming all renamings from Excel columns (B,C,X) are correct, is the logic the same? I get only zeros where as in the excel I have numbers. So maybe the code always goes to else 0 somehow.

Thanks in advance

[v-sdhruv]

Hi @Damyan_ ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @Damyan_ ,
You can try using this:
Table.AddColumn(profitOB, "Branch", each
if List.Contains({410, 411, 415}, [Company]) and [Related Company] = 210 then "Biovet"
else if [Reference] = "Purchase order" and List.Contains({310, 610}, [Company]) then "PO 310/610"
else if [Reference] = "Purchase order" and [Company] = 160 then "PO 160"
else if [Reference] = "Purchase order" then "PO Other"
else "No Match"
)
This will help you to see which branch of logic is being trigggered.
Also while using the logic :
try Numerator / Denominator otherwise 0,

If denominator= 0, this will correctly fall back to 0. But if Numerator is also zero or null, that might also trigger this fallback. Make sure Numeratorand Denominator are non-null and non-zero in your test data.
Hope this helps!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeaBF]

@Damyan_ Hi! You have to debug the code.

1. Create a temporary column with:

Table.AddColumn(profitOB, "Check_CompanyMatch", each List.Contains({410, 411, 415}, [Company]) and [Related Company] = 210)

and see which rows are TRUE.

2. Add this to see how many rows are matching

let
FilteredRows = Table.SelectRows(Step5_Biovet_filtered, each _[Item Number] = [ItemID] and _[Batch number] = [Batch]),
RowCount = Table.RowCount(FilteredRows),
Numerator = List.Sum(FilteredRows[profit/line])
in
[RowCount, Numerator]

and check these:

• Data types

• Column names

• Case-sensitivity

• Empty filtered tables

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[Damyan_]

@BeaBF Hi,
I've implemented the first line and it's taking ages to load for some unknown reason. I've cleared the cache, buffered the profitOB table and still it's extremely slow. 
I do not know what the reason is.