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   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  " = Table.ReplaceValue(#"Replaced HTML </p>"," ","",Replacer.ReplaceText,{"Changes"}),
#"Replaced HTML <ul><li>" = Table.ReplaceValue(#"Replaced HTML  ","<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.
Hey Paul, I’ve always done this with VBA in Excel, as I don’t use the PowerQuery function – for a basic Excel report are there any other advantages of using PowerQuery?
Hi Ben,
The good things about Power Query is that is has lots of functions available to transform the data without having the write code using the tool bar options. You can still write the code directly using the advanced editor if you like – this is M code. Paul