Forum Discussion

umairarshad's avatar
umairarshad
Helper 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 only. how to do it in power query?

Thanks and regards,

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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:

     

     

     

     

     

3 Replies

  • uzuntasgokberk's avatar
    uzuntasgokberk
    Super User
    1 year ago

    Hello umairarshad ,

     

    You can use the solution below the images.

    Go to the table that leads to power query.

    Click Group by. The table is an example data.

    Example how to do it and result of the table.

    Lastly, close & load.

    Kind Regards,
    Gökberk Uzuntaş

    📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

    🔗 Stay Connected:
    📘 Medium |
    📺 YouTube |
    💼 LinkedIn |
    📷 Instagram |
    🐦 X |
    👽 Reddit |
    🌐 Website |
    🎵 TikTok |

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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: