Forum Discussion

Soc3's avatar
Soc3
Helper I
4 years ago

Microsoft Exchange - Excel files with multiple tabs

I'm using Microsoft Exchange to insert attachments from emails that come monthly. Within the attachment, there are multiple tabs. I need to filter to the specific tab I want to be combined each month...
Need Help
Soc3's avatar
Soc3
Helper I
4 years ago

Hi KNP ,

 

Your assumptions were correct and the output looks great! Is this still using Microsoft Exchange? How do I provide a file path to my inbox?

KNP's avatar
KNP
Super User
4 years ago

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

 

  • Soc3's avatar
    Soc3
    Helper I
    4 years ago

    Hi again KNP , thank you so much for your help! Last question, how do I switch the data source for pFileDirectory to point to a Sharepoint folder? I tried replacing the file path with the link and get this error. 

     

     

  • KNP's avatar
    KNP
    Super User
    4 years ago

    The original query...

    let
    Source = Folder.Files(pFileDirectory),
    FilterRows = Table.SelectRows(Source, each Text.Contains(Text.Lower([Name]), "usage report"))
in
    FilterRows

     

    You'd need to change the query to use SharePoint.Files not Folder.Files.

    There may be some other minor tweaks required but that'll give you a start.