Translation of SUMIFS in Excel to Power Query / M

[Damyan_]

Hello,
how can i correctly translate the following formula from Excel to Power Query:
=IF(AND(OR(B8="410",B8="411",B8="415"),X8="210"),IFERROR(SUMIFS(Step5_Biovet!M:M,Step5_Biovet!A:A,E8,Step5_Biovet!D:D,G8)/SUMIFS(Step5_Biovet!E:E,Step5_Biovet!A:A,E8,Step5_Biovet!D:D,G8)*IF(LEFT(U8,6)="Biovet",J8,0),0)

I've managed to reach the following:
Profit =
if List.Contains({"410", "411", "415"}, Company) and [RelatedCompany] = "210" then
let
Numerator = List.Sum(Table.SelectRows(Step5_Biovet_filtered, each_["Item Number"] = [ItemID] and _["Batch number"] = [Batch])["profit/line"]),
Denominator = List.Sum(Table.SelectRows(Step5_Biovet_filtered, each _["Item Number"] = [ItemID] and _["Batch number"] = [Batch])["Quantity"] *
if Text.StartsWith(AccountName, "Biovet") then [newQty] else 0

Bear in mind, the column naming (from letters in Excel to column headings) is correct, but I keep getting an invalid identifier for the square bracket expression at the end (what I should sum by) e.g. ["profit/line"] even if I change it to something else. I'm guessing it has to do with the fact that I use 2 tables, which I should probably merge into one, although I'm a bit reluctant.

Do you have any idea what the problem might be? 
Thanks in advance.
Damyan

[AntrikshSharma · Accepted solution]

@Damyan_ if the column name has special characters like "/ ^ $ %" then you need to use this syntax [#"Profit/Line"] for accessing the column, or #"Profit & Line" when refrencing a step/query.

Batch Number has no special character but ["Batch number"] is not a correct syntax in M either use [#"Batch Number"] or just [Batch Number]

[v-dineshya · Accepted solution]

Hi @Damyan_ ,

Thank you for reaching out to the Microsoft Community Forum.

Please check the updated M code.

Profit =
let
currentItem = [ItemID],
currentBatch = [Batch],
currentCompany = [Company],
relatedCompany = [RelatedCompany],
currentAccountName = [AccountName],
newQty = [newQty],
numerator = List.Sum(
Table.SelectRows(Step5_Biovet_filtered, each
[Item Number] = currentItem and [Batch number] = currentBatch
)[#"profit/line"]
),
denominator = List.Sum(
Table.SelectRows(Step5_Biovet_filtered, each
[Item Number] = currentItem and [Batch number] = currentBatch
)[Quantity]
),
result = if List.Contains({"410", "411", "415"}, currentCompany) and relatedCompany = "210" then
try numerator / denominator * (if Text.StartsWith(currentAccountName, "Biovet") then newQty else 0) otherwise 0
else 0
in
result

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Damyan_]

Thanks, that was indeed a part of the problem. I suppose you meant [Batch number] though since it is case-sensitive, as far as I know at least and although it is a keyword.

[AntrikshSharma]

Yup, I didn't notice the lower case n.

[AntrikshSharma · Accepted solution]

@Damyan_ if the column name has special characters like "/ ^ $ %" then you need to use this syntax [#"Profit/Line"] for accessing the column, or #"Profit & Line" when refrencing a step/query.

Batch Number has no special character but ["Batch number"] is not a correct syntax in M either use [#"Batch Number"] or just [Batch Number]

[BA_Pete]

Hi @Damyan_ ,

An initial quick scan suggests you may have issues here:

'Company' should probably be in square brackets, and you've opened a 'let' block without a closing 'in' statement.

Beyond that, please provide some anonymised, copyable, sample data with expected output values that I can work with.

Pete

[Damyan_]

Your remarks are correct, I've just copied a piece of the code block, that's why not everything is visible and hence misleading from my side.