Skip to main content

Notifications

Power Automate - Building Flows
Answered

SEARCH ITEMS IN OTHER TABLES

(0) ShareShare
ReportReport
Posted on by 290

Hello Community,

I have two Excel tables, named DTE and BCO, I want to create a new table that shows me the Items not found or that do not match in their three columns (RUT, FOLIO AND CR).
The search must be done with the DTE Items and look for them in the BCO table.

I leave an example.

 

fgonzalez515_0-1646508290035.png

 

  • takolota1 Profile Picture
    takolota1 4,779 on at
    Re: SEARCH ITEMS IN OTHER TABLES

    Oh nice @DamoBird365 already has some cleaner documentation for this.

     

    That actually may help me make some of my other flows more readable.

  • fgonzalez515 Profile Picture
    fgonzalez515 290 on at
    Re: SEARCH ITEMS IN OTHER TABLES

    Hi @takolota,

     

    Thanks.

    This link is helpful

    https://www.damobird365.com/efficien-union-except-and-intersect-great-method/ 

  • takolota1 Profile Picture
    takolota1 4,779 on at
    Re: SEARCH ITEMS IN OTHER TABLES

    Hello @fgonzalez515 

     

    Well you would want to use the Append to Array variable not Append to String in that case.

     

    However, I think the Parse JSON body should already have the array set up that way. Did you try just putting the JSON body in the Create HTML From field? Heck, you may not even need the Parse JSON that action may also take the Filter array outputs.

    But you may need to change the expressions in the Values column there to select just that Key’s value for each row there.

    So…

    item()?[‘Rut’]

    item()?[‘Folio Dte’]

    item()?[‘CR Luciano’]

  • fgonzalez515 Profile Picture
    fgonzalez515 290 on at
    Re: SEARCH ITEMS IN OTHER TABLES

    Hi @takolota,

     

    Thanks for the help.

    I want to take the obtained result to an HTML table, but it throws me the following error.

    How can I solve that?

     

    fgonzalez515_0-1646581114015.png

     

    fgonzalez515_1-1646581144538.png

     

     

  • Verified answer
    takolota1 Profile Picture
    takolota1 4,779 on at
    Re: SEARCH ITEMS IN OTHER TABLES

    Hello @fgonzalez515 

     

    As long as RUT FOLIO and CR form a unique key that is different for every row in the table…

     

    I would use two Excel List rows actions to get both the tables in Power Automate..

     

    Then use a Select action and in the array From input field, use the intersection expression on the values output of both the Excel List rows, like: Intersection(values(List rows 1), values(List rows 2)). That should generate an array with only the matching values between the two tables. In the Key field just put “Key” and in the value field put item()?[‘RUT’] item()?[‘FOLIO’] item()?[‘CR’] just enter each expression one after the other so it basically concatenates them. That should generate a unique key for each of the matching items in the two tables.

     

    Then use a Filter array action with the BCO table’s Values output in the From array input.

    In the filter conditional use String(Body(‘Select’)) Does not contain item()?[‘RUT’] item()?[‘FOLIO’] item()?[‘CR’]

    That should check if the list of unique keys from the matching records contains the unique key of each record from the BCO table and only return the records not in the matching records list.

     

    From there you can use a Parse JSON on the Filter array action to get the records into dynamic content.

     

    That should accomplish things in a really efficient way incase you have a lot of records.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard