cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Yucy
Helper I
Helper I

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)
plan resurrected notified to proceed

2020-01-03 11:14:12 AM - Tom (Work notes)
Plan completed

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

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

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

View solution in original post

13 REPLIES 13
sturlaws
Resident Rockstar
Resident Rockstar

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.

 

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 Corrections

 

Thanks

sturlaws
Resident Rockstar
Resident Rockstar

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:

 

Capture.PNG

 

Then split the remaining column by #(lf):
Capture2.PNG

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:
Capture3.PNG

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Thanks so much, I am sorry I was not clare, my example is all in one filed, but not three fields in three row.

Here is a screenshot of my data, one field has two notes, another two has nothing, and another one only has one field. I need to split the notes

 
 
sturlaws
Resident Rockstar
Resident Rockstar

Could you, based on the sample data you have provided, show in an excel sheet how you would like your data to look like at the end?

Here is my data in powerbi,  i would to see either split the multiple worknotes in the same field into multiple column, or split it into multi row. However if it is multi row, the assigned to has to repeat, Also, one could have no worknotes, one could have one worknote, and could have 2,3,4,5 or more

 

pic.png

 

sturlaws
Resident Rockstar
Resident Rockstar

I found a solution, but it might be a bit fragile. It relies on (Work notes) being at the end of the line that defines a new entry.

Here is a report which shows how it can be achieved: report

Much appreciated. But it still not I want😭

sturlaws
Resident Rockstar
Resident Rockstar

Then perhaps explain exactly what you want to achieve by using excel, and post a screenshot?

Yes,  here is my data

data.gif

 

What I want to see:

data1.gif

 

 

Thanks for all your effort

 

sturlaws
Resident Rockstar
Resident Rockstar

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

Thank you so much, that is exactly what I was looking for. Much Appreciated.

Anonymous
Not applicable

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors