cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlbertPietens
Frequent Visitor

Combining Tables / Array

Hello,

 

I have two datatables with the columns "ID" and "Color". I want an Array variable that only has the values of the first datatable for which the "ID" is in both. For example, I have these two datatables.

 

AlbertPietens_0-1630591641475.png

As you can see the ID's are the same in both datatables, only ID 3 is missing. But the Color value is only the same at ID 4. The result should be this:

AlbertPietens_1-1630591789875.png

 

My current Flow is this, but of course I only get the rows that are an exact match and not all the rows with matching ID's.

 

AlbertPietens_2-1630591910650.png

 

This is the result of the Flow:

AlbertPietens_3-1630592060583.png

 

How can I get the right result?

 

Thanks,

 

Albert

2 ACCEPTED SOLUTIONS

Accepted Solutions
happyume
Solution Sage
Solution Sage

1 - Get All rows from Excel Table 1

2 - Use Select Action and pick ID.

3 - Repeat same for Excel Table 2

happyume_0-1630596000710.png

4 - Now you have two arrays which contain ID, now create a Compose action with expression doing intersection of two arrays:

intersection(body('Select1'),body('Select2'))

5 -  This gives you common IDs between the two arrays

6 - Now use this array of IDs to retrieve color names from Excel Table 1

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up

View solution in original post

I have duplicated your data-structure (with slight change in data) and using steps in my previous solution received output as below:

happyume_0-1630652383116.png

Sample Input data for Table1:

happyume_3-1630652675016.png

Sample Input data for Table2:

happyume_4-1630652711501.png

After step5 (intersection expression):

happyume_1-1630652447498.png

 

 

6.1 - Initialize an Array variable to store final result

6.2 - Loop through output of intersection

6.3 - Filter the "value" array received at very start of the Flow from Table1 (see screenshot below)
happyume_2-1630652546590.png

6.4 - Append output to array variable created in 6.1 with value: first(body('Filter_array'))

 

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up

View solution in original post

4 REPLIES 4
happyume
Solution Sage
Solution Sage

1 - Get All rows from Excel Table 1

2 - Use Select Action and pick ID.

3 - Repeat same for Excel Table 2

happyume_0-1630596000710.png

4 - Now you have two arrays which contain ID, now create a Compose action with expression doing intersection of two arrays:

intersection(body('Select1'),body('Select2'))

5 -  This gives you common IDs between the two arrays

6 - Now use this array of IDs to retrieve color names from Excel Table 1

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up

View solution in original post

Thank you, steps 1 to 5 are successful, but I can't get step 6 done. Could you please explain how I retrieve the color names from Table 1?

I have duplicated your data-structure (with slight change in data) and using steps in my previous solution received output as below:

happyume_0-1630652383116.png

Sample Input data for Table1:

happyume_3-1630652675016.png

Sample Input data for Table2:

happyume_4-1630652711501.png

After step5 (intersection expression):

happyume_1-1630652447498.png

 

 

6.1 - Initialize an Array variable to store final result

6.2 - Loop through output of intersection

6.3 - Filter the "value" array received at very start of the Flow from Table1 (see screenshot below)
happyume_2-1630652546590.png

6.4 - Append output to array variable created in 6.1 with value: first(body('Filter_array'))

 

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up

View solution in original post

Thank you! It works 🙌

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Top Solution Authors
Users online (1,378)