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
yashag2255
Dual Super User II
Dual Super User II

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
yashag2255
Dual Super User II
Dual Super User II

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

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.

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

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. 

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,013)