Split column by Nth character into rows

[johndolan2]

Hi - I have not been able to find an answer and am looking for some help. I need to split a column after every 3d space and have these split into new rows … not new columns. Any help would be appreciated. I have pbix file as an example. Thanks, John

[mahoneypat · Accepted solution]

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

[v-stephen-msft · Accepted solution]

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.

[v-stephen-msft · Accepted solution]

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.

[johndolan2]

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

[mahoneypat · Accepted solution]

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

[johndolan2 · Accepted solution]

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