I cannot add rows to a table from another table

[Anonymous]

Hello,

I have problem with my flow. 

I want to give a summary. 

• My flow starts with an email 
• I'm getting email attchement and creating an excel file in OneDrive for Business. By the way I have three folders for files. These are: Unprocessed (first file), template(I'm using this file as generic), processed
• Then I'm listing my files in the onedrive
• After I'm trying to get rows from the email attachment file to another file in the template
• After I'm copying file to processed folder
• Then I'm trying to delete file in the Unprocessed folder
• And trying to clear template file
• Then I want to send email as scheduled

Here I couldn't add rows from a table to another excel table. I'm using expression. 

Also I couldn't empty the template file after moving to processed folder. 

Is there any one to help me urgnetly please?

Thanks a lot. 

[manuelstgomes]

Hi @Anonymous 

The issue is that Power Automate doesn't support accessing Excel files using dynamic paths. I wrote 2 articles with workarounds so that you can achieve this:

https://manueltgomes.com/microsoft/powerautomate/how-to-access-dynamic-path-excel/

https://manueltgomes.com/microsoft/powerautomate/how-to-access-dynamic-path-excel-part-ii/

Can you please check if and let me know if you have any questions?

Cheers
Manuel

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[Anonymous]

Dear @manuelstgomes  thanks for your response. But I have tried above solutions. It didn't work. 

[manuelstgomes]

Hi @Anonymous 

What was the issue? Did you get an error?

Can you please share the Power Automate and obfuscate anything private that you can't share so that I can have a better understanding of what you're trying to do and the issues.

Cheers
Manuel

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[Anonymous]

The problem is I have no ID column. I have two excel files. And I have to match columns with each other and insert the values to table in template. That's why I cannot use get a row. @manuelstgomes 

[AntoniaN · Accepted solution]

Hi @Anonymous ,

Thanks for reaching out.

There is indeed a known issue with the Excel (Business) connector, that it does not support dynamic content in the "File" and "Table" fields.

Below is a similar request from users on the Power Automate Ideas Forum, which you can vote for: Dynamic content in File & Table inputs - Excel (B... - Power Platform Community (microsoft.com)

Alternatively, you can propose a new idea for voting by other users in the same Forum.

----------------------------------------------
However, there is a workaround to implement your needed functionality by using IDs in those fields from file metadata and the table inside your Excel file.

For your case, after you use the List files in folder action from the OneDrive for Business connector, inside the Apply to each loop, you have the following:

1. Get file metadata (OneDrive connector) - pass the ID value from the "List files in folder" as dynamic content;
2. Get tables (Excel Business) - select OneDrive in the first 2 fields and pass the ID value from "Get file metadata" as dynamic content;
3. Open another Apply to each loop (iterates through tables in file)
4. List rows present in a table (Excel business) - select OneDrive in the first 2 fields and pass the ID value from "Get file metadata" for the "File" field and the ID value from "Get tables" for the "Table" field as dynamic content;
5. Open another Apply to each loop (iterates through rows in table)
6. Add row into a table (Excel Business) - select OneDrive, your destination file and your table for first 4 fields, and for your columns write the expression item()?['SourceTable_ColumnName']

Your flow should look like this:

And here is the complete flow:

The ID value of the created excel file and the table make it possible for the Excel connector to reference those elements through dynamic content.
The last "Apply to each" loop is needed in order for the "item()?['Value']" expression to reference the values of each row, as "item()?" gives you context to the currently running item in the loop.

Please try this and then apply the same logic to the rest of your flow.

Let me know if there is anything else I can help with.

Regards,
Antonia Nanau

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.