Merging Excel files in Onedrive for business

[MichaelURev]

Hi All,

I have a process that downloads a bunch of similar excel reports from my email to a specific folder in my onedrive.

These reports are not formatted as tables in any way, and there is no way for me to get them as tables (they are like PDFs dropped into excel, I will use Power Query later to clean it up once I have it in the main excel).

How can I create a flow that once a week the flow will copy all the rows from all the reports into one new excel file? (and then delete all the old files if possible)

[wskinnermctc]

You need magic, not Power Automate Cloud Flow.

At the very least starting point you should try to deal with individual files as they come in. So like as a single new excel file comes into your email, it needs to be formatted and put into a master table or something.

If you can get that far you have a starting point.

You might try something with Power Automate Desktop Flow, but I don't know anything about the Desktop type flow builder. 

[MichaelURev]

@wskinnermctc I'm game! Can you help me with something like that?

I actually built a desktop flow to do it, but I would much prefer to have this in cloud as I don't really have a machine to keep the flow on.

[wskinnermctc]

Will all of the excel files be the same? You said similar. What will be the differences of these Excel files?

[MichaelURev]

@wskinnermctc Just the rows of data, so I guess they are all the same.

The software generating these reports isn't great, but I think its safe to assume that all the columns will be the same.

[wskinnermctc]

Ok lets test getting individual files from an email and creating a new file for that attachment, and then creating a table in the new file.

This will only work if all of the columns are the same for each excel file that you get from an email. The flow is not complicated, you just have to be specific in the Create Table action to prevent errors.

I made an example below that should be easy to follow. You need to use one of the files from your email that doesn't have a table and send it to yourself to test.

Here is the overall flow that will get an attachment and create a file then a table:

Example Flow Overview

Here is the example excel file and email that I will be using for my testing.

Excel File No Table that will be attachmentEmail with Attachment Sending to Myself to Test Flow

Below are the flow steps in detail:

Flow Steps Detail to Create File from Attachment and Create Table

Example Flow Detail 1Example Flow Detail 2

The key to this is creating a table step. This uses a formula in the Table Range field that will make a range. The formula is using the OFFSET function which will output a range. This needs to have the starting point which will be the first column cell.

 OFFSET(reference, rows, cols, [height], [width])

We are most concerned with the reference and [height] and [width]. The rows, cols part of the function is for actually offsetting something which we don't want to do.

Then it uses the SUBTOTAL function in place of the the [height] which needs to use a type of Function_num code followed by a range.

SUBTOTAL(function_num,ref1,[ref2],...)

We are using the function_num of 103 which is a COUNTA type function. Then the reference is all of column A.

The last part of the OFFSET function is the [width] which is where you put the column counts.

Create Table

So the example formula I'm using for my Table Range is

=OFFSET(Sheet1!A1,0,0,SUBTOTAL(103,Sheet1!$A:$A),10)

Then I listed the columns in the Column Names field.

When I send the email it created a new file and in that file is a table.

New File CreatedTable Created Inside of New File

If you can follow these steps and have the files being created from email attachments with tables that are the same then you can start looking for a method for how to combine this data.

[MichaelURev]

@wskinnermctc  I really really appreciate the assistance, thank you so much!

So everything seems to work, up to the create table. Either the flow fails at create table, or it just runs at that step and I eventually cancelled those flows after a few minutes.

I anonymized a sample of the report and attached it here.

A few points to mention, the report comes as an .xls and comes full of hyperlinks as well.
On the flow, I changed the offset a little as you can see the report comes with the sheet named the same as the report and the real data starts on A6.

[wskinnermctc · Accepted solution]

This was very frustrating. I initially was trying to solve it with the text of the sheet name written directly into the step. However, if an Excel sheet is named with special characters or spaces, it needs to be wrapped with 'single quotes' so that it is read as text.

Instead of =OFFSET(Sheet1!A1,.........

It should be =OFFSET('Special.Tab-Name 01'!A1,.......

Surprisingly, if you are typing in the sheet name and using single quotes, it doesn't work because of how text is converted in Power Automate when the data is transmitted.

However, if I use the dynamic content like you have for a name and then put single quotes around it, then it does work. Simple.

Create Table With Sheet Name

Not simple is the formula to use for a range that does not start one A1 but starts on A6. I'm not going to waste time explaining how it works but it does. If you want to test it, you can actually write the formula in an Excel cell with the sheet name replaced and see the results. 

Table Range Formula

Here is the basic formula below:

=OFFSET('{SheetName}'!$A6,0,0,SUBTOTAL(103,OFFSET('{SheetName}'!$A6,0,0,SUM((ROWS('{SheetName}'!$A:$A)-ROW('{SheetName}'!$A6)),1))),23)

The curly brackets and SheetName between single quotes '{SheetName}' would be replaced with dynamic content or a variable. But the single quotes need to stay outside of the value.

For this specific example that is using the newly Create File - Name Without Extension as the sheet name it will be this example below. You can copy and paste this into your field and it should add the dynamic content so long as you didn't rename the "Create file" step.

=OFFSET('@{outputs('Create_file')?['body/NameNoExt']}'!$A6,0,0,SUBTOTAL(103,OFFSET('@{outputs('Create_file')?['body/NameNoExt']}'!$A6,0,0,SUM((ROWS('@{outputs('Create_file')?['body/NameNoExt']}'!$A:$A)-ROW('@{outputs('Create_file')?['body/NameNoExt']}'!$A6)),1))),23)

I must have tested 100 emails to finally get this to work. But at least I learned something.

Now the bad news...........

When I used your file to test, I was unable to get it to work. It would just keep running (the flow shouldn't take more than 10 seconds) and after a few minutes I would Cancel the flow.

When I copied the data from your worksheet and pasted it into a new workbook as values only, the flow worked successfully.

Things I tried to change in your original file but the flow still failed:

• Adding the gridlines
• Unmerging cell A5 (I ran my version with A5 merged and it was successful, so that not the problem)
• Deleting the top 5 rows with the report info and replaced with blank rows. (I thought maybe they are formatted weird and messing up the formula.)
• Deleting all the data and copy and paste it back into the worksheet.

So basically there is something wrong with the file itself.

I went into the Inspect Document and found the workbook has "Invisible Content" Number of invisible objects found 40.

Inspect Workbook - Invisible Content

I could not find these objects anywhere. I don't know what they are.

I clicked Remove All to delete that Invisible Content. Ran the flow and it was successful.

Successful File and Table Creation

So I don't know if that Invisible Content is something from the report maker itself or something from a result of you uploading here and then me downloading it.

All I can say is for you to make your Create Table formula look exactly like mine and triple check the formula. If the flow is not successful then try saving the file and scrubbing the Invisible Content to make sure the flow works.

If the Invisible Content is coming from the report maker, I don't know how to fix that.

Good Luck!

[MichaelURev]

@wskinnermctc Wow wow!

I really appreciate the effort you put into this. I would say that the content is coming from the report maker and they are not going to change anything for me..

I am going to look into using an automated desktop flow to do it I guess.

Thank you again!