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
KNP's avatar
KNP
Super User
4 years ago

The attaching is restricted unfortunately. 

Just share using Google Drive, Drop Box etc.

 

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi Soc3,

     

    Have a look at the attached PBIX file.

    First thing, change the pFileDirectory parameter to wherever you have the files stored locally. (This can be altered later obviously if they aren't going to be local files)

     

    I made some assumptions.

    • That you wanted the sheets in the workbooks named 'January Usage Report', 'February Usage Report' etc. 
    • You weren't interested in the other sheets.
    • That each workbook will only have one sheet with 'Usage Report' in the name. If it doesn't, the function I built will only pickup the first one.

    This should be a good starting point, let me know if you have questions.

     

    • 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