Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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)

xmlPowerBI.PNG

 

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

xmlOriginal.PNG

 

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

 

Thanks,

 

5 REPLIES 5
v-yadongf-msft
Community Support
Community Support

Hi @Anonymous ,

 

This is my test table:

vyadongfmsft_0-1668412235425.png

 

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

vyadongfmsft_1-1668412271851.png

 

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

vyadongfmsft_3-1668412412159.png

 

I think this is the result you want:

vyadongfmsft_4-1668412531811.png

 

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
Solution Sage
Solution Sage

Hi @Anonymous , the result is only 1 column:

 

Do you have tranpose the table?.

 

Final result:

Bifinity_75_0-1668082935939.png

 

Anonymous
Not applicable

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
Solution Sage
Solution Sage

Hi @Anonymous , try this:

 

1.- Split column by delimiter ">":

Bifinity_75_0-1668081369783.png

 

2.- Tranpose the table:

Bifinity_75_1-1668081410798.png

I hope it works for you, best regards

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.