Forum Discussion
Split the rows into miltiple rows based on a cell value
Hello There,
I am trying to split my rows of data into multiple rows if the value of a cell is >1
There are 50+ columns in a table, but I would like to duplicate the whole data into a separate rows.
Current data format
| Org Name | FTE requested |
| ABC | 3 |
| XYZ | 1.5 |
Needed data format
| Org Name | FTE requested |
| ABC | 1 |
| ABC | 1 |
| ABC | 1 |
| XYZ | 1 |
| XYZ | 0.5 |
I have seen the post https://community.powerbi.com/t5/Desktop/Is-it-possible-to-split-rows/m-p/273011 and also https://community.powerbi.com/t5/Power-Query/How-to-split-row-to-n-number-of-rows-based-on-cell-value/m-p/2901471 -- But I am not able to replicate this.
Thanks in advance!
1 Reply
- barritown
Solution Sage
Hello AS123,
Just a warning - the solution I propose below is a bit crazy one and is based on the assumption that your dataset is pretty small and the maximum value of the column "FTE requested" is not huge (otherwise it may be very slow).
I just wanted to check if I can solve your problem with [DAX]. I think I have succeeded. 🙂
Here it is (you need to create a new table with this code):
Split Data = SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( CROSSJOIN ( data, GENERATESERIES ( 0, ROUNDUP( MAX ( data[FTE requested] ), 0 ) ) ), "Res", VAR x = [FTE requested] - [Value] RETURN SWITCH ( TRUE (), x >= 1, 1, AND ( x > 0, x < 1 ), x, -1 ) ), [Res] > -1 ), "Org Name", [Org Name], "FTE requested", [Res] )The idea is:
- we crossjoin each row with a column starting from 0 to the maximum I mentioned;
- then we filter out the odd rows which were previously marked by -1 in the added column;
- then we choose only required columns.
You may want to ask for help in the Power Query section - https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services - if you decide that my solution is too crazy. 😉
Here is my test dataset and result:
Best Regards,
Alexander
