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 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,

 

Need Help

5 Replies

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi Anonymous , try this:

     

    1.- Split column by delimiter ">":

     

    2.- Tranpose the table:

    I hope it works for you, best regards

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi Anonymous , the result is only 1 column:

     

    Do you have tranpose the table?.

     

    Final result:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!

  • 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.