Forum Discussion
Split a single row into multiple rows
Hello Power BI community,
I am new to power BI and DAX therefore my skills are kinda of limited. I have to split a single row into multiple rows. let me illustrate my request :
| Employee ID | month | salary | bonus |
| 1 | Annual | 12000 | 3000 |
The month cell is supposed to have 12 month in stead of annual and i am supposed to have the salary and the bonus split into month (so divided by 12). The final result I am expecting should look like this:
| Employee ID | month | salary | bonus |
| 1 | january | 1000 | 250 |
| 1 | february | 1000 | 250 |
| 1 | march | 1000 | 250 |
| 1 | april | 1000 | 250 |
| 1 | may | 1000 | 250 |
| 1 | june | 1000 | 250 |
| 1 | july | 1000 | 250 |
| 1 | august | 1000 | 250 |
| 1 | september | 1000 | 250 |
| 1 | october | 1000 | 250 |
| 1 | november | 1000 | 250 |
| 1 | december | 1000 | 250 |
So I wanted to know if it is possible to do that directly in Power BI or i have to change everything in the excel sheet?
Thank you :)
Anonymous Please try with below steps:
1. Create a new MonthName table from your existing DimDate table (if you don't have one, create a Date table)
_DimMonth = VALUES(_Date[Date].[Month])
2. Then, create a new table as below which will be your expected output
Test273Out = CROSSJOIN(SELECTCOLUMNS(Test273SplitRows,"EmployeeID",Test273SplitRows[EmployeeID],"Salary",Test273SplitRows[Salary]/12,"Bonus",Test273SplitRows[Bonus]/12),_DimMonth)
1 Reply
Anonymous Please try with below steps:
1. Create a new MonthName table from your existing DimDate table (if you don't have one, create a Date table)
_DimMonth = VALUES(_Date[Date].[Month])
2. Then, create a new table as below which will be your expected output
Test273Out = CROSSJOIN(SELECTCOLUMNS(Test273SplitRows,"EmployeeID",Test273SplitRows[EmployeeID],"Salary",Test273SplitRows[Salary]/12,"Bonus",Test273SplitRows[Bonus]/12),_DimMonth)