Forum Discussion
Split rows into nested list
Hi,
this might be an easy task but I'm struggeling the whole morning with this.
In my table I have a column [Owners] which contains a string of emails separated by comma.
I want to convert these emails per row into nested lists within the same column (no extra column).
I already tried Text.SplitAny in combination with Table.TransformRows etc. but only get errors...
Maybe you can help? 🙂
Hi FabvE ,
Thanks for Uzi2019 and rajendraongole1 reply.
FabvE Do you want to change value of Owner column to List without adding custom column? If so, you can try this.
= Table.TransformColumns(#"Changed Type",{ "Owner" , each Text.Split(_,",") } )Best regards,
Mengmeng Li
5 Replies
Hi FabvE - Certainly! It sounds like you want each cell in the [Owners] column to contain a list of emails (instead of a single comma-separated string)
Select the column [Owners] and go to the Add Column tab.
Choose Custom Column, and in the dialog box that appears, use the following M code
= Text.Split([Owners], ",")
Sometimes emails separated by commas have extra spaces, which can interfere with your list format.
= List.Transform(Text.Split([Owners], ","), each Text.Trim(_))
Once done, select Close & Apply to load your transformed data back into Power BI.
Hope this works.
HI,
thank you but this creates another column. Is there a way to transform the original column without creating an additional one?
I want to avoid unnecessary steps in my query. 🙂
Hi FabvE
Check these steps:
I created similar scenario , this is my data with comma seperated.
1) Go to power query
2) select your owner column
3) go to transform tab
4) split column
5) select by delimiter
6) select comma seperator then go to advance setting select row > ok
check the result are in seperate rows not in columns.
I hope i answered your question!
Hi FabvE ,
Thanks for Uzi2019 and rajendraongole1 reply.
FabvE Do you want to change value of Owner column to List without adding custom column? If so, you can try this.
= Table.TransformColumns(#"Changed Type",{ "Owner" , each Text.Split(_,",") } )Best regards,
Mengmeng Li
