How to do this in Excel using Powerpivot or PowerQuery -

how can group total per group 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   

in powerquery, home > group by

make sure pick "sum" in operations dropdown

then, can join initial table