How to format string text by delimiter

[Anonymous]

Hi All,

I have the XML string column in my table and I want to structure it to look better, i.e. want to move text to a new row after this character '>'

To give you more context, below is what I have;  (it's hard to read the logic)

 

and this is how I want to format it (it doesn't need to have indentation)

 

Any other idea on how to make it more readable is welcome! 

Thanks,

[v-yadongf-msft]

Hi @Anonymous ,

This is my test table:

Enter ">" in "value to find" and enter ">#(lf)" in "replace with".

You will get a table like this and delete the character "(#)" selected by the mouse:

I think this is the result you want:

M code for your reference:

 #"Changed Type" = Table.TransformColumnTypes(Sheet51_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"XST schema", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",">",">#(lf)",Replacer.ReplaceText,{"XST schema"})
in
    #"Replaced Value"

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bifinity_75]

Hi @Anonymous , the result is only 1 column:

Do you have tranpose the table?.

Final result:

[Anonymous]

Sorry, meant that I need to keep the string in one cell as shown in my first screenshot. The reason is that I have 10 more columns with the 'XSLT Schema' column, so I can't transpose the table and have what I need. Thanks for the help!

[Bifinity_75]

Hi @Anonymous , try this:

1.- Split column by delimiter ">":

2.- Tranpose the table:

I hope it works for you, best regards

[Anonymous]

Thanks, but unfortunately this doesn't work for me as I need to keep the string in one column as shown in my first screenshot. I was hoping that there is a regular expression maybe that could help me.