Forum Discussion
Solved
Conditional SUMIF Power Query with multiple criteria
Good morning, All!
I have a transaction list from Oracle ERP and I'm trying to do a sumif with a few conditions in Power Query. I've tried doing a GroupBy but I'm not getting the right numbers. There are three columns: Batch Number, Receipt Amount, and Batch Status. I need to do a sumif:
1. Batch Numbers are the same
2. Receipt Amounts (Absolute) are the same
3. Batch Status is both "Applied" and "Reversal"
The desireable output would be:
| Batch Number | Receipt Amount | Batch Status | Amount |
| 33135 | 53492.25 | Applied | 53492.25 |
| 33135 | -53492.3 | Applied | 0 |
| 33135 | 53492.25 | Reversal | 0 |
| 33135 | -53492.3 | Unapplied | -53492.3 |
I know it's much simpler with DAX but there's a few reports that I need to append in Power Query. Any help would be greatly appreciated!
Ok, so you have to modify the code a bit:
let Source = Folder.Files("\\Cash Application\Cash App - Oracle"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Cash App - Oracle", each #"Transform File from Cash App - Oracle"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Cash App - Oracle"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Cash App - Oracle", Table.ColumnNames(#"Transform File from Cash App - Oracle"(#"Sample File (7)"))), #"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"Column1", "Batch Number"}, {"Column2", "Document Date"}, {"Column3", "Customer Name"}, {"Column4", "Customer Number"}, {"Column5", "Document Number"}, {"Column6", "Posting Date"}, {"Column7", "Entry Date"}, {"Column8", "Receipt Amount"}}), #"Fiscal year" = Table.AddColumn(#"Renamed Columns", "Fiscal Year", each Date.Year([Entry Date])), #"Added Custom" = Table.AddColumn(#"Fiscal year", "Posting Period", each Date.Month([Entry Date])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Document Date", type date}, {"Posting Date", type date}, {"Entry Date", type date}, {"Receipt Amount", type number}, {"Document Number", type text}, {"Company Code", type text}, {"Batch Status", type text}, {"Fiscal Year", Int64.Type}, {"Posting Period", Int64.Type}}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Receipt Amount"}), #"Replaced Reversal" = Table.ReplaceValue(#"Removed Errors","Reversal-","Reversal",Replacer.ReplaceText,{"Batch Status"}), #"Replaced Unidentif" = Table.ReplaceValue(#"Replaced Reversal","Unidentif","Unapplied",Replacer.ReplaceText,{"Batch Status"}), #"Added ERP" = Table.AddColumn(#"Replaced Unidentif", "ERP", each "Oracle"), #"Removed Columns" = Table.RemoveColumns(#"Added ERP",{"Customer Name", "Customer Number"}), Source1 = #"Removed Columns" #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"Batch Number", Int64.Type}, {"Receipt Amount", type number}, {"Batch Status", type text}}), #"Inserted Sign" = Table.AddColumn(#"Changed Type1", "Abs", each Number.Abs([Receipt Amount]), Int64.Type), #"Added Custom1" = Table.AddColumn(#"Inserted Sign", "Custom", each if ([Batch Status] = "Reversal" and [Receipt Amount] > 0) or ([Batch Status] = "Applied" and [Receipt Amount] < 0) then "ReversalP" else if ([Batch Status] = "Reversal" and [Receipt Amount] < 0) or ([Batch Status] = "Applied" and [Receipt Amount] >0) then "ReversalN" else [Batch Status]), #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"Custom", "Batch Number", "Abs"}, {{"Sum", each List.Sum([Receipt Amount]), type number}, {"Partition", each _, type table}}), #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Index"}, {"Index"}) in #"Expanded Partition"
9 Replies
Invoking ImkeF
Does this work?:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2NDZV0lEyNTaxNNIzAjEdCwpyMlNTlGJ1ENK6BOSRpINSy1KLihNzsOjXASsIzUuEmRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Batch Number" = _t, #"Receipt Amount" = _t, #"Batch Status" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch Number", Int64.Type}, {"Receipt Amount", type number}, {"Batch Status", type text}}), #"Inserted Sign" = Table.AddColumn(#"Changed Type", "Abs", each Number.Abs([Receipt Amount]), Int64.Type), #"Added Custom" = Table.AddColumn(#"Inserted Sign", "Custom", each if ([Batch Status] = "Reversal" and [Receipt Amount] > 0) or ([Batch Status] = "Applied" and [Receipt Amount] < 0) then "ReversalP" else if ([Batch Status] = "Reversal" and [Receipt Amount] < 0) or ([Batch Status] = "Applied" and [Receipt Amount] >0) then "ReversalN" else [Batch Status]), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"Custom", "Batch Number", "Abs"}, {{"Sum", each List.Sum([Receipt Amount]), type number}, {"Partition", each _, type table}}), #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Index"}, {"Index"}) in #"Expanded Partition"ImkeF, Good morning!
Thanks for replying! I have a much larger query with more columns involved. Would that make a difference in your code? I will have the query steps posted below:let Source = Folder.Files("\\Cash Application\Cash App - Oracle"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Cash App - Oracle", each #"Transform File from Cash App - Oracle"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Cash App - Oracle"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Cash App - Oracle", Table.ColumnNames(#"Transform File from Cash App - Oracle"(#"Sample File (7)"))), #"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"Column1", "Batch Number"}, {"Column2", "Document Date"}, {"Column3", "Customer Name"}, {"Column4", "Customer Number"}, {"Column5", "Document Number"}, {"Column6", "Posting Date"}, {"Column7", "Entry Date"}, {"Column8", "Receipt Amount"}}), #"Fiscal year" = Table.AddColumn(#"Renamed Columns", "Fiscal Year", each Date.Year([Entry Date])), #"Added Custom" = Table.AddColumn(#"Fiscal year", "Posting Period", each Date.Month([Entry Date])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Document Date", type date}, {"Posting Date", type date}, {"Entry Date", type date}, {"Receipt Amount", type number}, {"Document Number", type text}, {"Customer Code", type text}, {"Batch Status", type text}, {"Fiscal Year", Int64.Type}, {"Posting Period", Int64.Type}}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Receipt Amount"}), #"Replaced Reversal" = Table.ReplaceValue(#"Removed Errors","Reversal-","Reversal",Replacer.ReplaceText,{"Batch Status"}), #"Replaced Unidentif" = Table.ReplaceValue(#"Replaced Reversal","Unidentif","Unapplied",Replacer.ReplaceText,{"Batch Status"}), #"Added ERP" = Table.AddColumn(#"Replaced Unidentif", "ERP", each "Oracle") in #"Added ERP"Can I just add your code at the end? Also, concerning processing time, do we need to add Table.Buffer anywhere?
