Forum Discussion
Adding values of two or more rows
- 1 year ago
This can be solved using the Power Query `Group By` function, aggregating the `Quantity` column using `Sum` function.
You only show a single month, and all `GSE ID`s correspond to a single `GSE`. And all the `Hrs` are identical for a single `GSE ID`. If your actual data is different, you may have to change your groupings.
Paste into the Advanced Editor changing the Source line as appropriate
let //Replace Source line with your actual data source Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Year", type text}, {"Station", type text}, {"Month", type text}, {"GSE", type text}, {"GSE ID", type text}, {"Quantity", type number}, {"Hrs", Int64.Type}}), //Group by all the columns except Quantity //Sum the Quantity and use some function that will only return a single item in the hours #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Station", "Month", "GSE", "GSE ID", "Hrs"}, { {"Quantity", each List.Sum([Quantity]), type nullable number} }), #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Year", "Station", "Month", "GSE", "GSE ID", "Quantity", "Hrs"}) in #"Reordered Columns"Results:
This can be solved using the Power Query `Group By` function, aggregating the `Quantity` column using `Sum` function.
You only show a single month, and all `GSE ID`s correspond to a single `GSE`. And all the `Hrs` are identical for a single `GSE ID`. If your actual data is different, you may have to change your groupings.
Paste into the Advanced Editor changing the Source line as appropriate
let
//Replace Source line with your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Year", type text}, {"Station", type text}, {"Month", type text},
{"GSE", type text}, {"GSE ID", type text}, {"Quantity", type number},
{"Hrs", Int64.Type}}),
//Group by all the columns except Quantity
//Sum the Quantity and use some function that will only return a single item in the hours
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Station", "Month", "GSE", "GSE ID", "Hrs"}, {
{"Quantity", each List.Sum([Quantity]), type nullable number}
}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Year", "Station", "Month", "GSE", "GSE ID", "Quantity", "Hrs"})
in
#"Reordered Columns"
Results:
Thank you man. It worked using group by function.