Sum in Power Query

P

potap

New Member
Joined
Sep 5, 2014
Messages
43
Hi,

I loaded this table (name = annex1) in Power Query and I'm trying to add two columns
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 325"]
<tbody>[TR]
[TD]Unit-Year[/TD]
[TD]Year[/TD]
[TD]Cat[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]1-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]2-2010[/TD]
[TD]2010[/TD]
[TD]B[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]3-2010[/TD]
[TD]2010[/TD]
[TD]C[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]4-2010[/TD]
[TD]2010[/TD]
[TD]D[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]5-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]6-2010[/TD]
[TD]2010[/TD]
[TD]B[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]7-2010[/TD]
[TD]2010[/TD]
[TD]C[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]8-2011[/TD]
[TD]2011[/TD]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]9-2011[/TD]
[TD]2011[/TD]
[TD]A[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]10-2011[/TD]
[TD]2011[/TD]
[TD]B[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]11-2011[/TD]
[TD]2011[/TD]
[TD]C[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]12-2012[/TD]
[TD]2012[/TD]
[TD]D[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]13-2012[/TD]
[TD]2012[/TD]
[TD]A[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]14-2012[/TD]
[TD]2012[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15-2012[/TD]
[TD]2012[/TD]
[TD]C[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]16-2012[/TD]
[TD]2012[/TD]
[TD]D[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]17-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Here are the 2 columns I want to add
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 234"]
<tbody>[TR]
[TD]TotHoursYear[/TD]
[TD]TotHoursYearCat[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
potap said:
Hi,

I loaded this table (name = annex1) in Power Query and I'm trying to add two columns
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 325"]
<tbody>[TR]
[TD]Unit-Year[/TD]
[TD]Year[/TD]
[TD]Cat[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]1-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]2-2010[/TD]
[TD]2010[/TD]
[TD]B[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]3-2010[/TD]
[TD]2010[/TD]
[TD]C[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]4-2010[/TD]
[TD]2010[/TD]
[TD]D[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]5-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]6-2010[/TD]
[TD]2010[/TD]
[TD]B[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]7-2010[/TD]
[TD]2010[/TD]
[TD]C[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]8-2011[/TD]
[TD]2011[/TD]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]9-2011[/TD]
[TD]2011[/TD]
[TD]A[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]10-2011[/TD]
[TD]2011[/TD]
[TD]B[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]11-2011[/TD]
[TD]2011[/TD]
[TD]C[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]12-2012[/TD]
[TD]2012[/TD]
[TD]D[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]13-2012[/TD]
[TD]2012[/TD]
[TD]A[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]14-2012[/TD]
[TD]2012[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15-2012[/TD]
[TD]2012[/TD]
[TD]C[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]16-2012[/TD]
[TD]2012[/TD]
[TD]D[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]17-2010[/TD]
[TD]2010[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Here are the 2 columns I want to add
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 234"]
<tbody>[TR]
[TD]TotHoursYear[/TD]
[TD]TotHoursYearCat[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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!
Click to expand...

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...


  1. In your ribbon select "Power Query".
  2. Click "Workbook" in the "Manage Queries" section.
  3. 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".
  4. This creates a new query on your above query in your query list with name like "MyQuery(2)".
  5. Right click this, select Edit.
  6. In it, change name to SumByYearCat.
  7. Click "Group By".
  8. In Group By column select "Year"
  9. Click "+"
  10. Select "Cat"
  11. Change "New Column Name" to YearCatSum
  12. Select Operation = SUM
  13. Column = Hours
  14. 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 ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
203
AlienSx
A
K
  • Question Question
Power bi Calculate the Monday dates
Replies
1
Views
136
GraH
GraH
S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
198
shansakhi
S
K
  • Question Question
Help with looping through an entire lists in Power Query
Replies
2
Views
522
JGordon11
JGordon11
J
  • Question Question
Adding stock market data from yahoo finance to master Excel file
Replies
3
Views
1K
kcmuppet
K

Forum statistics

Threads
1,224,046
Messages
6,176,069
Members
452,704
Latest member
Michael AA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top