Forum Discussion
Summarise multiple columns based on one column.
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
NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))
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.
2 Replies
NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))
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.