How to configure the Body.TextBody to import rows from table in an email body

[vscott]

I am struggling to configure an expanded column (Body.TextBody)  in a mail table to pick up each table row in the email message to create a record in Power BI.  See a picture of the body. 

[v-jingzhang · Accepted solution]

Hi @vscott 

You may try my solution as below.

First I assume your email body is similar to below. I assume that there is only one table in each email's body and the ending Sign-Off is "BR", so we need to extract the text starting from "File Created at ..." and ending before "BR". 

We can add a custom column with below code to get the table data. 

Text.Range([Body.TextBody], Text.PositionOf([Body.TextBody], "File Created at (EST)"), Text.PositionOf([Body.TextBody], "BR") - Text.PositionOf([Body.TextBody], "File Created at (EST)"))

Then split the "Table Data" column by delimiter #(lf) and split it to rows.

Then remove the redundant rows to keep only the highted rows as above.  

= Table.SelectRows(#"Split Column by Delimiter", each ([Table Data] <> "" and [Table Data] <> "#(cr)" and (not Text.Contains([Table Data], "File Created at (EST)"))))

Split "Table Data" column by the left-most delimiter "   " into columns. (Or use other split methods per your need.)

At last, trim the second column after splitting and rename both columns. Then you will have below result. 

The full code is

let
    Source = Exchange.Contents("xxxxxxxxxxxx@xxxxxxx.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    .......................,
    #"Expanded Body" = Table.ExpandRecordColumn(#"Kept First Rows", "Body", {"TextBody"}, {"Body.TextBody"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Body",{"Subject", "Body.TextBody"}),
    
    /* Replace above code with your source data */
    
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Table Data", each Text.Range([Body.TextBody], Text.PositionOf([Body.TextBody], "File Created at (EST)"), Text.PositionOf([Body.TextBody], "BR") - Text.PositionOf([Body.TextBody], "File Created at (EST)"))),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Table Data", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Table Data"),
    #"Filtered Rows2" = Table.SelectRows(#"Split Column by Delimiter", each ([Table Data] <> "" and [Table Data] <> "#(cr)" and (not Text.Contains([Table Data], "File Created at (EST)")))),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows2", "Table Data", Splitter.SplitTextByEachDelimiter({"   "}, QuoteStyle.Csv, false), {"Table Data.1", "Table Data.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Table Data.2", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Table Data.1", "File Created at (EST)"}, {"Table Data.2", "File path"}})
in
    #"Renamed Columns"

Hope this solution helps! 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.