Converting each row of text data to table.

[Anonymous]

Hello All,

I am querying automated email reports from a folder in my outlook inbox. The emails contain a table with varying number of rows of data each day. All of them have the same number of columns. 

It looks something like this in each row once I clean up some of the text before the actual table on the email.

This is how the original record looks for the body of text in each email (I am cleaning the "textbody" by expanding the record)

Is there a way to package text in each row back to an expandable table format so I can expand each of these to rows to append tables from each email and then analyze my data set? 

I am hoping the end result after expanding each row table to look something like this -

I am new to Power BI, any help is much appreciated.

Thanks,

K

[Anonymous · Accepted solution]

Thank you, everyone, for the prompt responses. Since there was a lot of variation in the data stored in the tables, I decided to expand the "Html Body" column, see my question.

The next step was to split the text using delimiters, like "<table> " to separate out the tables.

After this, I used this web-based tool to analyze where all the split points lied for each row in my data, usually split by ...

Once each row was separated out, I used the "</td>" delimiter to split each element of each row into...

I hope this helps anyone who might need to wrangle with tables in emails in the future.

-K

[Anonymous]

Hi there.

Please find the solution to your problem in the attached file.

Best

D

[Anonymous]

If you use the function that lets you import the content of a csv file on TextBody, then you'll be able to remove from the text the 8 first rows and chop into rows and columns the rest of the data. PQ's M language can do anything with the data. It's a question of creating some of the script manually since some actions can't be achieved through the UI.

Best
D

[dax]

Hi @Anonymous , 

Based on my test, I find that powerbi can't recognize the html style of table in email, so I suggest you could use other tool or python to extract the elements from it, then import this in powerbi to analyze. Or you could submit this in power-bi-ideas 

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Stachu]

---

@Anonymous wrote:

Is there a way to package text in each row back to an expandable table format so I can expand each of these to rows to append tables from each email and then analyze my data set? 

---

not really, you could create new logic separating the columns as you need, but it seems that at some point you actually have t his columns separated anyway, so the best way is not to merge the them like this in the first place

why do you need the merged column if you need them separated anyway?

[Anonymous]

Hi @Stachu ,

The "merged" column is the result of cleaning the "textbody" of the emails. This shows me all the data in the tables from the emails which I need to analyze.

The issue is that all the rows from the table are now like a huge line of text, hence the need to separate out each row so I can then split up each row's data by a delimiter to get back the original table structure.

Hope this makes sense. Any suggestions on techniques to achieve this result?

Thanks.

-K