Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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 < but that has been encoded as &lt; because the & sign itself has been encoded as &
To fix this you need to Replace Values on the column to switch & 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
Proud to be a Super User!
You're welcome.
Phil
Proud to be a Super User!
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 < but that has been encoded as &lt; because the & sign itself has been encoded as &
To fix this you need to Replace Values on the column to switch & 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
Proud to be a Super User!
@PhilipTreacy , thank you very much for your answer and detailed instructions!
This completely solved my problem!
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |