How to aggregate rows

Hi Syndicate_Admin 

You can create a blank query and put the following code to advanced editor in power query(This is the sample, you need to replace the source to yours)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLjFU0oHTiUBsaGQMIg2UYnUgCoygCoxQFBghFBhDFYDoJCA2MTUDksYIBSZQBSA6Ga7ABKHAFKoARKfArTAFKogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CSS Agency" = _t, #"Client Name per MFF" = _t, #"Direct Expense" = _t, #"Agency Client TP" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CSS Agency", type text}, {"Client Name per MFF", type text}, {"Direct Expense", type text}, {"Agency Client TP", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Direct Expense", "Agency Client TP"}, {{"Sum", each List.Sum([Value]), type nullable number}, {"Data", each _, type table [CSS Agency=nullable text, Client Name per MFF=nullable text, Direct Expense=nullable text, Agency Client TP=nullable number, Value=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"CSS Agency", "Client Name per MFF"}, {"CSS Agency", "Client Name per MFF"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"CSS Agency", "Client Name per MFF", "Direct Expense", "Agency Client TP", "Sum"})
in
    #"Reordered Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.