Answered

Comparing and writing to a dynamic excel file

(0) Share

Report

Posted on by 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.

Categories:

Using flows

I have the same question (0)

All responses (10)

Answers (1)

Sort by

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Daniel2023 22 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daniel2023 22 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daniel2023 22 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daniel2023 22 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daniel2023 22 on at

Like (0)

Report
Copy link

Link copied!

Thanks @v-wenjuan-msft !

Was this reply helpful? Yes No