cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adzaba
Regular Visitor

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

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.Screenshot 2021-10-01 112136.pngScreenshot 2021-10-01 112249.png

1 ACCEPTED SOLUTION

Accepted Solutions
adzaba
Regular Visitor

@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!

View solution in original post

6 REPLIES 6
abm
Super User
Super User

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
adzaba
Regular Visitor

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.

 

adzaba_0-1633113649080.png

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
adzaba
Regular Visitor

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. 

adzaba_0-1633126568526.png

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
Regular Visitor

@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!

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (4,212)