Quick Group by in Power Query

[ajw5173]

Hello,

I am working with a data set that has about 300 columns. There are 5 columns with attributes like department and store etc and the rest have data that I want to sum. Normally I would sum each column in Power Query group by but with so many columns of data that takes too long. Is there any way to do this quickly?

Thanks!

 

[ajw5173]

I am trying to use the group all function which seems to work except when go to expand the columns it only gives me the option to count. Is there any way to change this to sum in Excel 2010?

 

[ImkeF]

the group-way will take you quite a while to enter if you have 300 columns...

So if performance allows, you could instead just check the columns that shouldn't be summed and unpivot others. That would turn all values under each other and create an additional column with the column headers. Then check that column and pivot (back). Default-operation will be SUM. This should return the desired results.

Performance-wise this is costlier, but worth a try.