Two Collections and join n:m to one (new) collection

[VionMichael]

Hello all,

I have the following challenge. I need to make two collections into one. But I need to keep the behavior of a n:m relationship.

Here is a mini-example creates two new collections group_m and group_n:
ButtonA:

ClearCollect(
    group_m;
    {
        gm_nr: 1;
        gm_atribut: "Hello"
    };
    {
        gm_nr: 2;
        gm_atribut: "Hallo"
    };
    {
        gm_nr: 2;
        gm_atribut: "Gutentag"
    };
    {
        gm_nr: 3;
        gm_atribut: "Moin"
    }
);;
ClearCollect(
    group_n;
    {
        gn_nr: 1;
        gn_atribut: "Foo"
    };
    {
        gn_nr: 1;
        gn_atribut: "Bar"
    };
    {
        gn_nr: 2;
        gn_atribut: "Christian"
    }
);;

ButtonB should merge them into a new collection like the following example:

Collect(
    group_m_n;
    {
        gm_nr: 1;
        gm_atribut: "Hello";
        gn_nr: 1;
        gn_atribut: "Foo"
    };
     {
        gm_nr: 1;
        gm_atribut: "Hello";
        gn_nr: 1;
        gn_atribut: "Bar"
    };
    {
        gm_nr: 2;
        gm_atribut: "Hallo";
        gn_nr: 2;
        gn_atribut: "Christian"
    };
    {
        gm_nr: 2;
        gm_atribut: "Gutentag";
        gn_nr: 2;
        gn_atribut: "Christian"
    };
    {// Blank or without this Dataset: gm_nr: 3
        gm_nr: 3;
        gm_atribut: "Moin";
        gn_nr: Blank();
        gn_atribut: Blank()
    }
);;

 Which is the easiest way?

[timl]

@VionMichael 

It looks like you're trying to do a full cartesian join. The approach I would take would be to nest a call to ForAll inside another call to ForAll, like @PowerAddict describes here.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Cross-join-or-Cartesian-Join-of-collections/...

[VionMichael]

Thanks for your answer.

I need (unfortunately) not every combination, as requested in the question from the link.
But only the respective combinations if the gm_nr (Collection: group_m) and the gn_nr (Collection: group_n) are identical.

[timl · Accepted solution]

Hi @VionMichael 

You can do this by filtering the nested ForAll to return only those records where gm_nr=gn_nr. Provided it's acceptable to completely skip gm_nr, the formula would look like this:

ClearCollect(
    group_m_n;
    Ungroup(
        ForAll(
            group_m;
            ForAll(
                Filter(
                    group_n;
                    gm_nr = gn_nr
                );
                {
                    gm_nr: gm_nr;
                    gm_atribut: gm_atribut;
                    gn_nr: gn_nr;
                    gn_atribut: gn_atribut
                }
            )
        );
        "Value"
    )
)

[VionMichael]

Hello,
sorry for the late response, but that looks very good.
Thanks for that.

Unfortunately, the zero value is not included, but that's a 'nice to have' in my case.