SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

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.