Answered by:
Sumif equivalent
Question

Hello,
I have data organized as follows:
PO Num, $Amount
1, 5
1, (5)
2, 12
2, (6)
2, (6)
3, 9
3, (7)
I want a calculated column in my data model that for each row sums the amounts associated with the PO number in that row. So the first five rows would sum to zero, and the last two rows would sum to 2. This is a GRNI (goods received not invoiced) account, so when the goods are received without an invoice, we input the first transaction, then when we receive the invoice, we input the second transaction. My end goal is to find all the POs we still haven't received an invoice for. I would use sumif in Excel, but there are 300k rows.
Thank you!
Answers

You will need to Group By. Before that, if your "$Amount" in Power Query shows negative numbers as () then you can turn it into negatives with the following
#"Replaced Value" = Table.ReplaceValue(#"your previous step","(","",Replacer.ReplaceText,{"$Amount"}), #"Cleaned Text" = Table.TransformColumns(#"Replaced Value",{{"$Amount", each Number.FromText(Text.Trim(_,")")), type number}}),
Ignore this if your PQ interprets negatives correctly.
Then you group by all the dimensions you want, "PO Num" and whatever else, and sum $Amount.
#"Grouped Rows" = Table.Group(#"Cleaned Text", {"PO Num"}, {{"$Sum", each List.Sum([$Amount]), type number}})
If there are more columns you'd want to keep, you'd either have to add them to "grouped by dimensions" or aggregate them like $Amount somehow
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM

Quite similar. Create a new table agrregating the $Amount values by PO Num, then (this is my contribution) join both tables: the original (Source) and the grouped one(Grouped) in another table (Combined).
let
Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
Grouped = Table.Group(Source, {"PO Num"}, {{"Total_Amount", each List.Sum([#"$Amount"]), type number}}),
Combined = Table.Join(Source, "PO Num", Grouped, "PO Num")
in
CombinedInput table in blue, output table in green.
Regards
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM

Alternatively, if you wish to avoid grouping 300K rows and then joining 300K rows, you can group as follows:
groupedRows = Table.Group(<PreviousStepName>, {"PO Num"}, {{"Table", (i) => Table.AddColumn(i, "Total Amount", each List.Sum(i[#"$Amount"])), type table}}), combinedTables = Table.Combine(groupedRows[Table]) in combinedTables
However, you should select the option that provides the desired output and the best performance.
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM
All replies

You will need to Group By. Before that, if your "$Amount" in Power Query shows negative numbers as () then you can turn it into negatives with the following
#"Replaced Value" = Table.ReplaceValue(#"your previous step","(","",Replacer.ReplaceText,{"$Amount"}), #"Cleaned Text" = Table.TransformColumns(#"Replaced Value",{{"$Amount", each Number.FromText(Text.Trim(_,")")), type number}}),
Ignore this if your PQ interprets negatives correctly.
Then you group by all the dimensions you want, "PO Num" and whatever else, and sum $Amount.
#"Grouped Rows" = Table.Group(#"Cleaned Text", {"PO Num"}, {{"$Sum", each List.Sum([$Amount]), type number}})
If there are more columns you'd want to keep, you'd either have to add them to "grouped by dimensions" or aggregate them like $Amount somehow
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM

Quite similar. Create a new table agrregating the $Amount values by PO Num, then (this is my contribution) join both tables: the original (Source) and the grouped one(Grouped) in another table (Combined).
let
Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
Grouped = Table.Group(Source, {"PO Num"}, {{"Total_Amount", each List.Sum([#"$Amount"]), type number}}),
Combined = Table.Join(Source, "PO Num", Grouped, "PO Num")
in
CombinedInput table in blue, output table in green.
Regards
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM

Alternatively, if you wish to avoid grouping 300K rows and then joining 300K rows, you can group as follows:
groupedRows = Table.Group(<PreviousStepName>, {"PO Num"}, {{"Table", (i) => Table.AddColumn(i, "Total Amount", each List.Sum(i[#"$Amount"])), type table}}), combinedTables = Table.Combine(groupedRows[Table]) in combinedTables
However, you should select the option that provides the desired output and the best performance.
 Proposed as answer by Imke FeldmannMVP, Moderator Sunday, May 26, 2019 5:18 AM
 Marked as answer by Imke FeldmannMVP, Moderator Sunday, June 16, 2019 9:22 PM