Microsoft Exchange - Excel files with multiple tabs

[Soc3]

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. The tabs are not titled the exact same way (ex. January Usage Report, February Usage Report). Is the only way to combine files automatically to have the tabs named identically each month?

[Anonymous]

Hi @Soc3 ,

You could refer to the blog to know:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[KNP]

Not necessarily, can you provide two sample files that differ as you've described. Remove/change any sensitive data.

This will make it easier for us to offer a solution.

[Soc3]

Thanks for your quick reply! I don't see a way to attach the files on the forum, can you provide your email? 

[KNP]

The attaching is restricted unfortunately. 

Just share using Google Drive, Drop Box etc.

[Soc3]

Hi @KNP ,

Here are the links to my two files.

https://docs.google.com/spreadsheets/d/1PhLgcf-df9rQzv1Bsf5qF8f4o9FlwY4V/edit?usp=sharing&ouid=11440...

https://docs.google.com/spreadsheets/d/10kvbbQqJlGoPd97OfGocupyWGga1bo8Y/edit?usp=sharing&ouid=11440...

Thank you for your help!

[KNP]

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]

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]

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("youremail@yourdomain.com"),
  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]

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]

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.