Forum Discussion
Solved
Power Query SUMIFS with Multiple Conditions
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...
- You will need to list only the columns that are needed to create a unique grouping, so probably NO.
- For table type argument, you should list all of the columns that are in the "Grouped Table"
- In that case, I would use a different approach.
- I don't know how you will access [Original Balance].
- In the code below, I arbitrarily set it to 2x the value of the first [Ending Balance] so the results would be the same.
- 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"
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?
- You will need to list only the columns that are needed to create a unique grouping, so probably NO.
- For table type argument, you should list all of the columns that are in the "Grouped Table"
- In that case, I would use a different approach.
- I don't know how you will access [Original Balance].
- In the code below, I arbitrarily set it to 2x the value of the first [Ending Balance] so the results would be the same.
- 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"