Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have used PowerBi to import data from a salesfore report, it seems powerbi tried to parse the email field to a slice of html. (Both the plugin in Excel and PowerBI desktop perform as the same)
e.g. ABC@ABC.COM would be parsed to :"<A HREF="mailto:ABC@XYZ.COM">ABC@XYZ.COM</A>"
However, if the mailbox's format is little different, like ABC.DEF@XYZ.COM, it would always parsed to:
"<A HREF="http://ABC.DEF" TARGET="_blank">ABC.DEF</A>@<A HREF="http://XYZ.COM" TARGET="_blank">XYZ.COM</A>"
It would be easy for me to get the text of emailbox if the different format of emailbox could be treated consistently. So is there any advice for this issue?
Solved! Go to Solution.
@timeregained,
Firstly, add a blank query in Power BI, then paste the following code into Advanced Editor of the blank query.
let func = (HTML as text) => let Source = Text.From(HTML), SplitAny = Text.SplitAny(Source,"<>"), ListAlternate = List.Alternate(SplitAny,1,1,1), ListSelect = List.Select(ListAlternate, each _<>""), TextCombine = Text.Combine(ListSelect, "") in TextCombine , documentation = [ Documentation.Name = " Text.RemoveHtmlTags ", Documentation.Description = " Removes all Html tags from a text " , Documentation.LongDescription = " Removes all Html tags from a text ", Documentation.Category = " Text.Modification ", Documentation.Source = " Inspired by a solution from Bill Szysz ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com ", Documentation.Examples = {[Description = " " , Code = " ", Result = " "]}] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
Secondly, invoke the custom function in your table as shown in the following screenshots, and you will get email address from the HTML.
Regards,
Lydia
@timeregained,
Firstly, add a blank query in Power BI, then paste the following code into Advanced Editor of the blank query.
let func = (HTML as text) => let Source = Text.From(HTML), SplitAny = Text.SplitAny(Source,"<>"), ListAlternate = List.Alternate(SplitAny,1,1,1), ListSelect = List.Select(ListAlternate, each _<>""), TextCombine = Text.Combine(ListSelect, "") in TextCombine , documentation = [ Documentation.Name = " Text.RemoveHtmlTags ", Documentation.Description = " Removes all Html tags from a text " , Documentation.LongDescription = " Removes all Html tags from a text ", Documentation.Category = " Text.Modification ", Documentation.Source = " Inspired by a solution from Bill Szysz ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com ", Documentation.Examples = {[Description = " " , Code = " ", Result = " "]}] in Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
Secondly, invoke the custom function in your table as shown in the following screenshots, and you will get email address from the HTML.
Regards,
Lydia
Lydia, thanks a lot for your advice. I have tried your sugestion and it works well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.