Join Two Datasets in Flow

[Anonymous]

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 Number	Customer Number	Amount
30	123	$50
31	234	$51
32	123	$52

Excel Online:

Customer Number	Customer Email
123	123@123.com
234	234@234.com
345	345@345.com
456	456@456.com

What I want to get to:

Invoice Number	Customer Number	Amount	Customer Email
30	123	$50	123@123.com
31	234	$51	234@234.com
32	123	$52	123@123.com

Thanks!

[yashag2255 · Accepted solution]

Hey @Anonymous 

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

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

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

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)

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]

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]

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? 

[Tqphan87]

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]

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