cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisgchambers
Regular Visitor

Merge two arrays into all possible combinations

The Setup
I have a Sharepoint list named "Translation Dictionary" that allows users to review and edit translated strings used for localization in an internal application.  It has lookups for two other lists (Locales, Placeholders) that are populated by scraping the javascript locale definitions from the app's git repository. The user then provides or updates the Translation Text for each Locale and Placeholder.

The Problem
As application development continues, new placeholders are added to the locale definitions. I want the flow to create a list of Placeholder and Locale combinations that do not already exist in the dictionary so I can iterate through them, add automated literal translations to the dictionary as a starting point, and notify users to review and update the literal translations to be context-specific and fluent. 

I have a flow that works for this, but it's SLOW (ok, it takes two minutes with 50 threads, but it really seems like there should be a better way). The only part of the flow taking an appreciable amount of time is generating the initial list of combinations to filter. I've done this with nested Apply to Each loops that append an object with the pair of IDs to an array variable for each combination. 

It really seems like there ought to be a better way to come up with the initial array of items, but I haven't found it yet. I'm hoping fresh eyes will come up with something more elegant. The Translation Dictionary list also has a surrogate key in the Title column that's composed from the concatenated placeholder and locale names if that's helpful. Again, the problem isn't filtering against the existing pairs, it's creating the list to filter in the first place.

chrisgchambers_0-1638389514044.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
mahoneypat
Super User
Super User

Here is one way to cross join two arrays.  A Select can often be used to take one ATE out of a Flow and speed things up.  Note, however, in this one, I set the Concurrency of the ATE to 1, as the current output is unioned with the vResult array variable in each iteration.

 

An array variable called vResult is created, along with a string variable called vTemp.  The ATE is over the items in Array1.  In each iteration:

- the vTemp variable is set to the current value of Array1

- a Select is performed on Array2 with two columns (one to get the vTemp variable and the other to get the current Array2 item)

- a Compose is used to union the Select array with the vResult array

- the vResult array is Set to the Compose output

 

The vResult array can then be used downstream.

 

mahoneypat_0-1638406432898.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Super User
Super User

Here is one way to cross join two arrays.  A Select can often be used to take one ATE out of a Flow and speed things up.  Note, however, in this one, I set the Concurrency of the ATE to 1, as the current output is unioned with the vResult array variable in each iteration.

 

An array variable called vResult is created, along with a string variable called vTemp.  The ATE is over the items in Array1.  In each iteration:

- the vTemp variable is set to the current value of Array1

- a Select is performed on Array2 with two columns (one to get the vTemp variable and the other to get the current Array2 item)

- a Compose is used to union the Select array with the vResult array

- the vResult array is Set to the Compose output

 

The vResult array can then be used downstream.

 

mahoneypat_0-1638406432898.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

chrisgchambers
Regular Visitor

Thank you Pat! This is just the sort of solution I was hoping to find. This one goes right into the toolbox!

It seems to me it would make sense to use the ATE on the array you expect to have fewer elements. In my current case, there are about 256 placeholders but only a handful of locales and I expect the placeholder count to continue increasing much faster than the locales. Would I be correct to assume I should apply the ATE to the array of locales and the Select to the placeholders?

Thanks again for the assist. I've mocked this approach up in a flow to make sure I understand how it works. I'm going to try it in the real deal now and see how it performs.

chrisgchambers
Regular Visitor

High five Pat! What an improvement! From 2 minutes with max concurrency to 3 seconds single-threaded.

chrisgchambers_0-1638414111109.png

 

The only thing I tweaked is I dispensed with the vTemp variable and just used items('for_each_locale') in my select.

chrisgchambers_1-1638414258625.png

 

 

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,395)