Insert rows in excel file if it does not exist with composite key

[tracytran]

Hi friends, 

I have the excel table with a composite key: driver number and license number

Now I have create a flow to export data from SQL server to excel file. 

It works out, however, the data is duplicated if it is already existing in excel file. 

I followed this post to insert rows in excel file if it does not exist:

https://powerusers.microsoft.com/t5/Building-Flows/Re-insert-row-in-excel-file-if-it-does-not-exist/...

My excel is a composite key so it does not work out. 

Could anyone please help me out regarding this issue?

Highly appreciate your support.

[gabibalaban]

@tracytran ,

To make it work you need to remove spaces from your Excel columns.

Before adding to a row to Excel file, Use a List rows present in a table with the filter property set to:

Drivernumber eq 'items('Apply_to_each')['Drivernumber']' and Licensenumber eq 'items('Apply_to_each')['Licensenumber']'

and then check if the result is empty in a condition action:

empty(body('List_rows_present_in_a_table')?['value'])

Hope it helps !

[tracytran]

@gabibalaban thank for your reply.

I followed your guideline and created new flow:  

 I 

When I ran this flow, I got this error: 

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The action(s) 'List_rows_present_in_a_table' referenced by 'inputs' in action 'Condition' are not defined in the template.'.'.

Could you please tell me where I missed? 

Much appreciate your help. 

[gabibalaban]

@tracytran , please take a look on action “List_rows_present_in_a_table”. In my example is the same text, you have a “_2” at the end. You need to adapt the condition formula to reference the action from your flow.

empty(body('List_rows_present_in_a_table_2')?['value'])

[tracytran]

@gabibalaban Thank for for bringing that up. 

I corrected with this flow: 

At 3rd step, I config like you suggested: 

However, it raises error at Action 'List_rows_present_in_a_table'. 

Could you please have a check on this?

[gabibalaban]

@tracytran ,

items('Apply_to_each')['Drivernumber'] must be written in add dynamic content ->Expression. Also the Licensenumber part.

[tracytran]

@gabibalaban 

I  gave it a try, the expression is invalid. 

Do you have any clue for this error? 

Thank you for your support. 

[gabibalaban]

Only items('Apply_to_each')['Drivernumber']. The rest of the string remains in Filter query.

It should look like this (this is an example and is not linked to your specific)

[tracytran]

@gabibalaban Thank for your kind help.  

I corrected the code. 

When I save the flow, it poped up this error. 

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'List_rows_present_in_a_table' at line '1 and column '891' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'List_rows_present_in_a_table' to be referenced by 'repeatItems' or 'items' functions.'.'.

Do you have the idea for this bugs? 

I highly appreciate your assistance. 

[gabibalaban]

@tracytran ,

The flow isn't look like it should be ... So the logic after trigger is:

1. Get rows from SQL;

2. Apply to each from result step 1.

     2.1. List rows base on filter (there you missed the single quotes ' ', please take a look on my formula

     2.2. If clause to check the emptiness of list row from step 2.1

     2.3. On Yes branch create an Excel new row.

Step 2.1 to 2.3 are all inside the Apply to each.

[tracytran]

@gabibalaban thank for your detailed reply. 

I adjusted the flow as you suggested: 

At the action "List_rows_present_in_a_table", I use this filter property: 

When i ran the flow, the error with this error at "List_rows_present_in_a_table":

Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
clientRequestId: 40136f33-8bb6-4225-ac41-e3a95a9105f0

I did the research and It seems to use the List Rows Present in Table action within the Excel (Business) connector with filter on multiple "eq" conditions is not supported.

https://powerusers.microsoft.com/t5/Building-Flows/ODATA-filter-using-And-operator/td-p/70273

I'm not actually sure about this as I am new with Power Automate.  

Do you have the idea for this bug? 

I highly appreciate your assistance.

[gabibalaban · Accepted solution]

@tracytran ,

I have to recognize that this error is new for me. I do not work too much with Excel connector. The workaround is to leave inside List rows from a table only one condition like 

DriverNumber eq ‘items('Apply_to_each')['Drivernumber']’

and then to add a suplimentary action operations -> Filter array with items from body(‘list_rows…’)[‘value’] and condition in left branch item()?[‘Licensenumber’] and in right branch items(‘Apply_to_each’)?[‘Licensenumber’].

the condition which follows need to be changed to refer to this new action so instead of empty(body(‘List_rows….’)[‘value’]) put empty(body(‘Filter_array’)).

Hope it helps !

[tracytran]

@gabibalaban Thank for a great input!! You made my day!!