Is there a way to combine 2 tables and perform a FULL OUTER JOIN?

[Anonymous]

HI.

May I ask one more question?

I'm Korean
Understand that English is awkward.

Thanks for every help!

UPC dataform # 1 data and # 2 data were combined to give the result of # 3. However, I want to make comparison data with all the combined UPCs like # 4. Is there a concise syntax like # 3?

I made # 4 with the knowledge I know. Please refer.

See Group Button and Data Format Syntax.

If there is a better way, can you ask me for an idea?

Have Nice Day!!!!

[v-bofeng-msft · Accepted solution]

Hi @Anonymous :

Firstly,currently powerapps does not support full join.

Secondly, similar functions can be achieved by a combination of functions.I've made a test for your reference:

My data source(for some reason, I cannot download the attachment you provided):

collection1/collection2

ClearCollect(collection1,{UPC:"A",LOCbyQty:53},{UPC:"B",LOCbyQty:4},{UPC:"C",LOCbyQty:4},{UPC:"D",LOCbyQty:2});
ClearCollect(collection2,{UPC:"A",PackbyQty:51},{UPC:"B",PackbyQty:4},{UPC:"C",PackbyQty:5},{UPC:"E",PackbyQty:5})

1\add a datatable control and set it's items property to:

RenameColumns( /*Renamed "Result" column to "UPC"*/
    AddColumns(  /*Add two fields LOCbyQty and PackbyQty, use lookup to find the corresponding values in the two tables*/
        Distinct(/*Get the UPC values of the two tables, and get the single column list of UPC after deduplicating the results*/
            Split(
                Concatenate(
                    Concat(
                        collection1,
                        UPC,
                        "#"
                    ),
                    "#",
                    Concat(
                        collection2,
                        UPC,
                        "#"
                    )
                ),
                "#"
            ),
            Result
        ),
        "LOCbyQty",
        LookUp(
            collection1,
            UPC = Result
        ).LOCbyQty,
        "PackbyQty",
        LookUp(
            collection2,
            UPC = Result
        ).PackbyQty
    ),
    "Result",
    "UPC"
)

Best Regards,

Bof

[Anonymous]

hi @v-bofeng-msft 

Thanks for the answer. That solution is well used. Thank you every time.

The solution provided by the respondent can get a quick answer.!!

Could you please consider the method I used?

I've used a function like this in an unopened file I sent earlier.

Button on Select : 

Collect(Collection3,AddColumns(GroupBy(collection1,"UPC","ByUPC"),"LOCbyQty",Sum(ByUPC,LOCbyQty)),AddColumns(GroupBy(collection2,"UPC","Byupc2"),"PackbyQty",Sum(Byupc2,PackbyQty)))

Dataform Item : 

AddColumns(GroupBy(Collection3,"UPC","Fullouter"),"LocbyQty",Sum(Fullouter,LOCbyQty),"PackbyQty",Sum(Fullouter,PackbyQty))

Will it be a problem part?

Best Regards,

[v-bofeng-msft]

Hi @Anonymous :

Your method is good without any problems.

Could you tell me:

• Is your app working now?
• What is your problem now?

Best Regards,

Bof

[Anonymous]

hi @v-bofeng-msft 

The app works fine and there are no problems.

With the solution provided by the respondents, we were able to move forward with one step.

Thank you !! Have a nice day ~!