Forum Discussion
Translation SUM Excel formula to Power Query / M
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.
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!
