Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
NewStep=Table.Group(PreviousStepName,"Dept",List.Transform(List.Skip(Table.ColumnNames(PreviousStepName)),(x)=>{x,each List.Sum(Table.Column(_,x))}))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.