Removing HTML tags from #ProjectOnline fields #PowerQuery #PowerBI #Excel

October 9, 2015 at 1:03 pm | Posted in Administration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: Excel, Power Query, PowerBI, PPM, Project Online

A quick post to highlight a simple way to remove the HTML tags in the Project Online OData reports. If you are unsure about what I mean, see the image below:

Notice the <p> tag and &#160 tag in the Changes column above, as well as the list tags. An easy way to clean this up is to use Replace function in Power Query:

This can be seen below too:

let
    Source = OData.Feed("<PWAURL>/_api/ProjectData/Projects()?$Filter=ProjectType ne 7"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ProjectName", "Changes"}),
    #"Replace HTML <p>" = Table.ReplaceValue(#"Removed Other Columns","<p>","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </p>" = Table.ReplaceValue(#"Replace HTML <p>","</p>","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML &#160" = Table.ReplaceValue(#"Replaced HTML </p>"," ","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML <ul><li>" = Table.ReplaceValue(#"Replaced HTML &#160","<ul><li>"," ",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </li><li>" = Table.ReplaceValue(#"Replaced HTML <ul><li>","</li><li>",", ",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </li></ul>" = Table.ReplaceValue(#"Replaced HTML </li><li>","</li></ul>"," ",Replacer.ReplaceText,{"Changes"})
in
    #"Replaced HTML </li></ul>"

Then your report will look like this in Excel:

This also works for Power BI too, without the replace function:

Using the replace function:

I have only included a few of the HTML tags / mark-up that you will find but as you can see, it will be easy enough to do the rest.

This is a quick and simple way but you could look to do this in bulk for all columns and HTML tags by creating your own function if you needed to.

Advertisements