Deleting HTML tags

llleka 1 Reputation point


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!

    Source = Salesforce.Data("", [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"}})
    #"Renamed Columns4"

let func = (HTML) =>
        Check = if Value.Is(Value.FromText(HTML), type text) then HTML else "",
        Source = Salesforce.Data("", [ApiVersion=48, CreateNavigationProperties=true]),
        SplitAny = Text.SplitAny(Source,"<>"),
        ListAlternate = List.Alternate(SplitAny,1,1,1),
        ListSelect = List.Select(ListAlternate, each _<>""),
        TextCombine = Text.Combine(ListSelect, "")
        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: & 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 "
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))


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

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

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

    1 person found this answer helpful.