Forum Discussion

ryangoderre's avatar
ryangoderre
Regular Visitor
5 years ago
Solved

Looking for help to remove text between html tags, like this <*>

in microsoft excel you can do a wild card find and replace for <*> and it will remove all the html text and tags.  but i would like to have this step be a part of my power query steps.     I have l...
Need Help
Show and Tell
  • mussaenda's avatar
    mussaenda
    5 years ago

    Hi ryangoderre,

     

    If this is what you need,

    Try the attached pbix.

    It is not the most effecient way, maybe others can improve it but you can try.

    Hope this helps.

     

     

Jakinta's avatar
Jakinta
Icon for Solution Sage rankSolution Sage
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VNNT8JAEP0rE7xaqRhBSjwYORJj9OABOWzbabvJfrk7RfHX222LKakYCRxNL/tmZ+fNzHtdLgevZRheJQVJUZ+wwbFONw2GJpDyNTDBc3Xr8Wj8VmqaCcyoA8HRRmA3QXIVFMjzgqKp6aZ2uTKtCDKW7Ly8s5yJPQ+cYarP5csEjn9idLOXKm7AHDNMCOboEssNca2i5n4Yd7OHnuhoYvVSoAJbKsVVDlQg3JcxgkWjLblgsDrfinAavu6gz4TGAWl4QmN1Wib4t0GHnmMnUOm/4wctflCb2bwSnLSJQim3MNZEWvpIr9e2luB7a9naO6GhmWFpWi0w8J6rA4QfFHCVoqLo2rRryZjkYlMPOZqwbw9NZlXD2kZn4aX/DjEiO86Ih7TV5L43/4vSVjLRFvMUbaTXykJXi/Iae2sxYwRPmLc0MAcLXKOAKWjbHsdQOrQXv5mur73g/3KdTK4HtuY5I9wq9ljGlWKD1eoL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    TextSplit = Table.AddColumn(Source, "Custom", each List.Select(List.Transform(Text.Split([Column1],">"),Text.Trim), each not Text.StartsWith( _,  "<") )),
    Expanded = Table.ExpandListColumn(TextSplit, "Custom"),
    FINAL = Table.ReplaceValue(Expanded,":</b","",Replacer.ReplaceText,{"Custom"})
in
    FINAL

 

Start:

 

Result:

 

ryangoderre's avatar
ryangoderre
Regular Visitor
5 years ago

thanks for posting your code and the screenshots it is really helpful to learn and understand.  

 

My sample data is all inside of 1 cell.  you broke it out into multiple cells in column 1, and then removed the html tags in column 2/custom.  

 

will this code work if all my text inside a single cell? also not all my text is bolded, i was just doing that in the forum to highlight the text i wanted to extract out.  

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Sure, it will work. Just give it a try.

    • ryangoderre's avatar
      ryangoderre
      Regular Visitor
      5 years ago

      ok so how do i implement the code?  do i need to go to custom column, and paste in the code you provided?  I just tried that and it doesnt work in extracting the info in my cells, it extracted similar to the screenshot you sent. The yellow is the text i want extracted.  the red is names i didnt want to post to the forum.  you can see in the custom column it has defect description, but that isnt existent my cells, it was in your cells.  

       

      Sorry for all the back and forth.  

       

       

    • ryangoderre's avatar
      ryangoderre
      Regular Visitor
      5 years ago

      sorry for all the back and forth.  i really do appreciate the help.