Need help with a sum

[Knoen]

Hey Guys

Im trying to use Power Query and excel to get a sum of a colum right. This is how it looks now. As you can se there are blank rows between the groups.

Desc	Article number	Size	Sold	Bought	Over/Under
Description A	xxxsan	1	1	0	-1
Description A	xxxsan	2	2	0	-2
Description A	xxxsan	3	2	0	-2
Description A	xxxsan	4	3	0	-3
Description A	xxxsan	5	3	0	-3
Description A	xxxsan	6	3	0	-3
Description A	xxxsan	7	3	0	-3
Description A	xxxsan	8	1	0	-1
Description A	xxxsan	9	1	0	-1
Description A	xxxsan	10	1	0	-1
Description B	xx1out	1	1	0	-1
Description B	xx1out	2	1	0	-1
Description B	xx1out	3	5	0	-5
Description B	xx1out	4	2	0	-2
Description B	xx1out	5	2	0	-2
Description B	xx1out	6	2	0	-2
Description B	xx1out	7	1	0	-1
Description C	xx2out	1	2	2	0
Description C	xx2out	2	2	2	0
Description C	xx2out	3	2	2	0
Description C	xx2out	4	2	2	0
Description C	xx2out	5	1	2	1
Description C	xx2out	6	1	1	0
Description C	xx2out	7	1	1	0
Description C	xx2out	8	1	0	-1

I would like to sum the sold and bought columns of each group so it looks like this:

Desc	Article number	Size	Sold	Bought	Over/Under
Description A	xxxsan	1	1	0	-1
Description A	xxxsan	2	2	0	-2
Description A	xxxsan	3	2	0	-2
Description A	xxxsan	4	3	0	-3
Description A	xxxsan	5	3	0	-3
Description A	xxxsan	6	3	0	-3
Description A	xxxsan	7	3	0	-3
Description A	xxxsan	8	1	0	-1
Description A	xxxsan	9	1	0	-1
Description A	xxxsan	10	1	0	-1
			SUM()	SUM()
Description B	xx1out	1	1	0	-1
Description B	xx1out	2	1	0	-1
Description B	xx1out	3	5	0	-5
Description B	xx1out	4	2	0	-2
Description B	xx1out	5	2	0	-2
Description B	xx1out	6	2	0	-2
Description B	xx1out	7	1	0	-1
			SUM()	SUM()
Description C	xx2out	1	2	2	0
Description C	xx2out	2	2	2	0
Description C	xx2out	3	2	2	0
Description C	xx2out	4	2	2	0
Description C	xx2out	5	1	2	1
Description C	xx2out	6	1	1	0
Description C	xx2out	7	1	1	0
Description C	xx2out	8	1	0	-1
			SUM()	SUM()

How can i do this? My code looks like this:

[PhilipTreacy]

Hi @Knoen 

You want to do this inside PQ in a single table before outputting to Excel?  Whilst this could be done, I would advise against it.  PQ should be used to format data into a tabular layout that does not include totals.  One you insert totals into this data, it'll become a nightmare to work with if you want to do anything else.

Calculating things like totals should be done later in the Excel sheet or in PBI using DAX and/or visuals.

Try separate queries for each group and load each of these into separate tables in Excel, or load to the Data Model for use with Power Pivot.

Regards

Phil