Power Query SUMIFS with Multiple Conditions

[JustDavid]

As the subject says, given I'd like to do SUMIFS in PowerQuery with multiple conditions.

Using the print screen as an example, let's say PQ is in Row 22 (UID = B, Cost Code = Z101, Period = 3 and Ending Balance = 12), what I'd like PQ to return for that specific row 22 is:

Ending Balance (which is 12)

LESS

SUMIFS([Ending Balance], [UID] = "B", [Cost Code] = "Z101", [Period] = 3-1) (which in this case will return the result of 50, row 14).

Thus resulting the result to be 12 - 50 = -38

Note that screenshot I made it into unique values just for simplicity, in actuality, there's duplicates of UID, Cost Code, Period and Ending Balance, thus need SUMIFS.

Excel Sample File 

[JustDavid]

@ronrsnfld  Thank you!

I've accepted your solution. Just modified a little on your original post and in it, I just created 12 columns to see the change for 12 periods. With it, when it's period 1, just take the #result and less [Original Amount].

Thank you!

If I may ask, what's the double question mark (??) formula doing in this line of code?

#"Result" = Table.AddColumn(#"Shifted Balance","Desired Result", each [Ending Balance] - [Shifted Balance]??[Ending Balance])

[ronrsnfld]

That's in the original code.

When the Shifted column is created, the first entry is null.  So [Ending Balance]-[Shifted Balance] => null.

The coalesce operator replaces the null with the result which, initially, you wanted to be the [Ending Balance]

In the original code, you could replace the second argument of the coalesce operator resulting in

each [Ending Balance] - [Shifted Balance]??#"Original Balance"-[Ending Balance])

and that would also work.

The Index column method is just another way of doing things. You can try both and see which runs better on your actual data.

[Omid_Motamedise]

Based on your data, use the next formula in a new column namely Desired result (if the previous step is Source)


if [Period]=1 then [Ending Balance] else ([Ending Balance]-List.Last(Table.SelectRows(Source,(x)=> _[Cost Cod]=x[Cost Cod] and x[Period]<_[Period] and _[UID]=x[UID])[Ending Balance]))

[JustDavid]

@Omid_Motamedise Thanks for replying to my question.

For your code here, would you be able to explain to me the logic for the SUMIFS?

I'm assuming your code here works because of my dummy data which is simplified and no duplicates?

EDIT: Tried to implement your solution, however unable to verify if it works as it's been 15 minutes and it's till trying to "refresh" the preview. Thus had to abort it.

[Omid_Motamedise]

Sure,
in the proposed solution consider this part.

Table.SelectRows(Source,(x)=> _[Cost Cod]=x[Cost Cod] and x[Period]<_[Period] and _[UID]=x[UID])

for each row of table, it going to filter all the rows of that table with the same Cost code and UID but and with equal or less than period value .

by adding [Ending Balance] at the end of filtered table, the result is converted into a list including all the values in this column and then by list.last the will be selected.

to use this formula, your data should be sorted based on the period, otherwise you can use the next formula.

if [Period]=1 then [Ending Balance] else ([Ending Balance]-List.Last(Table.SelectRows(Source,(x)=> _[Cost Cod]=x[Cost Cod] and (x[Period]=_[Period]-1) and _[UID]=x[UID])[Ending Balance]))

[ronrsnfld]

This should do it based on the data you present. However, when you write "there are duplicates", without seeing a truly representative sample, it would be no surprise if this solution won't work on your actual data. If it doesn't, try posting a more representative data sample.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"UID", type text}, {"Cost Code", type text}, {"Period", Int64.Type}, {"Ending Balance", Int64.Type}}),

