Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
[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 |
Solved! Go to 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 : 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.