Removing   and &amp from notes column

Power Query
1

Hello,
We have an application where it populates one column with notes that user enters. When the user writes something in that notes column, somehow it is creates like   and & whenever there is a non-breaking space and when they use ampersand in between words. Is there a way to remove these html characters from the notes column other than replacing the above characters with blank values and ‘&’ separately in two separate steps?
A screenshot of the notes column is below:
image

Thank you.

2

Hi @Vsb79,

See if this is helpful. You can repeat this pattern for other strings you want to look for and remove separately, as you require. Just copy the script below into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xC8IwFIT/ytFdkQ46OBXEQdDNSR1ekzQJ0nshjRT/vU0nt+/Bu7vv8Wiukk14fna7ds9+SscVD2hb3JOV4rDBTWGC0DsMWUecXZ8/kr8oiouw4va/gEEmeKWD1ZkwyiQ50iPlqBmjsgQILah4s76sOzpgDo4owT1phOidj9yiO3XIMqYJSq+1p2bF2opz5DKCElmqzppdJJdLhyEa9y/WvF4/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Remove_nbsp = Table.AddColumn(Source, "Remove &nbsp", each Text.Combine( Splitter.SplitTextByDelimiter( "&nbsp" )([Column1]), "" ), type text)
in
    Remove_nbsp

.
with this result

image
image773×118 7.86 KB

3

Hi @Vsb79, you can also check out this blog

Regards

4

Hi Melissa, Thank you for the solution. It does work. But, is there a way this can be handled within the same column instead of creating a new column?

5

@Vsb79, sure here you go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xC8IwFIT/ytFdkQ46OBXEQdDNSR1ekzQJ0nshjRT/vU0nt+/Bu7vv8Wiukk14fna7ds9+SscVD2hb3JOV4rDBTWGC0DsMWUecXZ8/kr8oiouw4va/gEEmeKWD1ZkwyiQ50iPlqBmjsgQILah4s76sOzpgDo4owT1phOidj9yiO3XIMqYJSq+1p2bF2opz5DKCElmqzppdJJdLhyEa9y/WvF4/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ReplaceValues = Table.ReplaceValue(Source, each [Column1], each Text.Combine( Splitter.SplitTextByDelimiter( "&nbsp" )([Column1]), "" ),Replacer.ReplaceValue,{"Column1"})
in
    ReplaceValues
1 Like
6

Thank you Melissa.