Forum Discussion
Helper IGetting line item total without changing granularity
[OrderID] and [Line Amount] are original columns, Im trying to calculate the [Calculated Total]. My current method is duplicating the table, group by [OrderID] sum [Line Amount], then merge back to the original table. While my method works I wish to do it without duplicating table and entirely in Power Query. Appreciate any input
| OrderID | Line Amount | Calculated Total |
| 1 | 10 | 60 |
| 1 | 20 | 60 |
| 1 | 30 | 60 |
| 2 | 40 | 90 |
| 2 | 50 | 90 |
smko : If you want to use 'Group by' function in Power Query.
let
Source = Excel.Workbook(File.Contents("C:\Users\ponnu\OneDrive - Fresh Direct\Forum Test Files\total by group id.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"OrderID", Int64.Type}, {"Line Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OrderID"}, {{"Calculated Total", each List.Sum([Line Amount]), type nullable number}, {"OtherRows", each _, type table [OrderID=nullable number, Line Amount=nullable number]}}),
#"Expanded OtherRows" = Table.ExpandTableColumn(#"Grouped Rows", "OtherRows", {"Line Amount"}, { "OtherRows.Line Amount"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded OtherRows",{"OrderID", "OtherRows.Line Amount", "Calculated Total"})
in
#"Reordered Columns"I created test data like yours
Use group by and creat Sum of lineitem by OrderID
Expand the Row from previous step to get other columns
If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.
3 Replies
- ponnusamy
Solution Supplier
smko : If you want to use 'Group by' function in Power Query.
let
Source = Excel.Workbook(File.Contents("C:\Users\ponnu\OneDrive - Fresh Direct\Forum Test Files\total by group id.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"OrderID", Int64.Type}, {"Line Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OrderID"}, {{"Calculated Total", each List.Sum([Line Amount]), type nullable number}, {"OtherRows", each _, type table [OrderID=nullable number, Line Amount=nullable number]}}),
#"Expanded OtherRows" = Table.ExpandTableColumn(#"Grouped Rows", "OtherRows", {"Line Amount"}, { "OtherRows.Line Amount"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded OtherRows",{"OrderID", "OtherRows.Line Amount", "Calculated Total"})
in
#"Reordered Columns"I created test data like yours
Use group by and creat Sum of lineitem by OrderID
Expand the Row from previous step to get other columns
If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.
smkoHi, thanks for the input. What's the following code means? Did you manually code it in formula bar?
{"OtherRows", each _, type table [OrderID=nullable number, Line Amount=nullable number]}
- ponnusamy
smko : Not necessarily , You also can use the tool bar to do that.
See the following document for more details
https://docs.microsoft.com/en-us/power-query/group-by
If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.