//Index column to be able to restore processed results to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by UID and Cost Code
//  then compute the SUMIFS using a shifted column
// ASSUMES data sorted in Period order
    #"Grouped Rows" = Table.Group(#"Added Index", {"UID", "Cost Code"}, {
        {"Desired Result", (t)=>
            let 
                #"Shifted Balance" = Table.FromColumns(
                    Table.ToColumns(t) & 
                        {{null} & List.RemoveLastN(t[Ending Balance],1)},
                        {"UID","Cost Code","Period","Ending Balance","Index", "Shifted Balance"}),
                #"Result" = Table.AddColumn(#"Shifted Balance","Desired Result", each [Ending Balance] - [Shifted Balance]??[Ending Balance])
                in Table.RemoveColumns(#"Result",{"Shifted Balance"}), 
                    type table [UID=nullable text, Cost Code=nullable text, Period=nullable number, 
                        Ending Balance=nullable number,Index=Int64.Type, Desired Result=nullable number]}}),

//Expand grouped table, sort and cleanup
    #"Expanded Desired Result" = Table.ExpandTableColumn(#"Grouped Rows", "Desired Result", 
        {"Period", "Ending Balance", "Index", "Desired Result"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Desired Result",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[JustDavid]

@ronrsnfld Thank you for the reply.

Had a quick question with part of your PQ.

In this part here, do I need to write down all columns that I had with an exception to 'Index', 'Period' and 'Ending Balance'?

#"Grouped Rows" = Table.Group(#"Added Index", {"UID", "Cost Code"}, {

For these 2 part here, again, do I have to write/list down ALL columns? Where the 1st part is {UID, Cost Code, Period ...}  and  the 2nd part is [UID=nullable text, Cost Code=nullable text ...]

 Table.ToColumns(t) & 
                        {{null} & List.RemoveLastN(t[Ending Balance],1)},
                        {"UID","Cost Code","Period","Ending Balance","Index", "Shifted Balance"})

type table [UID=nullable text, Cost Code=nullable text, Period=nullable number, 
                        Ending Balance=nullable number,Index=Int64.Type, Desired Result=nullable number]

I realized a huge mistake in my logic in my original question. For period 1, instead of taking the balance of ending balance, I need to take the [Ending Balance] Less [Original Balance] (original balance is not in my demo). In the Power Query, I'm assuming that this piece of "code" determines the result.

#"Result" = Table.AddColumn(#"Shifted Balance","Desired Result", each [Ending Balance] - [Shifted Balance]??[Ending Balance])

 My question then is, how do I adjust the formula to look [Ending balance] Less [Original Balance] when it's period 1?

[ronrsnfld · Accepted solution]

1. You will need to list only the columns that are needed to create a unique grouping, so probably NO.
2. For table type argument, you should list all of the columns that are in the "Grouped Table"
3. In that case, I would use a different approach.
   1. I don't know how you will access [Original Balance].
   2. In the code below, I arbitrarily set it to 2x the value of the first [Ending Balance] so the results would be the same.
   3. You will need to modify depending on how you have your "Period 1 Starting Balance"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"UID", type text}, {"Cost Code", type text}, {"Period", Int64.Type}, {"Ending Balance", Int64.Type}}),

//Index column to be able to restore processed results to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by UID and Cost Code
//  then compute the SUMIFS using a shifted column
// ASSUMES data sorted in Period order
    #"Grouped Rows" = Table.Group(#"Added Index", {"UID", "Cost Code"}, {
        {"Desired Result", (t)=>
            let
                #"Original Balance" = t{0}[Ending Balance] * 2, 
                #"Add Index2" = Table.AddIndexColumn(t,"Index2",0,1,Int64.Type),
                #"Add Results" = Table.AddColumn(#"Add Index2", "Desired Results", each 
                    if [Index2]=0 then #"Original Balance"-[Ending Balance]
                    else [Ending Balance] - #"Add Index2"{[Index2]-1}[Ending Balance], type number)
            in #"Add Results",
            type table[UID=text, Cost Code=text, Period=Int64.Type, Ending Balance=number, Index=Int64.Type, Index2=Int64.Type, Desired Results = number]}}),
    
    #"Expanded Desired Result" = Table.ExpandTableColumn(#"Grouped Rows", "Desired Result", {"Period", "Ending Balance", "Index", "Desired Results"}, {"Period", "Ending Balance", "Index", "Desired Results"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Desired Result",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"