Forum Discussion
Getting line item total without changing granularity
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.
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]}
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.