Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to Solution.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I received a "Token Identifier expected" error on the second let. Here's my total code that I'm using
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"}), let Source = #"Removed Columns" #"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", in #"Removed Columns"
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Is there a way for me to add the rest of my columns back? Quite possibly using Table.AddColumns? I can figure it out if that's the next step I need.
You can edit the last step "Expanded Partition" and select all columns from your original table that you want to expand.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, Thank you for your solution! You've been nice and a great help! Pleased to have worked with you!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |