question

llleka-0406 avatar image
0 Votes"
llleka-0406 asked YevhenDeriy-3904 commented

Deleting HTML tags

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

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
1 Vote"
Ehren answered YevhenDeriy-3904 commented

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

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Apologies for the long delay on replying. This worked, for which I am beyond grateful!

I did get errors in four lines where the source field was left blank. Is there a way to get around it within the same step?







0 Votes 0 ·

Figured it out - I just replaced blanks with a text string prior to applying HTML step. Thanks again for your help!

1 Vote 1 ·