Forum Discussion
Split column by Nth character into rows
Please post a link to your pbix file. This should be doable with List functions in power query. You could split your text string at every space with Text.Split, then group those into a list of 3 element lists with List.Split, and then use List.Transform with Text.Combine on each sublist concatenated with spaces to get your desired List. You can then "Expand to New Row" to get your desired result.
Pat
Hi johndolan2 ,
You did not upload your file. And it's about spliting column by Nth character into rows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Pat - I flat out CANNOT THANK YOU ENOUGH!!!!!!!! I spent a frustrating number of hours over a stretch of weeks attempting to do this and you had it figured out in seconds. For anyone else reading this .... My situation was that I had a column in which each cell contained multiple entries seperated by spaces. I could not use the available split by delimiter since the number of characters varied. For example: 123 abc pair1 456 bcd pair 12 was in a single cell. I needed to have 123 abc pair 1 split into its own row and 456 bcd Pair 12 into it's own row. I have a column with hundreds of these and each row incldudes any where from a single entry (abc 123 pair 2) upwards to 12 .. and they were of various lengths. .... Also, Pat's got a great YouTube channel ....
I cannot possibly say thank you enough.
John
Pat - You have a great YouTube channel
4 Replies
Please post a link to your pbix file. This should be doable with List functions in power query. You could split your text string at every space with Text.Split, then group those into a list of 3 element lists with List.Split, and then use List.Transform with Text.Combine on each sublist concatenated with spaces to get your desired List. You can then "Expand to New Row" to get your desired result.
Pat
Pat - I flat out CANNOT THANK YOU ENOUGH!!!!!!!! I spent a frustrating number of hours over a stretch of weeks attempting to do this and you had it figured out in seconds. For anyone else reading this .... My situation was that I had a column in which each cell contained multiple entries seperated by spaces. I could not use the available split by delimiter since the number of characters varied. For example: 123 abc pair1 456 bcd pair 12 was in a single cell. I needed to have 123 abc pair 1 split into its own row and 456 bcd Pair 12 into it's own row. I have a column with hundreds of these and each row incldudes any where from a single entry (abc 123 pair 2) upwards to 12 .. and they were of various lengths. .... Also, Pat's got a great YouTube channel ....
I cannot possibly say thank you enough.
John
Pat - You have a great YouTube channel
Hi johndolan2 ,
You did not upload your file. And it's about spliting column by Nth character into rows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thankyou Stephen. I had not clearly stated my problem. .. which was the # of spaces varied. Pat Mahoney came up with the solution to my particular situation. .....Thank you very much Stephen and it's on me for not clearly stating my problem - So again I really apprecaite the reply. John