Forum Discussion
hnguyen76
7 years agoResolver II
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...
- 7 years ago
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"