Forum Discussion
Split field into multiple column
Hello everyone, I want to split one field into multiple columns or rows, the content in the field are like:
2020-01-03 02:21:12 PM - James (Work notes) 2020-01-02 10:00:01 - Mary (Work notes) received a new plan |
what I like to split this field into three notes column or row
Thanks
Perfect. In retrospect, those two screenshots is what you should have posted in your first post 😉
Also, my bad on the report I uploaded, I somehow must not have saved the last version I was working with.Try this instead: https://www.dropbox.com/s/fw81h93f73mi47m/Split%20field%20into%20multiple%20column2.pbix?dl=0
13 Replies
Hi, Yucy
this could solved by using the Split-function in Power Query/Advanced editor in Power BI. The first split is pretty simple, if all your values are formatted the same way. Split on the right most occurence of "-".
The second split is a bit more tricky, because you have to handle a new line(either Line feed or carriage return or both). Here is a description of how to do it: excelguru.ca/blog/2015/10/16/split-by-line-breaks/
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.Go to edit query -> Split column -> give delimiter there and then close and apply.
YOu will have your required columns.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.thanks, helped a lot. However, I want to split the note fields into multiple as long as a new date show up, but not only by line
example: I want the filed below split into 3 but not 9 columns
2020-01-06 11:05:17 AM - Bob (Work notes)
This Case will be Resolved as there is no correction.2020-01-06 10:40:49 AM - Bob (Work notes)
A review of CCT shows that the Transfer sets out the name as Lake No. Therefore, there is no correction for Resolutions to do.I have talked to K and informed her of the error
I have removed the locks
I have also emailed Sher thanking her for bringing this to our attention
2020-01-02 02:06:39 PM - Tam(Work notes)
Standard CorrectionsThanks
The split column in Power Query is still your best option to handle this.
First split your column by the left most " - ", that is space score space:
Then split the remaining column by #(lf):
But this step requires that you manually edit M-code in the Advances editor according to the link in my prevoius answer.
Once that is solved, your data should look like this:
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
