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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (2,509)