Split a single row into multiple rows

[Anonymous]

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 🙂

[PattemManohar · Accepted solution]

@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)