Forum Discussion

Barcoda's avatar
Barcoda
Frequent Visitor
3 years ago

Parsing Email Body into usable data

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.

 

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Barcoda
      Frequent Visitor
      3 years ago

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

      • Barcoda's avatar
        Barcoda
        Frequent Visitor
        3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Barcoda
      Frequent Visitor
      3 years ago

      Hello Anonymous ,

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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