Microsoft Exchange - Excel files with multiple tabs

Hi Soc3,

This isn't using Microsoft Exchange as I had no way to build the example that way.

To be honest, the performance I've seen when connecting to exchange would put me off using it. Typically, if I have files coming in via email, I'll use Power Automate to extract them and put them on a SharePoint site, then connect to them from there.

If you need to use exchange, create a new step that filters to the files you need and make sure you change the column name of 'AttachmentContent' to 'Content' then just change the reference in the 'UsageReports' query to point at your exchange query. 

^not sure if that description will make sense to you.

New query, something like this...

(you'll need to add other filtering as required)

let
  Source = Exchange.Contents("[email protected]"),
  Mail1 = Source{[Name = "Mail"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Mail1, each ([HasAttachments] = true)),
  #"Expanded Attachments" = Table.ExpandTableColumn(
    #"Filtered Rows",
    "Attachments",
    {"Name", "Extension", "AttachmentContent"},
    {"Name", "Extension", "AttachmentContent"}
  ),
  #"Renamed Columns" = Table.RenameColumns(
    #"Expanded Attachments",
    {{"AttachmentContent", "Content"}}
  )
in
  #"Renamed Columns"

For the sake of example, we'll say you called this new query 'Mail'.

Now change the 'UsageReports' query to reference 'Mail' instead of 'Files'...

(e.g. Source = Files to Source = Mail)

let
  Source = Mail,
  ROC1 = Table.SelectColumns(Source, {"Content"}),
  InvokefProcessUsageReportFiles = Table.AddColumn(
    ROC1,
    "fProcessUsageReportFiles",
    each fProcessUsageReportFiles([Content], "usage report")
  ),
  ROC2 = Table.SelectColumns(InvokefProcessUsageReportFiles, {"fProcessUsageReportFiles"}),
  ExpandColumns = Table.ExpandTableColumn(
    ROC2,
    "fProcessUsageReportFiles",
    {
      "Contact Name",
      "Contact: Email",
      "Case Origin",
      "Opened Date",
      "Case Number",
      "Disposition",
      "Topic",
      "Case Type",
      "Subject"
    },
    {
      "Contact Name",
      "Contact: Email",
      "Case Origin",
      "Opened Date",
      "Case Number",
      "Disposition",
      "Topic",
      "Case Type",
      "Subject"
    }
  )
in
  ExpandColumns