Forum Discussion

Damyan_'s avatar
Damyan_
New Member
1 year ago
Solved

Translation SUM Excel formula to Power Query / M

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_Biove...
Need Help
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    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.

v-sdhruv's avatar
v-sdhruv
Community Support
1 year ago

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.