How to apply group by to all columns (an unknown number of columns)?

[janitor048]

Hi,

I am importing data that looks like this:

Category	Sub-Category	Col1	Col2
A	A1	2	3
A	A2	3	5
B	B1	5	6
B	B2	6	8
C		2	3

I would like to condense this information such that the Sub-Category column gets aggregated, i.e. I want the result to be

Category	Col1	Col2
A	5	8
B	11	14
C	2	3

I believe that this is something like "Group by" with sum applied to column Category.

However, my problem is the following - there could be another column "Col3". Or "Col4" or "Col5", etc. And I want them to be grouped in the same way. I do not know the number of such columns when loading the data - and in fact this number can change. All I know is that those columns all have the same format. And such that I want to sum them in the same way.

The reason I am trying to do this inside the Power Query part is that after having my desired result above, I would proceed to trannspose the table and do some more stuff before actually starting to use the table.

Any help would be appreciated! Thanks.

[artemus]

Here is how you can do it without doing an Unpivot.

= Table.Group(PreviousStep, {Category}, List.Transform(List.Select(Table.ColumnNames(PreviousStep),_ <> "Category" and _ <> "Sub-Category"), each {_, (grp) => List.Sum(Table.Column(grp, _)), Int32.Type}))

[Greg_Deckler]

Having the number of columns change between data loads sounds bad to me. But, @ImkeF may have some tricks up her sleeve.

[janitor048]

@Greg_Deckler Yeah, it's actually columns of dates that get eventually added; i.e. new project day / week, new column. I can't change this right now but would still need to do some analysis. 

Would it maybe help transposing the table first? I will probably do this in a later step anyhow.

[Greg_Deckler]

@janitor048  - I would maybe select your Category and SubCategory columns and then right-click and Unpivot other columns.

[ImkeF]

Seconding Gregs suggestion!

[Greg_Deckler]

Hey hey! That might be a first!! 😄

[janitor048]

Ok, I might be a bit slow here. Doing what @Greg_Deckler suggests (unpivot on the columns other than Category and Subcategory) yields

Category	Subcategory	Attribute	Value
A	A1	Col1	2
A	A1	Col2	3
A	A2	Col1	3
A	A2	Col2	5
B	B1	Col1	5
B	B1	Col2	6
B	B2	Col1	6
B	B2	Col2	8
C		Col1	2
C		Col2	3

Originally I wanted to have "Category" and "Col1" etc. still as columns and rows. Also the result is not aggregated by Category (so --> combination of A and Col1 = 5; combination of A and Col2 = 8, etc.). How would I achieve this? 

[janitor048 · Accepted solution]

Ok, playing around a bit - if I do the following on my result from the unpivot step

1. Group by Category & Attribute

2. Pivot the column Attribute in the result from step 1

I get my desired result (see original post). Somehow I have the feeling that this is a bit more complicated than really necessary, but ok 😄

[Greg_Deckler]

Nice @janitor048 ! I don't know, I'm a big fan of whatever works! 🙂  And, dealing with a varying number of columns is a pretty nasty, complicated thing so it may not be overly complex.