O I love problems like this!
Big idea here is that every nth record lines up among the 3 tables--knowing this is going to help us.
There is no way to unite them out of the box. You'll need to bring them together with a repeated action. And when I think of repeated actions, I think of ForAll().
Below is the formula I'm suggesting:
Clear(joint_collection);
ForAll(any_table,
Collect(joint_collection,
{
column1: Last(FirstN(Split(string1,","),CountRows(joint_collection)+1)).Result,
column2: Last(FirstN(Split(string2,","),CountRows(joint_collection)+1)).Result,
column3: Last(FirstN(Split(string3,","),CountRows(joint_collection)+1)).Result...
}
)
)
Let's take this apart.
- string1, string2, string3 are the strings that are returned from your flow (offers.__)
- joint_collection is the collection that you want to result by bringing together the respective field from each string
- any_table could be any one of the split strings since it's just used for determining how many times to loop/repeat the actions, ex: Split(offers.___,",")
Clear(joint_collection);
ForAll(any_table,
Collect(joint_collection,
{
column1: Last(FirstN(Split(string1,","),CountRows(joint_collection)+1)).Result,
column2: Last(FirstN(Split(string2,","),CountRows(joint_collection)+1)).Result,
column3: Last(FirstN(Split(string3,","),CountRows(joint_collection)+1)).Result...
}
)
)
- Red: you already know this part. It's splitting up each string returned from flow.
- Blue: each time these actions run, one more record is added to the join_collection.
So this is getting the first n number of records from the split up strings as a table (FirstN). Last() picks the last of that subset. The Last(FirstN()) pattern gets you the nth record from a table.
It gets the first record from all split strings and puts them into a row (empty collection+1).
Then it gets a second record from all split strings and puts them into a row (1+1).
- Green: Result is the default name of the column that results when you split up strings. You can wrap the entire thing around with Value if you need it to be a number or Text() if you need to format dates and times, etc.
