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: , , , ,

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:

    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"})
    #"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.



  1. 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

  2. […] Source: Removing HTML tags from #ProjectOnline fields #PowerQuery #PowerBI #Excel […]

  3. […] As you can see the HTML tags are now removed from the StatusSummary field. So now in your Excel or Power BI reports you will no longer have to either use VBA in Excel to remove these or use a similar Power Query function in Excel or Power BI as detailed here. […]

Sorry, the comment form is closed at this time.

Blog at
Entries and comments feeds.

%d bloggers like this: