cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IsaacM
Level: Powered On

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

Re: Join Two Datasets in Flow

Hey @IsaacM 

 

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

3 REPLIES 3
Dual Super User
Dual Super User

Re: Join Two Datasets in Flow

Hey @IsaacM 

 

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

Highlighted
IsaacM
Level: Powered On

Re: Join Two Datasets in Flow

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.

gemma
Level: Powered On

Re: Join Two Datasets in Flow

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? 

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,055)