Deleting HTML tags

llleka 1 Reputation point
2021-08-25T17:37:58.12+00:00

Hello,

I am attempting to remove HTML tags from a Power BI text field which gets pulled from Salesforce. I've tried implementing several solutions offered here and on other forums with no luck as I am only starting to learn power query. The code provided here seems like it should fit my case, but I am lost trying to make it work for me. Below is the query for the table where the text field resides. Any help you can offer would be greatly appreciated!

let
    Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
    Claims__c = Source{[Name="Claims__c"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(Claims__c,{<!-- -->{"Name", "Claim Number"}, {"Claim_Notice_Date__c", "Claim Notice Date"}, {"Loss_Type__c", "Loss Type"}, {"Status__c", "Status"}, {"Date_of_Binding__c", "Date of Binding"}, {"Claim_Type__c", "Claim Type"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns", "Merged", each Text.Combine({[Status], " - ", Text.Proper([Loss Type])}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Merged"}),
    #"Inserted Merged Column1" = Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({[Status], " - ", [Loss Type]}), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column1", "Ststus & Loss Type", each if [Merged] = "Open - Third Party" then "Open 3rd" else if [Merged] = "Open - First Party" then "Open 1st" else if [Merged] = "Closed - Third Party" then "Closed 3rd" else if [Merged] = "Closed - First Party" then "Closed 1st" else null, type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{<!-- -->{"Ststus & Loss Type", "Status & Loss Type"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Status & Loss Type", "Merged"}),
    #"Inserted Merged Column2" = Table.AddColumn(#"Removed Columns1", "Merged", each Text.Combine({[Status], " ", [Loss Type]}), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Column2",{<!-- -->{"CreatedDate", Order.Descending}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows",{<!-- -->{"Merged", "Status & Loss Type"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Has Reserve?", each if [ET_Loss_Reserve__c] = null or [ET_Loss_Reserve__c] = 0 then "No" else "Yes"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [ET_Loss_Reserve__c]+[Paid_Loss__c]+[Paid_ALAE__c]+[Paid_Expense__c]),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom1",{<!-- -->{"Custom", "Known Exposure"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns3",{"Known Exposure"}),
    #"Inserted Sum" = Table.AddColumn(#"Removed Columns2", "Addition", each List.Sum({[Paid_Loss__c], [Paid_Expense__c], [Paid_ALAE__c], [ET_Loss_Reserve__c]}), type number),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Sum",{<!-- -->{"Addition", "Known Exposure"}})
in
    #"Renamed Columns4"

let func = (HTML) =>
    let
        Check = if Value.Is(Value.FromText(HTML), type text) then HTML else "",
        Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
        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 = "Remove Html Tags"
            , 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 & Mike Carlo: PowerBI.Tips"
            , Documentation.Examples = {[
                Description = "Function that enables one to pass in a column that has HTML tags."
                , Code = "<div>my bit of text</div>"
                , Result = "my bit of text "
                ]}] 
 in 
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Best,
YevD

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,565 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ehren (MSFT) 1,781 Reputation points Microsoft Employee
    2021-08-27T21:13:00.85+00:00

    In PBIDesktop, try adding a custom column that does the following:

    Html.Table([YourColumnThatContainsHtml], {{"Column1", ":root"}}){0}[Column1]

    1 person found this answer helpful.