email field imported from salesforce report was not treated consistently

[timeregained]

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?

[Anonymous · Accepted 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]

Lydia, thanks a lot for your advice. I have tried your sugestion and it works well.