cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Join Two Datasets in Flow

Hello,

 

I have two datasets, one from excel online and the other a sharepoint list, that I need to join. The excel list contains customer numbers and email addresses. The sharepoint list contains invoice numbers, amounts, and customer numbers. I need to join these on the customer number so that I can tie the email address of the customer to the invoices for that customer and send an email to the customer when that invoice comes due.

 

Example:

Sharepoint list:

Invoice NumberCustomer NumberAmount
30123$50
31234$51
32123$52

 

Excel Online:

Customer NumberCustomer Email
123123@123.com
234234@234.com
345345@345.com
456456@456.com

 

What I want to get to:

Invoice NumberCustomer NumberAmountCustomer Email
30123$50123@123.com
31234$51234@234.com
32123$52123@123.com

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Hey @Anonymous 

 

Please see the flow below: (Not sure if this is the best way to do it but it kinda works)

F112.PNG

 

You can replace the manual trigger with your recurrence trigger. Ensure that the number of items being returned in both the actions is same. 

F113.PNG

We will run a nested loop and take one common identifier (teh customer in your case) and check if it exists in both the data sources

F114.PNG

If it exists, start creating the JSON array with the desired values. You need to add the attributes and select from the dynamic selector. (basically add the "customer" : "select from dynamic values", next item and so on)

F115.PNG

 

Lastly create the table from the test array variable that was created. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

5 REPLIES 5

Hey @Anonymous 

 

Please see the flow below: (Not sure if this is the best way to do it but it kinda works)

F112.PNG

 

You can replace the manual trigger with your recurrence trigger. Ensure that the number of items being returned in both the actions is same. 

F113.PNG

We will run a nested loop and take one common identifier (teh customer in your case) and check if it exists in both the data sources

F114.PNG

If it exists, start creating the JSON array with the desired values. You need to add the attributes and select from the dynamic selector. (basically add the "customer" : "select from dynamic values", next item and so on)

F115.PNG

 

Lastly create the table from the test array variable that was created. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Anonymous
Not applicable

Thanks for the response! I had tried something like that where I used a nested loop and the action was to update the sharepoint list of invoices with the email address in a new column, but in my testing that was taking a very long time (about 100 customers with anywhere from 100-500 invoices coming due each time this is run means the nested loop has a lot of work to do). I was wondering if there is a way to join or create a relationship between the two tables that doesn't take as much time as the nested loop.

Anonymous
Not applicable

Hey, thanks for making this thread! I'm working with the same issue, and though the solution works, it takes a super long time. Did you ever come up with a faster solution? 

I've been scouring the internet for 2 weeks with this same issue. Nested Apply To Each even on less than 100 items take FOREVER. I've gotten it down to about 2 hours through a series of Filter Array and variables but it's not even worth it at this point. 

Anonymous
Not applicable

@Tqphan87  Did you try using Concurrency Control on for each loop and changing Degree of Parallelism to maximum (50), it will decrease the time exponentially for you but again if you have large set of entries (10K or so) then its not a good way as it will take much more time.

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.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,775)