Extract HTML Emails

[AZJohnPowerBI]

Hello,

Goal: I would like to extract information from emails that are in HTML format.

The Context: Cisco UCCX emails call center data in html form.

I open up Power BI desktop and select "Get Data"

I select "more" and type in search bar "Microsoft" and select "Microsoft Exchange".

From there, I select all my emails, some 200 of them (for example) that has the Cisco data.

I choose the body of the email, and click "Remove Other Columns".

However, the data is unusable because it's in HTML format.

Can anyone help?

[Anonymous · Accepted solution]

Hi @AZJohnPowerBI 

It seems that you want to extract data from HTML emails and use it in Power BI. However, the default method of importing data from Microsoft Exchange does not parse the HTML content properly. To solve this problem, you have two options:

Use a Power Query function to parse the HTML content. You can use the Web.Page function to convert the HTML content into a table. To do this, you need to add a custom column in Power Query Editor, and use this formula:

web.page()

Web.Page - PowerQuery M | Microsoft Learn

This will create a new column with a table value for each email. You can then expand the table and select the columns you want to keep. You may need to do some further transformations to clean up the data.

Use a third-party HTML parser to extract the data. There are some online tools that can parse HTML data and convert it into CSV or JSON format. For example, you can use Zebra BI or ParseHub to parse HTML data. You can then import the CSV or JSON file into Power BI using the Get Data option.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

could you pls provide some sample data and expected output?