Comparing and writing to a dynamic excel file

[Daniel2023]

Hi to all!

I new to PA and is trying to work out the below flow but was stuck at step 4. Would like to seek advice how do I proceed further, thank you!

1. New excel file uploaded to sharepoint (Table format with headers: ID, Description, Unit price). The excel filename is dynamic, but the table format is the same as below. 

2. PA will trigger to read the new file

3. Data from the new file is extracted

4. To compare the new file data to a database excel file (common header - ID, Unit price)

a) There is no dynamic content available (headers of the new file) when i tried to use "condition". 

*This is unlike when i have PA to look into a known excel file name.

5. If ID is found in the database, PA will extract the value of the Unit price and write into the new file.

=(

[v-wenjuan-msft]

Hi @Daniel2023 ,

Test to run your flow and check outputs of "List rows present in a table", to see whether it retrieved your dynamic columns or not.

If yes, you can call values from them with function:

items('Apply_to_each')?['changetoyourcolumnname']

Notice:

Power Automate cannot find your columns if they have blank space in column name. Remember to cancel it.

Community Support Team _ Wenjuan Zou

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

[Daniel2023]

Hi @v-wenjuan-msft ,

Yes i am able to retrieve the dynamic columns. 

I am now doing the comparison but when it comes to the condition (where the productID from new file can be found in the database file), I am unable to update the row in the new file.

Am i wrong with my flows? Thanks again for the help.

[v-wenjuan-msft]

Hi @Daniel2023 ,

You need to insert function like this:

So according to your logic, "value" you insert into "Apply to each" is from new file and "value" you insert into "Apply to each 2" is from database right?

Community Support Team _ Wenjuan Zou

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

[Daniel2023]

Hi @v-wenjuan-msft ,

Yes, your understanding is correct.

The intention is that I am taking the data from the new file (dynamic) and compare to another excel file (static database). If the data can be found in the database, I will want to pull out the relevant info from database and append into the new file.

[v-wenjuan-msft]

Sounds great to finish your task~

Does the flow run well now with the function? If yes, would you please mark my answer to resolve the post? Thank you~

Community Support Team _ Wenjuan Zou

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

[Daniel2023]

Hi @v-wenjuan-msft ,

Unfortunately i was not able to compare the values. It seems like I am not applying the right flow to do the comparison?

[v-wenjuan-msft]

Hi @Daniel2023 ,

What do you mean unable to update here?

Does it mean you cannot find expected column in "Key Column" drop down?

Or you mean you have run the flow but no modifications happened actually? If so, is there any error message shows up?

Community Support Team _ Wenjuan Zou

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

[Daniel2023]

Hi @v-wenjuan-msft ,

Correct, i am unable to find the expected drop down columns. I assume I have to enter "Table1" since it is a dynamic file. When it comes to Key Value, I am stuck as well.

I have the below screenshot to illustrate what I am trying to do. 

1. Below is the dynamic file which will be uploaded to sharepoint by my colleagues (different file names but same table format e.g. salesA.xls)

2. Use PA to cross check salesA.xls with a database xls which is stored in sharepoint

3. Product ID - BB and CC are found in the database xls. 

4. Use PA to update the "Unit Price" column in salesA.xls

Thanks again for your help!

[v-wenjuan-msft · Accepted solution]

You can not find expect column in drop downs as your file name is dynamic. So if the table name and key column are static, you can type them in as custom values directly.

Community Support Team _ Wenjuan Zou

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

[Daniel2023]

Thanks @v-wenjuan-msft !