Forum Discussion
How to aggregate rows
Hi!
I am new to Power Query and I am trying to aggregate the following rows by adding a custom column that will sum my data by Direct Expense and Agency Client TP while keeping all the rows and columns in the data. The Agency Client TP is not unique and I need to find all Direct Expenses associated with that column while keeping the other columns of data. Can someone please let me know the best formula to do that or if I should be grouping instead? Thank you!
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.
4 Replies
Hello,
For this problem you can use Power Query's grouping and aggregation features. Load your data into power query editor, after that you'll want to group your data by the "Agency Client TP" column and then aggregate the "Direct Expense" coulmn by summing its values while keeping all other columns intact. Select the Agency Client TP column, go to the transform tab and choose group by, in the new column field, enter new column field enter a name for the new column, in the operation dropdown, select sum, and in the column dropdown select direct expense and make sure select the keep all rows, and then click ok button to apply the grouping and aggregation.
You caan group and aggregate your data.
For a clearer answer, please provide a sample data and the expected output
Thank you @Syndicate_Admin for your help. When I applied the first grouping and tried to aggregate, I do not see where I can keep all rows? I only have those two columns when I do that
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.
