Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | |||
Vendor | Type | Product | Amount |
Taylor | Individual | 132 | 5 |
Taylor | Group | 123 | 3 |
Taylor | Group | 123 | 4 |
Taylor | Group | 123 | 8 |
Uline | Individual | 123 | 9 |
Uline | Individual | 123 | 7 |
Desired Result | |||
Vendor | Type | Product | Amount |
Taylor | Individual | 132 | 5 |
Taylor | Group | 123 | 15 |
Uline | Individual | 123 | 16 |
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
Solved! Go to Solution.
HI There,
Probably there are other ways t handle this too but you could handle it inside of query editor by groupings option.
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"
HI There,
Probably there are other ways t handle this too but you could handle it inside of query editor by groupings option.
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.
User | Count |
---|---|
127 | |
108 | |
100 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |