Forum Discussion
Split multiple columns has delimiter into rows
Hi
I have a employee and manager data, each employee's manager ID and Name till top level concatenated by delimiter in one column like below
| EMP ID | EMP Name | Manager ID hierarchy | Manager Name |
| 1 | AA | ||
| 4 | B | 1 | AA |
| 7 | C | 1>5>6>7 | AA>N>D>C |
When I Split both columns ID and Name into rows using Split Column by delimiter option, I'm not getting desired output.
EMP ID=7 has three managers in hierarchy( 1 is top level manager for EMP ID=7) , so have to Split into three rows with respective Manger ID and Manager Name like below
| EMP ID | EMP Name | Manager ID hierarchy | Manager Name |
| 1 | AA | ||
| 4 | B | 1 | AA |
| 7 | C | 1 | AA |
| 7 | C | 5 | N |
| 7 | C | 6 | D |
Any idea how to transform data in power BI M query ?
Thanks in advance
Hi Hari01 ,
Please check the steps in pbix file:Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
1 Reply
Hi Hari01 ,
Please check the steps in pbix file:Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data