Forum Discussion

FinanceBI's avatar
FinanceBI
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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" 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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" 

  • FinanceBI's avatar
    FinanceBI
    Frequent Visitor
    7 years ago

    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.