Combine rows with same data and sum data

[EvansRJ]

Good Afternoon,

Can someone please tell me how to combine the following data...

Issue Type	Shipment Type	Count Closed	Count Open
User	PO	33	9
User	PO	0	1
User	PO	1	1
User	PO	23	2
User	PO	34	0

Assuming Rows 1-6 and Columns A-D, how to I get a result where it combines the columns with the same name (A&B) and sums columns C&D. So the final result would be this...

User

PO

91

13

Thank you!

 

[sandy666]

with Power Query

Issue Type	Shipment Type	Count Closed	Count Open		Issue Type	Shipment Type	Count Closed	Count Open
User	PO	33	9		User	PO	91	13
User	PO	0	1
User	PO	1	1
User	PO	23	2
User	PO	34	0

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Issue Type", "Shipment Type"}, {{"Count Closed", each List.Sum([Count Closed]), type number}, {"Count Open", each List.Sum([Count Open]), type number}})
in
    Group

 

[EvansRJ]

with Power Query

Issue Type	Shipment Type	Count Closed	Count Open		Issue Type	Shipment Type	Count Closed	Count Open
User	PO	33	9		User	PO	91	13
User	PO	0	1
User	PO	1	1
User	PO	23	2
User	PO	34	0

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Issue Type", "Shipment Type"}, {{"Count Closed", each List.Sum([Count Closed]), type number}, {"Count Open", each List.Sum([Count Open]), type number}})
in
    Group

Thank you... with respect, I have no idea what this means. I have figured out where to find Power Query (Get Data) in Excel but no idea how to manipulate it to get the results you show. Can you explain?

 

[sandy666]

what is you excel version?
update your profile about it

 

[EvansRJ]

I have Office 2016 however it is my Corporate laptop so hopefully the tools I will need are enabled.

 

[sandy666]

XL2016 has Power Query built-in
your source data should be an Excel Table (check the name)
Data - New Query - From Other Source - Blank Query
open Advanced Editor and replace all there with code copied from the post
check if the name in first line (here: Table1) is the same as your source table, if not update first line
Close&Load

 

[sandy666]

more about Power Query

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

 

[EvansRJ]

I receive and error when I follow your steps.

Expression.Error: The column 'Issue Type' of the table wasn't found.
Details:
Issue Type

 

[sandy666]

so you posted improper table in the first post, check headers and fix it

use XL2BB to post your original source table

 

[sandy666]

or check headers against leading and trailing spaces