Copy Excel file data to different Excel file in SharePoint Document Library (dynamic)

[tylerwasson123]

Every day an excel file is emailed to me, and I save the email to a SharePoint document library manually, copy it's contents, and append the data to a master data excel file in a different document library. 

The excel file that is emailed to me does not come in a table, has the same column count for each file but dynamic rows.

I add a date column to the file and manually populate today's date for each row.

The master data file is in a table with all the same columns to include the date column.

If I do the steps individually in Power Automate, as a test, everything works like a charm as shown:  

Creating the table which is always Test1, using a fixed amount of rows that I know is well over the count I would receive in my email.  Adding the Date column to the table.

Filtering the table to only rows that have data and adding today's date.

Copying/appending the rows with data to the master data file.

But when I try to add all pieces together into one I cannot even get by List Rows Present when trying to filter the rows. 

[v-yujincui-msft · Accepted solution]

Hi@tylerwasson123,

Do you want to append the data in the excel file you uploaded to another excel file?

Maybe your issue is caused by the delay. After the Create table Action is completed, the List rows present in a table Action maybe does not get the data immediately. I suggest you could try to add a Delay Action.

I have made a test for your reference.

1.This is the excel file I will upload to a SharePoint document library()

2.This is the master data excel file.

3.Create a flow.

items('Apply_to_each')?['Item']
items('Apply_to_each')?['Item Description']
items('Apply_to_each')?['Qty']
items('Apply_to_each')?['Status']
formatDateTime(utcNow(),'yyyy/MM/dd')

Result Screenshots:

TestFile.xlsx

Master-data.xlsx

Best Regards,

Charlie Choi

[tylerwasson123]

Hello v-yujincui-msft,

Thank you for your reply and help.  Yes, I am interested in appending the data in the master data file table.

I added a few parts to this request prior to creating the table which all works fine.  The name of the attachment is dynamically changed for each email attachment based on a date and time stamp and the file is properly added to the SP site.

I now receive an error when trying to "Create Table" stating the item cannot be found.  I do not have the option to choose x-ms-file-id from the dynamic content as in your example.  I appreciate your or anyone's help in the matter, as after this step I'm looking to filter the table array to only non-null rows and appending those rows to the master data file.

PREVIEW

[v-yujincui-msft]

Hi @tylerwasson123,

You can change the ItemId in Create table Action to Id, and then try again。

Best Regards,

Charlie Choi

[tylerwasson123]

Hello v-yujincui-msft,

I went back to the drawing board after your first post and based on your post was able to get it work.  I'm very appreciative of your time to do that, thank-you very much.

Using x-ms-file-id was a major fix, because once I made that change things started to fall into place.

The last major change was your recommendation below because before that I struggled with populating the table.  I was also able to populate the Date column with the days date at the same time eliminating a step.

Below is my final product using 2 separate flows:

Flow 1:

Flow 2:

Thank-you again!