cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FinanceBI Frequent Visitor
Frequent Visitor

Sum all duplicates with filters

Hi All, 

 

First off, thank you for taking the time to help me with this. I'm attempting to sum all duplicate values based on a couple of filters. Here is an example of what my current table looks like versus what my desired table looks like. 

 

Current Table
VendorTypeProductAmount
TaylorIndividual 1325
TaylorGroup1233
TaylorGroup1234
TaylorGroup1238
UlineIndividual 1239
UlineIndividual 1237
    
Desired Result
VendorTypeProductAmount
TaylorIndividual 1325
TaylorGroup12315
UlineIndividual 12316

 

I have a data table linked to multiple lookup tables to generate the vendor/type values. I want to sum the "Amount" column for every unique combination of the "Vendor" and "Type" columns if the "Product" column is a duplicate. 

 

Thanks,

 

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
Arslan Frequent Visitor
Frequent Visitor

Re: Sum all duplicates with filters

HI There,

 

Probably there are other ways t handle this too but you could handle it inside of query editor by groupings option.

Capture.PNG

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUtJRCqksSAVSAUX5KaXJJUCWY25+aV6JUqxOtFJIYmUOWJFnXkpmWWZKaWKOApBnaGwEJE1RlbgX5ZcWgCSNjIGkMT5JE3ySFmDJ0JzMvFRMi8EqLAmqMFeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Table" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Table", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor", type text}, {"Type", type text}, {"Product", Int64.Type}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Vendor", "Type", "Product"}, {{"Amount", each List.Sum([Amount]), type number}})
in
#"Grouped Rows" 

2 REPLIES 2
Arslan Frequent Visitor
Frequent Visitor

Re: Sum all duplicates with filters

HI There,

 

Probably there are other ways t handle this too but you could handle it inside of query editor by groupings option.

Capture.PNG

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUtJRCqksSAVSAUX5KaXJJUCWY25+aV6JUqxOtFJIYmUOWJFnXkpmWWZKaWKOApBnaGwEJE1RlbgX5ZcWgCSNjIGkMT5JE3ySFmDJ0JzMvFRMi8EqLAmqMFeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Table" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Table", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor", type text}, {"Type", type text}, {"Product", Int64.Type}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Vendor", "Type", "Product"}, {{"Amount", each List.Sum([Amount]), type number}})
in
#"Grouped Rows" 

FinanceBI Frequent Visitor
Frequent Visitor

Re: Sum all duplicates with filters

Thank you for the answer! Although I was hoping to do this without merging my tables together I decided this solution was probably the best.