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:

image

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:

image

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:

image

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

image

Using the replace function:

image

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

5 Comments »

RSS feed for comments on this post. TrackBack URI

  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. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: