# Sum in Power Query



## potap (Oct 28, 2014)

Hi, 

I loaded this table (name = annex1) in Power Query and I'm trying to add two columns


Unit-YearYearCatHours1-20102010A102-20102010B153-20102010C204-20102010D155-20102010A146-20102010B127-20102010C88-20112011D69-20112011A7710-20112011B4511-20112011C2912-20122012D3113-20122012A4014-20122012B215-20122012C1816-20122012D417-20102010A10

<tbody>

</tbody>

<tbody>

</tbody>Here are the 2 columns I want to add


TotHoursYearTotHoursYearCat104341042710428104151043410427104281576157771574515729953595409529518953510434

<tbody>

</tbody>

<tbody>

</tbody>I believe the answer is here but I just can't make it work. I'm not an IT guy and I'm new to M language.

Thank you!


----------



## ContinuumDan (Nov 4, 2014)

potap said:


> Hi,
> 
> I loaded this table (name = annex1) in Power Query and I'm trying to add two columns
> 
> ...



OK, no need to use M I don't think !
There are quicker ways to do this, but I hope this is simple enough, and I haven't followed all steps but the approach should be clear...



In your ribbon select "Power Query".
Click "Workbook" in the "Manage Queries" section.
In the Workbook Queries section in Power Query right click on your above query (called "MyQuery" as an example for these purposes, I don't know your query name) and select "Reference".
This creates a new query on your above query in your query list with name like "MyQuery(2)".
Right click this, select Edit.
In it, change name to SumByYearCat.
Click "Group By".
In Group By column select "Year"
Click "+"
Select "Cat"
Change "New Column Name" to YearCatSum
Select Operation = SUM
Column = Hours
Close And Load

This gives you total hours for Year and Category.

Repeat the above process based on MyQuery except only Group By Year and call this Query "Sum By Year".
Close and Load.

You should now have three queries, MyQuery, SumByYearCat and SumByYear.

Finally, select SumByYearCat in your query list, right click and select Merge.
Select Year column.

Choose Sum By Year as the query to merge with.
Again, select Year.
Click Merge.

This now creates a new field called "Table".
Click the expand icon in the column header and select SumHours from SumByYear.
Rename this new column as "SumByYear".

This should be what you need ?


----------

