This is another one of those posts that originated from a question on the Power Platform Community. The question was whether we could implement a Cartesian or Cross Join in Power Apps.
Before we get into the details of how to implement this functionality, let's first understand what it is. A CROSS JOIN (Wikipedia) returns the Cartesian product of rows from tables in the join. A Cartesian product (Wikipedia of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B.
In simple words, if there are two tables, one with values [A, B, C] and another with values [1, 2], their Cartesian Join will be [(A, 1), (A, 2), (B, 1), (B, 2), (C, 1), (C, 2)].
To simulate this, I created a couple of collections representing the two tables from our example above.
ClearCollect(C1, {name1: "A"}, {name1: "B"}, {name1: "C"})ClearCollect(C2, {name2: "1"}, {name2: "2"})The basic concept for this implementation is to loop through each element of the 2nd collection while looping through each element of the 1st collection and collecting their values in the process. The following expression implements this concept.
ForAll(C1,ForAll(C2,Collect(C3,{column1:name1,column2:name2})))This is how the result looks like:
With a simple nested ForAll, we are able to create a Cartesian Join in Power Apps!
Update: After I posted this, my friend Hiro reached out with a different/cleaner method to accomplish this. Here is his expression.
ClearCollect(C3,Ungroup(AddColumns(C1,"newcolumn", C2),"newcolumn"))For anyone who is interested in implementing other Joins, here is a link to his blog post (in Japanese).
Have fun! Get addicted!