Decoding HTML symbols into actual symbols in Power BI

[inna_sysco]

Hello everyone!
I am trying to load HTML data (special characters, links, etc.) from Azure Database to Power BI.
During my research, I found many suggestions on how to do this, but, unfortunately, none of them work for me ...
Am I missing something?
The last solution I tried  - https://stackoverflow.com/questions/65105454/decoding-html-symbol-decimal-numbers-into-actual-symbol...
But after all, I have such a result:

Any suggestions?

I will be very grateful for any ideas!

[PhilipTreacy]

@inna_sysco 

You're welcome.

Phil

[PhilipTreacy · Accepted solution]

Hi @inna_sysco 

Download sample PBIX file with solution

The problem here is that the symbol has been 'double-encoded'.  For example the code for the HTML symbol less than < is &lt; but that has been encoded as &amp;lt; because the & sign itself has been encoded as &amp; 

To fix this you need to Replace Values on the column to switch &amp; back to just &

Then you can use Html.Table to decode

Here's the full example query that you can find in my PBIX file above

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDAyS8wtsM4psS5A8NJLrJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VALUE = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"&","&",Replacer.ReplaceText,{"VALUE"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Html.Table([VALUE],{{"HtmlDecoded",":root"}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"HtmlDecoded"}, {"HtmlDecoded"})
in
    #"Expanded Custom"

Regards

Phil

[inna_sysco]

@PhilipTreacy , thank you very much for your answer and detailed instructions!
This completely solved my problem!