Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to format string text by delimiter

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 wha...
Need Help
v-yadongf-msft's avatar
v-yadongf-msft
Community Support
3 years ago

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.