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 h...

Need Help

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.