Forum Discussion

umairarshad's avatar
umairarshad
Icon for Helper II rankHelper II
1 year ago
Solved

Adding values of two or more rows

Hi all, I am working in excel power query. I have the following data.     and i require below data from the above.      i.e all values of same GSE ID should be added/sum in one row...
  • ronrsnfld's avatar
    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: