Getting line item total without changing granularity

[smko]

[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

[ponnusamy · Accepted solution]

@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.

[smko]

Hi, 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.