Parsing Email Body into usable data

[Barcoda]

Hello PQ/PBI Friends.

First time poster, long time answer seaker & Power Query/BI infactuated.

I've imported my entire weather folder into Power Query successfully but can't seem to understand how to take the Body of the email column and convert it from a list of "Records" with a single field "TextBody" to Individual Fields where I can strip out only the desired rows or data. Basically I'm trying to parse the body within Power Query. Problem is the single TextBody field doesn't have row numbers to tell PQ to remove or separate from the rest of the unnecessary data. The original emails are not HTML format, they are rich text/ csv. And each email has four individual locations with three values each I would like to place in a seperate table. Each day one new email will arrive that would need to parsed and added to the final table. Below I've attached a sample email as well as after import to show the TextBody field.

[Daryl-Lynch-Bzy]

Hi @Barcoda - you need to delete the "in", "let" and "source" steps and delete the extra #"Filtered Rows" reference.  Focus on applying the #"Split Column by Delimiter" step to the #"Filtered Rows".  The moment this is applied to a column named "Column1".  You need to select your column.

[Barcoda]

Hello @Daryl-Lynch-Bzy ,

I'm confused by your instructon, "Focus on applying the #"Split Column by Delimiter" step to the #"Filtered Rows"." I think I omitted/ deleted the other lines correctly. Here's where I''m at presently.

Cheers,

Dean

[Daryl-Lynch-Bzy]

Hi @Barcoda - if you can provide a sample file it might be easier to show how this solution can be done.

[Barcoda]

Hi @Daryl-Lynch-Bzy ,

Is there an email address I can send it to? My work email is in the file and I'd rather not share that out here. If you can send me a mesage here with an email address I'll send you the file.

Cheers,

[Daryl-Lynch-Bzy]

Hi @Barcoda - you could try using the special character line feed "#(lf)" to split the Text into rows.  Then use if states to be figure out the type of row.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVQgszUzOVnAqyi/PU3DLL1fwKs0tKFbwL0stUgBJ+yRWVSq45KfH5A0KxUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    #"Split Column by Delimiter"

However, I would be tempted to use Power Automate to achieve this when the e-mail arrives.  The extract results can be appended to JSON file or Dataverse table.

[Barcoda]

Hello @Daryl-Lynch-Bzy ,
Im not all that proficient at utilizing the advanced editor. Could you explain why I'm receiving this Token Equal Error?

[Barcoda]

I think I figured that one out, now I'm stumped on the Token Camma that seems to be there and proper.