Forum Discussion

Lori_C's avatar
Lori_C
Frequent Visitor
8 years ago

Split text string column values into multiple rows at a position relative to a delimiter

Hello, I need to split the values in the Notes column (see BEFORE) into a separate row for each note (see AFTER).  A new note begins when a timestamp occurs in the text string.  The timestamp follows...
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
8 years ago

Hi Lori_C,

Based on my test, you can refer to below steps:

1.Use the “Split Column by Delimiter” function to different notes.

2.Unpivot the columns, except the [Order_ID] and the [Item_ID].

3.Remove the [Attribute] column and use the “Split Column by Delimiter” function again to achieve the correct result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/wrut5tvgy8xfqc3/Split%20text%20string%20column%20values%20into%20multiple%20rows%20at%20a%20position%20relative%20to%20a%20delimiter.pbix?dl=0

 

Regards,

Daniel He

  • Lori_C's avatar
    Lori_C
    Frequent Visitor
    8 years ago

    Hi Greg and Daniel, thank you both for the suggestions!  Unfortunately they don't work** because of inconsistent break tags in the source data.  The actual data is much uglier than the sanitzed example I posted (shocking, right?).  Each string is littered with breaks (<BR>, <BR/>, <BR>) based on operator entry styles which vary, and multiple operators appear in the same string.  I've tried splitting using the various break tags separately and in different ordered combinations but the results aren't clean enough; also replacing the various tags with a single delimiter but I get too many splits where I don't want them.

     

    **Greg I should add, I haven't gotten your code to work - I'm brand new to M and haven't figured out how to replace the posted table with my actual column name - but going through it conceptually and seeing Splitter.SplitTextByDelimiter("<BR/>" makes me think yours relies on the break tag as well.  Please correct me if I'm wrong about that!

     

    I keep coming back to my "true" delimiter being the datetime stamp because it's application-generated and always appears at the beginning of each manual entry.  My SAS brain tell me to get the position of every instance of MDT and then count back 19 or 20 characters (which points to the beginning of the date) and say "split here"!  Or to split at the MM of anything formatted like MM-DD-YYYY HH:MM AM (or PM) MDT.  I just can't figure out how to tell this to Get-and-Transform.  Any thoughts?  Thanks much!