How to do this in Excel using Powerpivot or PowerQuery

Question

How can i group the total per group just like this

Group1      GroupA  Tex1    100 300 A1  100
Group1      GroupA  Tex1    100     A2  100
Group1      GroupA  Tex1    100     A3  100
Group1      GroupB  Tex2    100 100 A1  200
Group1      GroupB  Tex3    100 100 A1  
Group2      GroupA  Tex1    100 200 A4  100
Group2      GroupA  Tex1    100     A5  100
Group2      GroupC  Tex2    100 200 A6  200
Group2      GroupC  Tex2    100     A6  

Answer 1

In PowerQuery, Home > Group By

Make sure you pick "Sum" in the Operations dropdown

Then, you can join it with the initial table