Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FinanceBI
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
Anonymous
Not applicable

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" 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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" 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.