Summarise multiple columns based on one column.

[Plump_Lil_Monk]

Hi,

I have a problem where I have a table with a department column and 12 other columns with each month of the year.

The department column has multiple rows and the subsequent month columns have values of no more than 1 in each cell.

I need to be able to sum the table by department so that each department shows up once, in the department column, while also adding up the values for each month for that department.

Sample table:

Dept	Jan	Feb	Mar	Apr	May	Jun
10011	1	1	1	0.8	1	1
10011	1	1	1	1	1	1
10013	0.5	1	0.7	0.5	1	1
10012	1	1	1	1	1	1
10012	1	1	1	1	1	1
10011	1	1	1	1	1	0.5

Required output:

Dept	Jan	Feb	Mar	Apr	May	Jun
10011	3	3	3	2.8	3	2.5
10012	2	2	2	2	2	2
10013	0.5	1	0.7	0.5	1	1

Any help would be greatly appreciated. 

Thanks

[wdx223_Daniel · Accepted solution]

NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))

[lbendlin · Accepted solution]

or, you know, unpivot your data to bring it into usable form, and then let the Power BI matrix visual do all the work for you.

[wdx223_Daniel · Accepted solution]

NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))