Solved: Re: Conditional SUMIF Power Query with multiple cr...

hnguyen76 · ‎01-22-2019

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!

ImkeF · ‎01-23-2019

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

View solution in original post

Greg_Deckler · ‎01-22-2019

Invoking @ImkeF

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

ImkeF · ‎01-23-2019

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

hnguyen76 · ‎01-23-2019

@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?

ImkeF · ‎01-23-2019

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

hnguyen76 · ‎01-23-2019

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"

ImkeF · ‎01-23-2019

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

hnguyen76 · ‎01-23-2019

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.

ImkeF · ‎01-23-2019

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

hnguyen76 · ‎01-23-2019

@ImkeF, Thank you for your solution! You've been nice and a great help! Pleased to have worked with you!

Conditional SUMIF Power Query with multiple criteria

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly