topic Re: Microsoft Exchange - Excel files with multiple tabs in Power Query

Microsoft Exchange - Excel files with multiple tabs

Soc3 — Wed, 16 Feb 2022 17:21:29 GMT

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?

Re: Microsoft Exchange - Excel files with multiple tabs

KNP — Wed, 16 Feb 2022 18:19:04 GMT

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.

Re: Microsoft Exchange - Excel files with multiple tabs

Soc3 — Wed, 16 Feb 2022 18:42:01 GMT

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

Re: Microsoft Exchange - Excel files with multiple tabs

KNP — Wed, 16 Feb 2022 19:00:41 GMT

The attaching is restricted unfortunately.

Just share using Google Drive, Drop Box etc.

Re: Microsoft Exchange - Excel files with multiple tabs

Anonymous — Tue, 22 Feb 2022 01:49:20 GMT

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

Re: Microsoft Exchange - Excel files with multiple tabs

Soc3 — Mon, 14 Mar 2022 16:18:02 GMT

Hi KNP ,

Here are the links to my two files.

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

https://docs.google.com/spreadsheets/d/10kvbbQqJlGoPd97OfGocupyWGga1bo8Y/edit?usp=sharing&ouid=114407523338456260967&rtpof=true&sd=true

Thank you for your help!

Re: Microsoft Exchange - Excel files with multiple tabs

KNP — Mon, 14 Mar 2022 18:11:51 GMT

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.

Re: Microsoft Exchange - Excel files with multiple tabs

Soc3 — Mon, 14 Mar 2022 18:31:17 GMT

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?

Re: Microsoft Exchange - Excel files with multiple tabs

KNP — Tue, 15 Mar 2022 00:12:37 GMT

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

Re: Microsoft Exchange - Excel files with multiple tabs

Soc3 — Mon, 21 Mar 2022 19:19:31 GMT

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.

Re: Microsoft Exchange - Excel files with multiple tabs

KNP — Mon, 21 Mar 2022 19:47:23 GMT

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.