Adding values of two or more rows

[umairarshad]

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,

[ronrsnfld · Accepted solution]

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:

[umairarshad]

Thank you man. It worked using group by function.

[uzuntasgokberk]

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 |