none
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!

    Tuesday, May 21, 2019 5:27 PM

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

    Wednesday, May 22, 2019 1:06 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
        Combined

    Input table in blue, output table in green.

                  

    Regards

    Wednesday, May 22, 2019 3:40 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.

    Wednesday, May 22, 2019 5:57 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

    Wednesday, May 22, 2019 1:06 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
        Combined

    Input table in blue, output table in green.

                  

    Regards

    Wednesday, May 22, 2019 3:40 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.

    Wednesday, May 22, 2019 5:57 PM