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. T...
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"
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?
Yes, you can add the code at the end. Just reference your last step in my source step instead what's in there currently.
