Create a table, parse it, add rows to another table if new.

[adzaba]

Hi - I'm new to Power Automate and struggling...

Here's my situation: 

A new excel file is dumped in SharePoint 2-3 times a day. They all have the same structure (data in columns A:L). I want to update a master excel file (also in SharePoint) with any new lines appearing in these files. Lines are new if the combination of columns C (integer) and H (datetime) is unique. The master file will have additional columns but the same stuff in A:L.

Here's what I have so far. I don't know what to do with the 'value' from 'List rows present in a table' to check it against the master.

What next? Any help greatly appreciated. Thanks.

[abm]

Hi @adzaba 

Believe you need to filter the record to find out whether it exists in Master record or not. Please have a look at this video about how to filter Excel records.

https://www.youtube.com/watch?v=r1LhHU5lXkM

If you need further help please let me know.

Thanks

[adzaba]

Hi @abm - thanks for the response. I'm trying this out but I don't know how to address the table I just created...

Here's where I am: I filter the new table to get rid of empty rows. My thinking is:

For each row in master:

    Check it against each row in the filtered table

        if the two columns match then continue loop

        if not then add row to master and continue loop

I don't know how to address the columns in the filtered table to compare to the master:

something like " items('Apply to each 4')?['Order ID'] " doesn't work.

[abm]

Hi @adzaba 

I can't see the filter query from your screenshot? Also you have three Apply to Each loop. Not sure that is quite right or not?

Thanks

[adzaba]

Thanks @abm ,

Here's the filter: @not(equals(length(item()?['ColumnNameExample']), 0))

The three Apply to Each loops:

The first was created automatically when I loop over items in the filtered table. 

The third was for lines in the master table.

So it ends up being...

for body in filtered table:

    for item in body:

        for value in master table:

            compare both column C's and both column H's match

                  do something to be determined

But I'm thinking maybe I should do it differently...

for body in filtered table:

    for item in body:

        filter master table for columns C and H values

            if length of filtered table = 0 

                add item to master

Does that make sense?

[adzaba · Accepted solution]

@abm 

It ended up working similarly to the second way. In the end...

apply to each body in filtered table:

        filter master table for columns C and H values (syntax = see below)

            if length of filtered table = 0 

                add item to master

@and(

equals(

item()?['OrderID'],

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

),

equals(

item()?['Order Date'],

items('Apply_to_each')?['Order Date']

)

)

...where item() = value in the master table.

I think I'm all set. Thanks for your help!

[abm]

Hi @adzaba 

Thanks for letting me know. Could you please mark it as solution accepted. This will help others when they looking for solutions.

Thanks