Comparing and writing to a dynamic excel file

Like (0) Share

Report

Posted on 13 Jul 2023 08:49:06 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.

All responses (10)

Answers (1)

Daniel2023 22 on 20 Jul 2023 at 00:02:15

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

Thanks @v-wenjuan-msft !
Verified answer

v-wenjuan-msft Microsoft Employee on 17 Jul 2023 at 03:02:23

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 22 on 14 Jul 2023 at 07:39:06

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 Microsoft Employee on 14 Jul 2023 at 07:22:58

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 22 on 14 Jul 2023 at 03:54:25

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 Microsoft Employee on 14 Jul 2023 at 03:41:09

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 22 on 14 Jul 2023 at 02:22:46

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 Microsoft Employee on 14 Jul 2023 at 02:07:54

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 22 on 13 Jul 2023 at 10:34:16

Like (0)

Report

Re: Comparing and writing to a dynamic excel file

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 Microsoft Employee on 13 Jul 2023 at 09:41:58

Like (0)

Report
Re: Comparing and writing to a dynamic excel file

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.